Using db2caem to diagnose queries which do not complete or are resource intensive

Answer

The db2caem tool can be used to execute a poorly performing query and obtain formatted explain (db2exfmt) output that includes section actuals showing the actual number of rows processed by each operator in the access plan. This information can be very useful identifying where the optimizer's cardinality estimates are inaccurate, and where use of additional statistics (table or index statistics, column group statistics, statistical views, etc) may help the optimizer choose a better access plan. However, the normal usage of db2caem reports the explain data at the end of the execution of the query. This can cause difficulties in using db2caem for very long running queries, or ones which are very resource intensive when they execute.

As of V10.1 FP4 and V10.5 FP3, the capability exists to run a query through db2caem and interrupt it before completion, while still obtaining the db2exfmt output and section actuals data, reflecting the actuals counts accumulated to the point when the query is interrupted. Using this method requires some additional steps as follows:

1) Start db2caem with the query of interest in one session

2) In a different session, do the following:

(i) Identify the application handle for the connection running the db2caem tool using application snapshot or list applications:

CONNECT Auth Id Application Appl. Application Id
Handle
------------------------- --------------- ---------- ----------------------
IMAIONE db2caem 438 *LOCAL.imaione.150614155153

(ii) Connect to the database and execute the following query, substituting the application handle number obtained in (1) in the first argument to the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table reference. Make note of the UOWID and ACTID returned by the query.

SELECT SUBSTR(CHAR(COORD_MEMBER),1,5) AS COORD,
SUBSTR(CHAR(MEMBER),1,4) AS MEMB,
SUBSTR(CHAR(UOW_ID),1,5) AS UOWID,
SUBSTR(CHAR(ACTIVITY_ID),1,5) AS ACTID,
SUBSTR(CHAR(PARENT_UOW_ID),1,8) AS PARUOWID,
SUBSTR(CHAR(PARENT_ACTIVITY_ID),1,8) AS PARACTID,
SUBSTR(ACTIVITY_TYPE,1,8) AS ACTTYPE,
SUBSTR(CHAR(NESTING_LEVEL),1,7) AS NESTING
FROM TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(438,-2)) AS WLOACTS ORDER BY MEMB,UOWID,ACTID

COORD MEMB UOWID ACTID PARUOWID PARACTID ACTTYPE NESTING
----- ---- ----- ----- -------- -------- -------- -------
0 0 32 1 - - READ_DML 0

(iii) After leaving db2caem executing for an adequate amount of time, from the second session then issue a call to WLM_CANCEL_ACTIVITY, substituting the appropriate application handle, UOWID, and ACTID obtained from (i) and (ii). This will interrupt the query, and also cause the db2caem tool to stop in the first session. Before it terminates db2caem will write its output, including the actuals accumulated to that point.

call wlm_cancel_activity(<appl handle>, <uowid>, <actid>)

CALL WLM_CANCEL_ACTIVITY(438,32,1)

Although gathering section actuals reflecting the entire execution of the query is the ideal case, if a query is allowed to run significantly past its expected or required execution time before interrupting db2caem, the actuals counts will often provide a sufficient indication of where cardinality estimates are low, and/or what parts of the access plan are processing large amounts of data.