If the authorization ID of the statement has SYSADM authority but not DBADM |authority, this ID is granted implicit DBADM authority for the purpose of |creating the procedure. This applies to the CREATE PROCEDURE statement (SQL) |and not the CREATE PROCEDURE statement (External).
The EXPLAIN_DIAGNOSTIC table contains an entry for each diagnostic message produced for a particular instance of an explained statement in the EXPLAIN_STATEMENT table.
The EXPLAIN_GET_MSGS table function queries the EXPLAIN_DIAGNOSTIC and EXPLAIN_DIAGNOSTIC_DATA Explain tables and returns formatted messages.
| Column name | Data type | Nullable | Key 1 | Description |
|---|---|---|---|---|
| EXPLAIN_REQUESTER | VARCHAR(128) | No | PK, FK | Authorization ID of initiator of this Explain request. |
| EXPLAIN_TIME | TIMESTAMP | No | PK, FK | Time of initiation for Explain request. |
| SOURCE_NAME | VARCHAR(128) | No | PK, FK | Name of the package running when the dynamic statement was explained or name of the source file when the static SQL was explained. |
| SOURCE_SCHEMA | VARCHAR(128) | No | PK, FK | Schema, or qualifier, of source of Explain request. |
| SOURCE_VERSION | VARCHAR(64) | No | PK, FK | Version of the source of the Explain request. |
| EXPLAIN_LEVEL | CHAR(1) | No | PK, FK | Level of Explain information for which this row is relevant.
Valid values are:
|
| STMTNO | INTEGER | No | PK, FK | Statement number within package to which this Explain information is related. Set to 1 for dynamic Explain SQL statements. For static SQL statements, this value is the same as the value used for the SYSCAT.STATEMENTS catalog view. |
| SECTNO | INTEGER | No | PK, FK | Section number within package that contains this SQL statement. For dynamic Explain SQL statements, this is the section number used to hold the section for this statement at runtime. For static SQL statements, this value is the same as the value used for the SYSCAT.STATEMENTS catalog view. |
| DIAGNOSTIC_ID | INTEGER | No | PK | ID of the diagnostic for a particular instance of a statement in the EXPLAIN_STATEMENT table. |
| CODE | INTEGER | No | No | A unique number assigned to each diagnostic message. The number can be used by a message API to retrieve the full text of the diagnostic message. |
|
The EXPLAIN_DIAGNOSTIC_DATA table contains message tokens for specific diagnostic messages that are recorded in the EXPLAIN_DIAGNOSTIC table. The message tokens provide additional information that is specific to the execution of the SQL statement that generated the message.
The EXPLAIN_GET_MSGS table function queries the EXPLAIN_DIAGNOSTIC and EXPLAIN_DIAGNOSTIC_DATA Explain tables, and returns formatted messages.
| Column name | Data type | Nullable | Key 1 | Description |
|---|---|---|---|---|
| EXPLAIN_REQUESTER | VARCHAR(128) | No | FK | Authorization ID of initiator of this Explain request. |
| EXPLAIN_TIME | TIMESTAMP | No | FK | Time of initiation for Explain request. |
| SOURCE_NAME | VARCHAR(128) | No | FK | Name of the package running when the dynamic statement was explained or name of the source file when the static SQL was explained. |
| SOURCE_SCHEMA | VARCHAR(128) | No | FK | Schema, or qualifier, of source of Explain request. |
| SOURCE_VERSION | VARCHAR(64) | No | FK | Version of the source of the Explain request. |
| EXPLAIN_LEVEL | CHAR(1) | No | FK | Level of Explain information for which this row is relevant.
Valid values are:
|
| STMTNO | INTEGER | No | FK | Statement number within package to which this Explain information is related. Set to 1 for dynamic Explain SQL statements. For static SQL statements, this value is the same as the value used for the SYSCAT.STATEMENTS catalog view. |
| SECTNO | INTEGER | No | FK | Section number within package that contains this SQL statement. For dynamic Explain SQL statements, this is the section number used to hold the section for this statement at runtime. For static SQL statements, this value is the same as the value used for the SYSCAT.STATEMENTS catalog view. |
| DIAGNOSTIC_ID | INTEGER | No | PK | ID of the diagnostic for a particular instance of a statement in the EXPLAIN_STATEMENT table. |
| ORDINAL | INTEGER | No | No | Position of token in the full message text. |
| TOKEN | VARCHAR(1000) | Yes | No | Message token to be inserted into the full message text; might be truncated. |
| TOKEN_LONG | BLOB(3M) | Yes | No | More detailed information, if available. |
|
The Explain facility uses the following IDs as the schema when qualifying Explain tables that it is populating:
The schema can be associated with a set of Explain tables, or aliases that point to a set of Explain tables under a different schema.
If no Explain tables are found under the schema, the Explain facility checks for Explain tables under the SYSTOOLS schema and attempts to use those tables.
A string representation of a time is a string that starts with a digit and has a length of at least 4 characters. Trailing blanks may be included; a leading zero may be omitted from the hour part of the time, and seconds may be omitted entirely. If seconds are omitted, an implicit specification of 0 seconds is assumed. Thus, 13:30 is equivalent to 13:30:00.
Valid string formats for times are listed in the following table. Each format is identified by name and associated abbreviation.
| Format Name | Abbreviation | Time Format | Example |
|---|---|---|---|
| International Standards Organization | ISO | hh.mm.ss | 13.30.05 |
| IBM USA standard | USA | hh:mm AM or PM | 1:30 PM |
| IBM European standard | EUR | hh.mm.ss | 13.30.05 |
| Japanese Industrial Standard Christian Era | JIS | hh:mm:ss | 13:30:05 |
| Site-defined | LOC | Depends on the territory code of the application | - |
Starting with Version 8.2, "AM" and "PM" can be represented in lowercase or uppercase.
[ Top of page |Previous page | Next page | Contents ]