How to reorg individual indexes in DB2.
BennyVerplancke 270006XMXM Comment (1) Visits (10690)
A REORG INDEXES command on a table, will start a rebuild of all indexes on a table.
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) ) ;
A reorg of an individual index is not available here, you get this error code :
$ db2 "REORG INDEX idx1 FOR TABLE FACT"
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:
The db2diag.log will also tell you which indexes are being recreated :
MESSAGE : ADM9503W Reorganizing index IID "5" (OBJECTID "6") in table space
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) )
create index idx1_p on fact_p ( col1 ) 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.