The DB2/400 Predictive Query Governor provided in V3R1 allows you to quickly determine the estimated run-time of a query without actually running it. The governor works with any of the DB2/400 query interfaces, such as SQL/400, Open Query File (OPNQRYF), and Query Management. You can use the governor to help you optimize queries for improved performance.

To optimize a query, start by instructing the governor to display the estimated run-time (I'll explain how to do this in a moment). You can then make adjustments that you think might improve performance, which might involve modifying the query or creating a new access path. Next, redisplay the estimated run-time to see if it helped. Continue making modifications and displaying the estimated run-time until you find the lowest estimated run-time.

To cause the governor to display the estimated run-time of a query, use the Change Query Attributes (CHGQRYA) command and set the Query Time Limit (QRYTIMLMT) parameter to zero. This command only effects your job, so you don't have to worry about anyone else seeing the estimated time. Next, run your query interactively. You'll see a message on your screen that says Estimated query processing time x exceeds limit 0. (The x will be replaced with the estimated number of seconds the query will take.)

You can receive additional information by placing the cursor on this message and pressing Help. If you roll to the end of the second-level text, you may get some hints on how to tune your query. For example, you might see a message that says a temporary access path will be created. If so, you can avoid the overhead of creating an access path each time the query is run by creating a permanent one. Once you've optimized the query, you can run the CHGQRYA command with QRYTIMLMT(*NOMAX) to instruct the governor to no longer display the estimated run-time.

- Robin Klima