EXPLAIN_FROM_SECTION procedure - Explain a statement using package cache or package cache event monitor information
The EXPLAIN_FROM_SECTION procedure explains a statement using the contents of the section obtained from the package cache or from the package cache event monitor.
The Explain output is placed in the Explain tables for processing using any existing explain tools (for example, db2exfmt).
The schema is SYSPROC.
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
Procedure parameters
- executable_id
- An input argument of type VARCHAR(32) FOR BIT DATA that uniquely identifies a section to be explained. If this argument is null or an empty string, SQL2032 is returned.
- section_source_type
- An input argument of type CHAR(1) that specifies the source of
the section to be explained. Valid values are:
- M - Section is obtained from the in-memory package cache
- P - Section is obtained from a package cache event monitor. Either regular or unformatted event tables can be used as the output type for the event monitor.
For static SQL, if the section_source_type is M and the section cannot be located in the package cache, the catalog tables are searched for the section.
- section_source_name
- An input argument of VARCHAR(128) that specifies the name of a package cache event monitor if the section_source_type is P. If the section_source_type is M, the name of a package cache event monitor can be optionally specified. The event monitor is searched for the section if the section cannot be found in the package cache (for example, if the section was flushed from the package cache before the EXPLAIN_FROM_SECTION stored procedure was invoked). If the source input event monitor is not a package cache event monitor created with the COLLECT DETAILED DATA option, SQL0204N is returned. If the caller does not have SELECT privilege on the package cache event monitor table, SQL0551N is returned.
- member
- An input argument of type INTEGER that specifies the member where the section to be explained resides in memory if the section_source_type is M. If -1 is specified, the procedure searches for the section on the current coordinator member and the section compilation member. This argument is ignored if the section_source_type is anything other than M.
- 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.
Usage notes
- Activity event monitor
- Package cache event monitor
- MON_GET_ACTIVITY_DETAILS table function
- MON_GET_PKG_CACHE_STMT table function
- WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function
- WLM_GET_SERVICE_CLASS_AGENTS table function
- MON_GET_PKG_CACHE_STMT_DETAILS table function
- MON_GET_APPL_LOCKWAIT table function
The output parameters explain_requester, explain_time, source_name, source_schema, source_version comprise the key used to look up the 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 statement after inserting into the Explain tables. It is the responsibility of the caller of the procedure to issue a COMMIT.
Example
SELECT executable_id,
Total_cpu_time,
Varchar(stmt_text, 100) as stmt_text
FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL,NULL, -1)) AS T
EXECUTABLE_ID TOTAL_CPU_TIME ...
-------------- -------------- ...
x'010000000000000012...200200811261904103698' 91875622 ...
x'010000000000000007...200200811261904103238' 300 ...
2 record(s) selected.
...STMT_TEXT
...----------------------
...SELECT * FROM SYSCAT.TABLES
...INSERT INTO T1 VALUES(123)
2 record(s) selected.
CALL EXPLAIN_FROM_SECTION
( x'01000000000000001200000000000000000000000200200811261904103698',
'M', NULL, 0, 'MYSCHEMA', ?, ?, ?, ?, ? )