Here are some general tips for improving massive inserts:
The default log buffer size (logbufsz) is usually way to small, and should be increased if you are doing large inserts (or just about any other SQL). You will need to increase the database heap size (dbheap) accordingly.
I assume your bufferpool is large enough. The total of all bufferpools should usually use about 50% or more of all system memory (watch out for maximums on certain OS's).
Also consider lowering the CHNGPGS_THRESH to about 15-25, which causes page cleaning (writing updated pages in the buffer pool to disk) to start sooner, which is usually advantageous with large insert/update jobs.
If you have version 8 fixpak 4 or later, you could also try enabling the registry variable DB2_USE_ALTERNATE_PAGE_CLEANING. Setting this variable to ON provides a more proactive approach to page cleaning than the default approach (based on CHNGPGS_THRESH and LSN gap triggers).
Another important parameter is the Number of asynchronous page cleaners (NUM_IOCLEANERS). If you have multiple processors, this should definitely be set to a value of at least n-1 (where n is number of processors). It might also be advantageous to set this higher on a single processor machine, but I am not sure about that.
Turning off DB2 logging to improve speed:
You could also try turning the logging off. This is a good option if the table is new and integrity is not a concern if there is failure. In other words, if the table starts out empty. Once the insert is done, you can do a backup and their will be no integrity issues.
To do this, the table must be created with NOT LOGGED INITIALLY. Then the table can be altered with:
ALTER TABLE table-name ACTIVATE NOT LOGGED INITIALLY;
INSERT INTO tableA SELECT FROM tableB;
All three statements must be in the same unit of work with auto-commit turned off in the CLI (use +c parameter). As soon as the COMMIT is executed, the logging will be turned back on. Then take a backup of the tablespace (or entire database if archive logging not used).
Last edited by Marcus_A; 05-27-04 at 20:20.
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390