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!

 

Don’t “Fall” Behind, Rake In the Latest Db2 Enhancements

 This entry title should make it easy to guess that my recent activities in Rochester, Minnesota have involved cleaning up the colorful leaves that drop in the Fall and enjoying the great outdoors while raking. While this time of year is a great for enjoying the cooler temperatures outside and the unique colors produced by Fall, it’s also a time of year when IBM i Technology Refreshes (TRs) are normally announced – that in fact occurred this week for the IBM i 7.4 & 7.5 releases.

A new IBM i TR signifies there are new Db2 for i capabilities that can make your job easier which means you and your company will be left behind without a plan to load the latest Database Group PTFs. Here I highlight some of the more interesting Db2 enhancements associated with the newly announced IBM i TRs. You will want to reference the Db2 for i Technology Updates site for a complete list of the enhancements.

Normally the Database Group PTFs contain a set of smaller Db2 enhancements, but this latest iteration contains a couple of major enhancements. These doozies include the integration of Watson technologies into Db2 for i in the form of Geospatial Analytics support and a new SQL Error Logging Facility.

Instead of having to reach out to an IBM Watson service to analyze spatial data, Geospatial Analytics enables all of this data to be stored and analyzed in your Db2 for i databases. This integrated support includes new data types for storing spatial data and new functions to process, analyze, and compare this spatial data. 

The following example demonstrates usage of the ST_POINT data type to store the geographic location of a store in the location column and the ST_POLYGON type to capture the geographic area that store location serves in the sales_area column. 

CREATE TABLE stores
 (id INT, address VARCHAR(100), city VARCHAR(50), 

  postal_code VARCHAR(5), location ST_POINT, 

  sales_area ST_POLYGON)

The following Update statements shows one of the Spatial functions, ST_BUFFER, in action. This new function is used to update the sales area column for a specific store to a circular area around the store location with a diameter of 40 kilometers.

UPDATE stores
  SET sales_area = ST_ToPOLYGON( ST_BUFFER(location, 40000) )
  WHERE id = 33

With that geographic sales area now define, you can use the ST_WITHIN function as demonstrated on the following query to identify customers that are located with the store’s sales area (this assumes you have the geographic location information for your customers). The result set generated by this query would enable your company to easily target only the customers that reside near this new store location.

SELECT c.first_name, c.last_name, c.mailing_address, c.city,

       c.postal_code
FROM customers c, stores s
WHERE s.id = 33 AND ST_WITHIN(c.location, s.sales_area)=1

The new SQL Error Logging Facility is also known as SELF. The SELF acronym reinforces that this new Db2 feature makes it easier for you to pinpoint specific SQL errors or warnings. This facility can be used to log information for a specific SQL error systemwide or scope the tracking to a specific job. The logged information not only includes the SQL statement that caused the error/warning, but it also includes the call stack for the job that first encountered the error/warning.

 

The new REPLICATION_OVERRIDE global variable can be used to simplify the process of copying or deploying your database tables to new systems. Previously if your tables contained generated columns based on special registers such as CURRENT CLIENT_PROGRAMID or built-in global variables like QSYS2.JOB_NAME, there was no way to override the generated value behavior like you could for identity or row-change-timestamp columns. This new global variable can be used to control the automatic value generation for all generated column types.

On the performance front, there’s another new option to control the number of queries on your system that can the leverage the parallel processing enabled by the Db2 SMP feature. The new PARALLEL_MIN_TIME option adds another parallel processing control to complement the PARALLEL_MAX_SYSTEM_CPU option delivered with GA of the IBM i 7.5 release. Parallel processing is best applied to longer runner queries on your system because there’s overhead involved in Db2 breaking a query into multiple parts that can be run across multiple processors concurrently and then gluing the results from the parallel threads back together. The new PARALLEL_MIN_TIME option enables you to have the Db2 query optimizer only consider applying parallel processing to queries that run longer than the specified minimum. The default value for this option is 60 seconds and is only applicable when the parallel degree value is *OPTIMIZE.

The Db2 for i graphical interface provided by IBM i Access Client Solution (ACS) also provides several Db2-related enhancements. If you spend as much time using ACS Run SQL Scripts as me, then I think you’ll agree one of the biggest improvements is the ability to enable Auto Save for your Run SQL Script sessions.

I’ve highlighted just some of new Db2 for i features that will be available with the upcoming Database Group PTFs for the IBM i 7.4 and 7.5. You need to make time to review the complete list and to make a plan to get these enhancements loaded on your system, so that you don’t “fall” behind in terms of the Db2 features and functions available for you to use.   

Why SQL... for the Money?

Our Db2 for i team in IBM Technology Services has touted the advantages of using SQL for many years at conferences and user-group meetings. Recently, the following headline in my newsfeed caught my attention with an SQL advantage that has not been directly touted in the past: Money

Python is the most-loved language, but SQL helps make More Money

While the Python part of this headline is not as applicable to IBM i developers, the SQL portion is definitely applicable to programmers in the IBM i world. 

Our team has pointed out for years that one reason to use SQL is that it is the de facto industry standard. Indirectly, this reason does have some monetary benefits. The linked article states that SQL is a leading desirable skill. Thus, using SQL with Db2 for i improves your marketability in the IT world if you ever chose to leave the greatest platform and database in the world. You also don’t necessarily need to leave your current position to cash in on your SQL skills. Developers that add new skills to their toolset make themselves more valuable to their company which in turn makes it easier for a company to reward that added value with a salary increase.

SQL also provides a monetary benefit to your company because it helps IT deliver on business requirements faster. Data-centric programming with SQL enables developers to focus their efforts on delivering business logic and relieves them from the responsibilities of implementing relational data processing. The result is a reduction in the amount of code that developers have to write and maintain as they rely more on the Db2 engine to take care of the implementation details. The SQL Rollup and Sequence features are good examples of this ability to deliver more with less code. The Rollup support allows a report to return multiple levels of aggregation (Country and Country & Region) with a single SQL statement. Similarly, a Sequence object enables you to hand over ID or key generation logic over to Db2 for i.

While there are other advantages to using SQL, hopefully the monetary advantages for you and your company are apparent. Let me know if your company needs help reaping this benefit of SQL – the Db2 for i team in Technology Services is here to help.

The Right Way to Compare Apples & Oranges with SQL

 Conventional wisdom says that you shouldn’t compare apples and oranges and that’s also a best practice you should be following when programming with SQL. Avoiding apples and oranges comparisons with SQL comes down to coding search predicates (CompanyNameCol = 'IBM') where the two values being compared are the same in terms of data type and precision. 

In the cases where the search value differs from the column definition, SQL provides a rich set of built-in functions to make it easy to turn an apples and oranges comparison into an apples-to-apples comparison. When using these built-in functions on comparisons, you need to be aware that there is a right way and a wrong way to using these functions. Both ways will functionally work, but the wrong way will result in subpar performance and more system resources being consumed. 

A couple of recent SQL performance reviews with clients revealed that there are way too many developers using built-in functions the wrong way. The following example contains a WHERE clause found in a recent Technology Services engagements where SQL built-in functions are used the wrong way on search predicates to make sure that like values are being compared (i.e., apples to apples)

SELECT * FROM SomeTable    
WHERE TO_NUMBER(TO_CHAR(Last_Change_TS, 'YYYYMMDDHH24MI'))
                                > 202205101702.000000000000000
          AND TO_NUMBER(TO_CHAR(Last_Change_TS, 'YYYYMMDDHH24MI'))
                               <= 202205102102.000000000000000

The Last_Change_TS column was defined in the client’s table with the timestamp data type, so the SQL request was coded to utilize a combination of SQL built-in functions to convert the timestamp value into a numeric value that is directly comparable with the numeric search value. 

While these search predicates generated the correct result set, performance was not acceptable due to how the functions were utilized. Placing the functions on the left-hand side of the comparison means that Db2 must convert every timestamp value in the Last_Change_TS column into a numeric value before the comparison can be made with the search value. The client’s table contained over 200 million rows, so that means this numeric to timestamp conversion was being performed over 200 million times each time the query was run. All these conversions consumed a significant amount of CPU resources on their system.

Not only does this “wrong way” SQL coding slow performance with CPU intensive data conversions - to make matters worse, it prevents the Db2 query optimizer from using a “normal” index to quickly filter and return the rows that meet the specified search criteria. The client’s queries were often returning less than 100 rows, but the poor SQL coding practice was forcing all of the rows to be searched.  An index with a leading key of LAST_CHANGE_TS would limit the query processing to only the rows meeting the search criteria.

Now, you might be thinking that I could create a derived key index to address the performance issues of this “wrong way” SQL coding. While that is a viable consideration, there are a couple of items to think about. First of all, a derived key index cannot be created for this example query. Not all SQL built-in functions are created equal - the TO_CHAR function is implemented as a Db2 user-defined function which means that TO_CHAR cannot be referenced on an index key definition. Second, even if the functions were allowed on the key definition – you’re creating a derived key index that may only provide performance benefits to a small number of queries on the system. Third, you’re paying the cost of that data conversion each time that a derived key index is maintained on the system. 

It’s okay to create derived key indexes to improve query performance, but I’d recommend saving those for situations where the SQL statement cannot be changed or it’s very difficult to change.

So, the easiest way to remember to use SQL functions the “right way” on search predicates is to always code the SQL functions on the right-hand side of the predicate. The following shows how the client query was rewritten to utilize the functions to make the right-hand side of the predicate directly comparable:

SELECT * FROM SomeTable    
WHERE Last_Change_TS > TIMESTAMP(CHAR(202205101702))
          AND Last_Change_TS <= TIMESTAMP(CHAR(202205102102))

Query performance improved dramatically because the numeric to timestamp conversion was performed twice instead of millions of times and a “normal” index over the Last_Change_TS column was used to quickly find the rows meeting the search criteria.  For best practices on indexing, check out our indexing strategy white paper.

Hopefully, you now have a good understanding of the right way and wrong way to use SQL functions to convert an apples to oranges comparison to an apples-to-apples comparison.  And by the way, if you’ve never played the Apples to Apples game, it is a fun game to play at parties and family gatherings.

DBA/DBE Appreciation Day

Not long ago, I discovered that DataBase Administrator(DBA)  Appreciation Day is observed annually on the first Friday in July which happens to be today!  I’ve recently discussed that although some of the traditional DBA tasks don’t apply to Db2 for i, that there is a real need for a Database Engineer (DBE) in IBM i shops. 

Based on our IBM Expert Labs interactions with clients, I think it will be difficult for IBM i shops to show appreciation to their DBEs on July 1 because far too many shops do NOT have a DBE or a DBE team.  Our team often sees the negative impact of the missing DBE during IBM i client engagements where we are brought into assess and analyze their SQL performance & Db2 for i databases. 

Because there’s not a DBE focusing on the database objects and data access, our team regularly sees issues like:

• Queries that are poor performers that can be easily fixed with the creation of an index
• Large tables that have grown past 90% of the Db2 size/row maximum limit – when the limit is reached no more rows can be added by your application!
• Overcommitment of system resources due to a system-wide parallel degree setting of *MAX for the Db2 SMP feature.
• Incorrect usage of SQL routine or program settings that are unnecessarily slowing performance
• Low SQL Plan Cache hit ratio due to runaway usage of QTEMP tables by developers
• Queries being run against tables with 60-70% of the rows deleted because tables are not being reorganized on a regular basis or they’re not configured to reuse deleted rows

It can be a challenge to find a qualified Db2 for i DBE, but that’s where IBM Expert Labs team can help with our DBE skills enablement offerings.  These services can help grown an existing IBM i team member into the DBE role or help convert a DBA from another platform into a DBE. So if you don’t have a DBE on your IBM i team to appreciate today, then make a goal to have one by DBA/DBE Appreciation Day 2023! 

I also discovered that July 1 holds the designation of International Joke Day as well, so I’d be remiss if I didn’t close out this July 1 focused entry with a database-related joke: 

Did you hear about the two relational databases that walked into a NoSQL bar. They left after 5 minutes.... 

    because they couldn't find a table! 🙃

No comments on the quality of my joke unless you’re willing to include your own database joke in the comment 😊