In DB2LUW Why index rebuild might need more resources.

Body

Though  most of the expert  DBAs   know  about this  I  wanted to highlight a common question I have faced  time to time.

When   index  rebuild  runs it might  take large amount of resources.

By  index  rebuild it  means the  system is  rebuilding the indexes on it's own for some reason.

It could be  as part of  making  indexes invalid  using a  db2dart option  to  fix index  corruption,   it could be index  rebuild as part of  db2 load  or,   even due to reorg.

When system  rebuild  indexes it does so treating  all the indexes as  single object and it  just rebuild the entire object as one step.

Internally it has  resource management ways.   But,  still it will be  different than  recreating  one index at a time.

Specially,  it might be more prominent in earlier  Db2 levels.

Can imagine how it might go  when a  table has  100+  index  which is what I noticed in a  recent case.

The  best way will be to avoid any other concurrent   workload while this index rebuild going on.   Or,  try to create back  one index at a time sequentially if possible.  Or,  provide more resources where it's  needing so.   Like memory  or,  temp space etc