What happens to an index on altered columns

Altering the data type of a column that is contained in an index has implications for the index.

Begin general-use programming interface information.

Example: Assume that the following indexes are defined on the ACCOUNTS table:
CREATE INDEX IX1 ON ACCOUNTS(ACCTID);
CREATE INDEX IX2 ON ACCOUNTS(NAME);

When the data type of the ACCTID column is altered from DECIMAL(4,0) to INTEGER, the IX1 index is placed in a REBUILD-pending (RBDP) state.

End general-use programming interface information.

Index inaccessibility and data availability

Whenever possible, DB2® tries to avoid using inaccessible indexes in an effort to increase data availability. DB2 allows you to insert into, and delete from, tables that have non-unique indexes that are in an RBDP state. DB2 also allows you to delete from tables that have unique indexes that are in an RBDP state.

REBUILD INDEX with the SHRLEVEL CHANGE option allows read and write access to the data for most of the rebuild operation.

In certain situations, when an index is inaccessible, DB2 can bypass the index to allow applications access to the underlying data. In these situations, DB2 offers accessibility at the expense of performance. In making its determination of the best access path, DB2 can bypass an index under the following circumstances:
  • Dynamic PREPAREs

    DB2 avoids choosing an index that is in an RBDP state. Bypassing the index typically degrades performance, but provides availability that would not be possible otherwise.

  • Cached PREPAREs

    DB2 avoids choosing an index that is both in an RBDP state and within a cached PREPARE statement, because the dynamic cache for the table is invalidated whenever an index is put into an RBDP state.

In the case of static BINDs, DB2 might choose an index that is in an RBDP state as the best access path. DB2 does so by making the optimistic assumption that the index will be available by the time it is actually used. (If the index is not available at that time, an application can receive a resource unavailable message.)

Padding

Begin general-use programming interface information.
When an index is not padded, the value of the PADDED column of the SYSINDEXES table is set to N. An index is only considered not padded when it is created with at least one varying length column and either:

  • The NOT PADDED keyword is specified.
  • The default padding value is NO.

When an index is padded, the value of the PADDED column of the SYSINDEXES table is set to Y. An index is padded if it is created with at least one varying length column and either:

  • The PADDED keyword is specified
  • The default padding is YES.

In the example of the ACCOUNTS table, the IX2 index retains its padding attribute. The padding attribute of an index is altered only if the value is inconsistent with the current state of the index. The value can be inconsistent, for example, if you change the value of the PADDED column in the SYSINDEXES table after creating the index.

Start of changeWhether indexes are padded by default depends on the DB2 release in which the index was created and the release in which the system was originally installed:End of change

Start of change
  • Indexes that were created in a pre-DB2 Version 8 release are padded by default. In this case, the value of the PADDED column of the SYSINDEXES catalog table is blank (PADDED = ' '). The PADDED column is also blank when there are no varying length columns.
  • In subsystems that were migrated from a pre-DB2 Version 8 release, the default is to pad all indexes that have a key with at least one varying length column. In this case, the value of the PADDED column of the SYSINDEXES catalog table is YES (PADDED = 'Y').
  • In subsystems that were originally installed in DB2 Version 8 new-function mode or a later DB2 release, indexes that are created with at least one varying length column are not padded by default. In this case, the PADDED column of the SYSINDEXES catalog table is set to NO (PADDED = 'N').
End of change

End general-use programming interface information.