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).
Note: If your database was created in Version
9.7 before Fix Pack 1, to run this routine you must have
already run the db2updv97 command. If your database
was created before Version
9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care
of by the database migration). If you downgrade to Version
9.7, this routine will no longer work.
>>-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
All of the following privileges
and authority are required:
- EXECUTE privilege on the EXPLAIN_FROM_SECTION procedure
- 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
- 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
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_V97 table function
- WLM_GET_SERVICE_CLASS_AGENTS_V97 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.