Is it just a coincidence that the two examples that did not work as
desired have the RCDFMT parameter?
Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Kurt Anderson
Sent: Wednesday, March 26, 2014 2:44 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL Alter Table Add Column - Indexes
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)
RCDFMT PkgAssgnF;
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 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"
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.
Kurt Anderson
Sr. Programmer/Analyst - Application Development, Service Delivery
Platform [cid:image002.jpg@01CD16F9.25D27630]
web: www.customcall.com<
http://www.customcall.com/> | email:
kurt.anderson@xxxxxxxxxxxx<mailto:kurt.anderson@xxxxxxxxxxxx>
A Division of Enghouse Systems Ltd.<
http://www.enghouse.com/> (TSX:ESL)
************************************************************************************************************************************************************************************************************
This message originates from Lincare Holdings Inc. It contains information which may be confidential or privileged and is intended only for the individual or entity named above.
It is prohibited for anyone else to disclose, copy, distribute or use the contents of this message.
All personal messages express views solely of the sender, which are not to be attributed to Lincare Holdings Inc., and may not be copied or distributed without this disclaimer.
If you received this message in error, please notify us immediately at MailAdmin@xxxxxxxxxxx or (800) 284-2006.
************************************************************************************************************************************************************************************************************
midrange.com
