Using Native SQL in ABAP for DB2 database

[Former Member]

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.

[Former Member]

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

[Former Member]

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

Thanks,

Rama.

[Former Member]

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

Rob

[Former Member]

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