How to deal with errors in Sender JDBC adapter

[iaki_vila]

Hi,

Right now, i need to develop an analysis about the possible errors in Sender JDBC adapter. Please, correct me if there is a better choice in the next scenarios that i'm going to describe.

1. Error in the comunicattion with the sender database

I suppose that the sender DBMS deals with this problem doing a rollback if the update fails and the next PI pool could fetch the same records.

2. Mapping error

This kind of problem shouldn't happen. If this problem appears may be some database data is erroneous and it must be fix there. The solution would be to establish (manually) the “update” flag to the original data for the records can be read another time.

3. Error in the connection between PI and the receiver or another internal PI internal problem.

Restart the message from monitoring when the connection or PI is up. Is it possible to resend the message automatically?. To check this problem (point 2 also) the best way is to configure the alert monitoring with the error and send an email.

4. Expected or not expected error inside the receiver system.

In order to have the sender and receiver databases consistent, there must exist a controll mechanism. I think the best way is to work in the sender database with three states (available, read, proccesed). Sender JDBC channel takes the records with available status and update them with read status. The receiver system process the message, if all is ok then call PI, and with a receiver JDBC channel the PI update with porccesed flag, if there is a data problem the receiver will put the status available.

If the receiver system crush during the message processing, the unique solution that i think about right now is to create another PI scenario that takes the records with the read status, polling weekly o daily depending of the bussines scenario.

Thanks in advance

[rajasekhar_reddy14 · Accepted solution]

Hi ,

1. Error in the comunicattion with the sender database.

i am sure 98% will not occur this error because Select and update query execution happens in millin second time gap,always safe to use isolation level SERIALIZABLE to avoid other errors too .

Thank you,

Raj

[ambrish_mishra]

Hi

>4. Expected or not expected error inside the receiver system.

I did a design for an interface which used to run daily EOD. I simply used a flag and made a Select if it was initial. On the return leg after the records were processed in receiver system, another interface updated the same table with FLAG value 1 (processed). So for the records which were not processed, a report used to go to the Admin, and the data corrected in SAP. This gave a lead time of a day to correct any errors.

The interface runs again next day and picks the records (which were not processed the day before) along with other records.

For a real time interface requirement with Sender JDBC, I would recommend to use a stored procedure which sets the flag to an intermediate value and then does a select. PI then Updates this flag to a processed flag. This ensures that while making a select and update, no other records were updated in the same table and marked as processed accidentally. Any problems in PI or receiver system are handled through reprocessing.

So in short, Interface design may vary from scenario to scenario. as does error handling.

Hope it helps!

Ambrish

[iaki_vila]

Hi,

Thanks for your inputs, Raj and Baskar.

Regards.

[baskar_gopalakrishnan2]

>4. Expected or not expected error inside the receiver system.

Syncing both the end resources or database system is always requirement for any scenarios including jdbc adapters. I think your idea seems okay. If you want,  I would recommend on the target system first write it in the temp database. Let the db guys move the data to the real db end of the day.  If there are issues in some records due to data or some values constraints then create an another scenario (like file to jdbc) to run on the end of the day to 1) update the (reprocess flag ) or column of the sender table record and 2)with details about errors.  So that those records after required changes they will be reprocessed again.