Latest IBM i TR adds Query Supervisor to Db2 & Your Team!

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.