Marking individual indexes invalid

Body

There has been a long standing request to allow for marking of individual indexes invalid via the db2dart /MI command.   This would be extremely useful in the case where crash recovery is failing on one invalid index page.  So even though all of the other indexes on a table may be valid,  using db2dart /MI invalidates ALL of the indexes on the table.  

By default all indexes on a table are stored in the same index object, and the db2dart /MI command operates at the index object level.  There is an option to change this behavior for tables that are not already range partitioned.   This involves creating a range partitioned table with only one range.   This allows for the creating of non partitioned indexes.   

Some definitions of terms:

partitioned table -   a table that has one or more ranges defined by the create table statement
partitioned index -  an index that references only rows in a single range of a partitioned table
not partitioned index - an index that references all rows in all ranges of a partitioned table

An example:

create table tab1(c1 int, c2 char(30));
create index i1 on tab1(c1) ;
create index i2 on tab1(c2) ;
create index i3 on tab1(c1,c2) ;

Looking at db2pd -db <db> -tcb all

TCB Table Information:
Address            TbspaceID TableID PartID MasterTbs MasterTab TableName          SchemaNm ObjClass DataSize   LfSize     LobSize    XMLSize
0x07000000DFE5CD58 5         4       n/a    5         4         TAB1               DB2INST1 Perm     1          0          0          0

TCB Table Stats:
Address            TableName          SchemaNm Scans      UDI        RTSUDI               PgReorgs   NoChgUpdts Reads      FscrUpdates Inserts    Updates    Deletes    OvFlReads  OvFlCrtes  RowsComp   RowsUncomp CCLogReads StoreBytes BytesSaved
0x07000000DFE5CD58 TAB1               DB2INST1 0          0          0                    0          0          0          0           0          0          0          0          0          0          0          0          -          -

TCB Index Information:
Address            InxTbspace ObjectID PartID TbspaceID TableID MasterTbs MasterTab TableName          SchemaNm IID   IndexObjSize
0x07000000DFE5DFD0 5          4        n/a    5         4       5         4         TAB1               DB2INST1 3     5
0x07000000DFE5DFD0 5          4        n/a    5         4       5         4         TAB1               DB2INST1 2     5
0x07000000DFE5DFD0 5          4        n/a    5         4       5         4         TAB1               DB2INST1 1     5

TCB Index Stats:
Address            TableName          IID   PartID EmpPgDel   RootSplits BndrySplts PseuEmptPg EmPgMkdUsd Scans      IxOnlyScns KeyUpdates InclUpdats NonBndSpts PgAllocs   Merges     PseuDels   DelClean   IntNodSpl
0x07000000DFE5DFD0 TAB1               3     n/a    0          0          0          0          0          0          0          0          0          0          1          0          0          0          0
0x07000000DFE5DFD0 TAB1               2     n/a    0          0          0          0          0          0          0          0          0          0          1          0          0          0          0
0x07000000DFE5DFD0 TAB1               1     n/a    0          0          0          0          0          0          0          0          0          0          1          0          0          0          0

Notice in TCB Index Information how each index shares the same index ObjectID.   This means that a db2dart /MI operation will mark all 3 indexes as invalid.   Now create the table as a range partitioned table and use not partitioned indexes.

create table tab1(c1 int, c2 char(30)) partition by range (c1) (starting MINVALUE ending MAXVALUE);
create index i1 on tab1(c1) not partitioned;
create index i2 on tab1(c2) not partitioned;
create index i3 on tab1(c1,c2) not partitioned;

Looking at db2pd -db <db> -tcb all

TCB Table Information:
Address            TbspaceID TableID PartID MasterTbs MasterTab TableName          SchemaNm ObjClass DataSize   LfSize     LobSize    XMLSize
0x07000000DFE3C7D8 5         4       0      -6        -32768    TAB1               DB2INST1 Perm     1          0          0          0

TCB Table Stats:
Address            TableName          SchemaNm Scans      UDI        RTSUDI               PgReorgs   NoChgUpdts Reads      FscrUpdates Inserts    Updates    Deletes    OvFlReads  OvFlCrtes  RowsComp   RowsUncomp CCLogReads StoreBytes BytesSaved
0x07000000DFE3C7D8 TAB1               DB2INST1 0          0          0                    0          0          0          0           0          0          0          0          0          0          0          0          -          -

TCB Index Information:
Address            InxTbspace ObjectID PartID TbspaceID TableID MasterTbs MasterTab TableName          SchemaNm IID   IndexObjSize
0x07000000DFE40F50 5          7        n/a    n/a       n/a     -6        -32768    TAB1               DB2INST1 3     3
0x07000000DFE3FF50 5          6        n/a    n/a       n/a     -6        -32768    TAB1               DB2INST1 2     3
0x07000000DFE3E450 5          5        n/a    n/a       n/a     -6        -32768    TAB1               DB2INST1 1     3

TCB Index Stats:
Address            TableName          IID   PartID EmpPgDel   RootSplits BndrySplts PseuEmptPg EmPgMkdUsd Scans      IxOnlyScns KeyUpdates InclUpdats NonBndSpts PgAllocs   Merges     PseuDels   DelClean   IntNodSpl
0x07000000DFE40F50 TAB1               3     n/a    0          0          0          0          0          0          0          0          0          0          1          0          0          0          0
0x07000000DFE3FF50 TAB1               2     n/a    0          0          0          0          0          0          0          0          0          0          1          0          0          0          0
0x07000000DFE3E450 TAB1               1     n/a    0          0          0          0          0          0          0          0          0          0          1          0          0          0          0

In the TCB Index Information each index now has its own index ObjectID.  Db2dart /MI requires input of the index object id.   By using not partitioned indexes this now separates each index into its own index object id.   So any db2dart /MI against this table will only be able to invalidate a single index.

Details for XML indexes:

When creating a table with an XML column, there are 2 system indexes created by default.   This same method allows for separating the xml indexes into separate index object ids.

create table tab1(c1 int, c2 xml) partition by range (c1) (starting MINVALUE ending MAXVALUE);
create index i1 on tab1(c1) not partitioned;

Looking at db2pd -db <db> -tcb all

TCB Table Information:
Address            TbspaceID TableID PartID MasterTbs MasterTab TableName          SchemaNm ObjClass DataSize   LfSize     LobSize    XMLSize
0x07000000DFE5CD58 5         4       0      -6        -32768    TAB1               DB2INST1 Perm     1          0          0          1

TCB Table Stats:
Address            TableName          SchemaNm Scans      UDI        RTSUDI               PgReorgs   NoChgUpdts Reads      FscrUpdates Inserts    Updates    Deletes    OvFlReads  OvFlCrtes  RowsComp   RowsUncomp CCLogReads StoreBytes BytesSaved
0x07000000DFE5CD58 TAB1               DB2INST1 0          0          0                    0          0          0          0           0          0          0          0          0          0          0          0          -          -

TCB Index Information:
Address            InxTbspace ObjectID PartID TbspaceID TableID MasterTbs MasterTab TableName          SchemaNm IID   IndexObjSize
0x07000000DFE40D50 5          6        n/a    n/a       n/a      -6        -32768    TAB1               DB2INST1 3     3
0x07000000DFE3FCD0 5         5        n/a    n/a       n/a      -6        -32768    TAB1               DB2INST1 2     3
0x07000000DFE1FC50 5         4          0        5         4        -6        -32768    TAB1               DB2INST1 1     3

TCB Index Stats:
Address            TableName          IID   PartID EmpPgDel   RootSplits BndrySplts PseuEmptPg EmPgMkdUsd Scans      IxOnlyScns KeyUpdates InclUpdats NonBndSpts PgAllocs   Merges     PseuDels   DelClean   IntNodSpl
0x07000000DFE40D50 TAB1               3     n/a    0          0          0          0          0          0          0          0          0          0          1          0          0          0          0
0x07000000DFE3FCD0 TAB1               2     n/a    0          0          0          0          0          0          0          0          0          0          1          0          0          0          0
0x07000000DFE1FC50 TAB1               1     0      0          0          0          0          0          0          0          0          0          0          1          0          0          0          0

In the TCB Index Information,  note that IID 1 is using the same objectID (4) as the base table.   This is the XML regions index.   Both the Column Path Index (IID 2) and the user created index (IID 3) are not partitioned indexes and exist in their own index object id.    

This method has allowed each XML index to be separated into a unique index object id.  Recreating the XML indexes can take a significant amount of time if the table is large or the xml is particularly complex.  Avoiding having to rebuild XML indexes will significantly speed up the index rebuild time.    

Advantages:

* db2dart /MI can be used to mark individual indexes as invalid

* Can avoid having to rebuild XML indexes if only user indexes are bad

* Can reorganize individual indexes by using ‘reorg index <index_name> for table <table_name>’

Disadvantages:

* Creating not partitioned indexes uses more object ids.   There is a limit to how many object ids can be used in a tablespace.  
  For an SMS tablespace the limit is 65532.  
  For a DMS tablespace the limit is based on page size

    4K - 51971 if extent size is 2,  otherwise 53212
    8K - 53299
    16K - 53747
    32K - 54264

*  This method does not scale when applied to range partitioned tables that have more than one range.    When attaching or detaching ranges from a range partitioned table, entries in a non partitioned indexes will have to be added or deleted before the attach or detach operation can be completed.  This can be a very time consuming process and is much slower than attaching or detaching ranges when using partitioned indexes.  

*  No online table reorg allowed on range partitioned table

*  Each index entry will require an additional 2 bytes of storage for the partition id