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

Read syntax diagramSkip visual syntax diagram
>>-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:                        
...  -                  -