[[{“value”:”If you have kids, there’s a pretty good chance they’ve
asked you at some point to open Christmas gifts early. The anticipation and excitement
associated with their gifts makes it difficult to wait until Christmas to open
gifts. Why wait, when you can do it now!
Speaking of new and exciting things, the
1.1.9.7 version of IBM i Access Client Solutions (ACS) includes some
significant enhancements to the SQL Performance Center. The good news is that
you don’t have to wait until Christmas because this update is available now!
These
enhancements enable you for the first time to perform all your SQL performance
analysis and tuning using just the SQL Performance Center. In prior versions,
the ACS Schemas tool had to be used any time that you wanted to use the Index
Advisor. As you can see in the figure below, a launchpad (i.e., green box) has
been added to the top of the SQL Performance Center that allows easy access to
index advice.
The red box in this figure highlights that other data
related to SQL performance can also be readily accessed from the
launchpad. The Maintained Temporary
Indexes option makes it simple to find all the temporary indexes on your
system using the MTI_INFO service that I’ve highlighted in the past. The Active
Query Info option streamlines the process of identifying active queries on
your system by returning the results from the ACTIVE_QUERY_INFO service which
also has been recommended on this blog.
The performance
data returned by these two new options can be sorted by clicking on a
column. However, the output data cannot be
filtered at all. To counteract this limitation, the output for these two new interfaces
(as well as other ACS interfaces) has been enhanced to include a new SQL widget
button highlighted in the figure below. This SQL widget option allows you to
easily access the SQL statement used to return the output. This SQL statement
text can be copied into a Run SQL Scripts window. Then, you can add a WHERE
clause to the query to return a filtered set of performance data (e.g., by job
or by table).
The updated SQL Performance Center also has a more
integrated solution when it comes to analyzing the Plan Cache entries at an ASP
(auxiliary storage pool) level. A couple of years ago, I wrote about
the need to consider Independent ASPs when reviewing Plan Cache entries or
creating a Plan Cache Snapshot. The default setting was to only show queries
run against tables created in the System ASP.
The default setting is still the same with the latest SQL Performance
Center, but now the ASP selection only appears when you are accessing an IBM i
server that has an Independent ASP attached. When an active Independent ASP
exists, the ASP (i.e., Database) selection is displayed next to the button to
display statements in the Plan Cache.
Notice that the first figure above had no selection option
next to the Plan Cache Statements button. I think this visual clue is a great
reminder to consider which ASP you want to focus your query performance
analysis efforts on.
Now
that you have a better understanding of the SQL Performance Center
enhancements, I hope you agree that Christmas did come early with this ACS update.
I also hope that your corporate IT policies allow you to access this gift right
away:) Merry Christmas and I look forward to talking to you
in 2025!”}]]
