IBM – How to determine if an index needs to be rebuilt

How to determine if an index needs to be rebuilt

Question
This document provides information about how to determine if an index
needs rebuilding and how to monitor index rebuilds to know if they have
completed.
Answer
You can check if an index needs to be rebuilt by one of the following means:

  • Using the inspect database tool. For example:
    1. Issue the command:
      db2 “inspect check database for error state all results inspect.out”

    2. Then cd to the DIAGPATH.
      For example on UNIX®, this would be: cd $INSTHOME/sqllib/db2dump

    3. Issue the command:
      db2inspf inspect.out inspect.fmt

    4. If you view the corresponding inspect.fmt file, a message similar to the following will appear:
      Index phase start. Object: 3 Tablespace: 2
      Warning: Object is known to be in error state x400.
      Warning: Index object is in invalid state, requires index be re-built.

  • Using the database analysis and reporting tool (db2dart). For example:
    1. If you have a specific table to query, issue the following command:
      select tableid,tbspaceid,tabname,tabschema from syscat.tables where tabname = ‘<name of your table>’

    2. Then run the db2dart tool:
      db2dart <dbname> /T /TSI <tbspaceid from above> /OI <tableid from above>

    3. View the <dbname>.RPT (found in your DIAGPATH). Look for a message similar to the following:
      Index phase start. Object: 3 Tablespace: 2
      Warning: Object is known to be in error state x400.
      Warning: Index object is in invalid state, requires index be re-built.

You can determine whether the index rebuild has completed using one of following two methods:

  • Parse the db2diag.log using the db2diag utility for the function sqlrlRecreateIndex or sqldIndexCreate.
    Here’s an example of a db2diag.log message related to rebuild:

    2005-12-06-11.38.50.559186-300 E179511A430 LEVEL: Warning
    PID : 22917368 TID : 1 PROC : db2agent (SAMPLE)
    INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
    APPHDL : 0-758 APPID: *...
    FUNCTION: DB2 UDB, data management, sqldBeginIndexCreate, probe:1
    MESSAGE : ADM5540W Beginning index rebuild of "1" indexes on table "DB2INST1.STAFF".

    2005-12-06-11.38.50.562902-300 E179942A405 LEVEL: Warning
    PID : 22917368 TID : 1 PROC : db2agent (SAMPLE)
    INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
    APPHDL : 0-758 APPID: *...
    FUNCTION: DB2 UDB, data management, sqldIndexCreate, probe:1
    MESSAGE : ADM5541W Rebuilding index with IID "1" on table "DB2INST1.STAFF".

    2005-12-06-11.38.50.686043-300 E180348A437 LEVEL: Warning
    PID : 22917368 TID : 1 PROC : db2agent (SAMPLE)
    INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
    APPHDL : 0-758 APPID: *...
    FUNCTION: DB2 UDB, data management, sqldEndIndexCreate, probe:1
    MESSAGE : ADM5542W Index rebuild of indexes on table "DB2INST1.STAFF" completed successfully.

  • Parse the instance notification log (for example, named <instance name>.nfy on UNIX) for index rebuilds.
    The messages will be similar to the following:

    2005-12-06-11.38.50.558642 Instance:db2inst1 Node:000
    PID:22917368(db2agent (SAMPLE)) TID:1 Appid:*...
    data management sqldBeginIndexCreate Probe:1 Database:SAMPLE
    ADM5540W Beginning index rebuild of "1" indexes on table "DB2INST1.STAFF".

    2005-12-06-11.38.50.562590 Instance:db2inst1 Node:000
    PID:22917368(db2agent (SAMPLE)) TID:1 Appid:*...
    data management sqldIndexCreate Probe:1 Database:SAMPLE
    ADM5541W Rebuilding index with IID "1" on table "DB2INST1.STAFF".

    2005-12-06-11.38.50.685618 Instance:db2inst1 Node:000
    PID:22917368(db2agent (SAMPLE)) TID:1 Appid:*...
    data management sqldEndIndexCreate Probe:1 Database:SAMPLE
    ADM5542W Index rebuild of indexes on table "DB2INST1.STAFF" completed successfully.

Here
is an example of how you might perform both the initial check (to
determine if the rebuild should be performed) and then the actual
monitoring of the rebuild:

  1. Issue the commands:
    db2 “export to staff.ixf of ixf select * from staff”
    db2 “create index id1 on staff (id)”
    db2 “load from staff.ixf of IXF replace into staff indexing mode deferred”

  2. Change to the $DIAGPATH directory (for example $INSTHOME/sqllib/db2dump on UNIX).
  3. Issue the commands:
    db2 “inspect check database for error state all results inspect.raw”
    db2inspf inspect.raw inspect.fmt

  4. Analyze the inspect.fmt file
  5. Issue the commands:
    db2 terminate
    db2 restart db sample

  6. View the db2diag.log and instance notification log for messages.

Advertisements