JDBC Receiver scenarios best practices – How to handle High volume load-Stored procedure design-Part5

Contracts are created in SAP ECC and contract data needs to insert in to 4 contract different oracle tables.

IDoc Type: ORDERS

Table1: Contract Header (Mandatory always one header record for one contract).

Table2:Contract_LineItem (Mandatory minimum 1 item record maximum depends in E1EDP01)

Table3:Contract_Partner (Mandatory minimum 11 records for all partner types, maximum depends on E1EDP01,  E1EDKA1 segments in IDoc)

Table4:Contract_DTN (Optional)

As per my business requirement one contract records may have maximum 388 E1EDP01 segments, this case maximum 4906 records (header, Line item, partner and DTN) needs to insert in 4 data base tables. Maximum number of contracts created in ECC per hour is 11000 and maximum number of records required to insert in Data base is 53666000(5.3 million).

This is one interface; similarly we have nearly 27 interfaces which deal with master data and transaction data.

Maximum 21 million records needs to insert in data base on peak business days) .

Now I need to design this integration approach to handle load more effectively and business expects some of the transactions data in real time, the biggest challenge here is handling huge load and high volume.

What I observed with standard traditional design was

1)       1) Standard INSERT statement design consumes more resources and observed that data base connection was released once INSERT operation completed.

 

2)      

2)        2) Multi mapping design taking more time than standard insert design.

3)       3) Stored procedure design gave good performance performance but still that is not enough to handle my business requirement.

4)       4) The tricky point while implementing stored procedure design was how many stored procedures required inserting data in to multiple tables, the problem here is all tables (header, line item, partner and DTN have different structures hence using multiple stored procedure is one way but performance was not good).

After so much analysis / troubleshooting identified 3 places where I can change design/configuration to improve the performance.

1) PI interface design.

2) JDBC adapter.

3) Oracle response time.

PI interface design:

I have come up with unique design to handle my requirement more effectively compare to traditional stored procedure design, the design as follows,

Created 3 data types

        Data Type1:

                        Created data type and all fields are mandatory.

          Data Type2:

  

          Data Type 3:

               This data type for stored procedure, this is having 4 input parameters (Header, Line Item, Partner and DTN).

This design involved 3 levels of mappings,

     Mapping1:

     Mapping between IDoc to Data type 1,

     I have written mapping logic as per business requirement and mapped default value (blank) for every field if there is no value in IDoc.

     Mapping 2:

              Concatenated all header fields in to one string with differentiator Pipe (|), same logic implemented for Line item, Partner and DTN.

               I used standard concat function like below to concatenate all fields .

PS:

1) Make sure that you are going to use right field differentiator, in my case Pipe is a differentiator and got confirmation from business team that IDoc payload is not going to have any Pipe .

2) I would recommend using unique special characters combination as a differentiator.

3) I would recommend to use UDF to concat all fields, in my case I have more fields for for couple of tables hence used concat but in other interfaces used UDF.

           2nd Level mapping outout:

      Mapping Output:

     Mapping 3:

     This mapping is between Concat mapping out put to stored procedure.

     Line item, partner and DTN records have multiple records per IDoc, so I need to concatenate all records with record separator, so I have used record separator $$,

             3rd level Mapping output:

SXMB_MONI output:

Stored Procedure Input:

I will explain how this design works , why it gave very good performance  and how stored procedure was designed in Data base.