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.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
>>-PD_GET_DIAG_HIST--(--facility--,--rectype--,--impact--,--start_time--,--end_time--,--member--)-><
The schema is SYSPROC.
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 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 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 |
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.
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.
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
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:
... - -