The latest IBM i Technology Refresh was announced this week which means there are new Db2 for i features and functions available to you. This latest announcement includes Db2 Mirror for i enhancements, SQL advancements, and new & updated IBM i services – however, the Query Supervisor is by far the most interesting new bell and whistle.
I like to remind IBM i clients that when they start using
SQL they’re actually extending the size of their development team. Your team
grows in size because the Db2 for i SQL engine features a Query Optimizer whose
job it is to determine the fastest way to access and retrieve the data. Your development
team is able to focus on the coding of business logic and leave the performance
logic to the Query Optimizer. This latest announcement adds another member to
your team with the introduction of the Query Supervisor.
The new Query Supervisor allows you to take query
governance and monitoring to another level. If you’ve been around the platform
for a while, you might be thinking that Db2 for i already supports query
governance and you would be right. The Db2
for i support for a Query Governor goes way back to V3R1.
The Query Governor is known as a predictive query governor
because it allows action to be taken before a query starts running. The
governor allows you to prevent a query from wasting system resources when the
query’s runtime implementation is predicted to exceed the time and/or temporary
storage thresholds that you’ve defined for your server. The predictive aspect
means that the Db2 for i Query Optimizer had to estimate the amount of time and
resources that it takes to run the requested query.
As you might imagine, generating an accurate estimate is a
tall task. One of the biggest hurdles is that the optimizer has no idea how
busy the system will be when the query starts running. A CPU intensive workload
could start running milliseconds after the current query starts and there’s no
easy to way to predict this. Thus, the optimizer estimates are not going to be
one hundred percent accurate. The optimizer’s estimates are relatively accurate
meaning that long-running queries will have larger estimates than small-running
queries.
This “relative” accuracy of the optimizer’s estimates made
it difficult to use the predictive query governor function system-wide. If you
set a query time limit of 5 minutes on the system, a query that runs closer to
two minutes might be prevented from running because the optimizer’s estimate
was 6 minutes. A query that was estimated to run in 3 minutes might actually
run for 7 minutes due to the system being busier than predicted. As a result of
these challenges, clients have asked IBM for additional query workload controls
and IBM has delivered on that request with the new Query Supervisor feature.
The Query Supervisor provides a solution for taking action
on an active query whose actual resource usage exceeds a user-defined
threshold. When the Db2 engine detects that your threshold has been exceeded,
it will call a user-defined exit program to take action. That action might be
cancelling the query or just logging it and notifying an administrator about
the offending query; the possibilities are endless. If the exit program chooses
to cancel the query, then one could argue that system resources were wasted by
a query that didn’t complete. However, the upside is that you were able to
limit the amount of time that the query caused overhead on your system. Like the Query Governor, the Query Supervisor
supports SQL and non-SQL queries (OPNQRYF, etc.).
The new Query Supervisor support allows you to set the following threshold types with a simple call to an SQL procedure - QSYS2.ADD_QUERY_THRESHOLD.
- ELAPSED TIME
- TEMPORARY STORAGE
- CPU TIME
- TOTAL IO COUNT
The threshold value is dependent on the type of threshold.
The threshold value for Elapsed Time and CPU Time would be specified in seconds
while the value specified for a Temporary Storage threshold would be given in
megabytes. The Total IO Count threshold value is just a count because it
specifies a limit on the number I/O operations that a query can perform.
With the Query Supervisor, you can also specify filters to
narrow the focus of the supervisor’s threshold monitoring. The threshold
filters that can be applied are subsystem names, job names, and user profile
names to include or exclude. The values for each of these thresholds can be up
to 100 names and can include generic names (eg, ‘RPTUSER*’).
Here’s an example of a threshold definition to prevent
QZDASOINIT jobs from having queries that run longer than 5 minutes, unless the
queries were submitted by a manager. Notice in this example that you’re able to
specify multiple threshold filters on the definition.
CALL
QSYS2.ADD_QUERY_THRESHOLD (
THRESHOLD_NAME => 'QZDASOINIT Time Limit ',
THRESHOLD_TYPE => 'ELAPSED TIME ',
THRESHOLD_VALUE =>
300 ,
JOB_NAMES => 'QZDASOINIT',
EXCLUDE_USERS => 'MGRID*',
SUBSYSTEMS => '*ALL' )
Thresholds can be easily removed by specifying the
THRESHOLD_NAME on the QSYS2.REMOVE_QUERY_THRESHOLD procedure
The user-defined exit program needs
to be registered to the new QIBM_QQQ_QRY_SUPER exit point. As mentioned earlier,
the exit program allows you take a wide variety of actions. That flexibility is
enhanced even further with the input values that are passed to the exit
program. Here’s some of the more interesting input values to consider utilizing
in your program logic:
–
Threshold name, type & value
–
User, Subsystem, and Job Info
–
SQL Statement text, Plan Identifier & Host Variable
values
– Client Register Values
To try out your new Query Supervisor team member, you just
need to load the Database
Group PTFs onto your IBM i 7.3 or 7.4 systems when they are released
next month.