Problem in stored Procedure to make database consistance.

[helloanikolkata]

Hi Experts,

I am facing an issue when trying to post data in database table by stored procedure. Suppose in one message the stored procedure is calling for 5 times as it has five repeated segment for StatementName and first call is inserting data in header table and next four calls are inserting data in detail table. Now in the four records insertion in detail table, one has bad data(say the last segment), so the stored procedure has failed. But what is happening, the stored procedure is inserting one record in header table and three records in detail table and for fourth it is failing in communication channel. And with a time interval again the whole message is trying to execute in database side and again inserting one record in header table and three records in detail table and for fourth it is failing in communication channel. and so on. What I need to do to make the database consistent, I need to fail the whole message, means either insert all the records in header and detail table or not a single record should be inserted in database.

How I can achive this?

Thanks,

Anirban Dutta.

[ambrish_mishra · Accepted solution]

Anirban,

this should be handled within the stored procedure... ask your database guys not to commit until Header and all line items are posted else rollback. From PI perspective, all you can do is make a robust mapping and ensure all mandatory fields are filled from the source so that it does not fail.

Ambrish

[helloanikolkata · Accepted solution]

Hi Ambrish,

Thanks for your reply. I have already done what you have suggested but their should be any option in receiver jdbc communication channel for a single message even if multiple statement in to that to commit only after the complete message will execute.

Any idea?

Thanks,

Anirban

[ambrish_mishra · Accepted solution]

Hi Anirban,

Can you explain me your design in detail and how does this stored procedure execute. It is weird why the whole process should execute again.

Ambrish

[helloanikolkata · Accepted solution]

Hi Ambrish,

It s a IDOC to JDBC scenario where I need to populate Header and Detail tables. now in receiver jdbc structure is as below..

<root>

     <StatementName>  --- representative segment.

          <storedProcedureName action=exec>

               <fields></fields>

          </storedProcedureName>

     </StatementName>

</root>

Now suppose in one message 5 StatementName segment is present. First one will populate header table and last 4 will populate detail table. Now once it will execute 5 SP call will be there. How we can achieve if  any one call fails means all 5 call will fail. Means if in last occurrence error occurred then changes by first 4 SP call will be rolled back. Only it will commit if all 5 call will have executed successfully.

Thanks,

Anirban

[ambrish_mishra · Accepted solution]

Hi Anirban,

I assume you use one stored procedure to update header table in database and another Stored procedure to update Item table.

On analysis, I was able to understand why it starts updates in the tables again.This is because all the Statements execute from within a message so even if one of them fail, the JDBC retry happens. This can be manipulated at 2 places.

One in the adapter in ID.

receiver JDBC adapter Advanced mode: Number of retries of database transaction on SQL error - 0 and another in RWB, comm channel monitoring -> Choose the particular adapter-> settings-> Maximum number 0 and choose overwrite.

Coming back to your solution, at runtime, the 5 StatementName calls execute as separate LUWs.

I assume you use one stored procedure to update header table in database and another Stored procedure to update Item table.

If you have to make this work, it has to execute as one LUW and not separate.

however, In the current design, this is how I think you can make it work.

The stored procedure for item table should be written such that if any of the insert fails, then delete the other line items (say for that Order) from the item table as well as the header line in the Header table assuming line item table and header table share the key field (say Order number). Likewise, if the header table update fails, delete the line items from the item table.You will need to suggest this to the database team and see if they can code it that way.

From PI perspective, you will get the error in PI in this case with no retries. If it was because database was down,then the message can be resent from RWB; else cancelled and transaction keyed in ECC with correction.

Hope it works.

Ambrish

PS: this solution should be implemented only on staging table. I won't recommend on actual DB tables.

[helloanikolkata · Accepted solution]

Hi Ambrish,

Sorry, it is not 2 different SP but only one. In the SP, I have an indicator to indicate whether the set of data is for header table or for detail.

Yes you are correct. I forgot one option, ie, from RWB settings. I have done that. Now it is not retrying, but insertion issue still there. DB people are unable to implement the check condition to delete data from table in case of unsuccessful insertion. So I have make my PI map so robust to fail for any issue in PI mapping itself .

Just for knowledge, will Database auto commit or batch mode can serve this type of issues?

Thanks,

AD

[ambrish_mishra · Accepted solution]

Hi Anirban,

Database auto commit or batch mode won't serve these kind of issues.

Hope it helps!

Ambrish

[ambrish_mishra · Accepted solution]

Hi Anirban,

<DB people are unable to implement the check condition to delete data from table in case of unsuccessful insertion. >

Can you give me details on what makes it to fail ? First, you should try to sort such issues in Source system (SAP in this case) if it has to do with a mandatory field missing.

If it has something to do with database set up, then database team should be able to code for deletion of records from the table. I don't think it should be that difficult to code.

<So I have make my PI map so robust to fail for any issue in PI mapping itself>

You need to make PI mapping robust but that does not mean you should look to fail the mapping in PI. that won't be a good design.

Hope it helps!

Ambrish

PS: Pls don't forget to close the thread when you think your question is answered.

[former_member184681]

Hi Anirban Dutta,

Search and read about the "database transaction" and "database rollback" concepts, it solves your requirement. In short, you start a database transaction to make several operations one by another, and then you confirm (commit) the entire transaction or cancel (rollback) it.

Regards,

Greg