JDBC Receiver Adapter questions

[former_member257758]

Hi Gurus,

I have few basic questions regarding JDBC Receiver Adapter. I have searched the possibilities of using JDBC receiver adapter with databases like DB2,Oracle 10g, Sybase 15, SQL server 2008.

But Not able to figure out the best way to implement it. Here is my understanding first.

1. JDBC Adapter using direct SQL statements  like INSERT,INSERT_UPDATE,DELETE

2. JDBC Adapter calling Stored procedure to send the data.

For Ex, If a message contained 1000 records, How JDBC adapter really works in both the cases. assume all the mapping is straight fwd.

     1. JDBC Adapter generates 1000 SQL Statements to send the data to database.

     2. JDBC Adapter calls the stored procedure 1000 times to send the data?

Which one is best practice?

In other forum I saw some thing like calling stored procedure only once for multiple records. Looks like the records are combined into CLOB data type(Creation of single XML file for all the records) and passed into Stored procedure(So its like XML parser to SP).

My requirement is: I have hierarchical structure which is passed from SAP and needs to be updated in multiple tables in target DB.

header section in 1 table and detailed section in other 2 tables. Single message from SAP will have multiple records.

Can you pls suggest me which option is good and how to implement this in better way and how it really works.

Appreciate your help.

[rajasekhar_reddy14 · Accepted solution]

if you standard approach insert / update/insert_upodate/delete statement PI uses 1000 connection to send data to Data base.

Even if you use Stored procedure also more or less same only. because 1000 message are considered as a 1000 indivudual messages in PI .

Use packaging concept at sender level and collect all messages and send as a one message then only you cam miminize the number of JDBC calls,this case your db team has to develop stored procedure to accept string as a input.

[baskar_gopalakrishnan2]

> 2. JDBC Adapter calls the stored procedure 1000 times to send the data?

The answer is yes/no. This is purely based on SP design. You can pass multiple values for different tables in a single call and avoid calling 1000 times.

[former_member257758]

Thanks Baskar,

I think you mean , we can pass multiple records for different tables in single call and avoid calling 1000 times.

I have seen one example which says, define SP input parameter as CLOB. So that PI needs to combine all records into single XML file and pass to SP. But client feels this is not good design as XML file is parsed a parameter to SP.

Do you have any example where SP parameters are defined to take input as table structure or array of records or something else.

Here the requirement is: From SAP, in a single message PI receives 1 header section and multiple detailed records which needs to be updated in  header DB table and 2 detailed tables.

So here we will have 1 header record and multiple detailed records.  So i am not sure the SP needs to be executed from PI end from header section and detailed sections.

I hope I have put the question rightly

Thanks

Arun

[rajasekhar_reddy14]

Dont pass whole IDoc xml as a imput to stired procedure, first concat all fields say header fields with seprator and line iteam fields with seperator.

Ask Data abse team to create a stored procedure with two input paramaters (header , item details). and map accordingly.

[Former Member]

Hi Arun,

Since your requirement involves more than 2 tables, it is always recommended to go for stored procedure.

Make sure to investigate the transaction isolation level and use the required setting as per your scenario.

Depending upon your frequence and volume of data , you can decide whether you want to club everything and pass it around in single XML file for all records.

I am still unsure that it will call 1000 times for 1000 records.

Regards

Anandh.B

[Former Member]

Hi,

Use SP for this.

>>In other forum I saw some thing like calling stored procedure only once for multiple records

Yes, indeed u can pass ur entire source structure as a xml string input (use return as xml feature) to the SP at a single go and then  at the DB level u can parse the xml string and do the required operations.

Thanks

Amit Srivastava

[former_member257758]

Thanks Amit,

Can you pls elaborate on how to use SP in hierarchical structure. In hierarchical structure there will be

1 header record and multiple detailed record.

Do I need to use 2 SP's or 1 SP? Let us assume if I use 2 SP's (1 for Header records and 1 for detailed records)  How the JDBC invokes SP's based on the data comes in?

        like executing the 1st SP which is developed for header sections only once and executing 2nd SP which is developed for detailed records multiple times based on the number of records?

Is my assumption correct? Can you pls share the link if someone already done this kind of scenario?

Thanks

Arun

[Former Member]

Hi,

>>like executing the 1st SP which is developed for header sections only once and executing 2nd SP which is developed for detailed records multiple times

In case u want to execute 2 different SP's, then u can create 2 statement nodes (one for Header section and the other one for detail section) and do the mappings accordingly ..

<Statement1>---> For Header having cardinality 1.1

                <SPName action=’Execute’>

                <ParameterName Type=’Varchar’/> …. N

<Statement2>---->For Detail having cardinality 1.1

                <SPName action=’Execute’>

                <ParameterName Type=’Varchar’/> …. N

.

or u can use different approach:

a) Create a intermediate structure something like (chage it as per ur req):

<MT_Inter>

<Header>

<Detail>

</Mt_Inter>

b) Do a mapping between SAP message and Intermediate structure

c) After that use another mapping between Intermediate structure and receiver JDBC SP structure (having only one input Field)...and then use the return xml feature as stated above.

d) And in OM, combine these two mappings

http://scn.sap.com/thread/3202714

Thanks

Amit Srivastava

Thanks

Amit Srivastava