Db2 Gives the Gift of Cache

If you’re like me during the Christmas season, you’re devoting a fair amount of brain cycles and time trying to come up with that perfect gift idea for a family member. There are obvious Christmas gifts to avoid like clothes for little kids, a gym membership for your spouse, etc. Then, there’s the controversial gift of cash which can lead to the question of how much thought you put into the gift (if you don’t think cash is controversial, try your own internet search on: is cash a good Christmas gift).

Luckily, Db2 for i has avoided all that controversy and debate by giving you the gift of cache instead of cash. Even better, you don’t have to wait until Christmas Day for this gift since the Db2 query engine has already given you the gift of cache - way back in V5R2!.

Several months ago, I highlighted one of the cache gifts – the Plan Cache. This month, I’m going to highlight some under the radar caching associated with Plan Cache that Db2 performs automatically to improve the performance of frequently run queries on your system.

Occasionally, the access plan (aka, query plan) constructed by the query optimizer utilizes temporary data structures and access methods  to implement your query. For example, a temporary sorted list may be used to perform an in-memory sort if your query contains an ORDER BY clause like the following. 

SELECT ordID, ordQty FROM orders 

  WHERE ordCustID = :inpID ORDER BY ordDate

When a query is done running and the query plan includes a temporary data structure, Db2 sometimes decides to leave that temporary data structure on the system populated with the data from your table.  The reason that it decides to leave your table data cached in temporary data structures is that it can improve performance on the next run of that query.

If that query is run again and the data in the underlying table has not been changed, Db2 can skip the work of repopulating the temporary data structure with your table data. Depending on the amount of table data being copied, this can provide a nice performance boost to the next run of the query. If a query contains predicates that reference parameter markers or host variables like ordCustID = :inpID in this example query, then Db2 will also validate that the query is searching for the same value In addition to verifying that the table data is unchanged. If a different host variable or parameter value is being searched for by the query, then the cached data in the temporary data cannot be used.

The Db2 query engine provides feedback when it boosts your query performance with cached temporary data structures on a couple of different interfaces.  If you’re analyzing a query plan with Visual Explain (VE), you can see below that the highlighted Final Select icon has a couple of fields in the Actual Runtime Information section providing feedback on the use of cached temporary data structures.

VE provides a count of the number of times a cached temporary result was used during the execution of the query. If Db2 was unable to reuse the cached data in a temporary data structure, then it provides the last reason that the cached data could not be used. For this query, the cached data was not able to be reused because the rows in the table had had changed (e.g., Insert, Update or Delete) since the temporary data structure had been populated.

The live Plan Cache viewer in the IBM i Access Client Solutions (ACS) SQL Performance Center can also be used to get feedback on the reuse of cached data from temporary data structures. After you launch the ACS SQL Performance Center, the live Plan Cache viewer is accessed by clicking on the Show Statements button. As you can see in the figure below, this interface allows you to see cached results feedback at a statement level. Most likely, you’ll have to use the Show Statements Columns editor to get your Total Cached Results Used column to display next to the query text.

In this case, half of the query runs were able to use the table data cached in a temporary data structure instead of spending time and resources copying the same data into the temporary data structure. The application did nothing but run the query 8 times and Db2 automatically boosted the performance on half of those runs with its temporary data structure caching.

As you’ve learned here, your mileage may vary on the frequency of cached results on your system based on how often the underlying tables are changed and how often the same host variable and parameter marker values are searched. Our IBM Technology Expert Labs team has noticed several instances were 10-15% of some query executions on busy systems are benefitting from this cache as we’ve assisted IBM i clients with SQL performance tuning.

This Db2 gift of cached temporary results is not a new gift, but hopefully you have a better understanding of the query performance benefits that this type of caching can provide. Merry Christmas and I look forward to talking to you in 2024!

Can SMP stand for System May Plod?!?!

 I'm guessing that you already know that the SMP abbreviation in Db2 for i SMP actually stands for Symmetric Multiprocessing and not System May Plod. However, I'm hoping a title that highlights a different meaning - especially one that implies slow performance - gets people's attention because the usage of Db2 SMP can have negative impacts.

One definition of plod that I found online is: walk doggedly and slowly with heavy steps. I'm not sure about you, but that's not how I'd want the performance of my IBM Power server described! How could slow ever be associated with the Db2 for i SMP feature?  When one thinks of parallel processing, it's easy to envision your queries receiving a performance boost like an F1 race car receives from its turbochargers. While this type of performance improvement can occur for your queries with Db2 SMP, it can also result in plodding system performance when parallel processing is not properly used.

The improper usage of Db2 SMP occurs when it's enabled systemwide instead of selectively applying parallel processing to long-running queries and database operations in specific jobs. A couple of years ago, I summarized parallel processing best practices for Db2 for i in an entry titled: The ABCs of Effective Db2 SMP Usage. The "C" stands for Controlled Usage - just another way of describing how parallel processing should only be applied to a subset of your workloads. Despite this strong recommendation from myself and other IBMers, too many IBM i clients are taking the easy road of activating Db2 SMP systemwide looking for a quick fix for their query performance challenges.

This "easy" approach of enabling database parallel processing systemwide may enhance query performance, but it can be at the expense of other workloads running on the system. Below you'll find an IBM iDoctor for IBM i graph of CPU utilization (green line) from a customer's system. Notice how the CPU utilization is holding steady around a 60% rate. That steady, healthy CPU usage rate is then interrupted by a sharp jump in CPU utilization for several minutes in the 85-90% range.

When our IBM Technology Expert Labs team was engaged by the client to determine the root cause of these system performance spikes, they found that the high CPU usage peaks were a result of multiple queries on the system using parallel processing. The query users were probably happy, but it's easy to see how other users and workloads on the system were negatively impacted. This client's experience was not just a one off - my colleagues in IBM Support and Technology Expert Labs have shared several stories me about clients engaging them to explain their system slowdowns.

This poor practice has been compounded by the fact that since June 2022, IBM i clients can try Db2 SMP without paying for a license (on all 7.* releases). In prior years, IBM i customers had to make a monetary investment before they could take the easy road of enabling Db2 SMP systemwide. In fact, IBM Support's official stance is that clients should only enable parallel processing systemwide on IBM i 7.5.  This latest release of the IBM i contains two new features, PARALLEL_MAX_SYSTEM_CPU and PARALLEL_MIN_TIME, which are very useful in preventing a system from being overrun from systemwide usage of Db2 SMP.    

Some clients that have taken the easy path of enabling Db2 SMP for all queries run on the system have also discovered that some of their high-level language programs are not thread safe. Consider an RPG program which has been registered as an external user-defined function (UDF) with the SQL CREATE FUNCTION statement. If parallel processing is applied to a query that invokes this external UDF, then you need to remember that multiple parallel threads can now be invoking the RPG program.  If this RPG program is not thread safe, then issues can arise.  In this situation, the developer can compile their high-level language programs to be thread-safe or specify the DISALLOW PARALLEL option on the CREATE FUNCTION statement which will prevent the database engine from using parallel processing on any query that references the UDF. It's very easy to overlook program thread safety when turning on parallel processing for all the queries on your system.

Systemwide activation of Db2 SMP is also risky because it can mask the root cause of your query performance problems. With Db2 SMP, you're essentially trading system resources for faster response times. While your queries can return faster with Db2 SMP, sometimes there are more efficient methods to improve query performance - if you've enabled parallel processing systemwide, it can be harder to find the queries that would benefit from these more efficient methods. 

A couple of years ago, our Technology Expert Labs team was hired to help a client experiencing degraded system performance. As we investigated the problem, we discovered that Db2 SMP was enabled systemwide. This systemwide enablement of Db2 SMP was causing a frequently run query to be implemented with a parallel degree of 38. This meant that every time this query was run on the system - there were 38 threads running in parallel consuming CPU, memory, and disk I/O resources. Further analysis of this query and database identified that the creation of a new index would enable the query to run in the same amount of time but with less system resources (i.e., use a single thread). This client had used Db2 SMP systemwide to "fix" their query performance problems with hardware instead of addressing the root cause of a missing index - eventually they ran out of hardware resources.

Hopefully, you now have a better awareness of the risks of taking the easy road of activating Db2 SMP systemwide. Putting in the extra effort to selectively use parallel processing should result in a healthier system performance.  I stumbled upon the following quote from Robert Kiyosaki and think it summarizes things nicely: Remember the easy road often becomes hard, and the hard road often becomes easy.

I hope all my US readers have a great Thanksgiving holiday.

Fall Into a Smorgasbord of Db2 Goodies

Last month's entry coincided with the start of Fall on the calendar. Another event that occurs each Fall is IBM announcing IBM i Technology Refresh content. This year's announcement happened on October 10^th and as you've come to expect there are several Db2 for i enhancements. No major additions to Db2 this time, but several small Db2 enhancements that could make your life easier - thus, usage of smorgasbord in this title is a good fit.

I'll illuminate my favorite Db2 enhancements in the latest IBM i 7.4 and 7.5 Technology Refreshes (TRs), but you'll want to review the complete list of enhancements. Feel free to add comments to this entry if you find other Db2 for i enhancements that you believe are worthy of highlighting.

Tightening security for the valuable business data stored on your IBM i systems is a top priority, so the latest TRs have a couple of interesting security enhancements. If you're using SQL to perform encryption, you can now perform 256-bit encryption with the new ENCRYPT_AES256 function. The original SQL function for AES encryption, ENCRYPT_AES, only supports 128-bit encryption. If you have applications using Dynamic SQL with interfaces that may be susceptible to SQL injection attacks, then you may want to consider using the enhanced PARSE_STATEMENT service to verify only the intended Db2 objects are being referenced by your Dynamic SQL statement strings.

The new HTTP functions in QSYS2 introduced 2 years ago have been a big boon to developers using SQL to integrate restful services into their IBM i applications. The latest TRs continue to enhance this HTTP services toolkit with the addition of new HTTP header response controls for the existing functions and two new functions. The new functions are HTML_ENTITY_ENCODE and HTML_ENTITY_DECODE. The encode can be used to convert a character string into a UTF-8 string that is valid HTML. For example, if your input string contains the less than or greater than characters like <b>, the encode function will produce &lt;b&gt; - the decode function provides similar functionality, just the other direction.

The usability of the SQL Error Logging Facility (SELF) is also improved with the ability to have Db2 monitor and log for groupings of SQL codes - specifically the warning codes (SQLCODE>0) and the error codes (SQLCODE<0). As shown below, the special values of *WARN & *ERROR can be specified instead of assigning multiple individual code values to the IBM-provided global variable.

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) DEFAULT '*WARN';

SET SYSIBMADM.SELFCODES = '*ERROR';

When the logged SQL error data in the SELF_ERROR_LOG view in QSYS2 is analyzed, one may also want to leverage the new SQLCODE_INFO function in SYSTOOLS to return the message text associated with the logged SQLCODE value.

Developers that are using Code for IBM i as their IDE will also want to give the new Db2 for i Extension a try to enrich their usage of SQL in this graphical development environment.

The final Db2 for I enhancement to highlight is the GENERATE_SPREADSHEET function in SYSTOOLS. This function provides a way for you to programmatically drive the Data Transfer feature in IBM i Access Client Solutions (ACS) to generate a spreadsheet. The function can be used to populate the contents of a spreadsheet file in IFS with the rows in table or the rows produced by a query.

Here's an example of using the new function to generate a spreadsheet file containing the results of the specified query.

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
   PATH_NAME => '/mydir/EmpQry_Spreadsheet', 
   SPREADSHEET_QUERY => 'SELECT fname, lname, workdept FROM emp WHERE role=''ANALYST'' ', 
   LIBRARY_NAME => NULL,  

   FILE_NAME => NULL, 
   SPREADSHEET_TYPE => 'csv', 
   COLUMN_HEADINGS => 'COLUMN' )

I hope you find a few tasty enhancements from the buffet of Db2 items delivered by this latest IBM i TRs that can make your IBM i life easier!

Jump-start your SQL Development with new ACS SQL Generators

With the first day of Fall quickly approaching, it won’t be too long before us Minnesotans need to make sure we have jumper cables in our vehicles for the cold winter months. Hopefully, that’s later rather than sooner…

The new SQL generators for Db2 tables (& physical files) in IBM i Access Client Solutions (ACS) 1.1.9.3 provide a different type of jump-start. These recently released generators should help developers that are new to SQL by providing the option of generating Insert, Update, and Delete statements along with Stored Procedures that perform the same data change operations.

The following figure highlights the new Generate options available for a table with ACS 1.1.9.3. Prior to this latest round of enhancements, ACS only provided the ability to generate the table definition or a query to retrieve all the columns from a table.

Here's an example of the SQL produced by the Update and Delete statements generated for the Employee table in the Db2 sample database.

Notice that the generated statements include host variable placeholders to make it easier to embed the SQL statements in your high-level language programs. Also, notice how the ACS generator utilizes the IS DISTINCT predicate on the WHERE clause. The IS DISTINCT predicate is handy with null capable columns because it automatically accounts for the null values - either both values being compared must both be null or both of the non-null values must match.

Here's an example of the SQL generated by the Procedure->Delete option.

You may be wondering what’s the point of generating a stored procedure that wrappers an Insert, Update or Delete statement. The usage of stored procedures to support CRUD (Create, Read, Update, Delete) operations on database tables is a pretty common coding pattern/architecture. Some DBMS products advocate this approach because their stored procedures offer better performance for Create, Read, Update, and Delete operations. While this performance difference doesn’t apply to Db2 for i, the CRUD stored procedure approach does offer security and application architecture advantages.

ACS 1.1.9.3 currently only generates stored procedures for create (ie, insert), update, and delete operations. Adding a stored procedure generator for the read operation is on IBM’s list of future possible enhancements.

You should now see how these new SQL generators in ACS can provide a nice boost to the coding productivity of developers who are new to using SQL and/or stored procedures on IBM i.  

Share & Communicate your SQL with Style

The Run SQL Scripts tool provided by IBM i Access Client Solutions (ACS) does a great job of making an SQL statement more understandable with its color highlighting of SQL keywords and its formatting of the statement text.

I think you would agree that it would be great if that same highlighting and formatting could ride along with your SQL statement text to make it clearer to your peers when sharing your SQL via email, presentations, design docs, etc. This actually is possible - assuming that you have ACS 1.1.9.2 or later installed on your workstation.

The Run SQL Scripts auto-prompting support for host variables and parameter markers got all the fanfare when ACS 1.1.9.2 was released, but this version of ACS also delivered Rich Text Format copy capabilities. Without this new capability, the following figure shows how the SQL syntax highlighting disappears when an SQL statement text is copied from Run SQL Scripts to a word processing tool or an email.

Now that the Run SQL Scripts copy captures the rich text format (i.e., color highlighting) associated with your SQL statement, you’ll be able to choose the highlighted Paste Option on the left as shown below to preserve the source statement format when performing a paste operation.

Once you select the  highlighted Keep Source Formatting paste option in the prior figure, the SQL will be transformed back to the highlighted and formatted SQL statement  as shown below. It's the same stylish presentation as Run SQL Scripts, but available in a different medium.

Hopefully, it’s now clear to how newer versions of ACS enable you to communicate and share your SQL statement text in a more comprehensible format.

Pivoting with SQL

Just like last month’s entry, the roots for this entry were formed from a recent discussion on an IBM TechXChange Community - the Db2 for i SQL Community. If you’re not yet part of this online community, it’s a good resource for getting your Db2 for i & SQL-related questions answered.

The discussion in this community was regarding two tables like the following which contained employee data and deductions data for employees.

An IBM i developer was looking for SQL support which would allow the deduction details to be returned alongside the employee data in the same row. Essentially, their goal was to take the data in these two tables above and produce the following result set. In their situation, an employee could have a maximum of four deductions. 

This transformation of the data that moves values from a single column into multiple columns is often referred to as a pivot operation. While Db2 for i SQL support doesn’t include a pivot operator, it is possible to perform this type of transformation using a combination of rich Db2 for i SQL syntax.

The first step is associating a unique identifier with each employee’s deduction using the ROW_NUMBER specification as shown in this query.

SELECT EmpID, Deductions,
       ROW_NUMBER() OVER (PARTITION BY EmpID) AS seqnum FROM deduction

This query converts the data in the Deduction table into the following output.  The Partition By keyword on the Row_Number specification is what causes the seqnum column (i.e., the unique identifier) value to reset to 1 when it encounters the deductions for a different employee.

Now that each employee’s deduction has a unique identifier associated with it, the next step is getting those individual deduction column values into a single row for each employee. The Group By EmpID clause in the following query is used to consolidate all of the deduction entries for an employee into a single result row.

SELECT EmpID,
       MAX(CASE WHEN seqnum = 1 THEN Deductions END) AS Deduct1,
       MAX(CASE WHEN seqnum = 2 THEN Deductions END) AS Deduct2,
       MAX(CASE WHEN seqnum = 3 THEN Deductions END) AS Deduct3,
       MAX(CASE WHEN seqnum = 4 THEN Deductions END) AS Deduct4
   FROM (SELECT EmpID, Deductions,
           ROW_NUMBER() OVER (PARTITION BY EmpID) AS seqnum FROM deduction)
   GROUP BY EmpID)

 As the group of deductions for an individual employee is processed, a Case expression is used to perform the pivot of the employee’s deductions values into separate output columns. The unique identifier (seqnum) for a deduction is checked to see which output column (Deduct1, Deduct2, Deduct3, Deduct4) will be assigned the value of one of the employee’s deduction values. The MAX function is used only as a trick to get the deduction output columns into the result set - the result set can only contain grouping columns and aggregate functions because the outer Select statement is using a grouping clause.  This grouping query produces the following result set.

As you can see in the complete query solution that follows, this grouping query is encapsulated within a common table expression (CTE) named pivot_deductions. The last step remaining step is to join the pivoted deductions with the employee table, so the employee name can be part of the the final pivoted result set. 

WITH pivot_deductions AS
  SELECT EmpID,
       MAX(CASE WHEN seqnum = 1 THEN Deductions END) AS Deduct1,
       MAX(CASE WHEN seqnum = 2 THEN Deductions END) AS Deduct2,
       MAX(CASE WHEN seqnum = 3 THEN Deductions END) AS Deduct3,
       MAX(CASE WHEN seqnum = 4 THEN Deductions END) AS Deduct4
   FROM (SELECT EmpID, Deductions,
           ROW_NUMBER() OVER (PARTITION BY EmpID) AS seqnum FROM deduction)
   GROUP BY EmpID)
SELECT e.EmpID, Name, Deduct1, Deduct2, Deduct3, Deduct4 
  FROM employee e 
     INNER JOIN pivot_deductions p ON e.EmpID = p.EmpID 

Hopefully, this article will give you confidence to leverage some of the SQL functionality highlighted here to more easily perform your own data transformations whether they involve pivoting or not.  

The Amazing & Disappearing Plan Cache

This month I decided to continue with a Plan Cache-related topic as a result of a recent discussion thread on IPL frequency on the IBM i Global TechXchange Community.  System IPLs are relevant to the Db2 for i Plan Cache used by the SQL Query Engine (SQE) because it’s a temporary object that gets recreated each time the system is restarted. Before diving into IPL considerations for the Plan Cache, let’s first focus on how SQE uses the Plan Cache and the benefits it provides. 

As I highlighted in my January entry, the Plan Cache is used by SQE to store the access plans (or query plans) created by the query optimizer. An access plan must be in place before a query can be run since it contains the details on what methods (e.g., table scan, index probe, etc.) and objects (e.g., index) will be used by Db2 during the runtime execution of a query. SQE’s usage of the Plan Cache as a single repository for all access plans on the system benefits IBM i clients in several different ways.

First, a centralized repository reduces the amount of system resources used to build query plans. If you had the same SQL request run by different programs, the Classic Query Engine (CQE) had to create and store a separate access plan for each program. With SQE, one copy of the access plan can be created and shared across multiple programs. 

Second, the Plan Cache enables a tremendous Db2 performance toolset with no overhead. Usage of plans from the Plan Cache is part of the normal SQE query execution process, so there’s no additional tool that must be run to collect data for detailed performance analysis with Visual Explain. The IBM i Access Client Solutions (ACS) SQL Performance Center interface shown below makes it fast and simple to perform analysis on a query plan. Just hit the highlighted “Show Statements” button and find the query that needs performance analysis.

Prior to the Plan Cache, Visual Explain analysis could only be performed if you had first collected database monitor data. This requirement was problematic because of the noticeable overhead caused by the database monitor collection process. This overhead meant that you couldn’t just keep a database monitor always running on your production system. When a query performance problem arose on your production system, Visual Explain could not be used to analyze the query because the required performance data wasn’t available. Then, when you went to collect the requisite database monitor data, you ran the risk of negatively impacting the performance of other workloads on your system. The Plan Cache automatically contains the data required to perform detailed analysis with Visual Explain without adding extra overhead to your system.

Lastly, the Plan Cache’s centralized plan repository provides a foundation for self-learning query optimization techniques. One of those self-learning query optimization techniques is Adaptive Query Processing or AQP for short. The ACS SQL Performance Center displays a Plan Cache property, Number of Plans Rebuilt due to AQP, where you can see how often this self-learning technology automatically has tuned the performance of queries on your system. 

Now that you know about the amazing benefits the Plan Cache provides, hopefully it’s also clearer how these great benefits are somewhat negated by frequent system IPLs.

The biggest issue is slower performance on Monday morning after a weekend featuring a system IPL. An empty Plan Cache has the potential to add to the Monday blues for your end users since all the query plans that we’re being reused by your applications and reports on Friday have to be rebuilt by the Db2 query optimizer on Monday morning when users start running your applications and reports. Building access plans takes time and system resources which can lead to sluggish system performance. 

This performance slowdown can be further compounded if some of your queries had been auto-tuned by SQE with the creation of maintained temporary indexes (MTIs). These temporary indexes obviously also pull a disappearing act when an IPL is performed. A query which previously relied on a temporary index will run slower post IPL until SQE makes the decision to auto-tune the query again. In addition, the recreation of these temporary indexes can cause additional overhead on your production system.

So, what actions can you take to minimize the performance impact of an IPL causing your Plan Cache to disappear?

If your system is frequently IPLed, then start by determining if frequent IPLs are truly required. If  the answer is we’ve always done it that way - that’s a good indicator you should keep investigating. When the AS/400 was first launched, weekly IPLs were common because it was possible for the system to run out of temporary addresses. However, that launch was 35 years ago! Even though IBM has delivered enhancements over the years including the switch to 64-bit RISC processors in 1995 which significantly expanded the temporary address support, our IBM Technology Expert Labs team encounters a significant number of clients still employing weekly IPLs when we’re helping them with SQL performance analysis and tuning. If your company is hesitant to change this old practice, make a small change in IPL frequency and evaluate the impact - for instance, move from weekly IPLs to biweekly or monthly IPLs. 

Regardless of the IPL frequency, proactively replacing the MTIs on your system can minimize the post IPL query performance blues. In 2022, the Db2 for i development team delivered the MTI_INFO service that makes it really easy to identify the temporary indexes currently on your system and  to replace them with permanent indexes. I’d recommend modifying your IPL preparation procedure to include using the MTI_INFO service to replace the temporary indexes with permanent indexes before they disappear at IPL time. The MTI_INFO service is supported back to the IBM i 7.3 release.

The final action you can take is to alter your post IPL procedures to include a warmup of key applications and reports. After an IPL on the weekend, set up a process to run some of your key applications and reports prior to Monday morning. This warmup action will pre-populate some of the Plan Cache to lessen the performance pain when end users ramp up system activity on Monday morning.

Hopefully, one of these actions or a combination of them will help you maximize the performance benefits that the Plan Cache can provide to your application and reports. Please reach out, if you need assistance learning how to leverage Visual Explain and the SQL Performance Center to manage and tune query performance on your systems.