EXPLAIN_FROM_ACTIVITY procedure - Explain statement using activity event monitor information

The EXPLAIN_FROM_ACTIVITY procedure explains a specific execution of a statement using the contents of the section obtained from an activity event monitor.

The Explain output is placed in the Explain tables for processing using any existing Explain tools (for example, db2exfmt). The Explain output contains, if available, both the access plan and section actuals (runtime statistics for operators in the access plan).

The schema is SYSPROC.

Authorization

One of the following authorities or privileges is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority
EXPLAIN authority

In addition, the privileges held by the authorization ID of the session, including privileges granted to groups, must include at least one of the following:

INSERT privilege on the explain tables in the specified schema
CONTROL privilege on the explain tables in the specified schema
DATAACCESS authority

Default PUBLIC privilege

None

Table function parameters

appl_id: An input argument of type VARCHAR(64) that uniquely identifies the application that issued the activity whose section is to be explained. If appl_id is null or an empty string, SQL2032N is returned.
uow_id: An input argument of type INTEGER specifying the unit of work identifier for the activity whose section is to be explained. Unit of work ID is only unique within a given application. If uow_id is null, SQL2032N is returned.
activity_id: An input argument of type INTEGER specifying the identifier of the activity whose section is to be explained. Activity ID is only unique within a unit of work. If activity_id is null, SQL2032N is returned.
activity_evmon_name: An input argument of VARCHAR(128) that specifies the name of a write to table activity event monitor containing the activity whose section is to be explained. If the event monitor does not exist or is not an activity event monitor, SQL0204N is returned. If the event monitor is not a write to table event monitor, SQL20502N is returned. If activity_evmon_name is not specified, SQL2032N is returned. If the caller does not have SELECT privilege on the activity event monitor tables, SQL0551N is returned.
explain_schema: An optional input or output argument of type VARCHAR(128) that specifies the schema containing the Explain tables where the explain information should be written. If an empty string or NULL is specified, a search is made for the explain tables under the session authorization ID and, following that, the SYSTOOLS schema. If the Explain tables cannot be found, SQL0219N is returned. If the caller does not have INSERT privilege on the Explain tables, SQL0551N is returned. On output, this parameter is set to the schema containing the Explain tables where the information was written.
explain_requester: An output argument of type VARCHAR(128) that contains the session authorization ID of the connection in which this routine was invoked.
explain_time: An output argument of type TIMESTAMP that contains the time of initiation for the Explain request.
source_name: An output argument of type VARCHAR(128) that contains the name of the package running when the statement was prepared or compiled.
source_schema: An output argument of type VARCHAR(128) that contains the schema, or qualifier, of the source of Explain request.
source_version: An output argument of type VARCHAR(64) that contains the version of the source of the Explain request.

Example

The following example assumes that you are mining the data collected in the activity event monitor over a period of time and using the following query, you notice a particularly expensive SQL statement in terms of CPU cost.

SELECT APPL_ID,
       UOW_ID,
       ACTIVITY_ID,
       USER_CPU_TIME
FROM ACTIVITY_A
ORDER BY USER_CPU_TIME

The following example shows output from this query. The application with an ID of N2.DB2INST1.0B5A12222841 is using a large amount of CPU time.

   APPL_ID                   UOW_ID     ACTIVITY_ID    USER_CPU_TIME
   ------------------------  -------- -------------- ---------------
   *N2.DB2INST1.0B5A12222841   1              1       92782334234
   *N2.DB2INST1.0B5A12725841   2              7               326

     2 record(s) selected.

You can use the EXPLAIN_FROM_ACTIVITY procedure to investigate the access plan for this activity, to determine if the activity could benefit from tuning, for example, by adding an index.

CALL EXPLAIN_FROM_ACTIVITY( '*N2.DB2INST1.0B5A12222841', 1, 1, 'A', 'MYSCHEMA',
     ?, ?, ?, ?, ? )

Usage notes

In order to run Explain on the section of the activity, you must specify the COLLECT ACTIVITY DATA WITH SECTION clause when you enable collection of activity data, so that the section is collected with the activity information. If there is no section stored with the identified activity entry, SQL20501 is returned.

If section actuals were not collected for an activity, the section explain will succeed, but the Explain output will not include actuals information. Section actuals are not collected in the following cases:

The activity specified as input was captured using the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure. In this case, the value of the partial_record element in the activity logical group is 1.
The activity event monitor ACTIVITY table is missing the SECTION_ACTUALS element.
Section actuals were not enabled for the section that was captured. Section actuals are enabled using the section_actuals database configuration parameter or for a specific application using the WLM_SET_CONN_ENV procedure. By default, section actuals are disabled.

Note: To verify that section actuals were collected for an activity, check whether the SECTION_ACTUALS element in the ACTIVITY table has a length greater than 0.

Note: The section_actuals setting specified by the WLM_SET_CONN_ENV procedure for an application takes effect immediately. Section actuals will be collected for the next statement issued by the application.

Note: In a partitioned database environment, section actuals will be collected only on members where activity data is collected. To collect actuals on all members, ensure the activity is collected using the COLLECT ACTIVITY DATA ON ALL MEMBERS WITH DETAILS, SECTION clause. If the user wants to enable collection at all members for a particular application, they can include the <collectactpartition> tag with a value of "ALL" in the second argument when calling the WLM_SET_CONN_ENV procedure.

If no activity can be found that corresponds to the appl_id, uow_id, and activity_id that you input, SQL20501 is returned. If multiple activities match, which may occur if an activity was collected multiple times during execution using the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure, the most recent entry for which a section was captured will be used for Explain.

The output parameters explain_requester, explain_time, source_name, source_schema, and source_version comprise the key used to look up the Explain information for the section in the Explain tables. Use these parameters with any existing Explain tools (for example, db2exfmt) to format the explain information retrieved from the section.

The EXPLAIN_FROM_ACTIVITY procedure does not issue a COMMIT statement after inserting into the Explain tables. It is the responsibility of the caller of the procedure to issue a COMMIT.

The following elements must be present in the ACTIVITYSTMT logical group: STMT_TEXT, ORIGINAL_STMT_TEXT, SECTION_ENV, EXECUTABLE_ID, APPL_ID, ACTIVITY_ID, UOW_ID. If any of these elements are missing, the stored procedure returns SQL206.