Under The Radar Visual Explain Addition

 The under the radar phrase originates from World War II aviation practices where a plane would fly low to avoid being detected by the enemy’s radar system.  Over time, the phrase is also now used to describe things or people that can be overlooked. With the constant stream of enhancements to Db2 for i, it’s easy to miss some improvements that can make working with Db2 easier. 

The new Show Indexes Considered support in Visual Explain is an enhancement that falls into this category. This improvement didn’t make the cut when I highlighted Db2 enhancements back in April, but that’s only because there’s a limit to how much time and space are allocated for each blog post.

Show Indexes Considered is a significant usability enhancement to Visual Explain that should make it easier and faster to consume the performance information displayed by this tool which is part of IBM Access Client Solutions (ACS).  This new feature makes it easier to understand why your existing indexes were not used by the Db2 query optimizer. 

 

This feedback for your existing indexes has always been available in Visual Explain, but it was hard to find and difficult to understand as shown in the following figure.  On table access methods like the Table Probe method in this example, a list of the indexes considered by the optimizer is provided.  However, a cryptic numeric reason code was all that you had for feedback. 

Understanding these reason codes involved going to the Db2 for i documentation, accessing the Database Performance and Query Optimization book, finding the description of the QQQ3007 database monitor view, and then reviewing the reason code explanations documented under the Index_Names column in this view.  Just a walk in the park – NOT!

 

The new Visual Explain enhancement in ACS 1.1.9.8 makes this process a simple walk in the park. First, you just right-click on the table access method and select the Show Indexes Considered option as demonstrated in the following screen capture.

Once that option has been selected, a new window like the following will be displayed on your workstation.  You can see that the cryptic codes are still there, but they’re accompanied with a Description column to provide a detailed explanation on why an index was not used by the query optimizer.

These easily accessible explanations were quite useful when recently working with a customer who had contracted our IBM Technology Expert Labs team to assist with SQL performance tuning.  In their situation, the table had lots of great indexes for the query being run. However, none of them were usable because the indexes were not created with the same sort sequence that the application was using. The new Show Indexes Considered feature made it quick and easy to find the root cause of their performance problems.

Although this is a relatively small improvement, it should be easy to see how it can provide a boost in productivity. No changes are required on the server, you just need to upgrade your ACS version.  It should be noted that this support is currently not working when your query references a view instead of a table. This limitation will be addressed in the next version of ACS.

Need a Db2 Identity or Sequence?

Previously, I wrote about speeding up the performance of values generated by an identity column using the cache setting. One reader suggested that I follow up that entry with a comparison with the other key generator provided by Db2, a sequence object. So here we go…

The biggest differentiator between these two key generators is that a sequence is not associated with any table. A sequence is a standalone object whose generated values can be used by any table.  This also means that the next key value needs to be explicitly generated – in comparison, an identity column results in Db2 automatically generating the next key value whenever a new row gets inserted. The following code snippet shows this explicit key generation associated with a sequence in action.

CREATE SEQUENCE orderSeq START WITH 1 INCREMENT BY 1;

VALUES (NEXT VALUE FOR orderSeq) INTO :hvOrdID;

INSERT INTO ordMaster (ordId, ordDate, OrdCust, ordAmount)
   VALUES (:hvOrdID, CURRENT DATE, 'C1013', 1013.97);

INSERT INTO ordDetail (ordNum, ItemNum, ItemQty)
   VALUES (:hvOrdID, 'T0612', 67);

INSERT INTO ordDetail (ordNum, ItemNum, ItemQty)
   VALUES (:hvOrdID, 'J1103', 95);

 

The NEXT VALUE expression is used to explicitly generate a key value from the orderSeq sequence object and is stored in a local host variable. This expression could be placed on the VALUES clause of an INSERT statement and eliminate the host variable assignment. However, the code in this example demonstrates one of the advantages of a sequence object – the generated value can be easily shared across multiple tables. In this case, the generated sequence value is used for the ordID colum in the ordMaster table and the ordNum foreign key column in the ordDetail table which is used to link the order details to the associated order in the ordMaster table.

 

This ability to share the generated key value across multiple tables was key for a client that engaged our IBM Expert Labs team to modernize their database and application with SQL. Their initial database design used an identity column to generate the key values for their account number column. A review of their application design, however, showed that their account creation process required the new account number to be known before inserting an account into their SQL table. Thus, they switched from a generated identity column to a sequence object.

 

A sequence also provides the flexibility of being able to generate an alphanumeric key value. The requirement to explicitly request a key provides an opportunity to convert the generated numeric key value into a character string as shown in the following example.

 

SET :hv1 = 'N' CONCAT CAST( NEXT VALUE FOR someSeq AS CHAR(4) );

 On the performance front, my tests showed that generated identity columns have a performance advantage over sequence objects. With the default cache settings, identity columns generated values about 25% faster. When the cache setting for both solutions was increased to 200, the sequence object was only 15% slower. Given that a single key value generated by a sequence object can be used on multiple insert operations, it’s difficult to put too much weight on the differences in performance.

 

Hopefully, this comparison has given you some things to consider when choosing between these two key generators provided by Db2.  One option is not better than the other, your requirements will dictate which option you should choose.

Db2 for i 7.6 Highlights

 IBM i 7.6 has now been announced which means a new set of features and functions for your favorite relational database management system – Db2 for i. Similar to the Db2 for i 7.5 release, Db2 for i includes a wide assortment of functional and performance enhancements to help developers and database engineers, but no major additions. 

Probably the most notable feature in the 7.6 release is the enhanced data change table reference support. For several releases, Db2 for i has only supported the data table change reference for Insert statement. This support has been most widely used to simplify retrieving that value that Db2 generates for an identity column like orderID as demonstrated in the following example.

SELECT orderID FROM FINAL TABLE (
     INSERT INTO orders(orderDate,orderQty,orderItem) 
            VALUES('11/03/2024',50,'JM12'))

 

Starting with 7.6, developers can also use data change reference table support with Delete and Update statements. This new support enables developers to retrieve values from rows that are being updated and deleted.  In this example, orders older than 4 years old are being removed from the Orders table.  The OLD TABLE intermediate result table represents the table rows before the delete. This SQL request returns the range of order date values deleted by the statement.

SELECT MIN(orderDate), MAX(orderDate) INTO :firstDate, :lastDate 

  FROM OLD TABLE 
   (DELETE FROM orders WHERE orderDate < (CURRENT DATE – 4 YEARS) )

 

What’s even better is that you can improve the performance of Delete and Update statements using this syntax with parallel processing for the first time! Db2 for i has supported parallel processing for a very long time with the Db2 Symmetric Multiprocessing (SMP) feature, but delete and update operations could only utilize a single thread until this new support in the IBM i 7.6 release. Parallel processing is definitely a great way to speed up long-running Delete and Update statements.

 

On the performance front, the SQL Query Engine (SQE) includes a couple of new features to boost query performance in the newest release.  The first is a new method for using indexes with composite keys.  This new data access method should be helpful for queries with a complex set of search conditions featuring inequalities (>, >=, etc.).

 

SQE in the IBM i 7.6 release also includes enhancements to its Adaptive Query Processing (AQP) support. AQP is one of the self-learning query optimization methods used by SQE during the execution of longer running queries. If AQP detects that a query’s progress is too slow, then it will attempt to build a better plan based on its current runtime statistics. In prior releases, AQP was only applied to queries with joins – the support to also assisting with tuning queries performing grouping.

 

The SQL performance analysis tools supporting SQE were also enhanced. One improvement that may seem minor but should greatly improve the usability of the tools is consistent labeling in the graphical tools provided by the IBM i Access Client Solutions (ACS) SQL Performance Center. For example, some metrics were reported in seconds and others in milliseconds in the past. This inconsistency made it difficult to understand and compare the feedback returned by SQE and Db2 for i. SQE was also enhanced to collect additional metrics while a query is running. Now, you will be able to see if a query was slowed by waiting for a lock and if so, how much time was spent waiting for a lock.

 

At the end of 2024, I highlighted some significant advancements to the ACS SQL Performance Center. One of those enhancements was providing access to index advisor. With the latest ACS, the SQL Performance Center Index Advisor interface was enhanced to provide access to condensed index advice and filtering capabilities.  The following graphic shows the new filter capabilities that can be applied to enable more efficient usage of the index advisor output.

The security of your Db2 for i databases is also stronger in the IBM i 7.6 release which features several security advancements such as the integrated multi-factor authentication.

This wraps up my highlights of the latest Db2 for i release, make sure you make time to review the complete list of Db2 7.6 enhancements. I hope this summary has whet your appetite for the IBM i 7.6 release.

Db2 Dummy Table Replacement Simplified

I’ve written in the past about the performance advantages of using tableless queries instead of the Db2 dummy table – SYSDUMMY1. As our IBM Expert Labs team has helped clients convert their queries from using SYSDUMMY1 to tableless syntax, we’ve found that some conversions are easier than others.

Simple SYSDUMMY1 queries like this one are easy to change to use a VALUES statement and there’s very little risk of breaking the query. 

  SELECT CURRENT DATE FROM sysibm.sysdummy1

 

The conversions get more interesting when SYSDUMMY1 is referenced in a query along with “real” tables that store your business data. Here’s an example of that type of query:

SELECT 1 INTO :hv1 FROM sysibm.sysdummy1
     WHERE EXISTS (SELECT 1 FROM table1 

                    WHERE col1=:hv2 AND col2=:hv3)

 

While the following converted version of this query is not super complex, the number of required changes to eliminate the usage of SYSDUMMY1 does increase the risk of the query modifications accidentally causing the query to return different results.

WITH return1 (rtnval) AS (VALUES 1)
SELECT 1 INTO :hv1 FROM return1
  WHERE EXISTS (SELECT 1 FROM table1 

                  WHERE col1=:hv2 AND col2=:hv3)

 

Even though the risk of breaking the query is still low, I’ve recently started to recommend a simpler conversion method. This method involves creating an SQL view and replacing the SYSDUMMY1 reference with the name of your view.

 

First, let’s look at the definition of this view. Effectively this view uses the VALUES statement to return the same column value and column name as Db2’s SYSDUMMY1 table. The earlier conversion examples don’t reference the column value or column name, but your application may have SYSDUMMY1 queries that are dependent on the column value or name.

CREATE VIEW mylib.mydummy1 AS
  WITH returny (ibmreqd) AS (VALUES 'Y')
  SELECT ibmreqd FROM returny

 

Now that a view is in place providing a logical equivalent of SYSDUMMY1 – converting a query from SYSDUMMY1 to use tableless syntax is as simple as changing the FROM clause. Essentially, all that’s needed is the Find & Replace option in your code editor is all that’s needed as demonstrated in thew new

SELECT 1 INTO :hv1 FROM mylib.mydummy1 
  WHERE EXISTS (SELECT 1 FROM table1 

                  WHERE col1=:hv2 AND col2=:hv3)

 

I think the only downside of using this view-based approach is that one may overlook whether the SYSDUMMY1 reference is even needed. I’ve recently worked with clients that used SYSDUMMY1 in the following manner. 

SELECT col2 FROM table1 
  WHERE col1 = (SELECT someUDF(:hv1) FROM sysibm.sysdummy1)

 

Not sure why the programmers thought the Db2 dummy table needed to be used – maybe the user-defined function reference. However, a tableless query is also not needed in this case. The better correction of the query would be to change the query to the following:

SELECT col2 FROM table1 WHERE col1 = someUDF(:hv1)

Hopefully, you are able to see how this view-based approach simplifies the conversion to tableless query syntax and eliminates the risk of accidentally breaking your query.

 

To Check or Not to Check (Uniqueness)

In Hamlet’s famous ‘To Be or Not To Be’ speech, he wrestles with thoughts about life and death. Obviously, slow application performance is not a life-or-death issue, but it can sometimes feel that way if you’re the developer whose program is slowing down a critical business process.

Clients frequently engage our IBM Expert Labs team to analyze applications to identify performance bottlenecks and to identify solutions to improve performance. A couple months ago, I was reviewing the SQL statements embedded within a client’s RPG program. The analysis of the SQL identified a coding pattern where the program would run a SELECT statement against a table and almost immediately turn around and run an INSERT against the same table. The code looked something like the following:

SELECT 1 INTO :outhv FROM sometab
  WHERE col1=:hv1 AND col2=:hv2 AND col3=:hv3 AND col4=:hv4;

If sqlCode = 100;
  INSERT INTO sometab
     VALUES(:hv1, :hv2, :hv3, :hv4, …)
Endif;

 

When I asked the customer about the purpose of this coding pattern, they shared that the columns referenced on the Where clause defined a unique key for the table. Thus, the SELECT statement was being run to verify if the specified key value already exists in the table. If the SELECT statement didn’t return a row, the program would know that there was no possibility of a duplicate key error – meaning that the INSERT statement would run successfully.

 

This explanation led the developer to ask if it was more efficient to have Db2 just check for the duplicate key value on the INSERT statement. With this approach, the program would be running a single SQL statement opposed to the coding pattern above that would result in two SQL statements being executed in the cases where the new values were unique. In general, the fewer calls that you make to Db2 for i (or any database), the faster that your application will run. 

 

I put together a small performance test to verify if less is more when it comes to inserting new rows that may result in a duplicate key error. Essentially, is it faster to check first to avoid the duplicate key exception or not to check by running the Insert statement and relying on Db2 to detect duplicate keys?

 

My performance test used an SQL stored procedure with a loop to insert 1,000 rows – the loop had logic that would cause every other INSERT statement to fail with a duplicate key error. For the “not to check” version of my test, the following condition handler was used to trap the duplicate key error and then allow the stored procedure to continue to run its loop inserting rows. Even with the condition handler in place, Db2 still writes a duplicate key error message into the job log.

DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' 
   SET errFlag = 'Y' ;

 

I ran each version of the stored procedure multiple times to get a consistent timing. In the end, the “not to check” version of the tests consistently ran 5-6% faster than the “check” version of the procedure which avoided the duplicate key error by first running a SELECT statement. The performance tests essentially showed that the overhead of running the second SQL statement was greater than the overhead of Db2 signaling an error back to the application.

 

These test results reinforce the earlier assertion that performance is usually best when your application program runs the fewest number of SQL statements possible. With this coding pattern related to the insertion of unique keys, the answer to the question posed at the start of this entry is: Not To Check!

QTEMP Performance Loses Again

In the past, I’ve written about the inefficiencies caused by referencing QTEMP tables in your query requests. Due to the performance overhead of this approach, our IBM Expert Labs team is often employed by clients to help them reduce the number of queries using QTEMP. 

While working with a customer recently, I was reminded of one reason where developers in the past were forced to use QTEMP queries with stored procedures. While Db2 for i has supported stored procedures which return result sets for a very long time, there were many releases where those result sets were not available to invokers using embedded SQL. If the invoking program wanted to consume a result set returned by a stored procedure, the program had to use an API-based interface like the SQL Call Level Interface (CLI) or JDBC.  So, if you were an RPG or COBOL programmer you could use embedded SQL to call a stored procedure, but you were out of luck when it came to using embedded SQL to consuming the contents of a result set returned by a stored procedure.

 

This limitation was what drove some developers to use QTEMP queries. The basic idea is that if the stored procedure copied the contents of its result set into a QTEMP table, then the invoker of the stored procedure just needed to know the name of the QTEMP table in order to be able to consume the result set with a simple query.

 

Here’s an example of a logic from a stored procedure using the QTEMP technique to return a result set to the invoker.

...

-- Create QTEMP result set table if it doesn't exist in job yet
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
  CREATE TABLE qtemp.results
   AS (SELECT ordID, ordCustID FROM orders
           WHERE ordDate = inputDate) WITH NO DATA;

-- Clear our result set rows from prior call
DELETE FROM qtemp.results;

-- Populate with result set rows
INSERT INTO qtemp.results
 (SELECT ordID, ordCustID FROM orders 

           WHERE ordDate = inputDate);

...

 

 

The invoker would then declare a cursor over the QTEMP table to consume the result set with this simple query: SELECT ordID, ordCustID FROM qtemp.results. This query is one reason that the QTEMP solution has slower performance. The application needs to one run query to populate the QTEMP table and a second query to unload the QTEMP table.

 

The good news is that there’s a simpler and faster solution which has been available since the IBM i 7.1 release. This solution is made possible through Db2’s support for result set locator variables. Think of result set locator variables like pointers. These result set locators enable the stored procedure invoker to simply point at the result set returned by the procedure in order to consume it. This approach is more efficient since it doesn’t require a copy of the result set to be made like the QTEMP approach.

 

Using the result set locators support, a stored procedure uses an SQL cursor to make the result set contents available to the invoker. The following SQL demonstrates how that is done.

CREATE PROCEDURE GetOrders()
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN 

DECLARE ordCursor CURSOR WITH RETURN FOR
 (SELECT ordID, ordCustID FROM orders 
           WHERE ordDate = inputDate);

OPEN ordCursor;

END

 

The consumption of this stored procedure’s result set is enabled by result set locators as shown in the following snippet of SQL code.

DECLARE rs1 DECLARERESULT_SET_LOCATOR VARYING;  

ASSOCIATE LOCATOR(rs1) WITH PROCEDURE GetOrders;

ALLOCATE rs1Cursor CURSOR FOR RESULT SET rs1;
   
FETCH rs1Cursor INTO ordIdVar, custIdVar;

...

 

After declaring variable rs1 as a result set locator, the first step is executing the ASSOCIATE LOCATOR statement to point the rs1 variable at the result set returned by the GetOrders stored procedure.  The ALLOCATE CURSOR statement is the final step that needs to be performed since a cursor is required to retrieve the rows contained in the result set. In this example, the a cursor named rs1Cursor is allocated to fetch the rows from the stored procedure result.

Now that the two different coding methodologies have been compared, let’s talk about performance. In my testing, I measured the performance of repeated calls to the stored procedure in a single job. With distributed applications, it’s very common for the same stored procedure to be called over and over again within a database connection or job. My performance tests showed the QTEMP-based solution to be 2-4.5x slower than the solution using result set locators.  The result set locator performance advantage grew as the number of stored procedure calls increased. When comparing the performance of 25 calls, the result set locator solution was 2x faster. The performance advantage of result set locators grew to 3.8x faster with 150 procedure calls and to 4.5x faster with 300 procedure calls.

 

In my performance tests, the stored procedure result set contained 25 rows. Thus, each call to the QTEMP-based stored procedure results in 25 rows being copied into the QTEMP table. The QTEMP solution performance would slow even further with result sets that are larger in size. In addition, if the stored procedures were being called in multiple jobs, the QTEMP solution would suffer from the Plan Cache inefficiencies that I highlighted in my original QTEMP article.

 

There are some cases where a QTEMP-based solution is the winning choice, but hopefully it’s now clear that using a QTEMP table to return a stored procedure result set is clearly not one of those situations.

SQL Performance Center's Early Christmas Gift

If you have kids, there’s a pretty good chance they’ve asked you at some point to open Christmas gifts early. The anticipation and excitement associated with their gifts makes it difficult to wait until Christmas to open gifts. Why wait, when you can do it now!

 

Speaking of new and exciting things, the 1.1.9.7 version of IBM i Access Client Solutions (ACS) includes some significant enhancements to the SQL Performance Center. The good news is that you don’t have to wait until Christmas because this update is available now!

 

These enhancements enable you for the first time to perform all your SQL performance analysis and tuning using just the SQL Performance Center. In prior versions, the ACS Schemas tool had to be used any time that you wanted to use the Index Advisor. As you can see in the figure below, a launchpad (i.e., green box) has been added to the top of the SQL Performance Center that allows easy access to index advice.

The red box in this figure highlights that other data related to SQL performance can also be readily accessed from the launchpad.  The Maintained Temporary Indexes option makes it simple to find all the temporary indexes on your system using the MTI_INFO service that I’ve highlighted in the past. The Active Query Info option streamlines the process of identifying active queries on your system by returning the results from the ACTIVE_QUERY_INFO service which also has been recommended on this blog.

The performance data returned by these two new options can be sorted by clicking on a column.  However, the output data cannot be filtered at all. To counteract this limitation, the output for these two new interfaces (as well as other ACS interfaces) has been enhanced to include a new SQL widget button highlighted in the figure below. This SQL widget option allows you to easily access the SQL statement used to return the output. This SQL statement text can be copied into a Run SQL Scripts window. Then, you can add a WHERE clause to the query to return a filtered set of performance data (e.g., by job or by table).

The updated SQL Performance Center also has a more integrated solution when it comes to analyzing the Plan Cache entries at an ASP (auxiliary storage pool) level. A couple of years ago, I wrote about the need to consider Independent ASPs when reviewing Plan Cache entries or creating a Plan Cache Snapshot. The default setting was to only show queries run against tables created in the System ASP.  The default setting is still the same with the latest SQL Performance Center, but now the ASP selection only appears when you are accessing an IBM i server that has an Independent ASP attached. When an active Independent ASP exists, the ASP (i.e., Database) selection is displayed next to the button to display statements in the Plan Cache.

Notice that the first figure above had no selection option next to the Plan Cache Statements button. I think this visual clue is a great reminder to consider which ASP you want to focus your query performance analysis efforts on.

Now that you have a better understanding of the SQL Performance Center enhancements, I hope you agree that Christmas did come early with this ACS update. I also hope that your corporate IT policies allow you to access this gift right away:)  Merry Christmas and I look forward to talking to you in 2025!