MON_GET_SECTION table function - Get a copy of a section from the package cache
The MON_GET_SECTION table function returns a copy of a section for a dynamic or static SQL statement from the package cache. The section that is returned can be saved for future analysis. For example, when you investigate a plan change for a statement, the saved section can be passed as input to the EXPLAIN_FROM_DATA stored procedure to examine the previous access plan.
Authorization
One of the following authorizations
is required:
- EXECUTE privilege on the routine
- DATAACCESS authority
- SQLADM authority
- DBADM authority
Default PUBLIC privilege
None
Syntax
The schema is SYSPROC.
Routine parameters
-
executable_id
- An
input argument of type VARCHAR (32) FOR BIT DATA that specifies a
unique section in the database package cache.
The routine searches the package cache on all members for the section. For static SQL, the catalogs are searched if the section is no longer in the package cache.
If a null or invalid value is specified, SQL0171 is returned.
Information returned
| Column name | Data type | Description |
|---|---|---|
| SECTION_TYPE | CHAR(1) | section_type - Section type indicator monitor element |
| SECTION_ENV | BLOB(134 MB) | section_env - Section environment monitor element |
Example
Use the following query to retrieve
the statement text, statement identifier, plan identifier, and section
information from the compilation environment. Information is returned
for all statements currently in the package cache. The information
is inserted into a table called REPOSITORY_SECTIONS.
INSERT INTO
REPOSITORY_SECTIONS(STMT_TEXT, STMTID, PLANID,
SEMANTIC_ENV_ID, SECTION_DATA)
SELECT STMT_TEXT, STMTID, PLANID, SEMANTIC_ENV_ID,
( SELECT B.SECTION_ENV
FROM TABLE(MON_GET_SECTION(A.EXECUTABLE_ID)) AS B)
SECTION_DATA
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS A