PI+JDBC+Bulk Insert to DB2

[Former Member]

Hello experts, I'm having a big problem i think.

The thing is I'm reading like 100,000 records from ECC and I hace to insert into DB2, but it takes like 3 hours. I changed indexes and primary key into DB2 table, and now it takes like an hour.

The question is, can I do a bulk insert to DB2 from PI, or a process that i don't know to lower the performance?

Thank you very much.

Sebastián.

[madhusudana_reddy2 · Accepted solution]

Obviously it will take lot of time to process 100000 records at a time. so please write sender query to read 10000 records at a time based on where condition. I also experienced the same situation then i written query in sender jdbc channel to read 10000 records at a time then my pi interface worked smoothly.

SELECT column1, column2 FROM myTable Where SIZE > 2000 ORDER BY NAME DESC or

select top 1 percent column1, column2 where column3 = "something".

thanks,

madhu

[Former Member]

Hi,

Not a good idea to insert that huge volume of data at one-go.

If you are using a proxy for ECC, then send 2000-4000 records at a time (completely depending on the size of record).

You can also check out the blog solution provided by Prateek.

Regards,

Neetesh

[prateek]

May be its better if you follow this blog and put a comment on the blog about your observations.

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

Regards,

Prateek