Problem in inserting huge data in oracle data base from SAP PI using JDBC

[Former Member]

Hi,

we are doing one SAP-APO (Proxy) to Database (oracle) interface. In that daily 5 lakhs records are going to flow from SAP APO to oracle data base in 10 thousand batch.

when we tested this interface in test environment it is taking very high time to insert 10 thousand records in data base.

In advance tab of JDBC receiver channel we have put following parameters.

1. poolWaitingTime = 5000

2. taskTimeout = 300

3. As data base is oracle Batch Mode option in advance mode will not work.

Have anybody faced this type of problem before?

Regards,

Prashant Kudtarkar

[Former Member]

You may think about your architecture of that scenario. It normally doesn't make much sense to pump that many records through XI for a single scenarioI, it may have negative side-effects regarding performance and also monitoring. Maybe there is a better way, e.g. access the Oracle database directly from SAP APO, in a batch job.

CSY

[former_member206760]

<keep the discussion meaningful for everyone - Moderator>

may be if athe above option suggested do not work ..then u can try to suggest the option of a CURSOR/STORED procedure

on the databse side so that the u just have to call the stored procedure and the insertion would be done by the stored procedure

Edited by: Prateek Raj Srivastava on Feb 25, 2010 3:06 PM

[former_member217032]

how to set time limits in jdbc recevier adapter, its means time limits, within 15 minits 150000 records i have to insert into data base. how can do this scarios?

[prateek]

Here are the approaches to improve JDBC receiver performance.

1. You may increase the thread count for JDBC related queues. This has to be done in accordance with SAP Note 1084161.

2. There is parameter in JDBC communication channel called Maximum Concurrency. It signifies that one communication channel can make how many connections to database. This is 1 by default and could be increased to some values like 3-4.

3. In the Visual Admin/ NWA, there is a parameter called as queueParallelism.maxReceivers which defines the number of parallel worker threads for one receiver channel instance. This should be done following SAP Note 1136790. This can be done along with the first point.

Regards,

Prateek

[Former Member]

Hi Prateek,

Have you ever tried this options before?

There are other interfaces of JDBC as well as other adaptors on production. Will this setting affect other interface on production?

Also is it require to follow all three advices given by you for performance tuning?

Thanks,

Prashant Kudtarkar

[prateek]

I have recently tried them on our production environment. But you should definitely try it on Test system first. There won't be any effect on other adapters. There would be some effect on JDBC adapter of course and thats why we are trying this

All three are not mandatory. Read the description and apply the ones that best suits you.

Regards,

Prateek

[Former Member]

Hi Prateek,

Today I got confirmation from our basis team that the setting suggested by you is already there in our test and production server. Is there any other way?

Thanks,

Prashant Kudtarkar

[Former Member]

Dear All,

We are using Oracle driver version 10.2.0.4.0.

Can anybody please confirm me does it support Batch Mode Option? If not, which version supports?

-Prashant.

[former_member217032]

hi,

im extracting records from ecc using proxys, records will be huge 150000, i have send this huge records to data base within 15 minits time limit, it possible to send huge data using SAP PI, how can we send huge reords to data base.how can i send records within time limits