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.

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.

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

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?

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.