Performance issues in loading Large data from SAS Dataset to DB2

[deleted_user]

I am facing Performance issues in loading Large data from SAS Dataset to DB2.
For loading 3 million records to DB2 table the elapsed time is 23:29 mins and CPU time is 02:56 mins.
I have used following system options:
compress=yes reuse=yes STATS MEMRPT FULLSTIMER MSGLEVEL=I bufsize=512K bufno=MAX SORTSIZE=MAX UBUFNO = 20;
Both SAS and DB2 are on z/OS.
I tried to use following options for optimization:

1. INSERTBUFF option in LIBNAME for DB2: I receive error ERROR 22-7: Invalid option name INSERTBUFF.

2. DBCOMMIT = 100 option in LIBNAME for DB2: This created problem when no of rows to be inserted to DB2 table are less then mentioned in the DBCOMMIT option. Either it would take abnormaly large amount of time or job would end in error.

3. Used BULKLOAD = YES in proc append. I receive following NOTE: Bulkload append to a DB2 table is not currently supported. Standard row insert is used instead.

Which options should I use to overcome this issue. I need to bring down the elapsed time and also make sure that while loading large amount of data I use DBLOAD utility.

[LinusH]

What SAS (and DB2 ) versions do you use?
BULKLOAD has been supported since at least 9.1.3.
Take a look at the documentation for you release, and verify that your installtion/configuration supports bulk-load.

/Linus

[deleted_user]

Hi Linus,
Thanks for your reply.

The SAS Ver is 9.1.3 ServicePack 4, The DB2 version is 9.
As you said it might be some installation/Configuration issue.

Also is there any way in which we can turn off Logging into DB2 transaction log for a particular peice of SAS Load?

[LinusH]

This is what BULKLOAD probably does.
If you want to turn it off outside bulk-load, it is question for your DB2 DBA.
/Linus

[SASKiwi]

In addition to Linus's comment, I suggest you search the SAS Support site using the keywords DB2 and bulkload. There are lots of resources that will help you.

There are apparently 3 supported methods for bulk loading with the CLI LOAD method being recommended for very large loads. You will find examples of how each of these are used.