EXPLAIN_FROM_CATALOG procedure - Explain a statement using section information from catalogs

The EXPLAIN_FROM_CATALOG procedure explains a statement using the contents of the section obtained from the catalogs.

The Explain output is placed in the Explain tables for processing using any existing explain tools (for example, db2exfmt).

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, the privileges held by the authorization ID of the session, including privileges granted to groups, must include at least one of the following:

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

The schema is SYSPROC.

Procedure parameters

pkgschema

An input argument of type VARCHAR(128) specifying the schema of the package containing the section to be explained. If pkgschema is null or an empty string, SQL2032N is returned.

pkgname

An input argument of type VARCHAR(128) specifying the package containing the section to be explained. If pkgname is null or an empty string, SQL2032N is returned.

pkgversion

An input argument of type VARCHAR(64) specifying the version identifier for the package containing the section to be explained. Specify an empty string if the package has no version (a blank ' ' character if VARCHAR2 compatibility mode is enabled). If pkgversion is null, SQL2032N is returned.

sectno

An input argument of type SMALLINT specifying the section to be explained. If sectno is null, SQL2032N is returned.

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

If no section can be found corresponding to the input parameters, SQL20501 is returned.

The output parameters explain_requester, explain_time, source_name, source_schema, source_version comprise the key used to look up the Explain 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

The following example demonstrates how to explain a static statement that was compiled and exists in the catalogs. First, you can identify the section by selecting from the SYSCAT.STATEMENTS catalog view, for example:

SELECT pkgschema,
       pkgname,
       version,
       Sectno
FROM SYSCAT.STATEMENTS
WHERE TEXT = 'select count(*) from syscat.tables'

This query returns the following sample output:

PKGSCHEMA  PKGNAME              VERSION               SECTNO 
---------- -------------------- --------------------- ------ 
NULLID     SQLE2G0S                                   1 
NULLID     SQLE2G0S             VERSION1              1 

     2 record(s) selected.

Then pass the pkgschema, pkgname, version and sectno identification information into the EXPLAIN_FROM_CATALOG procedure, for example:

CALL EXPLAIN_FROM_CATALOG( 'NULLID', 'SQLE2G0S', '', 1, 'MYSCHEMA', ?, ?, ?, ?, ? )