JDBC Receiver scenarios best practices – Stored procedure design-Part4

A stored procedure is a named group of SQL statements that have been previously created and stored in the database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance. Additionally, stored procedures can be used to help ensure the integrity of the database.

PI interface development point of view the difference between Standard INSERT statement design and stored procedure design is JDBC receiver structure,

JDBC Receiver Stored procedure structure:

Refer below SAP documentation to understand about JDBC receiver structure formats

http://help.sap.com/saphelp_nw73ehp1/helpdata/en/44/7b72b2fde93673e10000000a114a6b/content.htm

Below screenshot shows the mapping between IDoc to JDBC stored procedure.

Map action attribute to constant (EXECUTE) and TABLE to stored procedure name like below screen shot and make sure that stored procedure name is correct.

PS: Stored procedure will not support batch mode processing hence make sure that batch mode not selected in JDBC receiver channel.

How stored procedure design works:

PI JDBC adapter not going to perform any operation on Data base only it calls JDBC stored procedure with input parameters,Make sure that PI input put parameter hierarchy should match with stored procedure input parameters.

Once stored procedure receives a request from PI then it performs operation on table (INSERT/UPDATE/DELETE).

Advantages:

1)Performance point of view Stored procedure is always right a design to implement because PI is not going to perform any operation on data base it only calls stored procedure to pass input hence JDBC connection gets released short time compare to  compare to earlier designs.

2) Optimal solution to Handling high volume loads.

Disadvantages:

1)      1)As per my observation bit difficult to handle data issues, like MATERIAL number length defined in data base is 18 characters but PI sending more than 18  characters in this case PI calls stored procedure successfully  but record might fail at stored procedure level, but in case of insert statement design message fails at JDBC adapter level.

2)     2) Dependency on data base, DB team has to develop stored procedures hence it required development efforts.