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).
>>-EXPLAIN_FROM_SECTION----------------------------------------->
>--(--executable_id--,--section_source_type--,--section_source_name--,--member--,--explain_schema-->
>--,--explain_requester--,--explain_time--,--source_name--,--source_schema--,--source_version--)-><
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, all of the following privileges are required:
- INSERT privilege on the explain tables in the specified schema
- SELECT privilege on the package cache event monitor table, if
the section source name identifies a package cache event monitor
Default PUBLIC privilege
None
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.
Example
This example shows how to identify and analyze a particularly
expensive statement in the package cache by looking at the monitoring
metrics available per section. First, issue a query similar to the
following SELECT statement to determine the CPU time usage of sections.
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
The following sample shows output from this query.
EXECUTABLE_ID TOTAL_CPU_TIME ...
-------------- -------------- ...
x'010000000000000012...200200811261904103698' 91875622 ...
x'010000000000000007...200200811261904103238' 300 ...
2 record(s) selected.
The following sample
continues the output from this query.
...STMT_TEXT
...----------------------
...SELECT * FROM SYSCAT.TABLES
...INSERT INTO T1 VALUES(123)
2 record(s) selected.
To examine the access
plan for the expensive SELECT statement, pass its
executable_id to the EXPLAIN_FROM_SECTION procedure. Place the output in the Explain
tables in the MYSCHEMA schema.
CALL EXPLAIN_FROM_SECTION
( x'01000000000000001200000000000000000000000200200811261904103698',
'M', NULL, 0, 'MYSCHEMA', ?, ?, ?, ?, ? )
Usage notes
If the section corresponding
to the input executable ID cannot be found, SQL20501 is returned.
The input
executable_id can be obtained from the
following sources
- 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.
DB2 Version 10.1 for Linux, UNIX, and Windows