Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    18

    Unanswered: How does DB2 create index and enforce constraints?

    Hi
    I am loading a data warehouse having DB2 as target database. I load close to 1million records where commit frequency is 10000. I want that to improve performance all the contsrinats and indexes should be created at the end of complete load and not at every commit. How can I achieve that?How does DB2 normally works? Will the command of " drop index " before load and " create index " after load will make any sense since my loading type is truncate-insert.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are specifying COMMITCOUNT, then it applies to IMPORT, not LOAD. LOAD would be faster. Check the LOAD command syntax for what options you have on the index build (see the Command Reference manual).

    If you ever do a COMMITCOUNT with IMPORT, I would suggest you use a lower value of about 1000.
    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
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you are using a LOAD, only PK constraint violations will be detected and deleted .. SET INTEGRITY statement has to be run after the load command to validate data against other constraints .. Until this time, the table is inaccessible ..

    If you are using a LOAD, and the table is already big, use INDEXING MODE INCREMENTAL ... for million records, do not drop the indexes before loading .. the LOAD command starts building the indexes when loading the data itself ... Therefore, the index build is not only more efficient but also you can avoid a rebuild of the entire index ...

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    May 2006
    Posts
    18
    I am loading the data using ETL Informatica. How can i know whether it is using LOAD or import? Is there any way to explicitly specify LOAD? One more thing the will the time expenditure in loading data with index is less that loading it without them and then rebuilding it?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by kunal
    One more thing the will the time expenditure in loading data with index is less that loading it without them and then rebuilding it?
    If you are using IMPORT, then it is possible that dropping the indexes first and recreating them after the IMPORT is faster. But this depends on many factors, so the only way to know for sure is to try it.
    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
  •