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