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

[rajasekhar_reddy14]

Stored procedure was developed in data base with 4 input parameters (header, line item, partner and DTN); stored procedure always expects input parameters in same order and last input parameter DTN was optional.

PI interface development point of view | as a field separator and $$ used as a record separator, really challenging task to send default blank value for all fields when there are no values coming from IDoc.

if PI mapping logic was unable to populate default blank value then PI mapping gets failed because all fields in data type are mandatory.

Number of JDBC calls:

Number of JDBC calls to data base is always one (header, line item, partner and DTN) irrespective of number of E1EDP01 and E1EDKA1 segments in IDoc.This is main advantage with this design.

IDoc packaging:

This design support IDoc packaging, refer below blog by Shabarish,

http://scn.sap.com/community/pi-and-soa-middleware/blog/2010/01/22/idoc-packaging--sap-pi-71-ehp1-an...

The main advantage with IDoc package is number of  trfc calls to PI system is minimum , all collected IDoc will send one trfc Call, this packaging concepts avoids many performance issues.

The same way my design supports IDoc packaging to send collected IDocs in one JDBC calls to data base.

I tested my development with package size 1000  and results were positive and achived amazing performance..

As per my bussiness requirement 1000 IDocs required nearly 200k insert operations on data base  INSERT statement design and 1000 calls to stored procedure standard design but this design required only one data base call.

SAP recommends packaging   more than 4000 IDoc creates not a good practise.

Message Size:

Mapping output (stored procedure xml) size always less compare to standard designs, the memory took in BC_MSG JAVA table was less.

Challenges with this design:

1)     1)Writing PI mapping logic to send default blank value is really challenging when you have complex mapping logic.

2)     2) If mapping output stored procedure XML characters exceeds more than 32k then Data base side they need to change coding in stored procedure to accepts more than 32k,I have faced this problem my data base team had fixed it.

3)      3) Error handling logic required at data base level to validate the values of stored procedure XML because JDBC adapter does not throw any error if you send incompatible values, like INSERT statement design.

Results:

I consider this is one of the best design to achive best perfromance with JDBC receiver scenarios,we performed 15 million DB operations  with this design in 23 hours.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/09/04/jdbc-receiver-scenarios-best-prac...

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/09/05/jdbc-receiver-best-practices-scen...

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/09/07/jdbc-receiver-scenarios-best-prac...

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/09/07/jdbc-receiver-scenarios-best-prac...

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/09/20/jdbc-receiver-scenarios-best-prac...