11-20-03, 16:42 #1Registered User
- Join Date
- May 2002
Unanswered: Table reorg vs Clustered/primary index reorg
First off, we are running UDB version 7.2 and so we are not able to run a reorg of an index. I wrote a script to run a reorgchk and if the table is marked, I reorg it. I also check for marked indexes. If it is a normal index, I run db2look and drop and then re-create it. We have had problems when we drop a primary index and try recreating it... Another index automatically becomes the primary....
So what we have done is select the associated table for Reorg.... This is also done for cluster indexes.....
I first assumed that reorging the data table will do nothing for the indexes. Our Mainframe DB2 admin asked us to reorg the Tables....
My ? is, am I wrong on my assumption and a reorg of the table will not help the primary or clustered indexes?????
Thanks in advance.....
11-20-03, 18:01 #2Registered User
- Join Date
- May 2003
In version 7, re-orging the table reorgs all the indexes.
There must always be a unique index on a defined Primary Key that is used by DB2 to enforce uniqueness. Obviously, it usually is good for performance also. If you create an index before the Primary Key is defined (defined later with Alter Table) that is on the proper primary key columns (for a primary key to be defined later) and the index is defined as unique, then DB2 will use the index you created instead of creating its own.M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390