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 database notification log.
The information returned is intended for use by database administrators and system administrators.
Depending on if you
are using the administrative view or the table function, refer to
one of the following sections:
PDLOGMSGS_LAST24HOURS administrative view
The PDLOGMSGS_LAST24HOURS administrative view returns problem determination log messages that were logged in the database 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
- DBADM authority
- SQLADM authority
- ACCESSCTRL authority
- SECADM authority
Default PUBLIC privilege
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
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 DESCThe
following is an example of output from this query.
TIMESTAMP TIMEZONE INSTANCENAME MEMBER ...
-------------------------- ----------- ----------------- ------- ...
2005-11-23-21.56.41.240066 -300 svtdbm4 0 ...
...
...
...
...
...
...
2005-11-23-21.56.39.150597 -300 svtdbm4 0 ...
2005-11-23-21.56.37.363384 -300 svtdbm4 0 ...
...
...
...
2005-11-23-21.56.35.880314 -300 svtdbm4 0 ...
...
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.
Authorization
One of the
following authorizations is required:
- EXECUTE privilege on the PD_GET_LOG_MSGS table function
- 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
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. member
- An optional input argument of type INTEGER that specifies a valid database member from which the records should be fetched from. 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 a cluster caching facility (CF) is specified, an active member will be used to request this data. If the notification log is not accessible, an error will be returned.
Information returned
Note:
In a multi-member 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.
| 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 | dbpartitionnum - Database partition number monitor element |
| 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 database 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 database 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 Customer Support and Development to locate the point in the 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. |
| MEMBER | SMALLINT | member - Database member monitor element |
Examples
Example 1: Retrieve all
notification messages logged for database SAMPLE on instance XYZ 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 = 'XYZ' AND DBNAME = 'SAMPLE'
ORDER BY TIMESTAMP ASC
The following is an example of 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 XYZ 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 = 'XYZ' AND
DBPARTITIONNUM = 0
ORDER BY TIMESTAMP DESCThe following is an example of 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 database 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 is an example of 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 is an example of 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.
Example 5: Retrieve all notification messages
for a specific member.
SELECT MEMBER,DBPARTITIONNUM, TIMESTAMP, SUBSTR(MSG,1, 50)
FROM TABLE (PD_GET_LOG_MSGS(NULL,-2))The following
is an example of output from this query.
MEMBER DBPARTITIONNUM TIMESTAMP ...
------ -------------- -------------------------- ...
0 0 2011-04-27-09.51.17.725916 ...
...
2 2 2011-04-27-09.51.16.801966 ...
...
1 1 2011-04-27-09.51.16.747745 ...
...
3 3 2011-04-27-09.51.15.655162 ...
4 record(s) selected.Output from this query (continued).
... 4
... ------------------------------- -------------------
... ADM7513W Database manager has started.
...
... ADM7513W Database manager has started.
...
... ADM7513W Database manager has started.
...
... ADM7513W Database manager has started.