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: