DB2 Query Monitor (DB2 QM) has various methods that DBAs can use to manage the resources consumed by SQL statements. A popular approach is to manage resource usage via exception processing for specified thresholds.
DB2 QM’s EXCEPTION perspective provides a complete view of the performance metrics related to individual SQL statements which exceed user-specified thresholds. For example, a threshold may be set such that all SQL statements which have a DB2 elapsed time of greater than one second are captured.
After DB2 QM displays a list of the exceptions, the displayed data can be sorted to show the longest-running SQL exceptions at the top. This can be done by sorting by the DB2 elapsed time. In the resulting sorted panel, the primary view will have metrics such as the associated connection name (CORRNAME), DB2 package or DBRM name (Program), Delay Time, DB2 CPU Time, and Getpages (the number of times that DB2 requested a page from the buffer manager).
The EXCEPTION perspective also provides line commands that provide access to more detailed information related to the execution of the SQL statement which help DBAs debug the cause of the exception. Some very useful commands are:
- The “S” line command shows the SQL text for the selected record.
- The “B” line command shows the SQL I/O statistics that drive buffer pool usage.
DB2's data can be maintained in memory in what is referred to as the buffer pools. When DB2 can resolve a GETPAGE request from the buffer pool it saves DB2 the cost of the more expensive page I/O. High number of getpages requests means the SQL is accessing many pages to satisfy the request. This may or may not indicate inefficiency in the SQL. High number of page I/O (Synchronous Pages Read/Written) means there could be a bottleneck due to the buffer pool size, which needs to be increased.
For example: A SQL SELECT statement requests 99999 getpages . This is very high. The developer should analyze the SQL statement to determine if there is a way to rewrite the statement to reduce the getpages.
After rewriting we recheck the statistics and find that the new SQL still requests 9999 getpages , which means this cannot be reduced . However, the new SQL shows a buffer pool hit ratio of 50%, and Synchronous Pages Read as 5000. This means that only half of the 9999 getpage requests are satisfied by page I/O. And DB2’s requests have to wait to bring pages into the buffer pool from disk I/O. That will cause a long elapsed time. DBAs can increase the buffer pool size to increase the likelihood of the getpages being satisfied immediately from the buffer pool. The larger the buffer pool, the more pages are cached in memory.
- The “C” line command shows the SQL performance metrics at the individual call level.
In the display below the overhead for the component parts of the SQL statement are shown. For example, individual lines would be displayed for the open, fetch, and close for an SQL statement which was using cursor type processing.
In this second example below, we can see the STMT# in the Call Level Statistics panel, that’s the statement number to help locate the SQL statement .
DB2 QM panel: The STMT# is 183.
The host variables that are input to an SQL statement can be identified using the “H” line command. If the column titled “HV” contains the value of “YES”, this indicates that Query Monitor has collected host variables for that SQL statement. The variable’s type, length, and collected data are displayed. In addition, an indicator as to whether the variable can be a null value is displayed. The host variables are important because they can affect the size of the result set and/or the access path used by DB2. An example would be using the variable gender in a query. When that query is run against the Girl Scouts database, would return very distinct result sets for male or female. Conversely, the same query ran against the Boy Scouts database would return an inverse result.
Negative SQLCODE Exceptions
If the exception is caused by a negative SQL code, the ESQLCode column will contain the negative SQL Code. The “C” line command can be used to view the SQLCA information. The SQLCA will contain the information about the failure returned by DB2. For example, for a ‘-805’, we will see the package name and reason code. Possible solutions are rebinding the plan and checking whether the DBRM or PACKAGE name are correct
Combining columns to find exceptions
It is possible that using multiple columns in the exception display can simplify problem analysis. For example, if you observe that the DB2 Elapsed time is very large, and the ESQLCODE is a ‘-913’, you might infer that a deadlock happened. You can then use a “C” line command to analyze the SQLCA. You should also check the “L” line command for any locking activity.