Solved: Deleting XML messages, database growing

Former Member · ‎07-06-2009

I have a PI 7.11 SP7 MaxDB system that is being tested. It may have several hundred thousand XML messages sent through it each day. There is not a need to keep any messages, so we would like to have them deleted each night, but I am having trouble deleting messages from the database. I am not very experienced yet with SAP but have tried to go through a lot of the documentation and other requests for help, but still have a problem.

Summary of steps: (report outputs are at the end)

I've read and tried walking through SAP Note 872388.

RSXMB_SHOW_REORG_STATUS shows many messages that are not in the retention period, yet can't be deleted.

I've run RSXMB_CANCEL_MESSAGES several times, and it completes in one second and does not seem to do anything.

RSXMB_SHOW_STATUS times out after 5 minutes without completion. (Possible separate question - is there a way to allow one job to run long or do I have to edit rdisp/max_wprun_time in the profile and restart to get this to work?)

Because of the high volume of XML messages, per recommendation I'm using the 'switch' procedure for deletion.

I have the retention periods set down to 4 hours each. When SAP_BC_XMB_DELETE_001 runs each night, it sees 2 million messages, deletes none, yet takes over 19 hours to copy the messages from one table to another. This repeats every day.

I have DROP_MAX_TABLE_LOAD set to 10%, yet the fill level is showing 1,043 % full.

SAP_BC_XMB_HIST_DELETE_001 runs successfully, but usually shows 0 or very few messages.

Following SAP Note 807615, I have tried going to http://<host>:<port>/MessagingSystem/archiving/reorgdb.jsp and manully setting the expiriation down to 1 day and also deleting messages 1 day old or more. Both result in 0 messages being affected. "0 messages have been succesfully removed from the database."

One recommendation was to run db02 and check top n growing tables, but when I run db02 I get "DBM Error -16, command timeout exceeded", "The instance is not accessible for MaxDB CCMS".

I assume I'm still overlooking something. Any help would be greatly appreciated.

Output from reports:

-

The program "RSXMB_SHOW_STATUS" has exceeded the maximum permitted uninterrupted runtime and has therefore been terminated.

-

Job log overview for job: RSXMB_CANCEL_MESSAGES / 08523100

|06.07.2009|08:52:31|Job started

|06.07.2009|08:52:31|Step 001 started (program RSXMB_CANCEL_MESSAGES, variant , user ID JFAY2)

|06.07.2009|08:52:31|Job finished

-

RSXMB_SHOW_REORG_STATUS output:

Message and adapter status is ignored in the following

- Asynchr. msgs not in retention period (can be deleted): 383.761

- Asynchr. msgs in retention period (cannot be deleted): 0

Message and adapter status is considered in the following

- Asynchr. msgs not in retention period (can be deleted): 0

...

Number of messages already flagged for deletion

- Messages to be deleted: 0

-

Job log overview for job: SAP_BC_XMB_DELETE_001 / 21003400

|02.07.2009|21:00:00|Job started

|02.07.2009|21:00:00|Step 001 started (program RSXMB_DELETE_MESSAGES, variant &0000000000005, user ID JFAY2)

|02.07.2009|21:00:00|Value for parameters DELETION RELATED_OBJECTS ASYNC: 1

|02.07.2009|21:00:00|Value for parameters DELETION RELATED_OBJECTS ROWS: 100

|02.07.2009|21:00:00|Value for parameters DELETION ROWS_PER_LOOP DELETE: 100

|02.07.2009|21:00:00|Value for parameters DELETION PERSIST_DURATION ASYNC: 004

|02.07.2009|21:00:00|Value for parameters DELETION PERSIST_DURATION SYNC: 004

|02.07.2009|21:00:00|Value for parameters DELETION PERSIST_DURATION_ERROR SYNC: 004

|02.07.2009|21:00:00|Value for parameters DELETION PERSIST_DURATION_UNIT ASYNC: H

|02.07.2009|21:00:00|Value for parameters DELETION PERSIST_DURATION_UNIT SYNC: H

|02.07.2009|21:00:00|Value for parameters DELETION PERSIST_DURATION_ERROR_UNIT SYNC: H

|02.07.2009|21:12:02|0 XML messages deleted

|02.07.2009|21:12:02|Step 002 started (program RSXMB_TABLE_SWITCH, variant &0000000000005, user ID JFAY2)

|02.07.2009|21:12:02|Analyze Table Container

|02.07.2009|21:12:02|Current / Maximum Number of Table Entries: 2.085.164 / 200.000

|02.07.2009|21:12:02|Current Fill Level: 1.043 %

|02.07.2009|21:12:02|Table Switch Required

|02.07.2009|21:12:02|Table switch from container 2 to 1

|02.07.2009|21:12:02|Value for parameters DELETION ROWS_PER_LOOP SWITCH: 100

|02.07.2009|21:12:02|Value for parameters DELETION TABLE_SWITCH MOVE_INSTEAD_OF_COPY:

|02.07.2009|21:12:02|Value for parameters DELETION TABLE_SWITCH CLUSTER_MB_MAX: 0

|02.07.2009|21:12:02|Processed clients 001

|02.07.2009|21:17:04|Messages copied up to now: 30.600

|02.07.2009|21:22:04|Messages copied up to now: 63.500

...

|03.07.2009|16:52:00|Messages copied up to now: 2.042.980

|03.07.2009|16:57:00|Messages copied up to now: 2.082.380

|03.07.2009|16:57:44|Processed clients 000

|03.07.2009|16:57:44|Processed clients 066

|03.07.2009|16:57:44|Processed clients 100

|03.07.2009|16:57:44|Delete container 2

|03.07.2009|16:57:45|Persistence tables reorganized

|03.07.2009|16:58:24|@5C@ Recommendation for persistence tables

|03.07.2009|16:58:24|Reduce the retention period for XML messages

-

Job log overview for job: SAP_BC_XMB_HIST_DELETE_001 / 00003500

|04.07.2009|00:00:04|Job started

|04.07.2009|00:00:04|Step 001 started (program RSXMB_DELETE_HISTORY, variant &0000000000000, user ID JFAY2)

|04.07.2009|00:00:04|Value for parameters DELETION PERSIST_DURATION HISTORY: 007

|04.07.2009|00:00:04|Value for parameters DELETION PERSIST_DURATION_UNIT HISTORY: D

|04.07.2009|00:00:04|Value for parameters DELETION ROWS_PER_LOOP HISTORY: 100

|04.07.2009|00:06:31|Status: 12 data records deleted so far

|04.07.2009|00:06:33|12 XML messages deleted

|04.07.2009|00:06:33|Job finished

Edited by: Jason Fay on Jul 6, 2009 7:30 PM

Edited by: Jason Fay on Jul 6, 2009 7:36 PM

Former Member · ‎07-06-2009

Jason, are you running RSXMB_SHOW_STATUS as a background job? Also, have you defined interfaces for archive and deletion in SXMB_ADM?

former_member200962 · ‎07-07-2009

It may have several hundred thousand XML messages sent through it each day. There is not a need to keep any 
messages, so we would like to have them deleted each night, but I am having trouble deleting messages from the database. I am 
not very experienced yet with SAP but have tried to go through a lot of the documentation and other requests for help, but still 
have a problem

When the deletion job is not helping, you can try to keep the trace (logging) to a minimum level (min. is 1...max is 3)......this will result into less details going into the DB...may be then you will have some space left....

Some good suggestions are given in this thread..check if they help you

Regards,

Abhishek.

Former Member · ‎07-06-2009

>>RSXMB_SHOW_REORG_STATUS shows many messages that are not in the retention period, yet can't be deleted

Did you configure message archiving/deletion after the messages started going through XI.

Archiving/deletion takes place only after the jobs have been configured,so if any messages went through XI before archiving/deletion was scheduled it wont be flagged for archiving and will continue to sit on XI system and show in RSXMB_SHOW_REORG_STATUS as out of retention period.

I had a similar issue with the total number of message size.about 1.5 million to be archived.

in sxmb_moni,set the retention period to be very small,save this(don't run the archiving/deletion report right now),then go to RSXMB_SHOW_REORG_STATUS and see the messages whcih can be archived,if they are within reasonable limit,then schedule the archiving job in sxmb_adm(don't schedule the deletion job right now).keep on doing this until all the messages have been flagged for archiving(will show in RSXMB_SHOW_REORG_STATUS ).i was archiving 50,000 messages in a single chunk and it took about 1 hr.

Now run the deletion job in sxmb_adm,this will take quiet a bit of time so make sure that you run it at night or whenever the load is minimal on XI.

Thanks

Aamir

Edited by: Aamir Suhail on Jul 6, 2009 1:06 PM

Deleting XML messages, database growing

Accepted Solutions (1)

Accepted Solutions (1)

Answers (2)

Answers (2)

Re: CIG transaction tracker handling

Re Generate Co files and data files

Re: SBPA - How to handle Intermediate Message Even...

Re: Digital Signature on PDF

Re: CIG transaction tracker handling