How to reorg individual indexes in DB2.

Body

A REORG INDEXES command on a table, will start a rebuild of all indexes on a table.

( See : https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001966.html  )

This can be a long operation on a large table.

E.g. if there are 5 indexes on a table and the REORG INDEXES command is interrupted whilst the 5 index is being REORG'ed , then the whole operation will need to be re-executed.

In HADR environments, when LOGINDEXBUILD is typically set to ON, the rebuilding of the indexes can take a large amount of transaction log space, in such environments it would be desirable to have more control over these index reorgs by having the ability to reorg indexes one by one.

Luckily the ability is there in the REORG INDEX command, however there is a caveat. This is only available on nonpartitioned indexes on a partitioned table.

So what if you have a table that is not easily partitioned or it is not desirable to have the data partitioned.

The approach I wanted to illustrate here is to simply use a catch-all partition for such tables.

e.g. take this table :

create table fact ( col1 int , col2 char(200), col3 char(100), col4 char(50 ) , col5 char(10) ) ;
create index idx1 on fact ( col1 );
create index idx2 on fact ( col1 , col2 );
create index idx3 on fact ( col2, col3 );
create index idx4 on fact ( col1, col5 );
create index idx5 on fact ( col2, col3, col4 );
create index idx6 on fact ( col3, col4 ) ;

A reorg of an individual index is not available here,  you get this error code :

$ db2 "REORG INDEX idx1 FOR TABLE FACT"    
SQL0270N  Function not supported (Reason code = "89").  SQLSTATE=42997

the only option you have is to run a reorg on all indexes :

$ db2 "REORG INDEXES ALL FOR TABLE FACT"

this can be monitoring using db2pd

db2pd -db <db> -reorgs index

Index Reorg Stats:
Retrieval Time: 05/08/2017 06:03:04
TbspaceID: 3        TableID: 6     
Schema: BENNYV   TableName: FACT              
Access: Allow read
Status: In Progress
Start Time: 05/08/2017 06:02:54   End Time: -                  
Total Duration:  -
Prev Index Duration: 00:00:04
Cur Index Start: 05/08/2017 06:02:59
Cur Index: 2            Max Index: 5            Index ID: 2    
Cur Phase: 2          (Build  )   Max Phase: 2         
Cur Count: 131545                 Max Count: 1000000             
Total Row Count: 1000000          

The db2diag.log will also tell you which indexes are being recreated :

MESSAGE : ADM9503W  Reorganizing index IID "5" (OBJECTID "6") in table space
          "IBMDB2SAMPLEREL" (ID "3") for table "BENNYV  .FACT" (ID "6") in
          table space "IBMDB2SAMPLEREL" (ID "3").

As stated, for a large table with many indexes, it might be preferred to have a way of running that reorg one by one so that this can be scheduled.

When one index reorg completes, it is often also easier to then extrapolate the time needed for this operation to see how long it would take to reorganize the other indexes.

given our example, we can recreate the table as follows :

create table fact_p ( col1 int , col2 char(200), col3 char(100), col4 char(50 ) , col5 char(10) )
partition by range ( col1 ) ( starting MINVALUE ending  MAXVALUE ) ;

create index idx1_p on fact_p ( col1 ) not partitioned;
create index idx2_p on fact_p ( col1 , col2 ) not partitioned;
create index idx3_p on fact_p ( col2, col3 ) not partitioned ;
create index idx4_p on fact_p ( col1, col5 ) not partitioned;
create index idx5_p on fact_p ( col2, col3, col4 ) not partitioned;
create index idx6_p on fact_p ( col3, col4 ) not partitioned;

The table only has 1 partitioned defined and the range is from the minimum value of a 4 byte integer to the maximum value.. so whatever row is inserted into the table, it will end up in that partition.

Note that NULL values will also end up in this partition. 

The default partitioning option for indexes is to create them as partitioned indexes, hence the explicit not partitioned clause. It is possible to have a mix of the two types of indexes.

The reorg indexes all command for the table will be needed to reorg the partitioned indexes, whereas the nonpartitioned indexes require the individual reorg index command.

There would be no real visible change to this table, but we can now run individual index reorgs like :

db2 "reorg index idx1_p for table fact_p"

A row in a partitioned table is identified by a 2 byte partition identifier, a 4 byte page number and a 2 byte row number. In a nonpartitioned table, the 2 byte partition identifier is not needed.

This means that the Row IDs stored in the indexes will be 2 bytes longer for a partitioned table. This may make the indexes sizes a few percentages larger.