JDBC Receiver scenarios best practices - INSERT operation design-Part2

[rajasekhar_reddy14]

Design 1: INSERT operation

Lest start with standard INSERT statement design, every one familiar with insert operation  and it works really good way for medium and low high volume load.

There were many documents available on the same concept hence not going to give detailed description.

The first and foremost thing while working with the JDBC scenario is creating a JDBC receiver data type structure, SAP defined a standard format to use while working JDBC receiver scenario.

Refer below SAP help to understand the XML document structures for different operations.

http://help.sap.com/saphelp_nw73/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Below screenshots is an example for inserting data in to single data base table using INSERT operation.

Design2:Inserting data into multiple tables without multi mapping

This case creates a data type with multiple statement objects like below and map accordingly.

The advantage with using multiple statement objects in one JDBC receiver  Data type is within one process call we can PI executes a scenario, utilization of PI recourse less compare to multi mapping design.

Below is an example shows inserting MEDIA Products IDoc data in  two data base tables MATERIAL ACCOUNT TABLE and Material Basic data table.

Use this design when you want to process medium volume records to data base (Eg : 20k), INSERT operation takes high resources and once INSERT operation succesfuly perfrormed then only it releases JDBC connection.

Advantages:

1)   1) Error handling is very easy because if insert operation fails due data issue or field length exceeds issue or unsupported data issue or network connectivity issues then we can track it in CC monitoring.

2)   2) ALERT framework covers JDBC adapter issues so that Support team can easily identify the root case.

3)   3)Easy to develop and dependency on data base team always minimal.

Disadvantages:

1)   1)Processing high messages not recommended with this design and i had a serious problems with INSERT statment design while dealing with hig volume.

2)JDBC adapter got locked many cases and all connections showed in utilisation mode ,eventually JDBC adapter locked after Java stack restart problem was solved.