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


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

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.

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

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	dbpartitionnum - Database partition number monitor element
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 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: EVALUATION INDEX STATS INITIAL SAMPLING TABLE STATS TABLE AND INDEX STATS SAMPLING TEST STATS DAEMON
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.
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:                        
...  -                  -