Skip to Content
Author's profile photo Raj Thukiwakam

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)

/wp-content/uploads/2012/09/sp2_138127.jpg

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.

JDBC5_1.2.png

          Data Type2:

  

JDBC5_1.jpg

          Data Type 3:

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

/wp-content/uploads/2012/09/jdbc5_3_138140.jpg

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.

JDBC5_4.jpg

JDBC5_Mapping2.jpg

     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 .

JDBC5_6.jpg

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:

      Concat_Out.jpgMapping 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 $$,

JDBC_7.jpg

             3rd level Mapping output:

SP_Output.jpg

SXMB_MONI output:

MONI_OUT.jpg

Stored Procedure Input:

FinalOutput.jpg

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

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Raja Shekar,

      Thanks for sharing the blog. This would be very helpful in handling the high volume scenarios. Can you let us know if the Database guys had to make changes to the stored procedures programs to accommodate the Pipe(|) and Dollars($$) delimiters?

      Regards,

      Sudhir

      Author's profile photo Raj Thukiwakam
      Raj Thukiwakam
      Blog Post Author

      Hi Sudhir,

      They need to write a SP code to read every field based on field separator and to indentify record based on record separator.

      if already stored procedure design implemented and you wanna use this deisgn then they need to change code .

      did i answer your question?

      smiles,

      Raj

      Author's profile photo Former Member
      Former Member

      Yes Raj..Thank You!!!

      Regards,

      Sudhir

      Author's profile photo Former Member
      Former Member

      I used stored procedure in receiver JDBC but after reading your blog I came to know more about it.

      Thank you for sharing such a intresting blog.

      Author's profile photo Former Member
      Former Member

      Hello Raja,

      These functions FL_RecordConcat are UDF designed by you ?

      Can I import some of these functions in my project ?

      Can you give me an example of function to concat multiple records into one parameter for a SP.

      Thanks in advance.