DB2 10.5 for Linux, UNIX, and Windows

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.

Important: This routine is available starting with DB2® Cancun Release 10.5.0.4. If your database was created before you applied DB2 Cancun Release 10.5.0.4, then this routine becomes available after you apply DB2 Cancun Release 10.5.0.4 then issue the db2updv105 command. If your database was created before Version 10.5, upgrade to DB2 Cancun Release 10.5.0.4 or later pack to have this routine available.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the routine
DATAACCESS authority
SQLADM authority
DBADM authority

Default PUBLIC privilege

None

Syntax


>>-MON_GET_SECTION--(--executable_id--)------------------------><

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

Table 1. Information returned by the MON_GET_SECTION
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

You can use this information to build a history of statements that ran. You can use the EXPLAIN_FROM_DATA stored procedure to examine the access plan for each saved statement by passing the saved section to the stored procedure.