DB2 Version 9.7 for Linux, UNIX, and Windows

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 supported to filter based on the type of record, customer impact value of the record and from-until timestamps.

Syntax


>>-PD_GET_DIAG_HIST--(--facility--,--rectype--,--impact--,--start_time--,--end_time--)-><

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 DB2® 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

If this parameter is null or an empty string (''), 'ALL' is the default.

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.

If this parameter is null or an empty string (''), all records are returned.

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'

If this parameter is null or an empty string (''), all records are returned.

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.

Authorization

EXECUTE privilege on the PD_GET_DIAG_HIST table function.

Example

  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 example is a sample 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.

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.

Information returned

Table 1. Information returned by the PD_GET_DIAG_HIST table function
Column Name	Data Type	Description
FACILITY	VARCHAR(20)	A facility is a logical grouping which records relate to. The possible values are: ALL: Returns records from all facilities MAIN: Returns records from the DB2 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	VARCHAR(3)	The type of record. The possible values are: 'DI': Internal diagnostic record 'DX': External diagnostic record 'EI': Internal event record 'EX': External event record
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: 'C': Critical 'E': Error ' I': Informational 'S': Severe 'W': Warning
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: 'CRITICAL' 'IMMEDIATE' 'NONE' 'POTENTIAL' 'UNLIKELY'
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: 'APM' 'CATALOG CACHE ENTRY' 'CFG' 'CLI' 'CLP' 'CONTAINER' 'COUNTER' 'DAS' 'DB2AGENT' 'DB PART MAP ID' 'DB PART NUM' 'DBA' 'DBM' 'DMS' 'DPS' 'EDU' 'EVALUATION' 'EXTENDER' 'FCM' 'HISTOGRAM TEMPLATE' 'INDEX STATS' 'INITIAL SAMPLING' 'REDIST DB PART GROUP' 'REDIST TABLE' 'RDS' 'SAMPLING TEST' 'SERVICE CLASS' 'STATS' 'STATS DAEMON' 'TABLE' 'TABLE STATS' 'TABLE AND INDEX STATS' 'THRESHOLD' 'UDF' 'WORK ACTION SET' 'WORK CLASS SET' 'WORKLOAD'
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: 'ACCEPT' 'ACCESS' 'ADD' 'ALTER' 'ASSOCIATE' 'AVAILABLE' 'BRINGDOWN' 'CHANGE' 'CHANGECFG' 'CLOSE' 'COLLECT' 'CONNECT' 'CREATE' 'DEPENDENCY' 'DESTROY' 'DISASSOCIATE' 'DISCONNECT' 'DISPATCH' 'DROP' 'FINI' 'FREE' 'GET' 'INIT' 'INTERRUPT' 'OPEN','READ' 'RECV' 'REPLY' 'REPORT' 'REQUEST' 'RESET' 'SEND' 'START' 'STARTUP' 'STOP' 'SWITCH' 'TERMINATE' 'TRANSFER' 'WAIT' 'WORK' 'WRITE'
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: 'AT' 'BY' 'CONTEXT' 'DUE TO' 'FOR' 'FROM' 'ON' 'TO' If `facility` is OPTSTATS, the only value is 'AT'.
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: Asynchronous FABRICATE FABRICATE PARTIAL SYNCHRONOUS SYNCHRONOUS SAMPLED USER
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: Conflict Error Object unavailable RUNSTATS error Timeout
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: 'ASYNC' 'AUTO' 'CACHED' 'DIRECT' 'EXTERNAL' 'INDIRECT' 'INTERNAL' 'LOGICAL' 'PERMANENT' 'PHYSICAL' 'SYNC' 'TEMPORARY'
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.