Exactly-Once Handling for PI/XI JDBC Sender Adapter
In the current XI/PI version is the JDBC Adapter not 2-phase commit capable. There is EO Handling description for JDBC receiver Adapter available but not for JDBC Sender Adapter. The described scenario for the receiver adapter is applicable for JDBC sender Adapter as well. In the receiver direction XI committed the DB inserts/update before commit on the J2EE DB scheme (means commit on the XI message, XI Message will get final status Processed.). This Gap can be closed as documented under the help.sap.com; otherwise there is only Quality of Service (QoS) at least once guaranteed. In the sender direction, there are also two commits involved in the process. Nevertheless the commits will be done at first on the J2EE scheme (commit XI message to final status) and afterwards on the external Database, when the first attempt at message processing is interrupted by an irregular termination of the J2EE server immediately after the J2EE database commit (e.g. OOM), update can’t be commit on the external DB. Other possible scenario could be, the external DB crashed before the commit can be done, or connection between XI and external DB is broken, etc. After restart of J2EE server this JDBC sender Adapter will create one XI message with the same payload as the previous one again. In this case the QoS is actually also at least once.
One simple Adapter Module could help to close this GAP. The idea is to use the MessageIDMapper (IDMapper) to build one link between the resultset return from the Select/StoreProcedure and the XI Message. JDBC Adapter creates one XML document for the result set which return from the Select/StoreProcedure.
- Define the tags which build message ID for the resultset
- Define the life duration for the IDMapper (The Database table should not grow unlimited)
- Read the XML document in the Module build the external message ID
- Perform one lookup in the IDMapper
- If there is no XI message can be found with the external message ID, persist the external message ID along with the XI message ID in the IDMapper and return the control to the CallSapAdapter Module.
- Trigger one error if one XI message is found, or just delete the XI message payload and create one message with the external message ID and duplicated XI message ID as payload for statistic, etc and perform commit on the database table. In the second case, the duplicate will not stop the normal processing.
1. This solution can only be used in case the result set can be identified with one ID. Otherwise the duplicate check will throw error even if there is actually not a really duplicate.
2. The external Msg ID muss not exceeds the length 63. The IDMappter use J2EE internal table to persist the external message ID and XI message ID.
3. The IDMapper should be cleaned up after one defined period.
The API to use is available in the sample adapter, IDMapper is located in the package com.sap.aii.af.service.idmap