A Followup on Snapshot Scheduling

 

A colleague recently pointed out that while my Snapshot Scheduling blog entry was on target, it was missing a few details for a portion of the IBM i install base. Specifically, those IBM i clients that are using Independent Disk Pools  - also known as Independent Auxiliary Storage Pools (ASPs) or IASPs for short. IASP usage is on the rise by IBM i customers since they provide the foundation for IBM PowerHA.

In the prior blog entry, I advocated the use of the DUMP_PLAN_CACHE service for creating Plan Cache snapshots on your production system on a regular basis. The detail that I did not include regarding this service and IASPs is this - by default, the DUMP_PLAN_CACHE service only copies Plan Cache entries for those queries referencing tables in the System ASP.  That means if you had a critical query like the following and DBLIB resided in an IASP, then the Plan Cache snapshot would contain no record of this query. 

SELECT COUNT(*) FROM dblib.orders WHERE custid = 123

The snapshot would only contain queries that reference objects that reside in libraries and schemas that are housed in the System ASP. This default behavior of the DUMP_PLAN_CACHE service exactly matches the default behavior when you view the live Plan Cache entries with the IBM i Access Client Solutions (ACS) SQL Performance Center. 

The SQL Performance Center Database pull-down menu highlighted in the following figure defaults to the System ASP. Thus, clicking on the Show Statements button to analyze Plan Cache entries will result in a list of those queries that only reference objects that reside in libraries that are stored in the System ASP. If you want to view Plan Cache entries for queries referencing tables that reside in an IASP, then you’ll first need to select the IASP name from the highlighted Database pull-down menu.

On a side note, the Plan Cache Properties shown in this figure are summary metrics that include all queries regardless of which ASP the queried objects resides in.

Obviously, there’s no graphical interface for the DUMP_PLAN_CACHE service, so you’re probably wondering how that service can be used to capture all of the entries in the Plan Cache. One way to ensure that all Plan Cache entries are written to the snapshot is to use the Set ASP Group (SETASPGRP) CL command featured in the following CL script. 

RUNSQL SQL('CALL QSYS2.DUMP_PLAN_CACHE(''SOMELIB'',''SNAP1'')')

SETASPGRP ASPGRP(IASPNN)

RUNSQL SQL('CALL QSYS2.DUMP_PLAN_CACHE (''SOMELIB'',''SNAP1'')')

The first call to the DUMP_PLAN_CACHE service will dump the plan cache entries related to System ASP queries. After that dump has completed, the SETASPGRP command is run to reset the job to the independent ASP named IASPNN.  This ASP group reset causes the second DUMP_PLAN_CACHE service invocation to dump the plan cache entries related to the specified IASP. 

Take note in this CL script that the second DUMP_PLAN_CACHE request also uses the same snapshot name to ensure that all of the plan cache entries end up in a single Plan Cache Snapshot object. 

Now, you have all the details needed to guarantee that even environments utilizing IASPs can have scheduled Plan Cache snapshots that capture ALL of your queries.

Spring Forward with New Db2 for i Features

Spring Forward is a phrase that is often used in the United States to remind people which direction to move their clocks when Daylight Savings Time begins. I’m not going to debate Daylight Savings Time here since it’s a highly charged issue for many people. However, there is no disputing that many of us associate springtime with an adjustment to our clocks. 

Many in the IBM i world are also accustomed to IBM announcing new refreshes or releases for their favorite system around this time of the year. Spring 2023 follows that pattern with the recent announcement of Technology Refreshes (TRs) for the IBM i 7.4 and IBM i 7.5 releases. Like usual, these refreshes include new Db2 for i functionality to help you move your applications and databases forward.

Here are some of my favorite new Db2 capabilities in the recently announced TRs:

Those of you that have already upgraded to IBM i 7.5 will benefit from the extension of the RESTRICT ON DROP support. The RESTRICT ON DROP support initially could be used to prevent the accidental deletion of Db2 tables and physical files. Tables or physical files created with this option cannot be deleted with the SQL DROP statement or the DLTF CL command until the RESTRICT ON DROP setting is removed. Now, the RESTRICT ON DROP support can be used to prevent the accidental deletion of stored procedures and user-defined functions. This new support can be used with both the SQL and external flavor of stored procedures & functions.

Geospatial Analytics was a big addition to Db2 for i in 2022 and that support was also enhanced with a new scalar function - ST_GEOHASHVALUE.  The use of geohash values is a great way to speed up the performance of many solutions which integrates the Geospatial Analytics support.

As you’ve come to expect, new and enhanced IBM i Services are often found in IBM i TRs and that holds true for the 7.4 and 7.5 TRs announced this week. The new RDB_ENTRY_INFO view is a great addition from a Db2 perspective. Now, SQL can be used to easily retrieve your database directory entry details instead of having to run the Display Relational Database Directory Entries (DSPRDBDIRE) CL command.

IBM i Access Client Solutions (ACS) is a key tool for the support and management of your Db2 for i databases. Not surprisingly, it also includes several new enhancements that make working with SQL and Db2 for i easier. The biggest enhancement is the prompting support for Run SQL Scripts. Frequently, the SQL statements run by applications reference host variable or parameter markers like the following statements:

SELECT COUNT(*) FROM qsys2.systables WHERE table_schema = :H 

SELECT orderkey, quantity  FROM orders 

  WHERE shipdate BETWEEN ? AND ?

While this is a great SQL coding practice, it does make life difficult for anyone that wants to run these SQL requests from ACS Run SQL Scripts in order to analyze performance or debug an issue. Before these types of SQL statements can be run, all the host variables and parameter markers needed to be manually replaced with literal values. 

This cumbersome, manual replacement process has been eliminated with the new Run SQL Scripts prompting support. All you need to do is copy and paste the SQL statement text into Run SQL Scripts. When you go to run the SQL statement, ACS will automatically detect that there are host variables or parameter markers that need to be replaced and will prompt you for the replacement values as shown below.

You just enter the appropriate values into the input field(s) and hit the “OK” button. ACS will then run the SQL request with your input values.  Notice that the right-hand side of these two input windows include the data type definition of the column being compared (or assigned) which make its easier for you to supply a compatible replacement value.

Again, these are just a few of the highlights from the latest IBM i TRs, so make sure you take the time to review the complete list to see if there are other new features that can make your life easier and move your IBM i applications forward!

Shared CTEs - They're Here...

They’re Here is a famous quote from Poltergeist, a horror movie from the 1980s. In this movie, spirits visit a family’s home and appear playful at first. These spirits, however, do not remain cordial and end up wreaking havoc on the family. This turn of events shouldn’t be too much of a surprise given that this film is a horror movie.   

Last June, I had given everyone a heads up about a pending Db2 for i change for Shared Common Table Expressions (CTEs) that would be coming soon to visit your systems. Well, to borrow the quote from Poltergeist… They’re Here. The implementation changes to make the Db2 for i Shared CTE support compliant with the SQL Standard are now part of the IBM i 7.4 & 7.5 releases. This implementation change is required to eliminate inconsistent results which were a possibility with the prior non-compliant implementation. These changes were part of the IBM i 7.5 GA code delivered in May 2022. For the IBM i 7.4 release, the changes were introduced with the 7.4 Database Group PTF level #23 which was delivered in December 2022.

While these Shared CTE implementation changes are not to be feared like the evil spirits in the movie, they should not be ignored. The reason that they should not be ignored is that these changes could make some of your queries with Shared CTEs run faster or could make some of your queries using Shared CTEs run slower. Unfortunately, recent client interactions have made it clear to the IBM team that some clients have been ignoring this pending implementation change despite the warnings raised in this blog and the IBM i Memo to Users. 

Before delivering the Shared CTE implementation change, the IBM i development team did a great job delivering flagging support for the IBM i 7.3 and 7.4 releases which makes it easy to use an SQL Plan Cache Snapshot or SQL Monitor to identify your SQL requests that might be impacted by this change. Regrettably, it appears few clients have used this flagging support to proactively identify and evaluate queries using a Shared CTE.

A query with a Shared CTE running slower doesn’t sound like that big of a threat. However, if that same query is run frequently and processes a large amount of data, there is the potential for your overall system performance being negatively impacted. In fact, IBM has seen clients experience system performance issues due to queries affected by the Shared CTE implementation change. In these cases, some rewriting and tuning of the queries was necessary.

Instead of ignoring this pending performance behavior change in your queries, the following proactive actions are recommended:

• Before updating your 7.4 Database Group PTF level or upgrading to IBM i 7.4 or 7.5, use the flagging support to identify which SQL statements on your system use a Shared CTE.
• Before updating your 7.4 Database Group PTF level or upgrading to IBM i 7.4 or 7.5, simulate the Shared CTE coding change by adding the following predicate, AND RAND() IS NOT NULL, to your Shared CTE definition.  Rerun the query after making this change and measure the performance.
• If performance is not acceptable, try to improve your query performance by rewriting it or tuning it with new indexes. The Db2 team in IBM Technology Expert Labs can help with this!
•  When your systems get updated with new IBM i 7.4 or 7.5 code, make sure the following PTFs, MF70861 (7.4) & MF70868 (7.5), are applied to ensure you have the latest version of the Shared CTE code. As you hopefully gather from this update, this implementation change is significant - so it would be advantageous to stay current with the latest PTFs for Shared CTEs.

·        As the old saying goes, an ounce of prevention is worth a pound of cure.  Please reach out if you need help with your prevention plan.

Stop Before Transforming Your Data Yet Again

Assessing the performance of the SQL and queries running on your IBM i systems is the most popular service that our Db2 for i team in Systems Expert Labs performs. One of those things that I frequently notice when doing this work is that a lot of a client’s query and reports perform data transformations. These data transformations are required to get the data in a form that the end users can understand and digest. These transformations range from converting legacy data values into SQL dates to eliminating leading blanks in character columns to the aggregation of data. 

The transformations themselves are not an issue. The issue is the number of times that they are done and the number of queries that contain the same transformation code. As a result, these repeated transformations leave clients with both performance and code maintenance headaches.

This transformation overhead reared its head on a recent client engagement where they were experiencing system performance issues due to reporting workloads straining their system. Our analysis showed that a significant number of reports referenced the same SQL view. One transformation in this view was the aggregations of millions of rows of sales data. This same expensive data transformation was occurring hundreds of times of day on their system.  As it turned out, the analysts consuming these reports did not need the latest sales data being aggregated for their work.  The client addressed their performance woes by scheduling the needed aggregation of the sales data and changing the SQL view to reference the pre-aggregated data set. System performance improved dramatically after this repeated data transformation was reduced to a single daily event. After this change, their Analytics SME remarked that the performance change of our reports was quite literally astounding.

This client avoided the code maintenance burden of having the same data transformation code duplicated across many queries through the use of an SQL view, but I see many clients with the same data transformation code repeated in different queries. While the use of SQL views enables code reuse, they don’t address the performance overhead of having multiple queries performing the same data transformations over and over again on your system. 

Again, you could free up a lot of your system resources by reducing the number of times that the data transformations have to be performed. Schedule these data transformations once a day (or couple times) to get a copy of your data into a format that is more easily consumed by your reporting and analytic workloads. This data copy that enables easier and faster analytics is often referred to as a data mart or data warehouse.

Snapshot Scheduling New Year's Resolution?

The start of a new year can be filled with scheduling related to New Year's resolutions. For example, if you made health-related New Year's resolutions, then there’s a good chance you'll be scheduling an appointment to sign up for a gym membership or scheduling your annual physical exam with a doctor. 

In 2023, I’d like to challenge you to make a New Year’s resolution to schedule Plan Cache snapshots on your system.  Scheduling a Plan Cache snapshot will not improve the health of your system or database, but it is a good proactive action to make the resolution of query performance issues easier.

Let’s start with a review of the Plan Cache and Plan Cache snapshot objects, so you can better understand the benefits of scheduling a snapshot.

The Plan Cache is used by Db2 for i 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.  As the “cache” part of the Plan Cache name implies, the contents of this object are dynamic. Thus, there’s no guarantee that the access plan used to run a query yesterday will still reside in the Plan Cache when that query is run again today.  

The dynamic nature of the Plan Cache is what drives the need for Plan Cache snapshots. The contents of the Plan Cache can be copied into a snapshot to provide a static repository of the query plans used by Db2 for i. The static nature of the Plan Cache snapshot makes it easy to analyze the performance and implementation of your queries without having to worry about an access plan disappearing on you.

The static view of your query plans provided by a snapshot not only can deliver insights on your current query performance and behavior, but it also enables comparisons of query performance and behavior. And this comparison capability is where the scheduling of snapshots provides value.

Let’s say that you have a query this week that’s running noticeably slower.  If you have a snapshot from last week and a snapshot from his week on your system, you could easily use Visual Explain to compare the implementation as shown in the following figure.

A quick review of this output reveals that the faster run of the query from last week shows an index being used while the slower run this week is using a table scan. Now, you can go review your database to see if someone accidentally deleted the index (or keyed logical file) that was being used in the query plan generated by Db2 last week.

If the comparison of the access plans doesn’t lead to the root cause of your performance problems, then you may have a situation that requires contacting IBM Support. The good news is that your snapshots can also help IBM more quickly resolve your performance issue. Maybe you’ve contacted IBM Support in the past about a performance problem and had a tough time answering their question: “How much slower is your report running?”.  Not only does a Plan Cache snapshot make it easy to answer that question, the IBM team can also use the snapshot data to more quickly understand the performance issue and come up with a resolution.

Thanks to the IBM i integrated job scheduler and Db2 Plan Cache services, scheduling the daily or weekly creation of a Plan Cache snapshot is pretty simple. Most job schedule entries call a CL program, so you just need to create a CL program that uses the RUNSQL CL command to invoke the DUMP_PLAN_CACHE service.  The following example copies the Plan Cache contents to the specified snapshot table: SOMELIB/SNAP1

RUNSQL SQL('CALL QSYS2.DUMP_PLAN_CACHE(''SOMELIB'',''SNAP1'')')

If you’re worried about the disk space consumed by the collection snapshots, remember that the snapshot is a Db2 table – thus, it would be easy to add logic to your CL program to perform a save with compression to a save file.

The advantages and simplicity of Plan Cache snapshot scheduling should be clear, so get 2023 off to a good start by getting your snapshots scheduled. 

Confounding and Constricting Concatenations

 This month’s title is a bit of a tongue twister, but the title does capture a problematic SQL coding practice with concatenations that has been popping up in recent SQL Performance Assessments performed by our IBM Technology Services team. Speaking of tongue twisters, did you know that MIT researchers developed the toughest tongue twister in the world of “Pad kid poured curd pulled pod”? I have no idea what this phrase means, but I do know about the confusion and poor performance that can be caused by suboptimal usage of the SQL concatenation support. 

Let’s start by looking at the concatenation coding practices that we’ve been finding when analyzing customer’s SQL requests. The following two SELECT statements contain the subpar coding pattern of comparing the concatenation of two column values with the concatenated result of two other columns. This concatenated comparison shows up on the join condition of the first query and the WHERE clause of the second query.

SELECT * 

  FROM tab1 a INNER JOIN tab2 b
   ON a.c1 || a.c2 = b.c1 || b.c2


SELECT * FROM tab1 a
   WHERE CONCAT(a.c1, a.c2) IN
           (SELECT CONCAT(b.c1, b.c2) FROM tab2 b)

 

The alternative to the concatenated column comparisons is comparing the individual columns and then logically ANDing the results of those individual comparisons (e.g., a.c1 = b.c1 AND a.c2 = b.c2). When comparing the two approaches, I believe that the individual column comparisons are easier to read and understand. While this opinion can be debated as programmer preference, there’s no debating that the concatenated column comparison will result in slower query performance.

 

There are a couple of reasons that this concatenation coding practice is a poor performer. First of all, comparisons that include an expression or derivation severely limit the query optimizer’s ability to estimate how many rows in the table will be selected or processed. When this number of rows estimate generated by the query optimizer is inaccurate, there is a greater chance for poor query performance. Second, the concatenated columns comparison prevents the use of normal indexes to speed up the query execution. Indexes are often the fastest way to perform the specified column comparison, but normal indexes are not an option due to the concatenated expressions in these two examples. Yes, a derived key index could be created that includes the concatenation in the column example. However, there’s a good chance that you already have a normal index created over the columns being concatenated. 

 

Now that you understand the drawbacks of the concatenated column comparisons, let’s look at the optimal way of coding these two example queries. As you can see, the join query can be rewritten in one of two ways to improve performance and readability.

SELECT * 
  FROM tab1 a INNER JOIN tab2 b
   ON a.c1 = b.c1 AND a.c2 = b.c2

SELECT * 
  FROM tab1 a INNER JOIN tab2 b
   ON (a.c1, a.c2) = (b.c1, b.c2)

The second join query utilizes a row value expression to simplify the join condition by removing the need to include the logical AND operator. These two queries will perform exactly the same, so this is strictly a programming style preference when deciding which syntax to use.

 

The rewrite of our second example SELECT statement also uses the row value expression syntax to eliminate the concatenated column comparison. 

SELECT * FROM tab1 a
  WHERE (a.c1, a.c2) IN 
   (SELECT b.c1, b.c2 FROM tab2 b)

Like many programming languages, SQL offers more than one way to get your work done. While this flexibility can be a good thing, you have also now learned that not all SQL solutions are created equal in terms of performance.

This is my last entry for 2022 - have a Merry Christmas & wonderful holiday season and we’ll talk in 2023!

Another Reason to be Thankful for SQL VALUES

 Last year, I highlighted the performance advantages of writing tableless queries with the SQL VALUES support. In the spirit of the upcoming US Thanksgiving holiday, I want to point out another reason developers can be thankful for the VALUES support in SQL.

The VALUES support comes in two flavors, VALUES & VALUES INTO, just like the SQL SELECT syntax. The VALUES INTO and SELECT INTO statements enable you to run a query that generates a single row and assign the values in that row to variables. While both statements provide the same capability, the VALUES INTO statement has the added advantage that it can be dynamically prepared and executed. This advantage is a big one for SQL developers because it allows you to implement a solution with less SQL code.

To help you understand why developers should be thankful for the dynamic SQL support of VALUES INTO, let’s look at the hoops you had to jump through prior to the VALUES support. Since the SELECT INTO statement could not be dynamically prepared, the only way to assign the results of a dynamic query to variables was through the usage of a dynamic SQL cursor. 

Let’s assume that a program needs to return the count of rows in a table (e.g., MYTABLE) and dynamic SQL is required because that table can reside in many different libraries. The SELECT version of this solution would look like the following set of code. First, the count query statement text is assembled and prepared. Next, a cursor for that prepared statement must be declared and then that cursor must be opened, fetched, and closed.

STMTTXT = 'SELECT COUNT(*) FROM ' TBLIB + '/MYTABLE';

EXEC SQL 
   PREPARE QRY_ROWCOUNT FROM :STMTTXT;

EXEC SQL 
   DECLARE ROWCOUNT_CUR CURSOR FOR QRY_ROWCOUNT;

EXEC SQL 
   OPEN ROWCOUNT_CUR;

EXEC SQL 
   FETCH ROWCOUNT_CUR INTO :ROWCOUNT;

EXEC SQL
    CLOSE ROWCOUNT_CUR;

A lot of code and work to just run and save the results of a simple row count query. 

Now, let’s examine how the VALUES INTO statement provides for a simpler solution. The fact that this VALUES INTO implementation requires half as much code should make it clear why this is a preferred solution.

STMTTXT = 'VALUES (SELECT COUNT(*) FROM ' TBLIB + '/MYTABLE) INTO ? ';

EXEC SQL
   PREPARE QRY_ROWCOUNT FROM :STMTTXT;

EXEC SQL
   EXECUTE QRY_ROWCOUNT USING :ROWCOUNT;

 

The VALUES INTO support enables the simple execution of a dynamic query without the coding overhead of using an SQL cursor.

The fewer lines that you have to code means less code that you have to test and maintain going forward. And those are great reasons to be thankful if you’re an SQL developer!