DB2 Version 9.7 for Linux, UNIX, and Windows

PDLOGMSGS_LAST24HOURS administrative view and PD_GET_LOG_MSGS table function - Retrieve problem determination messages

The PDLOGMSGS_LAST24HOURS administrative view and the PD_GET_LOG_MSGS table function return problem determination log messages that were logged in the DB2® notification log. The information is intended for use by database and system administrators.

PDLOGMSGS_LAST24HOURS administrative view

The PDLOGMSGS_LAST24HOURS administrative view returns problem determination log messages that were logged in the DB2 notification log in the last 24 hours.

The schema is SYSIBMADM.

Refer to Table 1 for a complete list of information that can be returned.

Authorization

One of the following authorizations is required:

SELECT privilege on the PDLOGMSGS_LAST24HOURS administrative view
CONTROL privilege on the PDLOGMSGS_LAST24HOURS administrative view
DATAACCESS authority

In addition, one of the following privileges or authorities is also required:

EXECUTE privilege on the PD_GET_LOG_MSGS table function
DATAACCESS authority

Example

Get all critical log messages logged in the last 24 hours, ordered by most recent.

SELECT * FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS 
   WHERE MSGSEVERITY = 'C' ORDER BY TIMESTAMP DESC

The following example is a sample output from this query.

TIMESTAMP                  TIMEZONE    INSTANCENAME      ...
-------------------------- ----------- ----------------- ...
2005-11-23-21.56.41.240066        -300 svtdbm4           ...
                                                         ...
                                                         ...
                                                         ...
                                                         ...
                                                         ...
                                                         ...
2005-11-23-21.56.39.150597        -300 svtdbm4           ...
2005-11-23-21.56.37.363384        -300 svtdbm4           ...
                                                         ...
                                                         ...
                                                         ...
2005-11-23-21.56.35.880314        -300 svtdbm4           ...
                                                         ...
                                                            
                                                            
  4 record(s) selected.

Output from this query (continued).

...  DBPARTITIONNUM DBNAME        PID         PROCESSNAME          ... 
...  -------------- ------------- ---...----- -------------------- ... 
...               0 CAPTAIN           4239374 db2agent (CAPTAIN) 0 ... 
...                                                                ... 
...                                                                ... 
...                                                                ... 
...                                                                ... 
...                                                                ... 
...                                                                ... 
...               0 CAPTAIN           4239374 db2agent (CAPTAIN) 0 ... 
...               0 CAPTAIN           4239374 db2agent (CAPTAIN) 0 ... 
...                                                                ... 
...                                                                ... 
...                                                                ... 
...               0 CAPTAIN           4239374 db2agent (CAPTAIN) 0 ... 
...                                                                ... 
...                                                                ...

Output from this query (continued).

...TID APPL_ID                         COMPONENT             ...
...--- ------------------------------- --------------------- ...
...  1 9.26.15.148.36942.051124025612  oper system services  ...
...                                                          ...
...                                                          ...
...                                                          ...
...                                                          ...
...                                                          ...
...                                                          ...
...  1 9.26.15.148.36942.051124025612  base sys utilities    ...
...  1 9.26.15.148.36942.051124025612  relation data serv    ...
...                                                          ...
...                                                          ...
...                                                          ...
...  1 9.26.15.148.36942.051124025612  relation data serv    ...
...                                                          ...
...                                                          ...

Output from this query (continued).

... FUNCTION           PROBE  MSGNUM      MSGTYPE ...
... ------------------ ------ ----------- ------- ...
... sqloSleepInstance      38         504 ADM     ...
...                                               ...
...                                               ...
...                                               ...
...                                               ...
...                                               ...
...                                               ...
... sqleMarkDBad           10        7518 ADM     ...
... sqlrr_dump_ffdc        10           1 ADM     ...
...                                               ...
...                                               ...
...                                               ...
... sqlrr_dump_ffdc        10           1 ADM     ...
...                                               ...

Output from this query (continued).

...  MSGSEVERITY MSG                                       
...  ----------- -------------------------------------     
...  C           ADM0504C  An unexpected internal          
...              processing error has occurred.  ALL       
...              DB2 PROCESSES ASSOCIATED WITH THIS        
...              INSTANCE HAVE BEEN SUSPENDED.             
...              Diagnostic information has been           
...              recorded.  Contact IBM Support            
...              for further assistance.                   
...  C           ADM7518C  "CAPTAIN " marked bad.          
...  C           ADM0001C  A severe error has occurred.    
...              Examine the administration notification   
...              log and contact IBM Support if            
...              necessary.                                
...  C           ADM0001C  A severe error has occurred.    
...              Examine the administration notification   
...              log and contact IBM Support if necessary.

PD_GET_LOG_MSGS table function

The PD_GET_LOG_MSGS table function returns the same information as the PDLOGMSGS_LAST24HOURS administrative view, but allows you to specify a specific time period that is not limited to the last 24 hours.

Refer to Table 1 for a complete list of information that can be returned.

Syntax


>>-PD_GET_LOG_MSGS--(--oldest_timestamp--)---------------------><

The schema is SYSPROC.

Table function parameter

oldest_timestamp: An input argument of type TIMESTAMP that specifies a valid timestamp. Entries are returned starting with the most current timestamp and ending with the log entry with the timestamp specified by this input argument. If a null value is specified, all log entries are returned.

Authorization

EXECUTE privilege on the PD_GET_LOG_MSGS table function.

Examples

Example 1: Retrieve all notification messages logged for database SAMPLE on instance DB2 in the last week for all database partitions. Report messages in chronological order.

SELECT TIMESTAMP, APPL_ID, DBPARTITIONNUM, MSG 
   FROM TABLE ( PD_GET_LOG_MSGS( CURRENT_TIMESTAMP - 7 DAYS)) AS T 
   WHERE INSTANCENAME = 'DB2' AND DBNAME = 'SAMPLE' 
   ORDER BY TIMESTAMP ASC

The following example is a sample output from this query.

TIMESTAMP                  APPL_ID                    DBPARTITIONNUM ...
-------------------------- -------------------------- -------------- ...
2005-11-13-12.51.37.772000 *LOCAL.DB2.050324175005                 0 ...
                                                                     ...
2005-11-13-12.51.37.772001 *LOCAL.DB2.050324175005                 0 ...
                                                                     ...
2005-11-13-12.51.37.781000 *LOCAL.DB2.050324175005                 0 ...
                                                                     ...
2005-11-13-12.51.37.781001 *LOCAL.DB2.050324175005                 0 ...
                                                                     ...
                                                                     ...
2005-11-17-14.12.39.036001 *LOCAL.DB2.041117191249                 0 ...
2005-11-17-14.12.39.056000 *LOCAL.DB2.041117191249                 0 ...
2005-11-17-14.13.04.450000 *LOCAL.DB2.041117191307                 0 ...
2005-11-17-14.13.04.460000 *LOCAL.DB2.041117191307                 0 ...
2005-11-17-14.18.29.042000 *LOCAL.DB2.041117190824                 0 ...
...
...
...

Output from this query (continued).

... MSG                                                            
... ----------------------------------------------------------...--
... ADM5502W  The escalation of "143" locks on table    
...    "SYSIBM  .SYSINDEXAUTH" to lock intent "X" was successful. 
... ADM5502W  The escalation of "144" locks on table 
...    "SYSIBM  .SYSINDEXES" to lock intent "X" was successful. 
... ADM5502W  The escalation of "416" locks on table  
...    "SYSIBM  .SYSINDEXCOLUSE" tolock intent "X" was successful.
... ADM5500W  DB2 is performing lock escalation.  The total  
...    number of locks currently held is "1129", and the target 
...    number of locks to hold is "564". 
... ADM7506W  Database quiesce has been requested.   
... ADM7507W  Database quiesce request has completed successfully.
... ADM7510W  Database unquiesce  has been requested. 
... ADM7509W  Database unquiesce request has completed successfully.  
... ADM4500W  A package cache overflow condition has occurred.  There 
...    is no error but this indicates that the package cache has   
...    exceeded the configured maximum size.  If this condition persists, 
...    you may want to adjust the PCKCACHESZ DB configuration parameter.

Example 2: Retrieve all critical errors logged on instance DB2 for database partition 0 in the last day, sorted by most recent.

SELECT TIMESTAMP, DBNAME, MSG 
   FROM TABLE (PD_GET_LOG_MSGS(CURRENT_TIMESTAMP - 1 DAYS)) AS T 
   WHERE MSGSEVERITY = 'C' AND INSTANCENAME = 'DB2' AND 
   DBPARTITIONNUM = 0 
   ORDER BY TIMESTAMP DESC

The following example is a sample output from this query.

TIMESTAMP                  DBNAME      MSG                      
-------------------------- ----------- -------------------------
2004-11-04-13.49.17.022000 TESTSBCS    ADM0503C  An unexpected  
                                       internal processing error
                                       has occurred.  ALL DB2   
                                       PROCESSES ASSOCIATED WITH
                                       THIS INSTANCE HAVE BEEN  
                                       SHUTDOWN.  Diagnostic    
                                       information has been     
                                       recorded.  Contact IBM   
                                       Support for further      
                                       assistance.              
2004-11-04-11.32.26.760000 SAMPLE      ADM0503C  An unexpected  
                                       internal processing error
                                       has occurred.  ALL DB2   
                                       PROCESSES ASSOCIATED WITH
                                       THIS INSTANCE HAVE BEEN  
                                       SHUTDOWN.  Diagnostic    
                                       information has been     
                                       recorded.  Contact IBM   
                                       Support for further      
                                       assistance.              
                                                                
  2 record(s) selected.

Example 3: Retrieve messages written by DB2 processes servicing application with application ID of *LOCAL.DB2.050927195337, over the last day.

SELECT TIMESTAMP, MSG 
   FROM TABLE (PD_GET_LOG_MSGS(CURRENT_TIMESTAMP - 1 DAYS)) AS T
   WHERE APPL_ID = '*LOCAL.DB2.050927195337'

The following example is a sample output from this query.

TIMESTAMP                  MSG                                       
-------------------------- ------------------------------------------
                                                                     
2005-06-27-21.17.12.389000 ADM4500W  A package cache overflow        
                           condition has occurred.  There is no error
                           but this indicates that the package cache 
                           has exceeded the configured maximum       
                           size.  If this condition persists, you    
                           may want to adjust the PCKCACHESZ DB      
                           configuration parameter.                  
2005-06-27-18.41.22.248000 ADM4500W  A package cache overflow        
                           condition has occurred.  There is no error
                           but this indicates that the package cache 
                           has exceeded the configured maximum       
                           size.  If this condition persists, you    
                           may want to adjust the PCKCACHESZ DB      
                           configuration parameter.                  
2005-06-27-12.51.37.772001 ADM5502W  The escalation of "143" locks   
                           on table "SYSIBM  .SYSINDEXAUTH" to       
                           lock intent "X" was successful.           
2005-06-27-12.51.37.772000 ADM5502W  The escalation of "144" locks   
                           on table "SYSIBM  .SYSINDEXES" to lock    
                           intent "X" was successful.                
2005-06-27-12.51.37.761001 ADM5502W  The escalation of "416" locks   
                           on table "SYSIBM  .SYSINDEXCOLUSE" to     
                           lock intent "X" was successful.           
                                                                     
...

Example 4: Find all instances of message ADM0504C in the notification log. Note that the messages considered are not limited by a timestamp. This could be an expensive operation if the notification logfile is very large.

SELECT TIMESTAMP, DBPARTITIONNUM, DBNAME, MSG 
   FROM TABLE (PD_GET_LOG_MSGS(CAST(NULL AS TIMESTAMP))) AS T 
   WHERE MSGNUM = 504 AND MSGTYPE = 'ADM' AND MSGSEVERITY = 'C'

The following example is a sample output from this query.

TIMESTAMP                   DBPARTITIONNUM DBNAME       ... 
--------------------------  -------------- -------------... 
2005-11-23-21.56.41.240066               0 CAPTAIN      ...
...
...
...
...
...
...
...
...
...

Output from this query (continued).

... APPL_ID                           MSG                      
... --------------------------------- -------------------------
... 9.26.15.148.36942.051124025612    ADM0504C  An unexpected  
...                                   internal processing error
...                                   has occurred.  ALL DB2   
...                                   PROCESSES ASSOCIATED WITH
...                                   THIS INSTANCE HAVE BEEN  
...                                   SUSPENDED.  Diagnostic   
...                                   information has been     
...                                   recorded.  Contact IBM   
...                                   Support for further      
...                                   assistance.

Information returned

Note: In a partitioned database environment, the order in which log messages are returned cannot be guaranteed. If the order of log records is important, the results should be sorted by timestamp.

Table 1. Information returned by the PDLOGMSGS_LAST24HOURS administrative view and the PD_GET_LOG_MSGS table function
Column name	Data type	Description
TIMESTAMP	TIMESTAMP	The time when the entry was logged.
TIMEZONE	INTEGER	Time difference (in minutes) from Universal Coordinated Time (UCT). For example, -300 is EST.
INSTANCENAME	VARCHAR(128)	Name of the instance that generated the message.
DBPARTITIONNUM	SMALLINT	The database partition that generated the message. For a non partitioned database environment, 0 is returned.
DBNAME	VARCHAR(128)	The database on which the error or event occurred.
PID	BIGINT	Process ID of the process that generated the message.
PROCESSNAME	VARCHAR(255)	Name of process that generated the message.
TID	BIGINT	ID of the thread within the process that generated the message.
APPL_ID	VARCHAR(64)	appl_id - Application ID monitor element
COMPONENT	VARCHAR(255)	The name of the DB2 component that is providing the message. For messages written by user applications using the db2AdminMsgWrite API, "User Application" is returned.
FUNCTION	VARCHAR(255)	The name of the DB2 function that is providing the message. For messages written by user applications using the db2AdminMsgWrite API, "User Function" is returned.
PROBE	INTEGER	Unique internal identifier that allows DB2 Customer Support and Development to locate the point in the DB2 source code that generated the message.
MSGNUM	INTEGER	The numeric message number for the error or event.
MSGTYPE	CHAR(3)	Indicates the message type: ADM (for messages written to the administration notification log) or NULL if the message type cannot be determined.
MSGSEVERITY	CHAR(1)	Message severity: C (critical), E (error), W (warning), I (informational) or NULL (if the message severity could not be determined).
MSG	CLOB(16K)	Notification log message text.