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
-- 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:
