DB2 Version 10.1 for Linux, UNIX, and Windows

EXPLAIN_FROM_DATA procedure - Explain a statement using the input section

The EXPLAIN_FROM_DATA procedure explains a statement using the contents of the input section. The Explain output is placed in the Explain tables for processing using any existing Explain tools (for example, db2exfmt).


>>-EXPLAIN_FROM_DATA-------------------------------------------->

>--(--section--,--stmt_text--,--executable_id--,--explain_schema-->

>--,--explain_requester--,--explain_time--,--source_name--,--source_schema--,--source_version--)-><

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 following privilege is required:

INSERT privilege on the explain tables in the specified schema

Default PUBLIC privilege

None

Procedure parameters

section: An input argument of type BLOB(134M) that contains the section to be explained. You can obtain the section from various sources, including event monitor tables and the catalog tables. If the input section is not a valid section, SQL20503N is returned.
stmt_text: An optional input argument of type CLOB(2M) that contains the text of the statement corresponding to the input section. If stmt_text is NULL, the formatted Explain output will not contain any statement text.
executable_id: An optional input argument of type VARCHAR(32) FOR BIT DATA that contains the executable ID used to identify the section. If executable_id is NULL, the formatted explain output will not contain an executable ID.
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

Assume you have captured a number of statements using the package cache event monitor and extracted the event monitor data (using the EVMON_FORMAT_UE_TO_TABLE stored procedure) to a table named PKGCACHE. Looking at the data in the table, you identify a particularly expensive statement which has executable id x'0100000000000000070000000000000000000000000200200811261904103698'.

You issue the EXPLAIN_FROM_DATA procedure to understand the access plan for this statement, passing as input the section from the entry in the PKGCACHE table. You place the Explain output in the explain tables in the MYSCHEMA schema.

SET SERVEROUTPUT ON;

BEGIN
  DECLARE EXECUTABLE_ID VARCHAR(32) FOR BIT DATA; --
  DECLARE SECTION BLOB(134M); --
  DECLARE STMT_TEXT CLOB(2M); --
  DECLARE EXPLAIN_SCHEMA VARCHAR(128); --

  DECLARE EXPLAIN_REQUESTER VARCHAR(128); --
  DECLARE EXPLAIN_TIME TIMESTAMP; --
  DECLARE SOURCE_NAME VARCHAR(128); --
  DECLARE SOURCE_SCHEMA VARCHAR(128); --
  DECLARE SOURCE_VERSION VARCHAR(128); --

  SET EXPLAIN_SCHEMA = 'MYSCHEMA'; --

  SELECT P.SECTION, P.STMT_TEXT, P.EXECUTABLE_ID INTO 
         SECTION, STMT_TEXT, EXECUTABLE_ID
  FROM PKGCACHE WHERE EXECUTABLE_ID =
   x'0100000000000000070000000000000000000000000200200811261904103698'; --

  CALL EXPLAIN_FROM_DATA( SECTION, 
                          STMT_TEXT, 
                          EXECUTABLE_ID, 
                          EXPLAIN_SCHEMA, 
                          EXPLAIN_REQUESTER,
                          EXPLAIN_TIME,
                          SOURCE_NAME,
                          SOURCE_SCHEMA,
                          SOURCE_VERSION ); --

  CALL DBMS_OUTPUT.PUT( 'EXPLAIN_REQUESTER = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( EXPLAIN_REQUESTER ); --
  CALL DBMS_OUTPUT.PUT( 'EXPLAIN_TIME = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( EXPLAIN_TIME ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_NAME = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_NAME ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_SCHEMA = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_SCHEMA ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_VERSION = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_VERSION ); --
END;

SET SERVEROUTPUT OFF;

Usage notes

The input section can be obtained from a number of different sources:

Activity event monitor
Package cache event monitor
Catalog tables
Any user table or input source that has made a copy of the section from one of the locations listed previously.

The output parameters explain_requester, explain_time, source_name, source_schema, 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 procedure does not issue a COMMIT after inserting into the Explain tables. It is the responsibility of the caller of the procedure to issue a COMMIT.