Db2 for IBM i

 This week we celebrate the Thanksgiving holiday in the US, so I decided to write about an overlooked Db2 enhancement that I’m grateful to have as a tool in the toolbox when our IBM Expert Labs team helps clients optimize their SQL performance. 

One of the great things about the SQL user-defined function (UDF) support is that it allows programmers to use modular programming as they develop queries and reports. If there’s a calculation commonly used by your business, then good modular programming would dictate that the calculation is coded in a single program and reused as opposed to embedding that calculation in every application program that needs it.  UDFs enable this same type of code reuse, but at an SQL statement level.

Let’s use Costs of Goods Sold (COGS) as a common calculation or derivation that your reports frequently use.  COGS is a simple calculation that adds together beginning inventory and purchases during the period and subtracts the ending inventory. Note: You may be thinking that the conversion of a legacy date value to an SQL Date value would be a better example of a common derivation that queries need - however, remember that I've previously covered a Date lookup table as a better solution for that.

Here’s the SQL UDF definition to support the COGS calculation:

CREATE FUNCTION CostOfGoodsSold
           (beginInv DECIMAL(8,2), 

            endInv DECIMAL(8,2),

            purchasedInv DECIMAL(8,2))
 RETURNS DECIMAL(8,2) 
 LANGUAGE SQL
 NOT FENCED 
 GLOBAL DETERMINISTIC 
  RETURN( beginInv + purchasedInv — endInv)

While using the UDF is easy from a programming perspective, there is a performance cost since each call to the CostsOfGoodsSold UDF involves a call to an external program object. Invoking an external program takes much more time and resources than retrieving a column from a row in a table. Thus, the modular programming approach of using a UDF adds overhead due to the program call. 

IBM knows that both performance and modular programming are important, so they delivered SQL UDF inlining to allow SQL developers to get the best of both worlds. Inlining is a technique that is used by program compilers to improve performance of the generated program object. The compiler scans the program source code and notices that the main body of a program calls the same procedure or routine multiple times to perform a specific task. The compiler could decide to replace each procedure call in the program’s main body with the procedure logic itself. This inline replacement is done to eliminate the procedure call overhead to improve the overall performance. 

Db2 for i takes a similar approach with the UDF logic. Instead of taking time to invoke the external program object to perform the UDF logic, Db2 moves the function inline to the query as demonstrated in the following graphic. The SELECT statement coded in the application at the top of the figure is automatically transformed into the bottom SELECT statement when it’s run by the application program.

First, the Db2 inline support enables the application delivers to employ modular programming because their SQL statements reuse the calculation by invoking the UDF. Second, the Db2 inline support improves performance because when the SQL statement is run the UDF calculation is moved inline to the SQL statement which eliminates the overhead of the external program call. 

A simple performance test of the inline and non-inline versions of a UDF against a table with a half million rows showed a 4x performance improvement for the query using the inline version of the UDF.  This performance result reinforces how expensive it is for the database engine to perform external program calls.  

When the function is created, Db2 for i reviews the function definition to determine if the function is inline eligible. The main requirement is that the function logic must be simple – the function body must contain a RETURN statement or a RETURN statement wrapped within a BEGIN … END  pairing.  If the calculation requires multiple assignments and SQL statements, then that logic cannot be moved into another SQL statement.  If the UDF is a scalar user-defined function, the function must be defined with the GLOBAL DETERMINISTIC setting.  A user-defined table function must be defined with the NO EXTERNAL ACTION setting.  These are the main requirements, but the complete list of the inline requirements is documented for both scalar UDFs and table UDFs. 

At this point, you’re probably wondering how to verify if your existing SQL UDFs are being inlined. The SYSFUNCS view in QSYS2 contains all the details about UDFs on your system, so it’s easy to determine the inline status by running a query like the following:

SELECT routine_schema, routine_name, inline

 FROM qsys2.sysfuncs 
WHERE external_language IS NULL

Here’s an example of the output that you’ll receive:

 Fall means it is the time of year that we’re treated to a beautiful set of colors outdoors and that another round of IBM i Technology Refreshes have been announced.  As is the norm, the latest round of technology refreshes includes additions that enhance my favorite database, Db2 for i.

 I believe that this most recent round of Db2 for i enhancements really improves usability. One of the more notable enhancements in the Db2 performance tooling is backing away from the use of scientific notation in the Visual Explain tooling. Our IBM Expert Labs team frequently helps clients use Visual Explain more effectively to analyze and tune the performance of slow-running queries. While helping clients become more proficient with Visual Explain, I often heard requests to make the statistical data easier to understand by eliminating the use of scientific notation as shown in the following figure.

Visual Explain returns a ton of information that can be challenging to digest and some users (myself included) found that also being asked to perform mental math on the scientific notation (i.e., 1.231E7 means the table has 12.7 million rows) was too much to ask. Now, the Visual Explain interface does the math for you as demonstrated below.

This enhancement is only available when using Visual Explain with Db2 performance data (e.g., plan cache snapshot) captured after applying the Database Group PTF level associated with the IBM i 7.5 and 7.6  Technology Refreshes.

The latest round of Db2 enhancements also improve usability by eliminating work. Cleaning up old index advisor data is a best practice that I continually share with clients, but not everyone chooses to implement this recommendation. With these latest advancements, Db2 automatically will remove  old index advice.  Any index advice that is older than 365 days (according to the LAST_ADVISED column) will automatically be deleted from the SYSIXADV table in QSYS2 on a daily basis. 

You can change the definition of old related to index advice on your system with the IBM provided global variable named QIBM_SYSIXADV_BY_DAYS. For example, if you wanted index advice greater than 6 months old  to be automatically removed by Db2, then you would just run the following SQL statement to change the global variable default to 180.

CREATE OR REPLACE VARIABLE SYSIBMADM.QIBM_SYSIXADV_BY_DAYS

   INTEGER DEFAULT 180

The same automatic cleanup is also available for the error logging table used by the SQL Error Logging Facility (SELF) via the QIBM_SELF_BY_DAYS global variable.

On the performance front, parallel index creation - an overlooked capability provided by the Db2 Symmetric Multiprocessing (SMP) feature – has also been enhanced. The algorithm used to create an index with parallel processing was enhanced to use less temporary storage during the index creation process. Some internal tests showed a 99% reduction in the use of temporary storage! If you’re thinking of using Db2 SMP for parallel index creation, don’t forget to follow SMP best practices.

These are my favorite, notable Db2 enhancements in the newly announced IBM i Technology Refreshes, visit this website for a more complete list of the enhancements for IBM i 7.5 & 7.6.

 I have to admit that I didn’t know what an epoch was until a recent customer engagement. This customer enlisted the assistance of our IBM Technology Expert Labs team to help build a solution that would continually stream IBM i system performance data to a Splunk server. The message sent to the Splunk server needed to include the epoch value associated with the time that system performance data was generated.

With this epoch requirement, our Expert Labs team was pleasantly surprised to find that Db2 for i SQL support provides an EXTRACT function which has the ability to return the epoch value associated with a timestamp value. This built-in SQL function made it  simple to return the epoch value of 1,757,182,530 seconds for the input timestamp of 2025-09-06 18:15:30 as demonstrated in the Run SQL Scripts screen captured below. 

A solution to generate a message to Splunk with the required epoch value was put together quickly using this EXTRACT function. As testing began, however, we discovered that the EXTRACT function generated an unexpected epoch value. The prior example was run on an IBM i server with the time zone set to US Central Time.  So, all of us working on the project assumed that the epoch value returned by the EXTRACT function would be the number of seconds that have elapsed since January 1, 1970 00:00:00 (US Central).

Closer examination of the epoch value returned by the SQL function showed that the time zone setting of your IBM i server does not matter. The EXTRACT function always returns an epoch value which is the number of seconds that have elapsed since January 1, 1970 00:00:00 (UTC).  Depending on your application requirements, this may be good or bad news. For our client, the epoch value needed to be based on the time zone of the server and not UTC.

The IBM Expert Labs team relied on another SQL capability - the current timezone special register – to address this requirement. As the following example shows, subtracting the current timezone value from the input timestamp converts the timestamp value from your system to the corresponding UTC  value so that the generated epoch value is based off your system time.  

By sharing our adventures with the epoch values returned by the EXTRACT function, hopefully you can avoid your own missteps with epoch values. 

 One of the most common questions that our Db2 team in IBM Technology Expert Labs hears on SQL performance engagements is, Does my table have too many indexes? Given the efficiency of the Db2 for i index maintenance algorithms, I often respond by asking When was the last time you did housekeeping on the indexes for the table?.

If a table has a large number of indexes (which also includes keyed logical files), then there’s a strong chance there are probably some indexes that are no longer used by the Db2 query optimizer or your applications.  Meaning clients can often make room for new indexes by removing indexes that are no longer providing value.

The good news is that IBM i Access Client Solutions (ACS) for many yes has provided the ability to perform index housekeeping.  The bad news is that this ACS function was hard to find.  The even better news is that with the 1.1.9.9 version of ACS released this month, ACS provides an interface that makes it easier to evaluate the impact of your existing indexes.

Let’s start with the original ACS interface for index housekeeping, so that you can better appreciate the new streamlined interface delivered by ACS 1.1.9.9.  The biggest issue with the existing support is that the interface could not be accessed from the ACS SQL Performance Center - even though everyone agrees that indexes are a critical success factor for achieving good SQL performance.  The index usage feedback information was buried inside the ACS Schemas tool. 

The following figure shows the steps that were required to access the index usage feedback information. After launching the ACS Schemas tool, one needed to navigate to the table whose indexes they wanted to analyze and to select the Work With->Indexes option. 

After performing all these navigation steps, you were presented with a window like the following which displays the usage metrics needed to perform index housekeeping.

The 1.1.9.9 version of ACS streamlines access to these same index usage metrics. This streamlining was made possible by leveraging the launchpad that was added to the SQL Performance Center at the end of 2024. The following figure shows how the latest version of ACS adds an Index Evaluator option right in the middle of the SQL Performance Center launchpad. 

Once you click on the Index Evaluator icon in the launchpad, you are prompted for the name of the schema and table that you want to perform index housekeeping on.  No need to launch ACS Schemas tool, just a couple of clicks and you’re all set to start finding indexes which are just collecting dust and can be removed!

Earlier this month, IBM announced support for Power11. That announcement reminded me of a Db2 enhancement tied to the IBM Power10 portfolio which hasn’t been widely promoted. With Power10, the processors include an on-chip unit that supports hardware-based compression and decompression.

The Db2 for i development team decided to leverage this high-speed compression capability with its Plan Cache support. When a query running on Power10 (& Power11) servers produces a large query plan, the Db2 optimizer will store a compressed version of the query access plan in its Plan Cache. Compressed query plans means that Db2 can store more plans in the Plan Cache without increasing its overall size. More query plans increase the chances of  Db2 reusing a query plans which contributes to faster query performance since building a new query plan takes time and system resources. 

As you can see in the following figure, Db2 uses a couple of new Plan Cache properties in the ACS SQL Performance Center to highlight the temporary storage savings of query plan compression on newer Power servers. In this example, query plan compression is saving over half a gigabyte of temporary storage usage.

With this compression capability available, clients running with Power10 or Power11 may also want to review their Plan Cache configuration setting for the Plan Cache auto-sizer. That setting is also visible on the SQL Performance Center Plan Cache properties display as shown below.

Combining plan compression with auto-sizing enables clients to get a bigger bang for their buck when it comes to the temporary storage footprint used by Db2 for query workloads. That’s because the size of the Plan Cache not only controls the amount of temporary storage allocated for the Plan Cache, but it also impacts how much temporary storage is used to cache temporary runtime data structures to speed up future executions of the same query.

If the Plan Cache uses auto-sizing, Db2 will keep the same number of plans and cached temporary structures as it did before Power 10 hardware, but it will do so with a smaller temporary storage footprint. That’s due to the fact that the hit ratio drives the number of plans in the cache instead of the fixed size of the cache.  A comparison of the two sample Plan Cache configuration should make it clearer why auto-sizing can have an advantage when it comes to overall temporary storage usage by Db2 for query workloads.

First, let’s start with a Plan Cache using auto-sizing. On older hardware, assume that twenty thousand query plans require 2 GB of temporary storage in the auto-sized Plan Cache. With Power10 servers, we’ll assume that twenty thousand query plans could be stored in 1.5 GB of storage. 

In contrast, consider a Plan Cache manually set to a fixed size of 2 GB. On older hardware, it makes sense that twenty thousand query plans can also be stored in that fixed-size cache. Imagine that Power10 compression enables twenty-three thousand plans to be stored in the fixed-size 2 GB cache. While it’s great the fixed-size plan cache can store three thousand more plans with compression, keep in mind that the number of query plans dictates how much temporary storage Db2 allocates for the caching of temporary runtime structures mentioned earlier. The higher the number of query plans, the higher the number of temporary runtime structures that can be cached by Db2 for i.

With twenty-three thousand plans, let’s assume Db2 allocates 2 GB for cached temporary runtime structures. That means the total temporary storage footprint for the fixed-size cache is 4 GB – 2 GB for the fixed size Plan Cache and 2 GB for the cached temporary structures.  In comparison, assume that the twenty thousand plans in the auto-sized Plan Cache results in Db2 allocating 1.75 GB of storage for cached temporary runtime structures. That results in total temp storage footprint for the auto-sized Plan Cache being 3.25 GB.  Almost 20% less storage than the fixed-size cache temporary storage footprint.

The next logical question that pops to mind is why are not all clients using Plan Cache auto-sizing?  One of the primary reasons for this choice is that Db2’s auto-sizing algorithm prior to the IBM i 7.4 release was too conservative in its usage of temporary storage for the Plan Cache. This conservative nature often caused the auto-sizer to not allocate more temporary storage to store more query plans in the Plan Cache. Some customers’ query workloads were not able to meet their target Plan Cache Hit Ratio due to this behavior. As a result, some clients turned off the auto-sizer and manually increased the size of the Plan Cache to achieve a higher hit ratio. 

So, if you’re using newer Power hardware (Power10 or Power11),  it’s a good time to review your Plan Cache configuration to ensure that your query workloads are getting the most out of the temporary storage and hardware compression used by Db2 for your query workloads. If you’re not yet using newer Power hardware, then maybe it’s a good time to put a bug in the ear of your management. 

 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.

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.