Hi,

This is version 8.1 on AIX 5.2
We are trying to insert 3 million rows from table A to table B.
Its a simple insert into table B with select from A.

The script inserts first 700K rows within first minute but then slows down to 5000 rows per minute. In 'list applications show detail' it shows status as 'Compiling'.

Does anyone have faced anything like this?

Thanks in advance,
toby

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;
COMMIT;

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

I forget to mention that on massive inserts, the indexes are often a killer. If possible, create the indexes after the insert, even if that means dropping existing indexes. Same applies to referential constraints.

What type of tablespace , in which this table located.
if it is SMS, then SMS grows as you insert data and SMS generally allocate/format 1 page at a time.This feature can be changed by using
dbexfm utility allowing to allocate extent at a time.

regards,

mujeeb

I thnink mujeeb meant db2empfa utility ... You'll have to disconnect all applications before running this command.

There's also a developer domain article on the subject
http://www-106.ibm.com/developerwork...m-0403wilkins/

Cheers
sathyaram