Enherit column attributes when ALTERing column
I have this:
1. A field-reference table (fieldref):
CREATE TABLE mylib/fieldref ( A CHAR (10) NOT NULL WITH DEFAULT, B CHAR (10) NOT NULL WITH DEFAULT, C DECIMAL (7 , 5) NOT NULL WITH DEFAULT, PRIMARY KEY (A))
2. A data table (myfile) created on the basis of the above fieldref table:
CREATE TABLE mylib/myfile AS (SELECT A, C FROM mylib/fieldref) WITH NO DATA
If I alter the attributes of a column (field) in my fieldref table, I would like the alteration to cascade to all tables created on the basis of fieldref; - is this functionality available with SQL ?
To clarify: An ALTER TABLE mylib/fieldref ALTER COLUMN a SET DATA TYPE CHARACTER (12) + ??? shall cause the a column in the myfile to also expand from 10 to 12 chars.
Software/Hardware used:
OS i v6r1 on Power i
1. A field-reference table (fieldref):
CREATE TABLE mylib/fieldref ( A CHAR (10) NOT NULL WITH DEFAULT, B CHAR (10) NOT NULL WITH DEFAULT, C DECIMAL (7 , 5) NOT NULL WITH DEFAULT, PRIMARY KEY (A))
2. A data table (myfile) created on the basis of the above fieldref table:
CREATE TABLE mylib/myfile AS (SELECT A, C FROM mylib/fieldref) WITH NO DATA
If I alter the attributes of a column (field) in my fieldref table, I would like the alteration to cascade to all tables created on the basis of fieldref; - is this functionality available with SQL ?
To clarify: An ALTER TABLE mylib/fieldref ALTER COLUMN a SET DATA TYPE CHARACTER (12) + ??? shall cause the a column in the myfile to also expand from 10 to 12 chars.
Software/Hardware used:
OS i v6r1 on Power i
ASKED:
August 30, 2010 8:21 PM
UPDATED:
August 30, 2010 11:59 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
I haven’t heard of such a capability, and I can’t quite picture how it would work in practice. I’d expect potentially disastrous results in a number of cases.
Consider a simple foreign key constraint over a CustomerNumber column. Half a dozen large tables might relate to a CustomerNumber. A change to increase size of a reference column from eight to ten characters would need to cascade through all tables simultaneously. Each table would need to be duplicated in whole at once (and it might need to happen within journal entries).
I wouldn’t be surprised if it could bring a system to its knees.
Tom