The PD_GET_DIAG_HIST table function returns log records, event records and notification records from a given facility. Options are also supported to filter based on the type of record, customer impact value of the record and from-until timestamps.
>>-PD_GET_DIAG_HIST--(--facility--,--rectype--,--impact--,--start_time--,--end_time--)-><
The schema is SYSPROC.
EXECUTE privilege on the PD_GET_DIAG_HIST table function.
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'
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.
... IMPACT MSG
... ------------------ --------------------------------------------------
... - ADM7514W Database manager has stopped.
... - ADM7513W Database manager has started.
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.
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 | The partition number where the message was created. For non-partitioned database, 0 is returned. |
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 DB2 is part
of. The 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 partition 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(16KB) | 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) | The type of object the event applies to, if
it is available. The possible values are:
|
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. |