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).
None
>>-EXPLAIN_FROM_DATA--------------------------------------------> >--(--section--,--stmt_text--,--executable_id--,--explain_schema--> >--,--explain_requester--,--explain_time--,--source_name--,--source_schema--,--source_version--)-><
The schema is SYSPROC.
The section that is passed as input to the EXPLAIN_FROM_DATA stored procedure must be obtained from a database whose architecture is the same as the database used for the EXPLAIN_FROM_DATA stored procedure call.
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.
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'.
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;