To Reorg or Not to Reorg

 After thinking about a “To Be Or Not to Be” type of title in last month’s entry on database masking, it was an easy title choice for this entry. I don’t think that a discussion of whether or not to perform Db2 table reorganizations should be as controversial as the topic of masking, but I've been surprised before…

The IBM Expert Labs team recently received a question from client asking if it is still worth the time to reorganize tables in light of today’s faster disk technologies. The main benefit of running the RGZPFM command is to remove/compress deleted rows from a Db2 table. And given that disk technologies such as IBM Flash Storage can retrieve database rows from disk significantly faster than spinning drives, are my applications really going to notice deleted rows being paged into memory? 

The short answer is: yes, application and system performance can be negatively impacted. While today’s disk technology is faster, disk operations on deleted rows are wasteful and those deleted rows unnecessarily increase the memory working set size for an application.

Deleted rows can also waste CPU resources when Db2 performs a scan operation on a table with deleted rows. Assume that the customers table being referenced in the following query contains 1 million rows and there are 300,000 rows in the table that are deleted.

SELECT * FROM customers WHERE company_name LIKE '%INC.'

A Table Scan is the only access method that Db2 can use in the runtime implementation of this query because of the wild-card search criteria that looks for company names ending with ‘INC.’ (This statement is mostly true, but the IBM OmniFind Text Search Server is a topic for another day). The Table Scan method has to process every row in the table whether it’s deleted or not. Obviously, no deleted rows will be returned in the query result set, but this query will waste CPU resources on 300,000 rows checking if the rows are active or deleted. This unneeded processing is definitely something that can impact the scalability of your applications and systems.

 

Of course, it goes without saying that deleted rows affect disk storage requirements. In addition, deleted rows also count against the maximum storage size and row count for a Db2 table.  And yes, there are clients that have gotten close to the 1.7 TB size limit and the 4.2 billion rows limit for a table. 

Another reason to perform table reorgs is that IBM has enhanced the RGZPFM command through the years to minimize the time that the table is unavailable to your applications. Parallel processing, reorganize while active, and the ability to suspend a reorg request are all recent additions that make it easier/faster to perform reorgs on tables. Here are some useful links that provide more details regarding these different types of reorg options:

• RGZPFM Options Comparison
• RGZPFM FAQ

Assuming that you’re now sold on the benefits of reorgs, the next logical question is: When should a table be reorganized? A good rule of thumb is to wait until the table has a deleted row percentage of around 20%. This site outlines a method to determine the deleted row percentage for a table. It's also important to remember that the Reuse Deleted Rows feature can be used to reduce the number of times that a table has to be reorganized.

 

Happy RGZPFM’ing & Happy Thanksgiving to my US readers!

Masking your Db2 Databases to Protect Sensitive Data

I thought about titling this entry “To Mask or Not to Mask” to generate more visitors to the blog, but then I quickly thought better of it😊  While I did pickup some medical knowledge during my 5+ years of working on Watson Health solutions, I definitely do not have the credentials to discuss medical masking.  However, with over 25 years of experience working on Db2 for i, I am qualified to talk about protecting sensitive data in your databases with the Db2 masking support.

Before I highlight the Db2 masking support, I want to remind everyone that a solid object-based security model is the first step in securing all of the valuable business data that you store on IBM i. Masking is meant to be used as a granular security control after your Db2 for i objects have been secured with IBM i’s robust object-level security.

The column mask support was first delivered in the IBM i 7.2 release as part of the new security functionality known as Row and Column Access Control (RCAC). RCAC support is made available through the optional, no charge feature called IBM Advanced Data Security for i, also known as option 47 of the IBM i operating system.

A user authorized to a Db2 table object can view all of the column values stored in that table. A column mask enables you to provide another layer of protection around columns storing sensitive values like credit card numbers and tax identification numbers. This extra layer allows you to limit access to a column with sensitive data values to just a subset of the users that have object-level access.

For the moment, assume that you’re a retailer that stores credit card numbers in the orders table. The following mask definition only allows the card number to be visible to users that belong to the customer support group profile.  All other users (security administrator, programmers, etc.) are only allowed to view the last 4 digits of the credit card numbers stored in the cardnum column. 

CREATE MASK mask_cardnum ON orders FOR COLUMN cardnum
RETURN
   CASE WHEN VERIFY_GROUP_FOR_USER(SESSION_USER , 'SUPPORT') = 1
    THEN cardnum
    ELSE OVERLAY(cardnum PLACING '*************' FROM 1 FOR 12)
   END ENABLE 

Even though a column mask is defined with SQL, the masking is enforced on all SQL and non-SQL interfaces. Consult the Row and Column Access Control Support in IBM Db2 for i Redpaper for additional details. 

While a column mask is enforced on all interfaces that directly access the column, there is a security exposure that arises when a column is compared to a host variable or literal value on a query request.  A security exposure exists because the Db2 for i performance tools (e.g. Plan Cache) capture the value of the host variable or literal from the query execution since the actual value that is passed in can affect the query optimizer’s choice of data access methods.  

To highlight this exposure, let’s assume that this SQL statement is embedded within one of your programs.

   SELECT ordID INTO :hv1 FROM orders WHERE cardnum = :hv2

A developer that has access to the Db2 Performance tools on your system, uses that tooling to launch Visual Explain to analyze the performance of this Select statement. Notice below in the captured Visual Explain output that the both the SQL statement text and the host variable values are shown. 

Although the highlighted host variable value is not guaranteed to be stored in the orders table, there’s a high likelihood that it is one of the stored sensitive card numbers since the application program is searching for that value. 

To close this security exposure, IBM provides the SET_COLUMN_ATTRIBUTE service in the SYSPROC library. This service allows you to register your database columns that store sensitive data.  Once a column has been registered, Db2 for i will start masking the host variable or literal values compared to it during its collection of performance data. 

In our example, the following store procedure call is made to secure the cardnum column:  

CALL sysproc.set_column_attribute

                       ('DBLIB', 'ORDERS', 'CARDNUM', 'SECURE YES')

Now that this column has been registered, the following image shows Visual Explain displaying a masked value of *SECURE instead of a credit card number. 

The secure column attribute setting applies to data collected by the Plan Cache, Plan Cache Snapshot and the Database Performance Monitor tooling.  A value of *SECURE will be displayed for all users except the QSECOFR user.  

The SET_COLUMN_ATTRIBUTE should be used to prevent sensitive data leaks independent of any plans that you have for employing column mask definitions in your database.

Hopefully, you now see how Db2 masking functionality can add an extra layer of security to your database object-level security implementation.  Feel free to reach out if your company is need of assistance of securing its most important asset – data.

Give Web Service Calls a Rest with Newest Db2 Update

 One might imply from the title that I’m advocating that developers stop using SQL to invoke web services from their IBM i applications. That is not my intent at all! In fact, the Db2 enhancements associated with the newest IBM i Technology Refresh for the IBM i 7.3 and 7.4 releases make it even more compelling to use SQL to invoke web services from IBM i applications.

The reason that SQL invocation of services is more compelling with the latest updates is that Db2 for i now supports the following set of new HTTP functions in QSYS2. These HTTP functions allow SQL to use RESTful services without the overhead of creating a Java Virtual Machine (JVM) like the existing HTTP functions found in SYSTOOLS.

HTTP_GET	HTTP_GET_VERBOSE
HTTP_POST	HTTP_POST_VERBOSE
HTTP_PUT	HTTP_PUT_VERBOSE
HTTP_DELETE	HTTP_DELETE_VERBOSE

The elimination of the JVM requirement means that web service invocations will run faster and more efficiently on your IBM i server. IBM i applications can scale to support more users and more transactions with these new HTTP functions requiring less usage of CPU and heap temporary storage. More details from my IBM colleagues on these HTTP functions can be found here.

The latest Database Group PTFs also include a new Db2 for i service, ACTIVE_QUERY_INFO, that can be helpful in narrowing down your analysis and investigation of query performance issues.

While not delivered with the Database Group PTF, the newest IBM i Access Client Solutions (ACS) version also includes some nice add-ons for Db2 and SQL. These ACS updates include Run SQL Scripts supporting tighter transaction control as well as improved error feedback.

To demonstrate the improved Run SQL Scripts error feedback, let’s use a simple query that divides two integer columns: SELECT Col1/Col2 FROM Tab1. The following figure shows the output from the latest version of ACS Run SQL Scripts. In this example, the second row in table Tab1 has a Null value for Col2 and the fourth row in table Tab1 has a value of 0 for Col2. Those Col2 values explain why the result set returned by this simple query does not contain a numeric division result in the second and fourth rows. 

The “-“ value indicates a Null value was returned for the division result which makes sense since the divisor value in the second row was Null.  The “++++++++++++” value in the last row indicates an error occurred due to the divisor value being 0.  Prior to this latest enhancement, Run SQL Scripts would have returned the same “-“ value for both the second and fourth rows in the result set making it difficult for the user to detect that a divide by zero error had occurred during the execution of their query.  Disclaimer – this improved error feedback was actually part of the prior ACS update, but I forgot to highlight this enhancement in my coverage of the prior Db2 for i update.

These great enhancements are a good reminder about the importance of keeping both your Database Group PTF level and ACS versions current. 

Speed Dating Your Legacy Dates

Given that I’ve been happily married for over 30 years, I’ll have to admit that I have no experience with speed dating. However, I do have lots of experience seeing the many ways in which clients store date values in legacy formats in their Db2 for i databases.  These legacy date formats range from storing the entire date value in a single character or decimal column to storing the components of a date value (Year, Month, Day, Century) in separate columns.  This blog topic focuses on the intersection of performance (i.e., speed) and legacy dates because our team has also seen many customer SQL requests that consume a fair amount of system resources while transforming these legacy date values into SQL date values.

IBM certainly contributed to this use of legacy date formats since Db2 for i has not always supported a Date data type. The Date data type was not available to use until the V2R1.1 release of OS/400. While our Expert Labs team certainly works with many clients now using the Date data type, there are a significant number of clients still using Db2 for i tables that were designed and created prior to that release thanks to the strong backward compatibility of our great platform.

The large number of legacy date transformations on SQL requests are not a surprise due to the continued rise in SQL adoption by IBM i developers and SQL’s rich support for date processing. This support includes many SQL functions that make it easy to convert numeric and character date values into real SQL date values.

Here’s a sampling of some of the legacy date transformations that have shown up on IBM Expert Labs client engagements. Other clients have created their own user-defined functions(UDFs) to perform the legacy date conversions. 

   ==> TIMESTAMP_FORMAT(CHAR(DateDec8) , 'YYYYMMDD')
   ==> DATE((DTYEAR+2000) || '-' || DTMNTH || '-' || DTDAY)
   ==> ((YRVAL*10000) + (MTHVAL*100) + (DAYVAL))

While the built-in functions and UDFs make it easy to transform the legacy date formats, these transformations are CPU intensive.  If these transformations are performed on thousands of rows in your table, then the CPU costs start to add up. Especially, when you consider that it’s the same legacy date values being converted over and over again. And that’s exactly where speed dating of your legacy date values can help.

Speed dating of legacy date values involves pre-converting your legacy date values and storing them in a Date dimension or lookup table. The same SQL built-in and user-defined functions can be used to populate the table.  Once this lookup table has been populated, your SQL queries can simply join to the table whenever you want to use the equivalent SQL date value associated with your legacy date value.  Joining to a dimension table is a low-cost alternative to converting your existing Db2 tables to use the Date data type.

The following graphic visually depicts the join to a Dates lookup table from a legacy Orders table which has date values stored in a legacy character column.

The ORDERDATE column is storing a date value in an 8-byte character field.  The Dates lookup table has that legacy date value stored in the DC_MDYY_CHAR column and the equivalent SQL date value in the DC_DATE column. When an SQL query wants to display or process the ORDERDATE column as an SQL date value, it can just join to the Dates dimension table on the ORDERDATE and DC_MDYY_CHAR columns and then reference the DC_DATE column.  The SQL date value is accessible with a simple lookup instead of a CPU intensive transformation.  Assuming that the proper indexes are covering the join columns, a join can be processed by Db2 very efficiently and quickly. Even if you have 50 years of dates stored in the lookup table, the table is only going to have just over 18,000 rows – a small table when it comes to join performance.

Notice that in this example that the Dates dimension table also is being used to store contextual information such as Day of Week number and Day Name.  These contextual date values were also pre-calculated so they are also ready to be used by your SQL request without any dynamic calculation.  You could easily add other contextual date values such as business fiscal year or quarter to the lookup table. This Db2 Web Query Redbook has more details on creating and using a Date dimension table. Even better, Db2 Web Query also ships with a utility to create your own dimension table.

So how much performance savings can be realized with speed dating? As with any performance question the answer is “It Depends”.  One client recently made the switch to using a Date lookup table because they had an SQL query which performed many legacy date transformations using this pattern: DATE((DTYEAR+2000) ||'-'||DTMNTH||'-'||DTDAY). Obviously, your mileage may vary depending on the query complexity and the number of rows being processed.  

Feel free to reach out if you need assistance applying this speed dating technique to the legacy dates stored in your Db2 for i databases.

Tale of the Tape - SYSDUMMY1 vs Tableless SQL Performance

 I thought about titling this entry “Tableless SQL for Dummies” to play off the old Dummies book series and the focus of this entry: the Db2 dummy table, SYSDUMMY1.  However, I thought that might be a little too abrasive for some readers.  Plus, that catchy title doesn’t reflect that SQL performance can be improved by simply replacing SYSDUMMY1 queries with a tableless SQL request. This Tale of the Tape comparison will help you better understand this performance difference.  

You may or may not know that Db2 provides a dummy table, SYSDUMMY1, in the SYSIBM schema.  The SYSDUMMY1 table contains a single row and column which as you can see from the following example makes it really easy for a developer to invoke an SQL function or retrieve the value of an SQL special register. 

SELECT UPPER(:hv), CURRENT TIMESTAMP
  INTO :hv1, :hv2
FROM sysibm.sysdummy1

Back when I was working with software vendors, the SYSDUMMY1 table was also quite handy when porting Oracle applications to the IBM i platform because some of those applications relied on using the Oracle dummy table - DUAL.  Database vendors often included a dummy table because all SQL DML (Data Manipulation Language) statements required a table reference. However, that’s no longer case because the SQL standard has been enhanced to support tableless queries.

The VALUES statement was the new addition to the SQL standard which enables you to run tableless queries.  Taking the previous example, you can see how it is implemented with the tableless support:

VALUES (UPPER(:hv), CURRENT TIMESTAMP)
  INTO :hv1, :hv2

These two examples are almost identical except for the FROM clause. Not only does the VALUES statement save you from having to type the FROM clause, it also will return the results faster because there is no FROM clause! Let’s dig into why the FROM clause causes a performance difference. 

 

Any time there is a table referenced on the FROM clause on an SQL request, the Db2 for i engine has to performance serialization actions to support concurrent access of the specified table and its rows. Even though the SYSDUMMY1 table is small and contains a single row, the required serialization steps makes it slower to process than the equivalent tableless query. 

 

To measure this performance difference, I created separate SQL procedures with each one of them running these two equivalent SQL statements 50,000 times. 

SELECT CURRENT TIMESTAMP INTO :hv1 FROM sysibm.sysdummy1

VALUES CURRENT TIMESTAMP INTO :hv1

The average run time for the SELECT statement procedure call was 1,430 milliseconds while the average run time for the VALUES statement call was 340 milliseconds – a 4x times difference in performance!  While you might be thinking that the average execution time of each method is very small when you consider each statement was executed fifty thousand times, remember the old proverb that “Little by little, a little becomes a lot”.

You might find that this type of SYSDUMMY1 request is being run over and over again on your system.  In fact, I recently reviewed a customer’s SQL Plan Cache Snapshot data and they had a SYSDUMMY1 query that has been run over 360 million times on their system.  So even if the tableless query approach was just 100 microseconds faster, the multiplicative impact of that small improvement will add up to make a difference on their system. 

The performance advantages that tableless SQL requests hold over SYSDUMMY1 queries should make it clear that tableless queries are the clear winner in this comparison.

Performance Toolbox for IBM i Services

 In March, I wrote about the importance of thinking about performance as part of using IBM i Services with SQL.  One reader suggested a follow-up article highlighting which tools would be helpful for analyzing the performance of an IBM i Service, so that’s what I aim to do here. 

Since I’m a Db2 guy, we’ll start with the database performance tools first which means the IBM i Access Client Solutions (ACS) toolset. Visual Explain is THEE tool for understanding the implementation details of an SQL request. Visual Explain can be used from many different perspectives – interactively from Run SQL Scripts as you run your SQL or post-SQL execution from the Plan Cache or Plan Cache Snapshots.

 Visual Explain graphically lays out the blueprint of the query optimizer’s implementation for a query.  This graphical representation shows all of the Db2 objects used in the query implementation including tables, indexes (some might still refer to these as access paths), and user-defined functions.  The number of objects represented in a query plan can give you an indication in how much work you’re asking the system to do.  For example, joining together ten tables is usually going to require consume more system resources than joining together two tables.

Below I’ve included a portion of Visual Explain output from a customer’s report that was joining to the Group_Profile_Entries catalog view/service.   At face value, this view seems relatively simple in that it returns a row for each user profile that belongs to the group profile.  As you can see from the Visual Explain representation, the processing required to return data from this view is anything but simple. This view ends up joining together the result data from 5 different IBM i Service calls in order to return the user profile entries that belong to a specific group.  Instead of simply reading data from underlying table(s) like many of the Db2 for i catalog view, the Group_Profile_Entries view has to dynamically generate the data using user-defined table function calls (ie, external program calls).  As you might guess, program calls are more expensive than reading a row from a table. 

Based on this Visual Explain output, you should be getting a sense that SQL referencing the Group_Profile_Entries may consume a fair amount of system resources.  This perspective may influence how often you run the SQL request and when you run the SQL request.  In addition, you’ll want to look at the query to see if more filtering can be specified with the Group_Profile_Entries reference to limit the amount of data that must be dynamically generated. 

System Performance tools can also be used to analyze the performance signature of the IBM i Services that you’re trying to use. These tools are great at providing insights into the system resource utilization that are used by a service.  Performance tools that you might want to use to analyze the system impact of IBM i services queries are Collection Services and IBM iDoctor.

Here’s a relatively simple query using the Output_Queue_Entries service to count the number of output queue entries for each job on the system. 

SELECT job_name AS job, COUNT(*) AS sum_splf
  FROM qsys2.output_queue_entries
  GROUP BY job_name ORDER BY COUNT(*) DESC

 

While this query is simple, the following IBM iDoctor output shows that this service resulted in the job waiting on a large number of page faults while it was off processing each output queue entry on the system. This page fault wait time signature is probably an indicator that you should consider tightening the focus of your request by limiting the number of jobs that have output queue entries checked. 

If you’re looking for help becoming more proficient with these tools, IBM Expert Labs can help.  The Database Engineer (DBE) Enablement offering features hands-on training with Visual Explain and the full suite of ACS SQL performance tools.  For assistance getting started with the system performance tools, check out the IPAWs training or the IBM iDoctor workshop.

Simplify your SQL development by staying current

Since I returned to the IBM i world last Fall, I’ve spent time reviewing all of the Db2 for i and SQL enhancements that have been delivered during the 5+ years that I was off working on Watson.  The Rochester development team has definitely been busy cranking out some great functionality while I was away. It’s been nice to see improvements to the SQL standards that deliver real efficiency for developers.

One of those SQL standard additions that caught my eye is the LISTAGG aggregate function.  The documentation states that this function aggregates a set of string elements into one string by concatenating the strings.  I think a simpler real-world benefit description is that it makes it easy to combine values from multiple rows into a single row. For example, you’re asked to produce a report of the shipping companies that your business uses by region and the report format needs to be the following:

NORTH	FEDEX, GARZA SHIPPING, SPEEDEE DELIVERY
SOUTH	FEDEX, TYLER TRUCKING, UPS, USPS

 

This is easily done with the LISTAGG function with this simple, but powerful SQL statement:

SELECT region, 

       LISTAGG(DISTINCT carrier, ', ') WITHIN GROUP(ORDER BY carrier)
   FROM shipping_method GROUP BY region

I highlight the simplicity of this solution because you could do this in SQL before with recursive SQL syntax, but those types of SQL statements are longer and difficult to understand. In this article, I provide more details on LISTAGG and contrast it with the recursive SQL solutions.

I believe a feature like this is also a good reminder of the benefits of staying current on your IBM i release level and Database Group PTFs.  The more current your systems are, the more Db2 & SQL features there are available to simplify life for your developers.