Multiple records via JDBC Receiver Adapter using Stored Procedure

[Former Member]

Hi,

I have a requirement to send around 2.5 million records from ECC to an Oracle database. The Oracle database is located remotely from the PI system and the connection is via a VPN. The requirement is that this update should be sent daily and as the update is currently taking 55 hours this will not be possible. The PI system is a single stack 7.3 installation.

A suggestion for improving performance is that a stored procedure should be called instead of using an INSERT statement. I have tried this but I am unable to call the stored procedure from PI for more than a single record in a single call. I am aware that you can send multiple records by using multiple iterations of the statement node but this results in multiple calls to the Oracle database and so I don't think this would be any more efficient than using the insert statement.

My question is is it possible to insert multiple rows using a stored procedure without calling the stored procedure multiple times?

The suggestion that I have seen in other threads of using a local adapter engine is not possible in this case as the oracle database belongs to a third party.

Are there any alternative methods of carrying out this update that may result in improved performance? I have seen some suggestions of a java proxy but I'm not sure how you would go about this.

The xml I am currently using is included below.

Thanks

Ian

<?xml version="1.0" encoding="utf-8"?>

<n0:MT_PROMOTION_DFUS_TEST_SP xmlns:n0="urn:test.com:test:promotion" xmlns:prx="urn:sap.com:proxy:SRD:/1SAI/TASFEBAECF14DE5723EBF92:701:2009/02/10">

<Statement>

<dbTableName action="EXECUTE">

<table>TESTSP</table>

<PROMOID type="CHAR" isInput="True">1234</PROMOID>

<DMDUNIT type="CHAR" isInput="True">IHTST01</DMDUNIT>

<DMDGROUP type="CHAR" IsInput="True">IHTST</DMDGROUP>

<LOC type="CHAR" IsInput="True">5522</LOC>

<PROMO_THEME type="CHAR" IsInput="True">Test</PROMO_THEME>

<PROMO_THEME_DESCR type="CHAR" IsInput="True">Test Descr</PROMO_THEME_DESCR>

</dbTableName>

</Statement>

</n0:MT_PROMOTION_DFUS_TEST_SP>

[Former Member · Accepted solution]

Hi,
U can pass input structure as a xml string to stored procedure...so ur entire input structure will be send at a single go to SP.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Thanks Amit. Do you have any examples of how the input xml to the JDBC call should be structured for this? At the moment the structure looks like that below. I'm guessing that your solution requires the fields below node <table> to be structured differently.

<?xml version="1.0" encoding="utf-8"?>

<n0:MT_PROMOTION_DFUS_TEST_SP xmlns:n0="urn:test.com:test:promotion" xmlns:prx="urn:sap.com:proxy:SRD:/1SAI/TASFEBAECF14DE5723EBF92:701:2009/02/10">

<Statement>

<dbTableName action="EXECUTE">

<table>TESTSP</table>

<PROMOID type="CHAR" isInput="True">1234</PROMOID>

<DMDUNIT type="CHAR" isInput="True">IHTST01</DMDUNIT>

<DMDGROUP type="CHAR" IsInput="True">IHTST</DMDGROUP>

<LOC type="CHAR" IsInput="True">5522</LOC>

<PROMO_THEME type="CHAR" IsInput="True">Test</PROMO_THEME>

<PROMO_THEME_DESCR type="CHAR" IsInput="True">Test Descr</PROMO_THEME_DESCR>

</dbTableName>

</Statement>

</n0:MT_PROMOTION_DFUS_TEST_SP>

[Former Member · Accepted solution]

Hi,

Create a target stored procedure structure having only one field, after that  right click on ur input parent node and using return as xml ( i hope ur PI version is >= 7.1) pass the entire input structure as a xml string to the SP. Once done ur backend system SP has to read and parse the data from the xml string and do the further processing.

Hope it helps u...and please let me know in case u need some more inputs specific to this design.

<Statement>

<dbTableName action="EXECUTE">

<table>TESTSP</table>

<XML_Document type="VARCHAR" />

</dbTableName>

</Statement>

</n0:MT_PROMOTION_DFUS_TEST_SP>

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Thanks Amit. In the example structure that you provided the content should be included within the XML_Document element?

<Statement>

<dbTableName action="EXECUTE">

<table>TESTSP</table>

<XML_Document type="VARCHAR" /> <--- should the xml string be included here.

</dbTableName>

</Statement>

[Former Member · Accepted solution]

Hi,

>> In the example structure that you provided the content should be included within the XML_Document element

Correct...and while doing mapping use replace string function to remove the xml declaration from the input so that it becomes xml string.

Let me know in case u need more clarifications.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit,

Thanks for your reply.

I have now created a mapping which places all of the contents within a single string. However the JDBC adapter is still validating the contents of the string and throwing an error.

MP: exception caught with
cause com.sap.engine.interfaces.messaging.api.exception.MessagingException:
Error processing request in sax parser: Error when executing statement for
table/stored proc. 'INSERT_SOH_DATA' (structure 'stmt'): java.sql.SQLException:
ERROR: Invalid XML document format for stored procedure:
'type="<SQL-type>"' attribute is missing for element 'access'
(Setting
a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

Does my xml look as if it is structured correctly now? or do I need to add some additional parameters into the Receiver JDBC adapter? The message contents are below.

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_STOCK_ON_HAND_TEST xmlns:ns0="urn:test.com:jda:test">

<stmt>

     <dbTableName action="EXECUTE">

          <table>INSERT_SOH_DATA</table>

          <access>

          <p_soh_xml_string type="XMLTYPE" isInput="True">![CDATA[&lt;?xml                     version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;content&gt;&lt;access&gt;&lt;ITEM&gt;123001&lt;/ITEM&gt;&lt;LOC&gt;1001&lt;/LOC&gt;&lt;SPECIAL_STOCK&gt;0&lt;/SPECIAL_STOCK&gt;

&lt;VENDOR/&gt;&lt;OH&gt;1&lt;/OH&gt;&lt;QOH&gt;0&lt;/QOH&gt;&lt;/access&gt;&lt;access&gt;&lt;ITEM&gt;123002&lt;/ITEM&gt;&lt;LOC&gt;1001&lt;/LOC&gt;

&lt;SPECIAL_STOCK&gt;0&lt;/SPECIAL_STOCK&gt;&lt;VENDOR/&gt;&lt;OH&gt;1&lt;/OH&gt;&lt;QOH&gt;0&lt;/QOH&gt;&lt;/access&gt;&lt;/content&gt;.]

          </p_soh_xml_string>

          </access>

     </dbTableName>

     </stmt>

</ns0:MT_STOCK_ON_HAND_TEST>

Regards

Ian

[Former Member · Accepted solution]

Hi,

There are few remediation's required in your structure and mapping also.

1) Use attribute "type" value as "VARCHAR" instead of "XMLTYPE".

2) The parent node should be "Statement" instead of "Stmnt" and remove "access" element tag also (there is no need for the same).

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

3) Cross check your mapping, you have to remove xml declaration "<?xml version="1.0" encoding="UTF-8"?>" from the input to make it as XML string ( i have already pointed the mapping above).

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit,

Thanks for all of your help. The interface is now working as required at a more than acceptable performance level. Using the Stored Procedure the interface is taking under an hour to process 2.3 million records instead of 55 hours before.

The final data structure that was used is below.

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_STOCK_ON_HAND_TEST xmlns:ns0="test.com:jda:stockOnHand">

<stmt>

     <dbTableName action="EXECUTE">

     <table>INSERT_SOH_DATA</table>

     <p_soh_xml_string type="CLOB" isInput="true">![CDATA[&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;content&gt;&lt;access&gt;&lt;ITEM&gt;      1&lt;/ITEM&gt;&lt;LOC&gt;1001&lt;/LOC&gt;&lt;SPECIAL_STOCK&gt;0&lt;/SPECIAL_STOCK&gt;&lt;VENDOR/&gt;&lt;OH&gt;1&lt;/OH&gt;&lt;QOH&gt;0&lt;/QOH&gt;&lt;/access&gt;&lt;access&gt;&lt;ITEM&gt;      2&lt;/ITEM&gt;&lt;LOC&gt;1001&lt;/LOC&gt;&lt;SPECIAL_STOCK&gt;0&lt;/SPECIAL_STOCK&gt;&lt;VENDOR/&gt;&lt;OH&gt;1&lt;/OH&gt;&lt;QOH&gt;0&lt;/QOH&gt;&lt;/access&gt;

&lt;/content&gt;.]

     </p_soh_xml_string>

     </dbTableName>

</stmt>

</ns0:MT_STOCK_ON_HAND_TEST>

Regards

Ian

[Former Member · Accepted solution]

Hi,

Glad to know that.

"Return as xml" functionality is one of the most promising feature which can be used when sending huge amount of data to DB. In one of my project I have used the same design to send close to 3-3.5 millions records.

From a long time i am thinking of publishing a article on this but i guess eventually time has come.

Thanks

Amit Srivastava

[Muniyappan · Accepted solution]

Hi Amit Srivastava ,

I am also having the same scenario for select statement in synchronous interface. from ecc we will be sending product numbers. max records will be 200k.

input structure will be.

<product>--1 to unbounded

</productno>

</product>

in turn DB will be sending 10 fields per record.

<product>--1 to unbounded

</productno>

</name>

etc...

</product>

as per your comment

Once done ur backend system SP has to read and parse the data from the xml string and do the further processing.

1) is parsing going to be challenging one for DB guys?

2) so it will be one parameter(isInput="true" and isOutput="true") to stored procedure? sorry for asking this question as i new to JDBC

3) will db be sending the response in one field? or it will be like simple xml.

Regards,

Muni.

[Former Member · Accepted solution]

Hello,

1) is parsing going to be challenging one for DB guys?

>>No, I don't think so. There are lot of online links available, ur DB team can check that.

2) so it will be one parameter(isInput="true" and isOutput="true") to stored procedure? sorry for asking this question as i new to JDBC

>> Yes, u can pass entire request input in XML string (u can refer the structure/mapping shown above) and for that u need only one input parameter of type varchar.

3) will db be sending the response in one field? or it will be like simple xml.

>> I don' t think that will be a good idea, becoz if they send response in 1 field then u have to parse the same in PI and then only  u can map corresponding ECC structure.

So, ask them to send response in a normal format.

Thanks

Amit Srivastava

[Muniyappan · Accepted solution]

Thanks Amit for your quick response. it gives me an idea how it is going to work.

Regards,

Muni.

[former_member184948 · Accepted solution]

Thanks Amit!!!

your solution has helped me today!!!

[Former Member · Accepted solution]

HI Amit ,

I created similar target structure as yours but for me stored procedure is failing with below error :

Error While Sending Message: Additional error text from response: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'XXXX' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'XXXXXX' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

my target structure comes out as below which is without CDATA tag:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_XX xmlns:ns0="urn:XXXXX">

- <Statement>

- <ProcedureName action="EXECUTE">

<table>XXXX</table><CO_Area isInput="TRUE" isOutput="TRUE" type="CLOB"><RecordSet> <Record> <CO_Area/> <DocNo/> <LineItemNo/> <Amt/> <DCIndicator/> <PPStatus/> </Record> <Record> <CO_Area/> <DocNo/> <LineItemNo/> <Amt/> <DCIndicator/> <PPStatus/> </Record> </RecordSet></CO_Area>

</ProcedureName>

</Statement>

</ns0:MT_ExecuteCOReconcile>

  also can you please share how your Stored procedure looks like and how does it accepts multiple values . I have project delivery date near , urgent help is requested .

[Former Member]

HI Ian ,

I created similar target structure as yours but for me stored procedure is failing with below error :

Error While Sending Message: Additional error text from response: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'XXXX' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'XXXXXX' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

my target structure comes out as below which is without CDATA tag:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_XX xmlns:ns0="urn:XXXXX">

- <Statement>

- <ProcedureName action="EXECUTE">

<table>XXXX</table>

<CO_Area isInput="TRUE" isOutput="TRUE" type="CLOB"><RecordSet> <Record> <CO_Area/> <DocNo/> <LineItemNo/> <Amt/> <DCIndicator/> <PPStatus/> </Record> <Record> <CO_Area/> <DocNo/> <LineItemNo/> <Amt/> <DCIndicator/> <PPStatus/> </Record> </RecordSet></CO_Area>

</ProcedureName>

</Statement>

</ns0:MT_ExecuteCOReconcile>

  also can you please share how your Stored procedure looks like and how does it accepts multiple values . I have project delivery date near , urgent help is requested .

[Senthilprakash1]

Hi Ian,

Similar case with Oracle Db was addressed by one of my friend sometime back. have a look at his blog:

http://scn.sap.com/people/varun.mukund/blog/2010/03/25/fast-bulk-database-inserts-from-sap-pi

Regards,

Senthilprakash.

[baskar_gopalakrishnan2]

IMO, we can insert multiple rows in single call of stored procedure. Take help from DBA for writing stored procedure.

>The suggestion that I have seen in other threads of using a local adapter engine is not possible in this case as the oracle database belongs to a third party.

I don't think this statement is right.

[Former Member]

Hi Baskar,

Thanks for your reply.

The Oracle stored procedure is already written and can successfully insert multiple entries from an array. The issue is with passing an array from the JDBC adapter to the stored procedure.

Can you please clarify why you don't think my statement concerning the local adpater engine is correct?  The reason why this is the case is that the Oracle Database is hosted in a Cloud environment to which we only have limited access. Installing additional software on the servers where Oracle is hosted would not be permitted.