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).
Authorization
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- EXPLAIN authority
- 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
Syntax
The schema is SYSPROC.
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. SQL20503N is returned
in the following situations:
- The input section is not a valid section
- The input section is from a database with a different operating system
- 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.
Restrictions
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.
Usage notes
- 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.
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'.
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;