PD_GET_DIAG_HIST table function - Return records from a given facility
The PD_GET_DIAG_HIST table function returns log records, event records and notification records from a given facility.
Options are also provided to filter based on the type of record, customer impact value of the record, and from-until timestamps.
Authorization
One
of the following authorities is required to execute the routine:
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
Syntax
The schema is SYSPROC.
Table function parameters
- facility
- An optional input argument of type VARCHAR(20) that specifies
the facility from which records are to be returned. A facility is
a logical grouping that records relate to. The possible values are:
- ALL: Returns records from all facilities
- MAIN: Returns records from the general diagnostic logs. This currently means the db2diag log files, the admin notification log, and the rotating event logs.
- OPTSTATS: Return records related to optimizer statistics
- rectype
- An optional input argument of type VARCHAR(30) that specifies
which record type to return. A combination of types separated by '+'
are supported, for example: 'D + EI'. The possible values are:
- 'ALL': Return all record types.
- 'D': Return all diagnostic records.
- 'E': Return all event records.
- 'DI': Internal diagnostic records. These are non-translated diagnostic record that are used by IBM support in a diagnostic situation.
- 'DX': External diagnostic records. These are translated diagnostic that are of use to the user. These records are the notification records.
- 'EI': Internal event record. These are event record that are used by IBM support in a diagnostic situation.
- 'EX': External event record. These are diagnostic record that are of use to the user.
- impact
- An optional input argument of type VARCHAR(18) that specifies
the minimum customer impact level of the record returned. The possible
values are:
- 'NONE'
- 'UNLIKELY'
- 'POTENTIAL'
- 'IMMEDIATE'
- 'CRITICAL'
- start_time
- An optional input argument of type TIMESTAMP that specifies a valid timestamp. Entries are returned if their timestamp is more recent than this value. If this parameter is null, records are returned regardless of how old they are.
- end_time
- An optional input argument of type TIMESTAMP that specifies a valid timestamp. Entries are returned if their timestamp is older than this value. If this parameter is null, records are returned regardless of how recent they are.
- member
- An optional input argument of type INTEGER that specifies a valid database member from which the records should be fetched. Specify -1 or null for the current member, or -2 for information from all active database members. An active database member is where the database is available for connection and use by applications. If the parameter is not specified, the default value is all active database members.
Information returned
Column Name | Data Type | Description |
---|---|---|
FACILITY | VARCHAR(20) | A facility is a logical grouping which records
relate to. The possible values are:
|
RECTYPE | VARCHAR(3) | The type of record. The possible values are:
|
TIMESTAMP | TIMESTAMP | The time that the message was created. |
TIMEZONE | INTEGER | The time difference (in minutes) from the Universal Coordinated Time (UCT). For example, -300 is EST. |
INSTANCENAME | VARCHAR(128) | The name of the instance where the message was created. |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
LEVEL | CHAR(1) | The severity level of the record. The possible
values are:
|
IMPACT | VARCHAR(18) | Qualifies the impact of this message from a
user's perspective. This clarifies the impact of the message on the
business process the database system is part
of. Possible values are:
|
DBNAME | VARCHAR(128) | The name of the database being accessed while this message was created. |
EDU_ID | BIGINT | edu_ID - Engine dispatchable unit ID monitor element |
EDUNAME | VARCHAR(64) | The name of the engine Dispatched Unit that created this message. |
PID | BIGINT | The operating system process identifier that created this message. |
PROCESS_NAME | VARCHAR(255) | The operating system process name that created this message. |
TID | BIGINT | The thread numeric identifier that created this message. |
APPLNAME | VARCHAR(255) | The name of the client application that initiated the connection, if it is available. |
APPL_ID | VARCHAR(64) | appl_id - Application ID monitor element |
APPLHANDLE | VARCHAR(9) | A system-wide unique identifier for the application that initiated the connection when available. This is synonymous to agent ID. The identifier consists of the coordinating member number and a 16-bit counter separated by a '-'. The format is as follows: 'nnn-xxxxx' |
AUTH_ID | VARCHAR(30) | auth_id - Authorization ID monitor element |
PRODUCT | VARCHAR(50) | The name of the product that created the message. For example 'Db2 Common'. |
COMPONENT | VARCHAR(255) | The name of the component that created the message. |
FUNCTION | VARCHAR(255) | The name of the function that generated the message. |
PROBE | INTEGER | Probe point number used to identify where the message was generated in the function. |
CALLEDPRODUCT | VARCHAR(50) | The name of the product at the source of the error. This is used when the source of an error is not where the message was created. |
CALLEDCOMPONENT | VARCHAR(255) | The name of the component at the source of the error. This is used when the source of an error is not where the message was created. |
CALLEDFUNCTION | VARCHAR(255) | The name of the function at the source of the error. This is used when the source of an error is not where the message was created. |
OSERR | INTEGER | The operating system error number. |
RETCODE | INTEGER | The product specific return code. |
MSGNUM | INTEGER | The numeric message number for the associated message, if it is available. For example, this is the numeric portion of ADM7513W. |
MSGTYPE | CHAR(3) | The type related to the message identifier, if it is available. For example, ADM is used for administration notification log messages. |
MSG | CLOB(16K) | The short description text for this record. This is the translated message text corresponding to the MSGNUM, and MSGTYPE for translated messages. For non-translated messages, this is the short description. For example : 'Bringing down all db2fmp processes as part of db2stop'. |
OBJTYPE | VARCHAR(64) | Type
of object. One of:
|
OBJNAME | VARCHAR(255) | The name of the object the event relates to, if it is available. |
OBJNAME_QUALIFIER | VARCHAR(255) | Additional information about the object, if it is available. |
EVENTTYPE | VARCHAR(24) | The event type is the action or verb associated
with this event. The possible values are:
|
EVENTDESC | VARCHAR(256) | A short representation of the key fields for this event. |
FIRST_EVENTQUALIFIERTYPE | VARCHAR(64) | The type of the first event qualifier. Event
qualifiers are used to describe what was affected by the event. The
possible values are:
|
FIRST_EVENTQUALIFIER | CLOB(16K) | The first qualifier for the event. If facility is OPTSTATS, this will be a timestamp indicating when the statistics collection occurred. |
SECOND_EVENTQUALIFIERTYPE | VARCHAR(64) | The type of the second event qualifier. If facility is OPTSTATS, the value is 'BY'. |
SECOND_EVENTQUALIFIER | CLOB(16K) | The second qualifier for the event. If facility is
OPTSTATS, the possible values are:
|
THIRD_EVENTQUALIFIERTYPE | VARCHAR(64) | The type of the third event qualifier. If facility is OPTSTATS, the value is 'DUE TO'. |
THIRD_EVENTQUALIFIER | CLOB(16K) | The third qualifier for the event. If facility is
OPTSTATS, the possible values are:
|
EVENTSTATE | VARCHAR(255) | State of the object or action as a result of the event. This can also contain a percentage indicating the progression of the event. |
EVENTATTRIBUTE | VARCHAR(255) | The event attributes. This is a list of attributes
associated with the event. when more than one attribute is used, the
list is separated by '+' characters. For example 'CACHED + LOGICAL
+ AUTO'. The possible values are:
|
EVENTSTACK | CLOB(16K) | The logical event stack at the point the record was logged when applicable. |
CALLSTACK | CLOB(16K) | The operating system stack dump for the thread that generated this record when applicable. |
DUMPFILE | CLOB(5000) | The name of the secondary dump file associated with the log record when applicable. This is a fully qualified path to a file or directory where additional information related to the message can be retrieved. |
FULLREC | CLOB(16K) | Formatted text version of the entire record. This section also contains additional DATA fields. |
MEMBER | SMALLINT | member - Database member monitor element |
HOSTNAME | VARCHAR(255) | hostname - Host name |
Usage note
The PD_GET_DIAG_HIST table function requires that the associated database has a temporary table space with minimum page size of 8K. If the page size is less than 8K, the function will return an SQL1585N error message.
Examples
Example 1: Retrieve records
from a given facility.
SELECT FACILITY, RECTYPE, TIMESTAMP, IMPACT, SUBSTR(MSG,1, 50) AS MSG
FROM TABLE (PD_GET_DIAG_HIST( 'MAIN', 'E', '', NULL,
NULL ) ) AS T
WHERE T.PROCESS_NAME = 'db2star2' OR T.PROCESS_NAME = 'db2stop2'
The
following is an example of output from this query.
FACILITY RECTYPE TIMESTAMP ...
-------------------- ------- -------------------------- ...
MAIN EX 2007-06-25-11.34.05.756171 ...
MAIN EX 2007-06-25-11.34.25.946646 ...
2 record(s) selected.
Output from this query (continued).
... IMPACT MSG
... ------------------ --------------------------------------------------
... - ADM7514W Database manager has stopped.
... - ADM7513W Database manager has started.
Example 2: Retrieve records
for a specific member.
SELECT MEMBER,DBPARTITIONNUM, FACILITY, RECTYPE, TIMESTAMP, IMPACT,
SUBSTR(MSG,1, 50) AS MSG FROM TABLE (PD_GET_DIAG_HIST('MAIN', 'E', '',
CAST (NULL AS TIMESTAMP), CAST (NULL AS TIMESTAMP), NULL ) ) AS T
WHERE T.PROCESS_NAME = 'db2star2' OR T.PROCESS_NAME = 'db2stop2' ORDER BY MEMBER
The following is an example of output from this query.
MEMBER DBPARTITIONNUM FACILITY RECTYPE TIMESTAMP ...
--------- ----------------- ------------- ------- -------------------------- ...
0 0 MAIN EI 2011-04-28-09.44.57.720041 ...
0 0 MAIN EI 2011-04-28-09.44.57.723015 ...
0 0 MAIN EI 2011-04-28-09.44.57.723736 ...
0 0 MAIN EI 2011-04-28-09.44.59.409586 ...
0 0 MAIN EX 2011-04-28-09.45.01.554096 ...
0 0 MAIN EI 2011-04-28-09.45.01.605231 ...
0 0 MAIN EI 2011-04-28-12.34.20.571551 ...
0 0 MAIN EI 2011-04-28-12.34.20.574612 ...
0 0 MAIN EI 2011-04-28-12.34.20.575323 ...
0 0 MAIN EI 2011-04-28-12.34.20.602452 ...
0 0 MAIN EI 2011-04-28-12.34.20.665227 ...
0 0 MAIN EI 2011-04-28-09.44.57.715392 ...
12 record(s) selected.
Output from this query (continued). ... IMPACT MSG
... ------------------ --------------------------------------------------
... - -
... - -
... - Obtained exclusive mode lock on the file:
... - -
... - ADM7513W Database manager has started.
... - Released lock on the file:
... - -
... - -
... - Obtained exclusive mode lock on the file:
... - ZRC=0xFFFFFBFE=-1026
... - Released lock on the file:
... - -