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

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 database server, the value is ignored.

Authorization

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

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority
EXPLAIN authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

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 database 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 is an example of 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".