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).
Note: If your database was created in Version
9.7 before Fix Pack 1, to run this routine you must have
already run the db2updv97 command. If your database
was created before Version
9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care
of by the database migration). If you downgrade to Version
9.7, this routine will no longer work.
>>-EXPLAIN_FROM_CATALOG----------------------------------------->
>--(--pkgschema--,--pkgname--,--pkgversion--,--sectno--,--explain_schema-->
>--,--explain_requester--,--explain_time--,--source_name--,--source_schema--,--source_version--)-><
Authorization
All of the following privileges
and authority are required:
- EXECUTE privilege on the EXPLAIN_FROM_CATALOG procedure
- INSERT privilege on the Explain tables in the specified schema
- EXPLAIN authority
- 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.