Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004

    Unanswered: commit data while importing

    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.



  2. #2
    Join Date
    Jan 2003
    Provided Answers: 1

    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,

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 5
    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.


  4. #4
    Join Date
    May 2003
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts