This document is intended to provide details regarding the switch deletion procedure along with tips to configure and monitor this procedure.
The functionality of the Switch procedure will be explained along with the method to configure its frequency.
The Switch Procedure Logic
The switch procedure is based on the fact that an identical table copy exists for each persistence layer table. The copies are shipped by SAP. To begin with, the original tables, such as SXMSPMAST, are the active tables. All XML messages are saved in these tables.
If the switch procedure is activated then this job is executed by the deletion job SAP_BC_XMB_DELETE<CLIENT>. When the deletion procedure runs, it checks the percentage fill level of the table (report RSXMB_ANALYZE_DB) and if this is higher than that which is the configured threshold for the switch procedure, the switch is started. When this occurs the copy of the tables become the active containers i.e. SXMSPMAST2 becomes active. All new messages will now be written to this active container. Any messages which are not eligible to be deleted will be copied across from the inactive container to the active container, i.e. messages inside the configured retention period and any messages with a non-final message and adapter status.
When the Switch run is completed the inactive container is dropped and recreated again immediately. When the Switch procedure runs once more the container which has become active after the last run will become the inactive container and the same process is carried out once more.
From the attached screenshot of report RSXMB_ANALYZE_DB, we can see that the original container is the active container (SXMSPMAST, SXMSCLUR etc....), while the current fill level is less than 1%.
Activating and configuring the Switch Procedure
To activate the Switch procedure for your PI system you can run the report RSXMB_CUSTOMIZE_SWITCH and select ‘Switch procedure activated’. It should be noted that the Switch procedure can only be deactivated when the original tables are the active containers. Therefore if container 2 (SXMSPMAST2, SXMSCLUR2, etc…), is the active container you cannot deactivate the Switch at that time.
To configure the percentage fill level of the table at which the Switch procedure is initialized you set the DELETION parameter DROP_MAX_TABLE_LOAD. The value of this parameter is the percentage of the table that needs to be used before the Switch procedure will be executed by the deletion job. The recommendation is that this is configured to only copy across 20% of the total messages in the system. This is to ensure that there is not a huge amount of growth in the DB space, as additional table space is required for the copying of messages from one table to the other.
To calculate this you should multiply the number of messages processed per day by the retention period of the messages in the system. This should then amount to the number of messages which will be copied when the switch procedure runs, although this does not take into account any messages in non-final status.
Daily # of messages: 25.000
Retention time: 5 days
Max. # of table entries: 900.000
Messages to be copied during switch: 25.000 x 5 = 125.000
Total number of messages for the 20% ratio = 125.000 x 5 =
DROP_MAX_TABLE_LOAD = 625.000 / 900.000 = 70 [%]
As stated previously the Maximum number of table entries can be seen in the report RSXMB_ANALYZE_DB. If you wish to alter this maximum number of table entries you can check transaction SE11+,+ (table e.g. SXMSPMAST) -> Technical Settings -> Sizecategory. This value was calculated by DDIC. Depending on table size category, DB Hardware, etc.
Tables Used by the Switch Procedure
The following tables are used during the switch procedure:
SXMSPMAST, SXMSPEMAS, SXMSPVERS, SXMSPERROR, SXMSCLUP+,+ and SXMSCLUR. These alternate tables are also used SXMSPMAST2, SXMSPEMAS2, SXMSPVERS2, SXMSPERROR2, SXMSCLUP2 and SXMSCLUR2.
Troubleshooting the Switch Procedure
A huge number of messages are copied to the active container even though DROP_MAX_TABLE_LOAD is configured to ensure this should not occur:
If you see this occur then the most likely issue is that there are a large number of messages with a non-final adapter or messages status. You can check this within the report RSXMB_SHOW_STATUS. This report shows the number of messages in each message and adapter status, numerically. You can check the meaning of each messages status in table SXMSMSTATT.
I am running out of Table Space when running the Switch Procedure:
Within SXMB_ADMIN -> Integration Engine Configuration you can implement the following parameter:
AREA = 'DELETION'
PARAMETER = 'TABLE_SWITCH'
SUBPARAMETER = 'MOVE_INSTEAD_OF_COPY'
This parameter ensures a message is deleted from the inactive container as soon as it is copied across instead of when the inactive container is dropped after the Switch complete. Please note that this parameter will not resolve this issue for all databases. When a message is deleted from a container this only ensures that the disc space is no longer in use, this does not mean the space is released on DB level. It is up to the DBMS to release unused disc space to the DB.
SAP Note 872388 Troubleshooting archiving and deletion in PI