Another Round of Db2 for i Enhancements

Today, IBM announced the content of its biannual delivery of new and enhanced IBM i capabilities via the upcoming TRs (Technology Refresh) for the 7.4 and 7.5 releases. As always, there are some nice additions to  Db2 for i to make your life as an application developer and/or Database Engineer (DBE) easier!

On the programming side of things, there’s a new aggregate function, ANY_VALUE, for your grouping queries. This addition should make your grouping queries easier to write and maintain by providing a vehicle to include non-aggregated values in your grouping results. 

Walking through an example is the best way to understand the flexibility that the new ANY_VALUE function offers. The following grouping query generates a list of the Top 10 clients in terms of sales revenue.

SELECT clientName, SUM(salesAmt) AS totSales
  FROM salesHistory
  GROUP BY clientName
  ORDER BY totSales DESC LIMIT 10

Let’s says the business needs the report modified to include the client’s email address to make it easier for them to contact and thank the client for their loyalty. Instinctively, a developer might just add that column to the Select List as follows:

SELECT clientName, SUM(salesAmt) AS totSales, clientEmail
  FROM salesHistory
  GROUP BY clientName
  ORDER BY totSales DESC LIMIT 10

 However, this change results in an SQL syntax error because the GROUP BY clause requires the Select List to only contain the grouping columns or an aggregate function - the clientEmail column clearly doesn’t fall into either category. Before the ANY_VALUE function, there were two possible workarounds as shown below – neither of them ideal.

-- Workaround #1, update grouping clause
SELECT clientName, SUM(salesAmt) AS totSales, clientEmail
  FROM salesHistory
  GROUP BY clientName, clientEmail
  ORDER BY totSales DESC LIMIT 10

-- Workaround #2, use MAX grouping function
SELECT clientName, SUM(salesAmt) AS totSales, MAX(clientEmail)
  FROM salesHistory
  GROUP BY clientName
  ORDER BY totSales DESC LIMIT 10 

The first workaround adds performance overhead to the grouping query. Adding the email address to the GROUP BY clause causes Db2 to have a larger grouping value that must be compared as part of finding all the rows that belong to each group. In addition, it reduces the number of indexes that could be used by Db2 to speed up the aggregation process. Now, the index key definition must start with clientName and clientEmail in order for it to be usable by Db2. Before the grouping clause change, any index that had clientName as the leading key could be used by Db2 to perform the aggregation.

The second alternative makes the query harder to understand. The next developer that comes along may wonder why the maximum function is being used when the client email address is a constant value. Usage of the maximum function also adds a little performance overhead because Db2 must go through the work of computing the maximum value – even though the email column value will be the same within a client group.

The ANY_VALUE aggregate function simplifies the query writing and eliminates the performance overhead by telling Db2 that any old value in the specified column (or expression) will do as shown in this example query.

SELECT clientName, SUM(salesAmt) AS totSales, 

       ANY_VALUE(clientEmail) 
  FROM salesHistory
  GROUP BY clientName
  ORDER BY totSales DESC LIMIT 10

If you started leveraging the GENERATE_SPREADSHEET service delivered in the last IBM i TR in your programs, then you may appreciate that service being enhanced to support more spreadsheet types and smarter column width generation for xlsx and xls files.

The latest version of IBM i Access Client Solutions (ACS) also includes some additions to help developers looking for SQL syntax help. The relatively new SQL Generators for Update and Delete statements were enhanced to generate more efficient SQL for non-nullable columns.  In ACS Run SQL Scripts, the performance of the Content Assist feature was improved to make it faster for those wanting to prompt their way through building an SQL request. The Content Assist feature for SQL statement building is launched by using the F4 key or Ctrl+Space.

ACS also includes new capabilities for the DBE. Specifically, new toolbar options to make customizing and interpreting Visual Explain output easier. Instead of having to navigate the Actions pull-down menu in Visual Explain, these new toolbar options make it easy to change the highlighting, icon labels, or arrow labels as you attempt to optimize the performance of your queries.

On the server-side, DBEs should also enjoy the enhancements to the DUMP_PLAN_CACHE and ACTIVE_QUERY_INFO. In the past, I wrote about the challenges of using the DUMP_PLAN_CACHE service with servers that utilize Independent Auxiliary Storage Pools (IASPs). The good news is that this service is no longer IASP challenged! A new parameter, IASP_NAME, has been added to enable the seamless collection of plan cache entries related to Db2 databases in your IASP as shown in this example.

CALL QSYS2.DUMP_PLAN_CACHE(FILESCHEMA=> 'SOMELIB',
                           FILENAME  => 'SNAP1',
                           IASP_NAME => '*ALL')

The usability of the ACTIVE_QUERY_INFO service was also greatly enhanced for those of you that are trying to monitor and track queries running in host server jobs like the QZDASOINIT jobs.  The original version of the service had a job user parameter, JOB_USER, to track queries for a specific user. However, the JOB_USER filter was not applied to the current user of host server jobs.  So essentially, there was no way to do user-level filtering for active queries running in host server jobs. That limitation is now gone with the addition of a new USER_NAME parameter that is applied to the current user of a host server job. The following example shows how you can easily target the active queries run by BADUSER in the QZDASOINIT server jobs.

SELECT * FROM
   TABLE(ACTIVE_QUERY_INFO(JOB_NAME=> 'QZDASOINIT', 

                           USER_NAME=> 'BADUSER'))

These are my highlights of some of the Db2 for i advancements in the latest IBM i TR, so I’d encourage you to review the complete list on the IBM i Technology Updates wiki.  Also, don’t forget that the Memorandum to Users (MTU) gets updated with each IBM i TR as well – many users mistakenly believe that the MTU only gets updated at the GA of a new release.  Here are the links to the MTU:

•  IBM i 7.5 MTU
•  IBM i 7.4 MTU
•  IBM i 7.3 MTU