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 😊

Got Shared CTEs?

This entry title harkens back to the Got Milk? advertising campaign. Who would have guessed that this campaign goes back almost 30 years?!? As they say, time really flies… now, back to the topic at hand.

If you’re reading this entry, hopefully you already know that the CTE acronym in the SQL world stands for Common Table Expression.  Even if you already knew what a CTE is, you may not understand what the term “shared CTE” is referring to.  A shared CTE is the term used to describe any CTE that is referenced more than once on a query definition.

Here’s an example of a query that contains a shared CTE.  In this query, the staff CTE is referenced twice in the main query definition which qualifies the staff CTE to be categorized as a shared CTE.

WITH staff (deptno, empcount) AS
   (SELECT deptno, COUNT(*) FROM employee
    GROUP BY deptno)
SELECT deptno, empcount FROM staff
WHERE empcount = (SELECT MAX(empcount) FROM staff)

In contrast, the following query contains multiple CTES, but no shared CTEs.  Each of the CTEs (top10_2020 & top10_2021) are only referenced once in the main query, so they don’t meet the criteria of a shared CTE.

WITH top10_2020 (customer_name, total_sales
    (SELECT customer_name, SUM(sales_amt) FROM sales
     WHERE year=2020
     GROUP BY customer_name
     ORDER BY SUM(sales_amt) DESC
     FETCH FIRST 10 ROWS ONLY) ,
   top10_2021 (customer_name, total_sales) AS
     (SELECT customer_name, SUM(sales_amt) FROM sales
      WHERE YEAR=2021
      GROUP BY customer_name
      ORDER BY SUM(sales_amt) DESC
      FETCH FIRST 10 ROWS ONLY)
SELECT Y1.customer_name, Y1.total_sales AS sales2020, 

       Y2.total_sales AS sales2021
   FROM top10_2020 Y1 INNER JOIN top10_2021 Y2
       ON Y1.customer_name = Y2.customer_name                

Knowing whether or not a CTE is a shared CTE is significant because an SQL Standards compatibility fix was recently delivered in the IBM i 7.5 release for SQL statements with shared CTEs. This fix had to be made because there’s a possibility that some queries with shared CTEs may return incorrect results if the queries are run while the tables referenced by a shared CTE are being changed. The fix will guarantee as dictated by the SQL standards that each reference to the shared CTE generates the same result set. The fix delivered by IBM may cause some queries with shared CTE references to run slower and some queries with shared CTEs to run faster.

Now, you may be thinking that it will be a long time before your company installs the IBM i 7.5 release, so why pay attention to this change. The reason that you should pay attention is that the code fixes for shared CTEs that were made for the IBM i 7.5 release will eventually be delivered as PTFs for the IBM i 7.4 release (NOTE: Level 23 of the 7.4 Database Group PTF includes the shared CTE fix). IBM recommends analyzing queries with shared CTEs before the PTFs are delivered so that clients understand the possible impact of these PTFs and have time to change their SQL statements, if needed.

To help with this analysis effort, IBM in late 2021 delivered PTFs for IBM i 7.3 and 7.4 that flag SQL statements with shared CTEs in both Plan Cache Snapshot and SQL Performance Monitor collections. In addition, these flags classify whether or not a Shared CTE is estimated to generate a large result set. Shared CTEs that generate a large result set have a greater chance of having performance issues after the fix is delivered as compared to CTEs with a smaller result set size. However, all SQL statements using shared CTEs have the potential to perform differently once the PTFs are applied.

It is possible to predict the possible performance impact of the future PTFs on SQL requests containing Shared CTEs with a simple coding change.  This simple change involves adding the following predicate, AND RAND() IS NOT NULL, to the CTE that is shared (i.e., referenced multiple times) on the SQL request. This predicate forces the Db2 query optimizer to use the same CTE runtime implementation which is used by the fix in the IBM i 7.5 release.

The IBM development team has published a detailed writeup which includes details on the SQL statement flagging that will aid analysis on 7.3 & 7.4 along with possible coding changes that you may want to consider. Our Db2 team in IBM Expert Labs can also be engaged to provide additional assistance, so let me know if we can help.

Before I close, I also want to highlight that the licensing change that I highlighted in last month's entry is effective starting June 1, 2022.  This change converts Db2 Add-On features such as Db2 SMP and Db2 Multisystem into no additional charge features for all of the IBM i 7.x releases (7.1, 7.2, 7.3, 7.4, 7.5)

What's New with Db2 for i 7.5

The announcement of a new software release is an exciting time since you get to discover all the new enhancements that can make your life easier as a software user or consumer. Without further ado, let’s dive into some of the new capabilities that are available with the Db2 for i 7.5 release.

You will probably quickly notice that Db2 for i 7.5 doesn’t contain any heavy hitters like Temporal Tables & RCAC that were delivered in prior releases. However, the latest release does contain an impressive of collection of smaller improvements that enhance SQL development, data security, Db2 performance & scalability, and DBE enablement. So instead of having to make room for a couple of big tools in your IBM i toolbox, you’ll be dropping in an assortment of smaller tools. 

 

On the SQL development front, you will find a new data type and a built-in function. Boolean is the new data type available to SQL developers. This new Boolean type will enable your programs to be more strongly-typed and easier to understand.

CREATE TABLE supplier (suppID INT, suppName VARCHAR(10), 

                       activeSupp BOOLEAN);

INSERT INTO supplier
   VALUES(1, 'ABC CO', TRUE),
         (2, 'ACME', FALSE),
         (3, 'IBM', NULL);

SELECT suppName FROM supplier WHERE activeSupp IS TRUE;

 

The new TRY_CAST function enables easier identification of invalid column values since it returns the NULL value when invalid data is detected as opposed to signaling a hard failure like the CAST function.

 

The IBM i 7.5 release contains several enhancements that tighten access to the valuable business data stored in your Db2 for i databases. In addition, the new RESTRICT ON DROP clause can be used to prevent accidental deletion of your data. Once this setting is added to your Db2 tables, you no longer have to worry about someone accidentally wiping out a table by typing in the wrong table name on an SQL DROP statement or the DLTF CL command. Any tables using this setting cannot be deleted until someone runs an SQL ALTER statement to remove the setting.

 

The Db2 performance and scalability improvements provide constructs to allow you to both scale the size and performance of your databases. The maximum size of Db2 indexes has been increased from 1.7 TB to 4-16 TB.  The new maximum size is dependent on the logical page size of your existing indexes. 16 TB is the new max size for SQL indexes since they're created with a 64K logical page size. The good news is the maximum size does not require a rebuild of your existing indexes. This larger index maximum size removes one of the last limits to growth barriers for Db2 for i databases. Prior to the new release, partitioned tables allowed clients to grow tables way past the 1.7 TB size limit, but non-partitioned indexes over partitioned tables were still stuck with the 1.7 TB max size. With Db2 for i 7.5, index size should no longer be a concern when moving to a partitioned table implementation.

 

Speaking of table partitioning, the IBM i 7.5 release is the latest release to includes a licensing change to the software feature, Db2 Multisystem, which enables the usage of partitioning. Db2 Multisystem is now a no charge feature instead of the chargeable feature. The no charge licensing change also applies to Db2 SMP and the High Availability Journal Performance features: journal caching and journal standby. It’s on my to-do list to my update my recent Db2 Add-Ons entry to highlight that these four features as of June 1, 2022 are no longer chargeable for the IBM i 7.x releases (7.1, 7.2, 7.3, 7.4  and 7.5). Just because Db2 SMP & Db2 Multisystem are simpler to obtain, does not mean that you should rush out to use them. These two features work best when careful planning and preparation are part of the deployment process and our IBM Expert Labs team has the expertise to assist in that process.

 

Db2 SMP also has some interesting enhancements in the latest release. First, the optimizer was enhanced to do a more accurate optimization with *OPTIMIZE % setting for the PARALLEL_DEGREE query option. This improvement should result in a more balanced use of system resources when a query is running with this setting. Second, DBEs can also protect their system CPU resources from being overrun by queries using parallel processing with the new PARALLEL_MAX_SYSTEM_CPU query option. Let’s say you set this option to 80 (which is the default) - once your server CPU utilization exceeds 80%, Db2 will automatically start throttling back the number of parallel threads used by Db2 for parallel processing until the server CPU utilization drops below 80%.  As you can see below, the Plan Cache Properties were updated to give DBEs insights into how many SMP queries have been automatically governed by Db2 due to this new query option.  The Total Number of Queries with Runtime Adjusted SMP Degree property is used to track this event – these properties show that no automatic SMP throttling has occurred yet on the system.

Temporary index tracking and analysis is also simpler with Db2 for i 7.5 thanks to some new Plan Cache Properties as well as a new service – MTI_INFO. In the figure below, notice the MTI related properties now have their own properties section and there are new properties to track the usage of non-reusable temporary indexes. 

If these temporary index metrics start to rise, then DBEs will have a much easier time getting the details for each MTI with the new MTI_INFO service.  The following query shows how straightforward it is to generate a list of all the MTIs currently created over the tables in a specific schema. The Table_Schema parameter could be changed to *ALL, if one needed a list of all the MTIs on the system.

SELECT * FROM 

 TABLE(MTI_INFO(Table_Schema => 'TOYSTORE3', Table_Name => '*ALL'))

 

This figure shows just a subset of the details returned by the new service for each MTI.

One last item to highlight is a Db2 usability enhancement in the newest version of the IBM i Access Client Solutions (ACS). Usability of Run SQL Scripts is improved with the addition of file tab support. As you can see in the figure below, this new support allows you to easily jump back and forth between SQL scripts. Providing a big productivity boost when you need to make similar changes to multiple SQL script files. I’ve also found it quite useful when developing and testing a new SQL routine. The file tabs allow me to keep my SQL routine source separate from the SQL that I use to test out the new routine.

Hopefully, these Db2 for i 7.5 highlights have you gotten you excited to try out the new release.  Although there are not any major Db2 enhancements in this release, remember that Aesop once said, Good things come in small packages.  More complete details on the Db2 for i 7.5 release can be found here. 

Look Before you Merge

The more common version of this title phrase is look before you leap. However, this same recommendation applies to driving a car – if you don’t look before you merge your vehicle into traffic, there’s a good chance that eventually you will either damage your car or cause a car accident. In this entry, you will also see why this is also good advice from a performance perspective when using the SQL MERGE statement.

 

The MERGE statement has been available for use with Db2 for i since the IBM i 7.1 release. The merge support was designed for logic where you’re taking data from a source table/query and using that data to perform either an insert or update operation on the target table. If the row already exists in the target table, then you want to “add” to the existing row by performing an update; if the row doesn’t exist, then a new row needs to be inserted into the target table. In fact, some DBMS products provide this feature with an UPSERT statement since it is a database operation statement that support both insert and update operations.

 

The following MERGE statement merges data into an account summary table by updating the balance from the set of transactions for an existing account and inserting a new balance for the new accounts. 

                 

MERGE INTO account_summary AS a
   USING (SELECT id, SUM(trans_amount) sum_amount FROM trans 
          GROUP BY id) AS t 
     ON a.id = t.id
   WHEN MATCHED THEN UPDATE SET balance = a.balance + t.sum_amount
   WHEN NOT MATCHED THEN
      INSERT (id, balance) VALUES (t.id, t.sum_amount)

While the Insert & Update combo is the sweet spot for the MERGE statement, the syntax actually supports many different use cases including the ability to delete rows from the target table. While recently assisting clients with SQL performance tuning, our IBM Expert Labs team has encountered clients that are using the MERGE statement as an alternative syntax for performing update operations. The same data changes could have been coded with an UPDATE statement, but the developers chose to use the MERGE statement instead. 

 

Developers at these shops made this choice because they found the MERGE statement syntax easier to use.  When you compare these two equivalent data change operations, you can see how some developers may find the MERGE version to be simpler to understand.

MERGE INTO employee 
  USING (SELECT jobcode, jobrate FROM jobinfo) j
      ON (empcode = j.jobcode)
    WHEN MATCHED THEN UPDATE SET emprate = j.jobrate
    ELSE IGNORE
 

UPDATE employee 
  SET emprate = (SELECT jobrate FROM jobinfo j 
                  WHERE j.jobcode = empcode)
  WHERE empcode IN (SELECT jobcode FROM jobinfo)

 

A developer should definitely consider how easy an SQL statement is to code and maintain during the coding process, but a developer also needs to make sure they “look” at all coding factors before taking the “leap”.  Performance was a factor that was overlooked during the development process in the client situations that our team was recently involved with.

 

To be fair, the IBM documentation currently does not clearly spell out the performance considerations for the MERGE statement. So here are some key performance factors based on the internal Db2 implementation that should be considered when you weigh using MERGE as an alternative for an UPDATE request.

–       Query plan will create & populate a temporary data structure identifying the target rows in the target table

–       Merge processing applies update locks to every target row in the target table based on the ON clause before performing any updates. 

–       Merge update process results in extra read lock being applied to every updated row.

–       With No Commit, Update & read row locks are held until the end of the Merge statement

 

The net of these performance factors is that a MERGE statement will acquire twice as many row locks as the equivalent UPDATE statement.  In addition, all of those row-level locks are held by Db2 until all of the target rows have been updated by the MERGE statement even with an isolation level of No Commit.  In contrast, an UPDATE statement running under No Commit will release the row locks as soon as the change to the target row has been completed. 

 

If your MERGE statement only updates a small number of rows in the target table, the overhead causing by the database locking is probably not going to be noticeable. However, MERGE statements that end up changing thousands of rows have the potential to generate significant performance overhead on your system. One client used a MERGE statement to update about 550,000 rows – the statement held over 1.1 million row locks at the time the last row was updated even with an isolation level of No Commit! The equivalent UPDATE statement running No Commit would have held only a single row lock at any point in time while it changed the 550,000 rows. 

 

Like many things in IT, there are tradeoffs with the solutions that you can implement.  Hopefully, you now have a much better understanding of the performance tradeoffs between a MERGE and the equivalent UPDATE statement.

Tools for the Db2 for i DBE

In my prior entry, I reviewed the roles and responsibilities of a Db2 for i Database Engineer (DBE). This time around I want to highlight the tools that Db2 for i DBE should have in their toolbox.

The first tool in the list, IBM i Access Client Solutions (ACS), should come as no surprise. When it comes to Db2 for i SQL performance analysis and management, the ACS SQL Performance Center is the clear choice. This tool enables you to easily drill into the details of the longest running queries on your system with Visual Explain as well as get a reading on the overall health of SQL performance with the Plan Cache properties. 

 

In addition to supporting the creation & management of Db2 for i SQL objects, the ACS Schemas tool also houses the system-wide index advisor and index evaluator.  Index evaluator is not a term you’ll find within ACS, but it’s the label that I use to describe the “Work with -> Indexes” task you can access when right-clicking on a table object to quickly understand which indexes on your system are being used by the optimizer to improve query performance and which indexes are candidates for removal. 

 

The Schemas tool also serves as the launch point for the Db2 Health Center. The Health Center enables you to track how your database objects and activity are doing from the perspective of system and Db2 limits such as the maximum number of rows in table or the maximum size of a table.  My IBM Rochester colleagues recently released a new video highlighting how to access and use the Health Center.

 

The last ACS component to highlight is Run SQL Scripts.  This tool can be used to run SQL database creation scripts and provides access to Visual Explain for detailed SQL performance analysis. One can also launch a graphical debugger to debug procedural SQL objects such as functions and procedures.

 

Run SQL Scripts also facilitates interaction with the second DBE Tool – Db2 for i Services.  There are many services available to help with the managements of Db2 for i object management and performance. For example, there are services that can be used to regularly capture a plan cache snapshot which can be useful when trying to determine the cause of changes in SQL performance.  In fact, the Run SQL Scripts Edit pull-down menu makes it easy to find examples of these services in action so that you don’t have to start from scratch.

For the database modeling and design activities that I highlighted for a Db2 for i DBE, I think there are two viable modeling tools. Back in 2015 before I left for my 5 year stint working in IBM Watson Health, there was much a longer list of industry data modeling tools that I suggested to IBM i clients. After spending the last couple of months reviewing the Db2 for i support in industry data modeling tools, however, I found many of these tools have not kept current with all of the great enhancements made to Db2 for i.  As a result, my view is that IBM InfoSphere Data Architect and XCase for i are the only modeling tools that I would recommend Db2 for i.  The XCase support for Db2 for i also include other interesting data-centric tools that support test data generation and the anonymization of sensitive data. 

 

On the database security front, IBM Security Guardium is a tool that can be helpful to DBEs.  This tool can help find sensitive data in your Db2 for i databases and capture details on how the data in your Db2 for i databases is being accessed.

 

Good tools make any job easier by allowing you to complete tasks more efficiently. If you’re not using some of these DBE tools yet, then now is a great time to start and our IBM Expert Labs team can help you get off to a good start.