Automatic integration message reprocessing

Technote (FAQ)

Question

How can I retry or delete a large number of messages without using the user interface to process small sets of messages for hours.

Answer

If you can identify the exact set of messages with a query against the MAXINTERROR table, then you can create an escalation to delete them or to change the status to RETRY.

Retrying Held Messages

Setup an escalation to select those messages you wish to reprocess and change their status to RETRY.

1. In the Escalations application, create a new escalation.
2. Applies To: MAXINTERROR
3. Condition: status='HOLD' and queuename = 'JNDI name of queue to process' and EXTSYSNAME = 'external system name' and IFACENAME = 'interface name' (See Note at end)
4. Add an escalation point and check the Repeat box.
5. Add an action. Use the lookup arrow to Go To the Actions app to create the Action.
6. Create a new Action with the following values:
7. Object: MAXINTERROR
8. Type: Change Status
9. Value: RETRY
10. Save and Return with Value to the Escalations application.
11. Set the desired schedule, such as every 5 minutes.
12. Save the record and Select Action - Activate/Deactivate.

When all the records have been reprocessed, deactivate the escalation and leave it in that state until such time as you might need it again.

Deleting Messages

Deleting messages requires two actions. First the DELETEFLAG must be set to 1, then the status changed to RETRY. Both of these are done transparently when deleting a message in the Message Reprocessing application. In an escalation they must be done in the correct order.

Warning: Be very careful when using this escalation to delete held messages. If valid transactions are sent in while this escalation is active, and they fail due to a correctable error, they can be deleted.

1. In the Escalations application, create a new escalation.
2. Applies To: MAXINTERROR
3. Condition: status='HOLD' and queuename = 'JNDI name of queue to process' and EXTSYSNAME = 'external system name' and IFACENAME = 'interface name' (See Note at end)
4. Add an escalation point and check the Repeat box.
5. Add an action. Use the lookup arrow to Go To the Actions app to create the Action.
6. Create a new Action with the following values:
7. Object: MAXINTERROR
8. Type: Set Value
9. Value: 1
10. Parameter/Attribute: DELETEFLAG
11. Save and Return with Value to the Escalations application.
12. Create a second Action with the following values:
13. Object: MAXINTERROR
14. Type: Change Status
15. Value: RETRY
16. Save and Return with Value to the Escalations application.
17. Double check the two actions. The Action to set the DELETEFLAG should have a lower sequence number than the Action to change the status. Change if necessary.
18. Set the desired schedule, such as every 5 minutes.
19. Save the record and Select Action - Activate/Deactivate.

When all the records have been deleted, deactivate the escalation and leave it in that state until such time as you might need it again.

Note: The Where Clause

The example where clauses for the two escalations contain most of the major fields in the table. You must always specify STATUS='HOLD' in order to avoid processing conflicts. In many cases you will only need this and the QUEUENAME.

If there is a huge buildup of messages that the escalation must process, there is a risk of the escalation failing with an out of memory error in attempting to change the status, especially if there are more than 10,000 messages. In this case, you can add the following condition to the where clause to limit it to 5000 or fewer records changed per iteration:

and maxinterrorid < ( select min(maxinterrorid) + 5000 from maxinterror)

You can also add a condition to prevent the escalation from deleting new messages which arrive while the escalation is running based on the highest value for maxinterrorid before any new messages arrive.

1. At a time when you know that the MAXINTERROR table only contains messages you wish to delete, note the maximum value of maxinterrorid with: select max(maxinterrorid) from maxinterror
2. Add the following condition to your where clause for the escalation:

and maxinterrorid < number from step 1

Note: 
In 7.5.0.x you may encounter APAR IV40754 which is fixed in 7.5.0.5.
APAR IV40754
NOT ABLE TO CREATE AN ACTION WITH CHANGE STATUS TYPE USING THE MAXINTERROR OBJECT One workaround is to use Set Value instead of Change Status in Step 14 above. You can also use Message Reprocessing to reprocess the messages manually.

source: http://www-01.ibm.com/support/docview.wss?uid=swg21511773