IBM i Query Supervisor
Wouldn’t it be nice to be able to automatically monitor queries running on your IBM i to make sure that they’re performing well and not unnecessarily using up valuable processing resources. You can with the V7R3 (Technology refresh 10) Query Supervisor.
Applications basically retrieve data and process it to produce a result. An application written in RPG for example is created by a programmer who determines what information is to be retrieved and how that will be accomplished; which physical files to use; which logical files to use; which file access operation codes will be used; etc. An application is implemented in a controlled way and it does the same thing time after time. Performance problems can be dealt with at the testing stage and usually the only performance worry once the application is live is how to deal with growth in your business and the resulting increase in information that needs to be processed.
The above scenario can be trickier to handle in the query world (SQL, OPNQRYF, QUERY for i, ODBC, JDBC, etc) when there are many tools available to retrieve information on the system without the need to write a program to do it. SQL is being used more and more and any web or client based interface will probably be using SQL to get the information from your IBM i.
All these query interfaces allow the user to specify what information they want but not how to get it; those decisions are taken by the system itself based on the system configuration and structure of the database. This can give us a few things to think about:
A query can automatically react to processing environment changes and perform data retrieval in a completely different way to the last time it ran. This may result in faster or slower completion of the job. A good example of this is the deletion of a logical file which a programmer thinks is no longer needed for their application. However, the Query Optimizer may very well be using it.
Queries put the power of data retrieval into many more hands than just programmers. The
resulting jobs can be long-running and performance hungry. And unlike traditional
applications, which are usually released in a controlled manner, queries can be ran ‘as and
when’ without consideration to the rest of the environment.
There are tools that can be used to check queries before they run and take action, such as sending messages to the system operator or preventing the job from even starting. These are based on estimates and this is the work of the Query Optimizer and Query Governor. However, these estimates may not always be accurate.
The Query Supervisor, as the name suggests, supervises queries that are actually running on the system and takes action should those queries exceed one or more of the following thresholds:
- CPU time (in seconds)
- Elapsed time, or clock time (in seconds)
- Temporary storage (in MB)
- Total I/O count
Temporary indexes, hash tables and stored lists
One situation we see at Chilli is where queries use large amounts of temporary storage. These temporary storage structures include objects like temporary indexes, hash tables, sorted lists, etc. If your system already has disk space constraints this can soon lead to serious problems. You may have tools to warn of disk space increases but it may be hard to find the offending job. The Query Supervisor can immediately identify the job using the large amounts of temporary storage. Let’s look at how we can configure the Query Supervisor to track queries which use more than 3GB of temporary storage.
There are several SQL procedures in library QSYS2 to work with the Query Supervisor thresholds.
The following screenshot shows how we add a threshold.
The ‘remove_query_threshold’ procedure will remove a defined procedure and the ‘query_supervisor’ SQL view shows the thresholds that have been defined. Before a query runs a list of configured thresholds is passed to the query engine.
The next step is to create and compile an exit point program that will be added to the ‘QIBM_QQQ_QRY_SUPER’ exit point. This program will receive the information about the job that has exceeded the threshold. Examples of such programs can be found on the IBM i web site.
My test program just sends an informational message to the system operator message queue about the relevant job. In a live situation you may want to hold the job, pending investigation and send a message to the system operator message queue.
To add the exit point program to the exit point the following command is used.
Now, when any query uses more than 3GB of temporary storage the following message will appear in the system operator message queue.
Start using the Query Supervisor and see how it can help you control those resource-hungry queries before they cause you a serious problem.