Purpose

The purpose of this page is to present troubleshooting methods to perform investigations for both Archive and Deletion procedures.

Overview

When dealing with issues involving the Archiving and Deletion procedures, we need to have information about a little bit of everything in order to check parameters, reports and current configurations that are set in the system to achieve a solution.

Focusing on the Integration Engine side, there are some range of information we should focus in order to understand the processes and how to analyze them in a more efficient way.

As an overview, when a message is created, let’s say for an external system, it’ll go from the Sender IE side, passing through the Integration Server (IS), where it’s validated with processes of mapping and security verification in the J2EE side and then it goes to the Receiver IE side for an external system as target. All the process between the Sender, IS and Receiver is done taking into account the Database as well, as the messages and stored on that.

Main tables to be considered

As mentioned, there are several tables that need to be considered related to data, properties, interfaces and versions, for example, related to the entire process, inside the Integration Engine, for the message processing.

• SXMSPMAST: This is the main table to be considered and corresponds to the master data’s, status, timestamp, message ID’s, etc… In brief, this one is the master table for all messages being processed through XI and is highly used as part of Persistence Layer activities, i.e., Archiving and Delete. It has 1 record per message and pipeline.
• SXMSPEMAS: This table also corresponds to master data’s and is known as an enhanced or extended version of the SXMSPMAST, but also deals with namespaces, business systems and the sender and receiver interfaces for the messages.
• SXMSPERROR: This table is related to the incorrect entries/error information, if any available. If necessary you’ll have the error data’s, categories, ID’s and related information that holds entries for each message with an error.
• SXMSPVERS: If needed to check message version, versions, release versions, i.e., information about all the versions of a message, that’s the table that needs to be consulted. As additional information, it can store information like Productive, Log and Error versions. Besides that it is only possible to create new versions. Since PI 7.10 there is a Payload link between versions for best analysis. It has 1 record per message version and pipeline.
• SXMSCLUP and SXMSCLUR: Related to mainly property cluster and headers for the CLUP and resource cluster and payloads for the CLUR, they are the largest tables in terms of size since storing properties and resources from all the messages, depending on how much the system is loaded, consumes a lot. In brief we could just say the CLUP one is related to header objects and the CLUR one to payload of message.

Reorganization of messages

Basically, there are two different tracks for messages:

• • With archiving: It’ll pass through the Archiving phase as per report RSXMB_ARCHIVE_MESSAGES and then will be considered for Deletion with report RSXMB_DELETE_ARCHIVED_MESSAGES.
  • Without archiving: It’ll go straight to the Deletion process using the report RSXMB_DELETE_MESSAGES.

For your information, as we have mentioned the table SXMSPHIST, the report for deleting messages from that table is the RSXMB_DELETE_HISTORY.

Going to SE38 and checking the report RSXMB_SHOW_STATUS, we can see the status of some messages, to check if they are in a final status, e.g., status 003 (processed with success).

There're three pre-requisites for deleting/archiving messages:

1. Age of message (older than the retention period)
2. Message status is final (e.g. 003, 021, …)
3. Adapter status is final (000 or 006)

Also, we have to consider the messages in report RSXMB_SHOW_REORG_STATUS, that shows us the messages that are being 'considered' and those that are being 'ignored'.

The first one takes all the three pre-requisites in account; the second one takes only the first pre-requisite in account. The statuses of the messages are taken into account when the default job you setup runs. A message is only allowed to be deleted/archived if ALL of the conditions are met.

In transaction SXMS_IECONF or SXMB_ADM -> Integration Engine Configuration, you will find parameters related to the retention period, for both ARCHIVE and DELETION categories. Alternatively one can access transaction SXMB_ITFACTION -> “Retention Periods” (or for 6.40: SXMB_ADMIN -> “Integration Engine” -> Configuration -> “Define Interfaces for Archiving and Retention Periods” -> “Retention Periods”)

as well as for the number of messages to be deleted in one single loop

Non-final message state

Messages that are not in a final status, e.g., with errors or scheduled, need some particular actions in order to be corrected.

All actions are described in SAP Note 872388.

Non-final adapter state

A message with status 003 (processed with success) can have its adapter status not yet in a final status. Generally the adapter status 000 and 006 are in a final status, however for such cases where you find messages having adapter status 001, 007 or 008 you have two basic solutions:

• Either running SXMS_REFRESH_ADAPTER_STATUS;
• Or reorganization of BPE is required.

as lined out SAP Note 872388 -> section 4 -> k.2).

Message status overall

For more information about the message statuses of the messages, check the following table:

Deletion procedures

In PI we use 2 different procedures for deletion:

• Simple Deletion Procedure: Scheduling the standard Deletion job SAP_BC_XMB_DELETE_<client> which consists in 2 steps
  • RSXMB_DELETE_MESSAGES: Always performs deletion of messages. However, depending on the selected deletion procedure the method of deletion differs:
    • If simple deletion is selected, messages are erased physically from data base
    • If switch deletion procedure is selected, messages are simply marked as “deleted” which we call “logical deletion”
  • RSXMB_TABLE_SWITCH: Afterwards report RSXMB_TABLE_SWITCH runs:
    • If simple deletion is selected, this report does nothing and terminates immediately
    • if switch deletion procedure is selected, report checks whether the threshold (DROP_MAX_TABLE_LOAD) is exceeded. If so the actual switch to the secondary data container is done
• Switch Deletion Procedure: It’ll trigger the Deletion process automatically.

We have also the report RSXMB_DELETE_ARCHIVED_MESSAGES, for when a message was previously archived and then needs to be deleted. For a better understanding on the 3 deletion reports, consider checking the following table:

Switch Procedure

The main reasons for Switch Procedure to be used are:

• • Performance of deletion
  • Deal with Fragmentation (Oracle)

The Simple Deletion Procedure is more expensive due to the fact since it’s a “steady-state”, by deleting as many messages from data container as new messages arrive within same period of time.

The switch procedure is beneficial from performance perspective if no more than 20% of all messages are copied over. In SM37 we can check the Job Log for what is the percentage value for the Transferred Messages.
 
The % rating of messages copied over is considered beneficial when it’s < 20%; when between 20-50% it’s considered as “yellow”; when it’s > 50% it’s “red” and then not beneficial at all.

What happens in the Switch Procedure is that it’ll fill the current container, let’s say container 1, which is the SXMSPMAST and will transfer the data to container 2, i.e., SXMSPMAST2, only for the active messages. They are copied over whereas all old messages remain in data container 1 waiting for TRUNCATE (=physical deletion). One more thing that needs to be taken into account is the value of the Fill Level, which is the % value that is configured from DROP_MAX_TABLE_LOAD parameter, configured in transaction SXMS_IECONF or SXMB_ADM -> Integration Engine Configuration.

In practice, it is related to the percentage of the "Data records expected" in SE11 -> Technical settings.

 
In theory, we referred to this number as the “Maximum Number of Entries” which leads to many misunderstandings. Therefore the UI and the Logs have been improved; in addition 2 new documentation objects explain the exact meaning of the numbers shown in “Persistence Layer Analysis” (transaction SXMS_MONI_DB):
 

 

 
For the Switch Procedure we have also the option of moving the messages from a container to the other, instead of copying. The subparameter to be used for that purpose is the MOVE_INSTEAD_OF_COPY for TABLE_SWITCH. However bear in mind that this nullifies the performance benefit and has no effect to disc space consumption for Oracle and IBM. With that, we also have ROWS_PER_LOOP and CLUSTER_MB_MAX parameters in which the amount of data to be copied in one go is controlled.
The proper configuration of the switch procedure depends on:

• Current settings for retention period
• Message throughput
• Sync : Async ratio
• Delete : Archive ratio

Setting up the switch procedure needs to be done for each system individually and is considered consulting. Besides that, turning on the switch deletion procedure might result in a statistic issue, in particular when running on Oracle or IBM database platforms, due to the fact that during the container swapping the statistics will be outdated for a while.
 
The general solution for these cases are:

• Oracle: SAP Note 1020260
• IBM: SAP note 1483627

By applying SAP Note 1020260 hard-coded statistics are set for a few critical tables. For these tables the automatic update is skipped. It's possible to check, for all other tables, whether the optimizer statistics are maintained by going to transaction DB14 and clicking on BRCONNECT. The “Update optimizer statistics” database operation should be running and updated, but it does not give us information if the note itself has been applied. For that purpose, we can go to transaction DB20, enter the name of the database table of PI persistence layer (for example, SXMSPMAST) and display the statistics (refresh button).

For IBM specifically, in transaction SXMS_IECONF or SXMB_ADM -> Integration Engine Configuration, category DELETION, you will find parameters for configuring the new feature called “Statistics on Demand”. For that to be turned on/off, you need to set subparameter called UPDATE_STATISTICS, for the TABLE_SWITCH parameter. You can even control how often this update will take place by setting as parameter the own UPDATE_STATISTICS with subparameters:

• INITIAL_RUN_ROWS: It’s related to the first run
• NEXT_RUN_GROWTH_FACTOR: Relates to the intermediate run
• NUMBER_RUN_MAX: Last run

For an illustration about how it works, check the picture below:
 

General issues

In order to discuss the most common issues and their solutions, this section is created.

Release Disc Space

A case where you have already deleted lots of messages, however, the consumption of disc space remains the same is simply because the Deletion of messages means that the allocated space is no longer used, however it does not mean that the space is released to the Operational system. Releasing the disc space is up to the database management system and
the procedure highly depends on the database platform in use.

• Oracle: Online reorganization using BRSPACE tool or run switch deletion procedure
• IBM: Online or Offline reorganization
• MS SQL and MaxDB: The database does it for you

In the case of Oracle where the reorganization using BRSPACE does not work, it may be due to the fact there is insufficient disc space to perform its steps that are:

1. Export data from table to temp space;
2. Truncate table;
3. Import data from temp space to table.

The solution for this case is to just add more disc space.

IDX5 messages

First things first, when talking about IDX5 we need to understand that it’s related to the IDOC processing from R/3 application, using an IDOC adapter, passing through XI, for further message processing and then it goes to R/3 application as target as well. For both Sender and Receiver IDOCs we do have a GUID related to the objects.

If you have a requirement of deleting messages from the system since you are dealing with a high volume of messages and need to keep with the IDX5 references for tracking purposes, the solution for this case would be de-coupling of deletion of IDX5 references.

IDX5 references get their own retention period and will remain at least as long in the system as the related PI message.

For that, in SXMS_IECONF or SXMB_ADM -> Integration Engine Configuration, we can set the parameter “RELATED_OBJECTS”. Related objects are entities that are directly related to a message; however, they are not part of the same. It can be used for correlation of incoming IDoc and PI message, correlation of PI message and outgoing IDoc, data for processing MMF messages, data for processing by Sync-Async-Bridge, etc…

For this case we were talking about earlier, we can turn on/off delayed deletion of IDX5 references by setting the RELATED_OBJECTS parameter, for category DELETION, using the “IDX5_DELAYED” as subparameter.

For the retention period we’ll use the known parameter PERSIST_DURATION  and PERSIST_DURATION_UNIT but with “IDX5” as subparameter.

As per SAP Note 1405871, the IDX5 references can be integrated into the results of RSXMB_SHOW_REORG_STATUS report.

 Deletion of history data

When dealing with the history data, sometimes we can face issues related to the long time for deletion of this data. In order to speed-up this, a re-coding of the deletion report RSXMB_DELETE_HISTORY was made and the deletion rate was improved by factors. It is available for the following releases/SP’s:

XI3.0 SP 26
PI 7.00 SP 22
PI 7.01 SP 07
PI 7.02 SP 04
PI 7.10 SP 10
PI 7.11 SP 05

Related Content

Related Docs

XI Tables

Exposing the XI monitoring functionality as a Web Service

Archiving messages on Integration Engine

Examining the status of messages in a PI system

Overview of the Switch Deletion Procedure

PI Archiving Conceptual Overview

Related Notes

SAP note 872388: Troubleshooting Archiving and Deletion in PI

SAP note 1020260: Delivery of Oracle statistics

SAP note 1042379: BPE-HT: Deleting messages that are no longer used

SAP note 1405871: XI runtime: Retention period for IDX5 references

SAP note 1483627: PI7.0x/7.1x: Table statistics during switch procedure