Hello All:

Is there an option in the db2move to commit data as the data similar to commit=y in oracle for the importing data.
I have one huge table to import, i.e around 10millions records. so if i have the option in db2 which can commit as soon as the transaction log is full, i will not be able to create a huge transaction log for 10million records.


regards

srinivas

Hi,

Open DB2 Command Window and write command:
db2 ? import

You will get help for the whole sintax of import command.

If I remember correctly, when no "commit" option is specified the auto commit is turned on, so log full command is not posible.

Try to consider of using load instread of import. Load is 100 times faster and does not use logs.

Hope this helps,
Grofaty

There is a commitcount = x parameter on the IMPORT statement. It basically means that after x inserts, issue a commit. This prevents the logs from filling up.

Andy

commitcount = 1000 should keep the logs from filling up and not hurt performance. The default log buffer size (logbufsz) is usually way to small, and should be increased if you are doing large imports (or just about any other SQL). You will need to increase the database heap size (dbheap) accordingly.

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