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).
>>-EXPLAIN_FROM_CATALOG----------------------------------------->
>--(--pkgschema--,--pkgname--,--pkgversion--,--sectno--,--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, the following privilege is required:
- INSERT privilege on the explain tables in the specified schema
Default PUBLIC privilege
None
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.
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', ?, ?, ?, ?, ? )
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.