MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » March 2014

Re: SQL Alter Table Add Column - Indexes



fixed

On 26-Mar-2014 11:44 -0700, Kurt Anderson wrote:
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.

When the Record Format is identical [e.g. prior to the RCDFMT clause], the INDEX will have the new column.

Note: The term "rebuild" is usually used specifically for a keyed "Access Path" of an INDEX [or keyed LF]. With regard to a new version of a file, the term would normally be "re-create" [recreate; I prefer to add the dash for visual effect, as emphasis for intended meaning].

Example of an index that picked up the new field:
CREATE INDEX PkgAsnDay3
ON PkgAsnDayP ( CUST#, PKGLVL, BTN#, TID, SEQ#, PKGID, ROWID);

In the output from the following request, the file PkgAsnDay3 would appear as the last in the list:
DSPDBR the_lib/PkgAsnDayP RCDFMT(*ALL)

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)
RCDFMT PkgAssgnF;

In the output from the following request, the file PkgAsnDay4 created with the above request, would *not* appear as a new entry in the list:
DSPDBR the_lib/PkgAsnDayP RCDFMT(*ALL)

CREATE INDEX PkgAsnDay5
ON PkgAsnDayP (CUST#, PKGLVL, BTN#, TID, SEQ#, PKGID)
Where PkgLvl<> '@'
RCDFMT PkgAssgnF ADD ALL COLUMNS;

In the output from the following request, the file PkgAsnDay5 created with the above request, would *not* appear as a new entry in the list:
DSPDBR the_lib/PkgAsnDayP RCDFMT(*ALL)

Here is the ALTER TABLE statement. (There was also a Label On
statement .)

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"

The term "index(es)" is a synonym for Access Path(s) in context of the above. The docs would best have used the AccPth term; there is of course, an Edit Rebuild of Access Paths (EDTRBDAP) command any numerous references to Access Path in places like DSPFD and all over the docs elsewhere, so there is little reason to use "index" when Access Path is meant. If the docs had been referring to the file, hopefully thy would have noted instead, to be more clear: "SQL INDEX database *FILE objects may need to be re-created." The page of doc has a _feedback_ link on the top right that can be clicked, wherein a suggestion can be submitted requesting the term Access Path be used to make the difference between the words "INDEX" and "index" more conspicuous.

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.


There is another doc reference [based on a KB item or Info APAR I wrote long ago] that describes that the files appearing in DSPDBR RCDFMT(*ALL) but which do not also appear in DSPDBR RCDFMT(*NONE) will be re-created as part of the ALTER request. I am sure I have posted a link to that in the past; I have no yet performed a web search to find it.






Return to Archive home page | Return to MIDRANGE.COM home page

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