Skip to main content

SAP PI 101: The database is getting fat!

As a SAP Basis consultant is a matter of time that you have to deal with a SAP PI (Process Integration). Those little b******s are in the middle of technical and customizing field so it is interesting to learn a bit about them. Otherwise you will find yourself on a situation like this one without knowing what to do…

The problem

The PSAPSR3DB tablespace of a SAP PI system growth really quick in a couple of hours. And with really quick I mean this:

PSAPSR3DB Growth

As you can see the daily growth is huge, about 300GB in a couple of days. Apparently there was no problem within the system, I asked the PI Team and they din’t find an issue on the system. This is when your SAP PI skills comes into play…

The investigation

First thing was to find the segment on the database that was growing too much. For doing so you can use transaction DB02 in case the SAP PI system is a dual-stack system. Otherwise you´ll have to figure it out using your database tools.  After checking it I saw that the first segment on the ranking was a LOBSEGMENT:

Top Daily Growth Segment
Top Daily Growth Segment

The Chg.Size/day is not even close to the total growth of the database, anyway this was a clue. For knowing which table belonged the LOBSEGMENT I execute the following query:

Table of the LOBGSEGMENT
Table of the LOBGSEGMENT

The LOBSEGMENT is related to table BC_MSG so at least I have something that I could work with. I looked for information about this table and I found this interesting wiki. So it seems the table is used as master table for persistence of message on the Java side. I execute the query that appears there to check the status of the messages and the number of them per status:

Inline image 2
Number of message per type

There are a lot of message with the status “On Hold”. Apparently some of the messages that arrived to the system were not being processed and they were on the “On Hold” status. This triggered that the BC_MSG table growth so much in a couple of days.

The solution

Once I knew what happened the next step was to find a solution. I got several message ID that were “On hold” status:

Inline image 1
On Hold Messages

XPI Inspector is an awesome tool you can use in this situations. SAP Note 1514898 – XPI Inspector for troubleshooting XI  describes how to download and install the application (just to deploy an ear file on the java stack). After installing it I followed SAP Note 2135741 – Finding the Message Blocking an EOIO Sequence in the PI Adapter Engine to find what caused the issue. I entered the message ID on the XPI Inspector and checked the results:

XPI Inspector main screen
XPI Inspector main screen

There is a place where you can see the Blocking EOIO Message where you can see the blocking message:

Blocking EOIO Message
Blocking EOIO Message

If you click on the message ID you can see some information about it. On the botton of the page you can find information Java AE Audit Logs:

Error related to meesage
Error related to message

So the message is getting blocked because the Channel has been stopped. I checked more message with the status “On Hold” and they were blocked by the same message. The issue was caused by a message with error status that failed to be processed because the Communication Channel was stopped by someone.

The final solution was quite easy, the PI Team stopped the sent of messages from the SAP PI system to the external system . You can reprocess the “On Hold” messages and the “Error” messages or just cancel them using the Message Monitor in the Monitoring Home for PI systems.

Next step was to recover the space on the tablespace. For doing so it was possible to execute an online reorganization of the table BC_MSG on tablespace PSAPSR3DB. Since it is online reorganization it is not necessary to stop the database so you can do it during business time. I will talk about this topic on a different post.

Conclusions

Unexpected and unknown action on the systems can cause a lot of trouble. In this case some on the SAP PI System decided to stop a Communication Channel without warning anyone and as a result it caused a huge trouble to DB Team and Basis Team. Remember kids, don’t play with a production system!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.