Testing performance with the query governor
You can use the query governor to test the performance of your queries.
To test the performance of a query with the query governor, do the following:
- Set the query time limit to zero ( QRYTIMLMT(0) ) using the Change Query Attributes (CHGQRYA) command or in the INI file. This forces an inquiry message from the governor stating that the estimated time to run the query exceeds the query time limit.
- Prompt for message help on the inquiry message and find the same information that you can find by running the Print SQL Information (PRTSQLINF) command.
The query governor lets you optimize performance without having to run through several iterations of the query.
Additionally, if the query is canceled, the query optimizer evaluates the access plan and sends the optimizer debug messages to the job log. This occurs even if the job is not in debug mode. You can then review the optimizer tuning messages in the job log to see if additional tuning is needed to obtain optimal query performance. This allows you to try several permutations of the query with different attributes, indexes, and syntax or both to determine what performs better through the optimizer without actually running the query to completion. This saves on system resources because the actual query of the data is never actually done. If the tables to be queried contain a large number of rows, this represents a significant savings in system resources.
Be careful when you use this technique for performance testing, because all query requests will be stopped before they are run. This is especially important for a CQE query that cannot be implemented in a single query step. For these types of queries, separate multiple query requests are issued, and then their results are accumulated before returning the final results. Stopping the query in one of these intermediate steps gives you only the performance information that relates to that intermediate step, and not for the entire query.
Parent topic:
Set resource limits with the Predictive Query Governor
Related information
Print SQL Information (PRTSQLINF) command
Change Query Attributes (CHGQRYA) command