What happens to an index on altered columns when immediate column alteration is in effect
When immediate column alteration is in effect, altering the data type of a column that is contained in an index has implications for the index.
Immediate column alteration is in effect when subsystem parameter DDL_MATERIALIZATION is set to ALWAYS_IMMEDIATE.
When subsystem parameter DDL_MATERIALIZATION is set to ALWAYS_PENDING, pending column alteration is in effect. Indexes on columns that have pending alterations are not affected by the column alterations.
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. 
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.
- 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
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.
Whether 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:
- 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').