Results 1 to 5 of 5

Thread: Slow Inserts

  1. #1
    Join Date
    Sep 2003
    Posts
    63

    Unanswered: Slow Inserts

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    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

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

  4. #4
    Join Date
    Mar 2004
    Posts
    448
    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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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