It has been my experience that when I add a column to a table via
ALTER TABLE that the INDEXes would also rebuild(?) and pick up the new
column. However that does not seem to the case with the latest file
implementation I ran. Some of the indexes have the new field, some
don't. We don't use dependencies.
Example of an index that picked up the new field:
CREATE INDEX PkgAsnDay3
ON PkgAsnDayP ( CUST#, PKGLVL, BTN#, TID, SEQ#, PKGID, ROWID);
Examples of indexes that didn't pick up the new field:
CREATE INDEX PkgAsnDay4
ON PkgAsnDayP (Cust#, BTN#, TID, PkgID, PkgLvl, RatePer
, UnlFlg, EffDte, ThrDte, ExpDte, PkgTyp)
CREATE INDEX PkgAsnDay5
ON PkgAsnDayP (CUST#, PKGLVL, BTN#, TID, SEQ#, PKGID)
Where PkgLvl<> '@'
RCDFMT PkgAssgnF ADD ALL COLUMNS;
Here is the ALTER TABLE statement. (There was also a Label On
Alter Table PkgAssgnP
Add Column Overage Decimal(15,4) NOT NULL DEFAULT 0;
LABEL ON COLUMN PkgAssgnP(
PkgLvl IS 'Package Level' ,
Overage Is 'Overage Amount '
I did find this in the SQL Reference under Alter Table:
"Indexes may need to be rebuilt. (Any indexes that need to be
rebuilt are rebuilt asynchronously by database server jobs.)
An index does not need to be rebuilt when columns are added to a
table or when columns are dropped or altered and those columns are
not referenced in the index key.
Altering a column that is used in the key of an index or constraint
usually requires the index to be rebuilt. The index does not need to
be rebuilt, however, in the following cases:
- The length attribute of a VARCHAR or VARGRAPHIC key is increasing.
- The CCSID of a column is changing but no conversion is necessary
between the old and new CCSID. For example, if one CCSID is 65535"
Overage is not a key field in any index, yet PkgAsnDay3 was rebuilt.
I'm a bit perplexed.
Any thoughts? We're on IBM i 7.1.
This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact