In DB2LUW Why index rebuild might need more resources.
Biswarup(Bis)Mukherjee 120000HKTY Visits (3402)
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