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.
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.