Purpose

The objective of this document is to provide a list of useful SQL statements for the PI Adapter Engine (AE). These statements can be used to help troubleshoot various problems in the AE.

Feel free to add your SQL statements to this list as needed. Please only add SELECT statements only. There should be no UPDATE or DELETE statements added to this document. UPDATE or DELETE SQL statements should only be run on your PI system when they have been provided by SAP development support team via a SAP customer incident or via a SAP note only. Otherwise there will be some unexpected data inconsistency issue caused.

Overview

This document shows the SQL statements and the purpose of each statement. You should use a DB tool such as SQLPlus or DBVisualizer (see wiki: How to connect DBVisualizer to your PI system ) to run these SQL statements on your PI system.

SQL 1: How many messages are stored in the AF Messages table, giving the count against message status.

This information may be needed if you are having archiving/deleting problems or problems with running RUN_OFFLINE_MIGRATION.

select COUNT(*), STATUS from <JavaSchema>.<AF Messages table> GROUP BY STATUS ;

Replace:
(a)
<AF Messages table >
< Use XI_AF_MSG for PI 7.0x and lower, BC_MSG for PI 7.1 and higher >

(b)
<SID> with PI system ID.

For example:
select COUNT(*), STATUS
from SAPSR3DB.BC_MSG
GROUP BY STATUS;

Result:
COUNT(1) STATUS
6624 DLVD
1472451 NDLV

SQL 2: How many messages are stored in the AF Messages audit log table, with more than 30 audit log entries for a single message.

This information may be needed to help identify problem messages and channel IDs which are causing the message audit log table to grow rapidly.

select count( * ) b, MSG_ID from <JavaSchema>.<AF Messages Audit Log table>
having count(MSG_ID) > 30
group by MSG_ID
order by b DESC

Replace:
(a)
<AF Messages table >
< Use XI_AF_MSG for PI 7.0x and lower, BC_MSG for PI 7.1 and higher >

(b)
<SID> with PI system ID.

For example:
select count( * ) b, MSG_ID from SAPSR3DB.BC_MSG
having count(MSG_ID) > 30
group by MSG_ID
order by b DESC

Result:
count 428962
MSG_ID 260eca99-293d-4389-01f9-ae9c41e0dd10

SQL 3: How many messages are stored in the AF Messages table for any single day.

The count of messages for any given date in AF Messages table. Useful to know if MDT in RWB/PIMON is down or count of messages per day is greater than the maximum number of records that the MDT in RWB/PIMON will allow you to view.

select COUNT( * ) AS NBMSGS from <JavaSchema>.<AF Messages table>
where to_char(SENT_RECV_TIME, 'YYYY-MM-DD HH24:MI:SS') > '<DATETIME>'
and to_char(SENT_RECV_TIME, 'YYYY-MM-DD HH24:MI:SS') < '<DATETIME>'

Replace:
(a)
<AF Messages table>
< Use XI_AF_MSG for PI 7.0x and lower, BC_MSG for PI 7.1 and higher >

(b)
<SID> with PI system ID.

(c)
<DATETIME> with the date you want the count of messages for.

For example:

select COUNT( * ) AS NBMSGS from SAPSR3DB.BC_MSG
where to_char(SENT_RECV_TIME, 'YYYY-MM-DD HH24:MI:SS') > '2012-02-29 00:00:00'
and to_char(SENT_RECV_TIME, 'YYYY-MM-DD HH24:MI:SS') < '2012-02-29 23:59:00'

Result:
NBMSGS
55941

SQL 4: The message ID and message size of all messages stored in the AF Messages table, where the size of the message is greater than ~100MB.

This information may be needed to help identify problem messages that may be causing OutOfMemory issues with the AF Archiving jobs.

select msg_id, SENT_RECV_TIME, bytes_length
from <JavaSchema>.<AF Messages table>
where bytes_length > '100000000'
order by bytes_length asc

Replace:
(a)
<AF Messages table>
< Use XI_AF_MSG for PI 7.0x and lower, BC_MSG for PI 7.1 and higher >

(b)
<SID> with PI system ID.

Related Content:

Related Notes:

SAP Note 1518045 - Messages failed with incorrect pipeline URL are not successfully resent after the configuration is corrected
SAP Note 872388 - Troubleshooting Archiving and Deletion in PI
SAP KBA 2592310 - PO/AEX Messaging tables relevant for DB reorg jobs

Related Documents