Skip to Content
0
Former Member
Jul 04, 2012 at 11:44 PM

Multiple records via JDBC Receiver Adapter using Stored Procedure

1001 Views

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"?>

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>