inserting data to multiple DB tables sequentially

[Former Member]

Hi Gurus,

I am writing my requirement and also my approach to achieve; could you please validate and share your thoughts:

Requirement: To insert data to multiple database tables say 8 tables in a sequential manner. If by any chance the insertion is failed in 3rd table then it should revert back or do not commit the records in the first two tables. In other way, commit should happen on all DB tables only when the insertion is successful on all 8 tables.

Approach: Create multiple target statement(one statement for each table) and do multiple inserts using different receiver interfaces. In ICO, check the maintain order at runtime, and create only one receiver comm.channel. This way if any of the insertion fails, commit will not happen in DB table.

Please let me know if my understanding is correct and request to share if any better approach.

Thank You...

[former_member190293 · Accepted solution]

Hi, Ravindra!

If you or someone at the receiver side have developer access to target DB, the most preferable way, to my opinion, would be to transfer insertion logic to DB side and to wrap it to some kind of stored procedure because in most cases DB level programming language gives you much more flexibility in your task realization.

Regards, Evgeniy.

[Former Member · Accepted solution]

Hi Evgeniy,

Thanks for your reply.

We discussed on this option but we dont have anyone from DB side who can write SP. And so we are looking feasibility from PI.

My understanding in PI, if we opt Transaction isolation level as Read_Committed and maintain order at runtime, it suffices the requirement. Not sure though...

Looking for inputs on the same....

Thank You....

[former_member190293 · Accepted solution]

Hi Ravindra!

I'm not much experiensed in JDBC communication, but it seems to me that each of your interface operations will trigger separate transaction in DB. So you will not be able to rollback previously commited transactions if anything goes wrong. And Transaction isolation level in this case just sets the way the records in destination table will be locked by insert transaction.

When rows are inserted, updated or deleted, the database system sets exclusive locks on the affected rows for the duration of the transaction. The database system releases these locks at the end of the transaction.

Please correct me if I'm going wrong. It's an interesting question for me.

Regards, Evgeniy.

[bhavesh_kantilal · Accepted solution]

Evgeniy is bang on! Your current approach with multiple Interfaces and maintain Interface Order at runtime will not work as each Interface is treated as a separate transaction. So if 3rd Interface fails, you cannot rollback your 1st 2 Interfaces.

What you would need to do is have a single Interface with multiple statement level tags in your Message Type ; 8 in your case  one for Each for each type of query.

This will make sure all or none get updated into the database,i.e, entire call is in a single transaction

For Eg: the SAP Help shows multiple Statement tags for different operations. You would need a similar structure corresponding to each query.

Document Formats for the Receiver JDBC Adapter - SAP NetWeaver Exchange Infrastructure - SAP Library

Regards,

Bhavesh

[former_member190293 · Accepted solution]

Hi, Bhavesh!

You're absolutely right! As far as I used JDBC adapter for just a couple of times I forgot about multiple occurences of "Statement" element in JDBC receiver Document format.

Thanks for sharing this info one more time!

Regards, Evgeniy.

[bhavesh_kantilal · Accepted solution]

Isnt this why we like the forum?

It reminds us of things we probably don't do everyday so when a real problem comes up we are prepared!

Cheers,

Bhavesh

[Former Member · Accepted solution]

Thank you Bhavesh, we are proceeding with this approach.

This forum is always admirable.