topic Re: Using Native SQL in ABAP for DB2 database in Application Development and Automation Discussions

Using Native SQL in ABAP for DB2 database

Former Member — Fri, 19 Oct 2007 20:59:33 GMT

Dear Friends,

I have 500K records in ITAB(Internal table) which needs to insert in 'Z' transparent table. Currently it is taking hours of time for insertion and commit.

Does using Native SQL helps in performance or any suggestions? If so please send the code. Our database is DB2.

Your immediate reply is appreciated. Thanks in advance.

Re: Using Native SQL in ABAP for DB2 database

Former Member — Fri, 19 Oct 2007 21:04:42 GMT

No - I have found that native SQL is quite a bit slower.

The problem may be in the commits. Try commiting every 10,000 or so records.

And do array inserts - insert an entire internal table at a time.

Rob

Re: Using Native SQL in ABAP for DB2 database

Former Member — Fri, 19 Oct 2007 21:10:48 GMT

I tried array insertion with intervel of 8,000 records but not much improvement.

Thanks,

Rama.

Re: Using Native SQL in ABAP for DB2 database

Former Member — Fri, 19 Oct 2007 21:14:54 GMT

Make the internal table large enough so that you can do as few commits as possible without dumping on the rollback area.

Rob

Re: Using Native SQL in ABAP for DB2 database

Former Member — Sat, 20 Oct 2007 06:21:34 GMT

Hi Rama,

Using array inserts and commit after each Insert, set up your array size as a parameter and try different array sizes.

I would start with 2000 and increment by 1000 or 2000 and see what the optimal array size is.

I would think more commits are desirable, as the array gets bigger it takes longer for the system to prepare in the event of a rollback.

Just remember to commit after each insert. And are you enqueueing the table first?

Hope this helps.

Filler