Thanks for all the input.
Trevor, it does seem like the RCDFMT on PkgAsnDay4 is pointless and only harming me here. PkgAsnDay5 needs the RCDFMT since it has the Where condition because we want more than just the key fields in the file. I pretty much never use the Where condition on an index in new development, but for older files they're pretty much there to stay.
Chuck, I did the dspdbr as you suggested with FMT(*ALL) and the logicals that weren't re-created aren't listed as you suspected. Sidenote: I'm using the DB2 for i SQL Reference 7.1 pdf.
Looks like I'm going to have to revisit our file implementation process.
Thanks for all the input,
Kurt Anderson
Sr. Programmer/Analyst - Application Development, Service Delivery Platform
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, March 26, 2014 3:34 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Alter Table Add Column - Indexes
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.
midrange.com
