Sending Data in Chunks in JDBC Adapter with rollback

[sabyasachi_mohapatra3]

Hello Experts

I have a proxy to jdbc scenario. The data volumes to be handled is around 100000.So we want to send the data in chunks of 10,000 ,but at the same time the rollback mechanism has to be followed i.e if there is any error during insertion of any chunks ,then all the data inserted should get rollbacked.More over after all the data are inserted into the staging table ,i am calling a stored procedure which is summarizing the data and populating it into another final table.

The approach i am following is using multiple statements in one message

<DeleteStatement>(0 to 1)

<InsertStatement>(0 to 1)

<access> (0 to n )

<CallSPStatement>(0 to 1)

Please suggest some approach for this scenario.

Thanks

sabyasachi

[Former Member · Accepted solution]

Hi sabyasachi,

I am not clear about your question, if it is jdbc target side structure then use below structure

STATEMENTNAME

test // Database Name

action (Attribute) // Specifying the statement it is insert/update/delete

table (it is an element) // Table name

access

f1

f2

..

..

if it is not correct then, what is your question? I understand the flow.

Regards

Ramesh

[sabyasachi_mohapatra3 · Accepted solution]

Hi

For sending data in chunks ,i have to split the message.If splitting is done the data would be inserted in chunks and then get committed.But if any error happens during insertion of any chunk in a message then all other insertions in the previous messages should also get rollbacked.How to achieve this.

[Former Member · Accepted solution]

Hi,

I think you can't achieve it the way you describe. If you have a sender proxy, why don't you let it sent the data in chunks. That way there is no problem for the jdbc receiver channel.

A message split won't help either, because you would have to create a MessageType with a certain amount of repeated sql xml inserts (just as you mentioned above).

I would suggest to try inserting the complete data and afterwards working on the staging tables with a stored procedure. This should get the best performance. (You can also tune your XI/PI installation for large messages).

Best Regards

Sven

[sabyasachi_mohapatra3 · Accepted solution]

Hi

If i send data in chunks through abap proxy then i would be getting different messages since the abap proxy method would be getting called n times for n chunks of data.The data would be inserted into the database after n messages are sucesfully exwcued at adapter engine,If during insertion failure of any message, would the data inserted in the previous messages get rollbacked.Please advise

[Former Member · Accepted solution]

Hi,

after thinking about your original approach a little longer, it doesn't seem unappropriate anymore.

If you do all your processing in one message, this is handled as one transaction against the database (I presume you are using oracle, mySQL or MS SQL Server).

If one insert fails, the complete message is rolled back. This also means, the delete statement at the beginning. Or in case the SP produces an error, all inserts are rolled back. So maybe you should only do your inserts in one message and the rest in different messages. (1:n mapping with source message --> 0/1 Delete message; 1/n insert messages; 1 SP message).

But (there always is a but) I don't think that is what you want, because all data will be contained in the access elements of your Insert Statement and in this way all 100000 rows are inserted in one transaction. No chunks.

Generating more than one message for your inserts will break the transaction and so making an complete rollback impossible.

This is way, I would recommend to insert all your data into the database, verify it afterwards with a stored procedure (if possible) and process it then.

Best Regards

Sven

[sabyasachi_mohapatra3 · Accepted solution]

Hi

You are exactly right. But there would be about 2,00000 records in one message since this would be a monthly batch and there is going to be a performance issue. if we are not splitting.Please suggest some new approach in which it is possible for splitting the records and at the same time following the rollback mechanism in xi.

Again after inserting 200,000 records into the table,am calling a stored procedure getting from XI which again will take time.Are there any chances of XI getting timedout because of the stored procedure taking too much time to get completly executed.

[sabyasachi_mohapatra3]

hello experts

please throw some light on this