DB2 Query Monitor’s most commonly used capabilities, is its ability to summarize data about SQL resource consumption down to the level of the individual SQL text statement. DB2 Query Monitor (DB2 QM) normally collects and summarizes SQL activity using what it calls three basic keys (Plan + Program + Section). This means that the data is summarized to the DB2 Plan name, then Program Name, then section. The section is a value assigned during the DB2 pre-compile process. The value is used to group related SQL calls (OPEN, FETCH, CLOSE) together into a single statement. If you want to obtain additional levels of summarization to describe monitored SQL activity, OPTKEYS can be used. Additional information collected by OPTKEYS can enable summarization to items such as the DB2 authorization id and individual SQL text statement.
It should be noted that the use of OPTKEYS increases both the volume of data that is collected by DB2 Query Monitor and the amount of data DB2 Query Monitor stores in data spaces. Thus, it is worthwhile to carefully consider which OPTKEYS you use, and select only those that are most meaningful in your environment.
One way to evaluate which OPTKEYS parameters to use is by looking at the type of workload being monitored.
Ad-hoc query-based DB2 Workload – consider PTEXT, AUTHID
OPTKEYS(AUTHID) In this type of workload the number of users is most likely relatively small, and summarizing by the DB2 Authid could uncover patterns on each individual DB2 authorization IDs. OPTKEYS(Authid) helps to reduce collected information down to the level of individual DB2 authorization IDs.
OPTKEYS(PTEXT) removes literals from ad-hoc SQL text; and the SQL is then summarized as needed. This helps because the SQL statements are easier to read.
OPTKEYS(TEXT), on the other hand, is probably not useful because most of the SQL in the systems contains literals and will not be summarized appropriately.
For dynamic SQL , OPTKEYS(TEXT) is used when the application uses parameter markers in their SQL, and OPTKEYS(PTEXT) is used when literal values are used in dynamic SQL.
On-Line Transaction Processing (OLTP)-based DB2 Workload – consider (TEXT)
If the dynamic SQL uses parameter markers instead of literals in the SQL text, OPTKEYS(TEXT) might be very useful. Contrast that with the OPTKEYS(AUTHID) parameter which might not be as useful. If the number of distinct AUTHIDs is large, there is more overhead and minimal summarization. If there is a single AUTHID that is used for all DB2 SQL, then all summary data is in a single bucket and there is no value to using the AUTHID OPTKEYs.
If there is no dynamic SQL, there is no benefit to using OPTKEYS(TEXT) or OPTKEYS(PTEXT), because the SQL text is always retrieved from the DB2 catalog for static SQL
SAP-based DB2 Workload – consider WSTRAN and TEXT
In an SAP-based DB2 summarizing by OPTKEYS(WSTRAN) and OPTKEYS(TEXT) can be helpful. The reason for this is that there is only one AUTHID used by SAP (usually SAPR3), so if the AUTHID key is used it could put all the information into one bucket. To avoid this, use OPTKEYS(WSTRAN) which helps identify and sort the information by the user.
SAP only uses dynamic SQL which uses parameter markers and the SQL is subject to repeated execution. OPTKEYS(TEXT) is needed to be able to summarize by SQL statement. OPTKEYS(TEXT) is more appropriate than OPTKEYS(PTEXT) in an SAP environment. This is also true in any application that uses dynamic SQL and parameter markers instead of literals.
CICS-based DB2 Workload – consider (CORRNAME)
OPTKEYS(CORRNAME) is recommended rather than OPTKEYS(CORRID). OPTKEYS(CORRNAME) and OPTKEYS(CORRID) are mutually exclusive (only one or the other can be specified at any time). When OPTKEYS(CORRNAME) is used, the field is a translation of the CORRID to the four-character CICS transaction code. This allows for CICS transactions which use pool threads to summarize into a single bucket based on the transaction code.
If OPTKEYS(CORRID) is used in a CICS/DB2 workload, the regular CORRID is collected which might not help since OPTKEYS(CORRID) gives the original correlation ID . In a CICS environment, the CORRNAME OPTKEY directs QM to collect and summarize by the CICS transaction code. This is a very useful summary in CICS.
The introduction above provides a small example of the many uses of the optional summaries (OPTKEYS) offered by DB2 Query Monitor. By tailoring the OPTKEY settings to specific workloads, the value and usability of the data collected by DB2 Query Monitor can be improved significantly.