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>