DB2 Version 9.7 for Linux, UNIX, and Windows

EXPLAIN_GET_MSGS table function

The EXPLAIN_GET_MSGS table function queries the EXPLAIN_DIAGNOSTIC and EXPLAIN_DIAGNOSTIC_DATA Explain tables, and returns formatted messages.

Syntax


>>-EXPLAIN_GET_MSGS--(--explain-requester--,--explain-time--,--->

>--source-name--,--source-schema--,--source-version--,---------->

>--explain-level--,--stmtno--,--sectno--,--locale--)-----------><

The schema is the same as the Explain table schema.

Table function parameters

Any of the following input arguments can be null. If an argument is null, it is not used to limit the query.

explain-requester: An input argument of type VARCHAR(128) that specifies the authorization ID of the initiator of this Explain request. A null value excludes this parameter from the search condition of the query.
explain-time: An input argument of type TIMESTAMP that specifies the time of initiation for the Explain request. A null value excludes this parameter from the search condition of the query.
source-name: An input argument of type VARCHAR(128) that specifies the name of the package running when the dynamic statement was explained, or the name of the source file when the static SQL statement was explained. A null value excludes this parameter from the search condition of the query.
source-schema: An input argument of type VARCHAR(128) that specifies the schema, or qualifier, of the source of the Explain request. A null value excludes this parameter from the search condition of the query.
source-version: An input argument of type VARCHAR(64) that specifies the version of the source of the Explain request. A null value excludes this parameter from the search condition of the query.
explain-level: An input argument of type CHAR(1) that specifies the level of Explain information for which this row is relevant. A null value excludes this parameter from the search condition of the query.
stmtno: An input argument of type INTEGER that specifies the statement number within the package to which this Explain information is related. A null value excludes this parameter from the search condition of the query.
sectno: An input argument of type INTEGER that specifies the section number within the package to which this Explain information is related. A null value excludes this parameter from the search condition of the query.
locale: An input argument of type VARCHAR(33) that specifies the locale of returned messages. If the specified locale is not installed on the DB2® server, the value is ignored.

Authorization

One of the following authorities is required to execute the function:

EXECUTE privilege on the function
DATAACCESS authority

Information returned

Table 1. Information returned by the EXPLAIN_GET_MSGS table function
Column name	Data type	Description
EXPLAIN_REQUESTER	VARCHAR(128)	Authorization ID of the initiator of this Explain request.
EXPLAIN_TIME	TIMESTAMP	Time of initiation for the Explain request.
SOURCE_NAME	VARCHAR(128)	Name of the package running when the dynamic statement was explained, or the name of the source file when the static SQL statement was explained.
SOURCE_SCHEMA	VARCHAR(128)	Schema, or qualifier, of the source of the Explain request.
SOURCE_VERSION	VARCHAR(64)	Version of the source of the Explain request.
EXPLAIN_LEVEL	CHAR(1)	Level of Explain information for which this row is relevant.
STMTNO	INTEGER	Statement number within the package to which this Explain information is related.
SECTNO	INTEGER	Section number within the package to which this Explain information is related.
DIAGNOSTIC_ID	INTEGER	ID of the diagnostic for a particular instance of a statement in the EXPLAIN_STATEMENT table.
LOCALE	VARCHAR(33)	Locale of returned messages. This locale will not match the specified locale if the latter is not installed on the DB2 server.
MSG	VARCHAR(4096)	Formatted message text.

Examples

Request formatted English messages from the Explain tables in the default schema for requester SIMMEN that were generated in the last hour. Specify a source name of SQLC2E03.

  SELECT MSG
    FROM TABLE(EXPLAIN_GET_MSGS(
      'SIMMEN',
      CAST(NULL AS TIMESTAMP),
      'SQLC2E03',
      CAST(NULL AS VARCHAR(128)),
      CAST(NULL AS VARCHAR(64)),
      CAST(NULL AS CHAR(1)),
      CAST(NULL AS INTEGER),
      CAST(NULL AS INTEGER),
      'en_US'))
    AS REGISTRYINFO
    WHERE EXPLAIN_TIME >= (CURRENT TIMESTAMP - 1 HOUR)
    ORDER BY DIAGNOSTIC_ID

The following example is a sample output from this query.

MSG
------------------------------------------------------------------------
EXP0012W  Invalid access request. The index "index1" could not be found.
  Line number "554", character number "20".
EXP0012W  Invalid access request. The index "index2" could not be found.
  Line number "573", character number "20".
EXP0015W  Invalid join request. Join refers to tables that are not in
  the same FROM clause. Line number "573", character number "20".