Use OPTKEYS to tailor data summary levels

Because OPTKEYS parameter enables you to specify additional levels of summarization, it can significantly increase both the volume of data collected by Db2® Query Monitor and the amount of data Db2 Query Monitor stored in data spaces. You must use OPTKEYS cautiously.

Db2 Query Monitor collects and summarizes SQL activity using the following basic key:

Plan + Program + Section

Note: It is recommended that you specify OPTKEYS in monitoring profiles, not in CQMPARMS.

Using OPTKEYS for an ad hoc query-based Db2

For an ad hoc Db2 subsystem, OPTKEYS(TEXT) is probably not useful, because most of the SQL in the systems is unique and not reused. But OPTKEYS(PTEXT) might be very useful, because literals are removed from the ad hoc SQL and the SQL can then be summarized appropriately. Additionally, OPTKEYS(AUTHID) might be useful in this scenario, because the number of users is most likely relatively small.

Note: Omitting TEXT from the OPTKEYS specification or using OPTKEYS PTEXT does not result in the loss of dynamic SQL statement text associated with an exception. The dynamic SQL statement text associated with an exception is always recorded.

Using OPTKEYS for an On-Line Transaction Processing (OLTP)-based Db2 subsystem

In an OLTP-based Db2 subsystem, if the dynamic SQL is repeated, OPTKEYS(TEXT) might be very useful, whereas OPTKEYS(AUTHID) is probably not useful. If the number of distinct AUTHIDs is large, there is more overhead and minimal summarization. If a single AUTHID is used for all Db2 SQL, the summary data is grouped into a single bucket, making OPTKEYS ineffective. If there is no dynamic SQL, there is no benefit to using OPTKEYS(TEXT) or OPTKEYS(PTEXT), because the default summarization key is enough to identify each SQL statement being executed.

The number of summary buckets grows quickly, and this can be exacerbated by specifying multiple OPTKEYS. For example, consider a system that has 1,000 distinct dynamic SQL statements and 1,000 users. Also consider that each user executes every SQL statement at least once during each interval. Finally, consider that each SQL statement accesses 3 application objects. For this example, the OPTKEYS affect the summary collection as follows:

  • Specifying OPTKEYS(TEXT) – This adds 1,000 summary buckets to the METR data and 15,000 (1,000*15) buckets to the OBJS data.
  • Specifying OPTKEYS(AUTHID) – This adds 1,000 summary buckets to the METR data and 15,000 (1,000*15) buckets to the OBJS data.
  • Specifying OPTKEYS(TEXT,AUTHID) – This adds 1,000,000 (1,000*1,000) summary buckets to the METR data and 15,000,000 (1,000*1,000*15) buckets to the OBJS data.
Note: In the above example, the CALLS OPTKEY must be enabled.

When you choose OPTKEYS settings, it is important to determine what categories of summarization are meaningful and useful in your environment. For example, with SAP, in contrast to the settings for the OLTP-based Db2 and the Data Warehouse-based Db2 discussed above, it is probably more appropriate to summarize by WSTRAN and TEXT, because only one AUTHID is used by SAP (usually SAPR3), whereas WSTRAN helps you identify the user. Since SAP only uses dynamic SQL which is subject to repeated execution, TEXT is needed to be able to summarize by SQL statement. SAP also makes extensive used of host variables, hence TEXT is more appropriate than PTEXT in an SAP environment. This is also true in any application that uses dynamic SQL and host variables instead of literals.

Note: WSTRAN and TEXT both act as multipliers to the number of summary buckets, so many users may mean that specifying WSTRAN is undesirable.