Inserting data into multiple tables sequentially

[Former Member]

Experts,

We have a requirement where

SAP sends (1 idoc)-PI---Receiver- insert data into two Tables MainTable and DetailsTable which has referential integrity maintained in them.

We created a single target message structure with two statements; one for MainTable INSERT and another for DetailsTable INSERT.

<ReceiverServiceInterface>

<Statement1>

for MAINTABLE insert

</Statement1>

<Statement2>

for DETAILSTABLE insert

</Statement2>

</ReceiverServiceInterface>

We want to make sure that all the MainTable record MUST go first and then only the insert for the DetailsTable should start.

Is there a way to enforce this?

We were looking into the option to use "Maintain Order at runtime" but in one of the forum I read that it maintains the order for message processing; but it doesnt ensures that the first table insert will complete then only the second table insert will start.

We dont want to go through the Stored Procedure option.

Thanks for your time.

UV

[former_member181985 · Accepted solution]

Hi Usha,

I recommend a stored procedure for such scenarios, which should basically validate the incoming data and insert/update multiple table accordingly.

Regards,

Praveen Gujjeti

[Former Member]

Hi UV,

When you want to insert/update data into multiple tables in a database from PI you should remember that the communication channel creates one Logical Unit of Work per message it is handling. This means that before the channel starts processing any of the statements you've created/mapped in XML-SQL format or native format it will instruct the database to start a transaction. After successfully insert/update of data the channel will perform a commit of the previously started transaction.

Within one transaction the database allows you to insert/update data into multiple tables while adhering to how the tables are defined in regards to foreign key relations. Let me give you an example.

1. Data is mapped to XML-SQL format.

2. XML-SQL message is transferred to the communication channel.

3. Communication channel instructs database to start a transaction.

4. Communication channel executes all the statements in XML-SQL message.

5. First part of the XML-SQL contains all the main-table insert/update statements. (Data in main-table is now known only by the transaction and can because of this also be read by the transaction.)

6. Second part of the XML-SQL contains all the detail-table insert/update statements. (Because the second part is executed within the same transaction as the first part and the transaction knows the data from main-table, there is no problem in inserting the data into detail-table)

7. Communication channel instructs database to commit the transaction.

If any errors occur during insert/update in the transaction the communication channel will perform a roll-back of the transaction in the database and no data will then have been updated in the database. This ensures data integrity between main-table and detail-table.

Hope this will help you further along in your development. I use the above approach extensively and have had no problems with it.

Best regards,

Jacob

[Former Member]

Hi Jacob,

From your response it looks like having two statements in one message guarantees that all the Main table record will go first and Detail table insert will ONLY start AFTER the Main tables.

I am little confused now, based on the previous response from the other PI expects, it seemed that order of message delivery is not confirmed... ...

Appreciate if someone can clarify this.

UV

[Former Member]

Hi UV,

In regards to 'Maintain Order at runtime' it only relates to having one source message mapped to multiple target messages e.g.:

                                ------------> Target Message 1

                              |

Source Message-------

                              |

                                ------------> Target Message 2

When having such a scenario it is sometimes necessary to ensure processing of TM1 before TM2 and this is where 'Maintain Order at runtime' can help you.

A situation could be like this: You receive a 'create customer' message from the source system in SAP PI. At the target SAP ERP system you need to have two interfaces to create the customer - ADRMAS and DEBMAS. In order to successfully process DEBMAS in SAP ERP you need to have SAP PI first process ADRMAS, which creates all the address information - before creating the actual customer with DEBMAS.

Hope this helps clarify things a bit for you.

Best regards,

Jacob

[Former Member]

as already suggested SP will be the most suitable option in ur case......SP will have a greater control over the processing of data.

infact there are lot of advantages of SP which u can use for instance during the insert operation, exception occurs while SP is executing generally PI will stop the process and exception is thrown back.....but in order to avoid such a situation and to continue with the execution of SP, we can log the exception and resume the processing of rest the messages....

[markangelo_dihiansan]

Hello,

Adding to what Raja said, you can also do a JDBC lookup to the same db you have inserted to check the records and then use that logic to create second message.

Regards,

Mark

[rajasekhar_reddy14]

Use multi mapping and process data in to two tables as a two different messages.

For second message write simple UDF and use wait time as 30sec like that, partially guarantees that once first message processed then only second message will go through.

Regards,

Raj

[Former Member]

> For second message write simple UDF and use wait time as 30sec like that, partially guarantees that once first message processed then only second message will go through.

>

@Raj, Thanks for your response. But II dont think we can go with a this option wont work in production as its not 100% foolproof.

@Mark, This is a batch interface with huge volume,data wouldnt the JDBC lookup would cause some performance issue?

Could anyone please help in ensuring that below statement is correct :

"Maintain Order at runtime" cannot guaranteed the 1st message will complete its process fully by inserting the data into database and then only the 2nd msg will start processing and will get into the database. As per my understanding the "Maintain Order at runtime" only ensures that message will process in the order.

Thx

[rajasekhar_reddy14]

> "Maintain Order at runtime" cannot guaranteed the 1st message will complete its process fully by inserting the data into database and then only the 2nd msg will start processing and will get into the database. As per my understanding the "Maintain Order at runtime" only ensures that message will process in the order.

>

Above statement perfectly true,maintain order run time only ensure message processing in order but will not guarantee the message was delivered to target system or not after delivering data to receiver adapter.

JDBC look up option also one good idea to implement.

otherwise try t handle it in DB level like implementing stored procedure.

[Former Member]

Thanks Raj.

It looks like, for the JDBC inserts, there are basically 3 options to ensure delivery of msgs in order :

1. JDBC lookup in mapping

2. BPM

3. Stored Procedure

Hmmm... I was hoping that there would be something in the Communication channel level to ensure the msg delivery.

[rajasekhar_reddy14]

There is no such an option in Communication channel, i prefer Stored procedure approach because Data base team can handle this easily.

[former_member181985]

Use multi mapping and process data in to two tables as a two different messages.
> 
> For second message write simple UDF and use wait time as 30sec like that, partially guarantees that once first message processed then only second message will go through.
> 
> 
> Regards,
> Raj

Hi Raj,

Multimapping creates the final bulk message in one shot irrespective of different interface messages associated with it. I am little confused about your statement, as giving 30 secs delay can only delay final bulk message formulation not the individual message, i believe. I am only just curious how this is possible...

Regards,

Praveen Gujjeti

[markangelo_dihiansan]

Hello,

@Mark, This is a batch interface with huge volume,data wouldnt the JDBC lookup would cause some performance issue? 
Could anyone please help in ensuring that below statement is correct :
"Maintain Order at runtime" cannot guaranteed the 1st message will complete its process fully by inserting the data into database and then only the 2nd msg will start processing and will get into the database. As per my understanding the "Maintain Order at runtime" only ensures that message will process in the order.

You could try using integrated configuration to improve performance. But it is always best to use stored procedure as suggested by the posters above.

Regards,

Mark