Creating New Index on BIG Table

Hi,

As always Prakash promised to back with the solutions with respect to practical scenario.
Here is another one for you.

Task: You are asked to create an Index in an existing table which is very huge.

Problem: You might face this issue. When you fired this SQL through batch spufi, it will run for minutes and at last it will abend with -904 return code saying 4K page is not available.
Investigation: You will find many messages in DSN?MSTR and DSN?DBM1 whcih says the temporary tablespace DSNDB07. unable to extend itself as it might reached it's limit of 2GB.

Solution: There are some fact you must know while creating index in a big table.
When you are creating the index, by default it will try to build the index instantly. CREATE INDEX statement uses RDS Sort to sort the keys. RDS Sort is very efficient for smaller tables. But when there is a huge table you must defer the creation of index by specifying DEFER YES in CREATE INDEX statement so that index for the table is registered in the DB2 catalog but the new index is in Rebuild pending status giving warning while creation of index.
REBUILD INDEX uses the the EXTERNAL SORT which outperforms the RDS Sort if the table size is significant. Even the performance of REBUILD INDEX is improved with parallel partition key extract and parallel index build.

By deferring the index creation and rebuilding the index, your index creation is over in few minutes and you will not get any extend failure message in MSTR started task.

Cheers..
Prakash C. Singh
IBM Certified DB2 DBA