Cleanup of tables SXMSCLUP and SXMSCLUR

[anupam_ghosh2]

Hi Experts,

Need to reduce size of SXMSCLUP and SXMSCLUR tables by 60%-70%. I followed data archival followed by data deletion.

In acceptance server business wanted to have a message retention period of 30 days. Selected all interfaces for archival and scheduled archival followed by deletion process. After deletion I was able to achiev only 5% decrease in number of entries in the table. Have you achieved reducing the size of these tables by 60%- 70% .

Could you please kindly share your experience in this regard.

I have gone through many blogs, articles and threads in this matter. We are working with PI 7.0.

Regards

Anupam

[Former Member · Accepted solution]

Hi Anupam,

have you seen these threads ?

[http://forums.sdn.sap.com/thread.jspa?threadID=1848653]

[http://forums.sdn.sap.com/thread.jspa?threadID=321944] (see last post: option to be selected)

Mickael

[anupam_ghosh2 · Accepted solution]

Hi Mickael ,

Thank you so much for your kind response. I went through those threads earlier but I was unable to get good results. I have gone through many threads and articles in this regard. Thus I need some inputs from experts who have actually reduced the size of those tables.

Hi Experts,

Could you please share your views in this regard.

Thanks

Anupam

[Former Member · Accepted solution]

Hi,

You should run report "RSXMB_DEL_CLUST_TABLE" to delete entries from this

two tables. For mor information please read SAP Note 761713.

The procedure for scheduling deletion job is as follows.

1. Define retention periods which should be stored data in database.

2. Start transaction "SXMB_ADM"

3. Execute "Define Interfaces for Archiving and Retention Periods"

4. Click "Retention Period" button

5. Maintain retention periods.

Create deletion job

1 Start transaction "SXMB_ADM"

2 Execute "Schedule Delete Jobs"

3 Maintain start date, start time and period for each of jobs.

(It is necessary to schedule for XML Messages and History Entries).

regards,

ganesh

[Former Member · Accepted solution]

Hi ganesh,

I would like to know if this can be done in ECC/R3??

Thanks

[anupam_ghosh2]

Hi All,

Finally the incident was referred to SAP itself. They deleted the messages in debugging mode. Here are few tips they gave

1. Not to retain messages older than 7 days.

2. Switch procedure should not be used unless its higly essential. This prevents actual deletion of messages.

Regards

Anupam

[former_member334189]

Dear Anupam,

in the linked by Mickael the recommendation was given to follow SAP Note 872388. This is the perfectly right thing to start with as that note provides a comprehensive summary of all required tasks. Kindly walk through this note step by step and perform the actions given for the ABAP stack.

I strongly advise against SAP Note 761713 as that one is valid for XI2.0 (620APPINT) only. Please note that XI persistence layer has changed significantly as of XI3.0. In case you do want to run report RSXMB_DEL_CLUST_TABLE kindly refer to SAP Note 774858. However, this report will not help in your situation. It was develeoped to fix a know bug in XI2.0 and then it has been adjusted to new DDIC structure of XI3.0 for compatibility reasons.

The backlog in reorganisation is due to messages having a non-final message state and/or non-final adapter state. SAP Note 872388 will help you to identify the messages and it also provides the actions you need to take in order to finally delete the messages from DB.

Best regards,

Harald Keimer

XI Development Support

[anupam_ghosh2]

Hi Ganesh,

Thank you so much for your kind response. I cannot run RSXMB_DEL_CLUST_TABLE process. This is because business requirement is to archive all messages older than 731 days. After archival I need to delete the archived messages from persistence layer. RSXMB_DEL_CLUST_TABLE is an independent process, thus not helpful enough to solve this case.

Hi HareenKumar,

Thank you so much for your kind response. I consulted the thread you mentioned in past. In the thread Rocco has reduced retention period for processed message to 1 day and went for deletion rather than archiving. I am unable to follow what he mentions in his response due to business restrictions. Business needs retention period of 731 days.

Dear Herald,

Thank you so much for your kind response. I have largely follwed this link for archival and deletion of XML messages

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/402fae48-0601-0010-3088-85c46a236...

I feel this blog has been written in accordance to SAP note 872388. Anyway I am going through this note once again, in case I missed some step. I will also email you screen shot of my settings.If possible could you please go through and suggest where ever correction is necessary.

Regards

Anupam

[Former Member]

If the messages are processed before the retention period sets , all the entries in SXMSPMAST are set to DEL mode which cant be archived if the archive job is scheduled also. These entries should be set up back to INIT to archive and delete which subsequntly reduce the entries in master tables.

Read this one for more info..

http://wiki.sdn.sap.com/wiki/display/XI/PIArchivingConceptual+Overview

[anupam_ghosh2]

Hi Sekhar,

Thank you so much for your kind response. Indeed the messages beyond retention period have been marked in DEL mode. Then our BASIS team tried running RSXMB_DEL_TO_ARCH to bring the messages back to ARCH mode in production.

Now although the size of archived file has increased a lot still after deletion we have more than 62 million entries in the tables.

Could you please kindly suggest how should I proceed now? Is there any way to bring back the messages from to INIT stste as you mentioned in your post.

Regards

Anupam

[former_member334189]

Dear Anupam,

I would like to add one remark just to avoid misunderstanding... Both values DEL and INIT for field ITFACTION result in deletion of the message without prior archiving. From your posting I understand that you do want to archive messages. Therefore the value of field ITFACTION needs to be set to ARCH to archive all versions of a message.

In case the total number of message in database does not decrease although you ran archiving sessions successfully this might indicate that only the write phase has been executed so far.

Archiving in PI works in 2 phases:

(1) write phase: messages are read from PI database and are written

to archive files

(2) deletion phase: messages are read from previously written

archive files in order to ensure data integrity; if successful,

messages are deleted from PI database

In order to trigger deletion for existing archive files manually run transaction SARA for archiving object BC_XMB, click button "Delete" and select a session or file. Enter start date and spool parameters (for example, LP01) and press F8. You may also want to check SAP Note 1603376.

To start deletion runs automatically run transaction AOBJ, double-click archiving object BC_XMB in the list and change to "Customizing Settings" in the left hand tree. In frame "Settings for Delete Program" choose option "Start automatically". In case a popup appears when saving the change asking for a package name, enter: SXMSPERSIST

If this does not resolve the issue you may want to consider opening an OSS ticket at BC-XI-IS-IEN as to my point of view the subsequent analysis steps exceed the scope of this forum. When opening an OSS ticket please refer to this thread.

Best regards,

Harald Keimer

[Former Member]

Hi Anupam,

Check this : http://forums.sdn.sap.com/thread.jspa?threadID=902373