I've got an odd problem ... if I create two tables that are mostly identical ... the only difference is the value specified as the default ...
CREATE TABLE DEVDMGWRK/TEST1 (C1 CHAR (10 ) not null DEFAULT ' ')
CREATE TABLE DEVDMGWRK/TEST2 (C1 CHAR (10 ) not null DEFAULT)
... and then I try to drop the default & not null attributes ...
alter table DEVDMGWRK/test1 alter column c1 drop default not null
alter table DEVDMGWRK/test2 alter column c1 drop default not null
... the alter on TEST1 works fine ... but the alter on TEST2 fails with a SQL0190 "Attributes of column C1 in TEST2 in DEVDMGWRK not compatible".
If I do a DSPFFD, the column in TEST1 shows the default value of ' '... but TEST2 shows no default value.
If I query the system table QADBIFLD, the columns in both tables show the same attributes for null & default.
FILE FIELD ALW DFT DEFAULT
NAME NAME NULL VALUE
TEST1 C1 N Y ' '
TEST2 C1 N Y ' '
The SYSCOLUMNS table shows the same thing ...
SYSTEM_COLUMN_NAME SYSTEM_TABLE_NAME IS_NULLABLE HAS_DEFAULT COLUMN_DEFAULT
C1 TEST1 N Y ' '
C1 TEST2 N Y ' '
Oddly enough ... if I drop the default and not null attributes separately, they both work fine.
My questions are these ...
1. Why, if the column attributes are identical, am I unable to drop the 'default not null' attributes from TEST2.
2. If no specific default value is specified when the table is created, how should I drop the not null & default values?
3. Is there a way, by looking at system tables, to determine that no specific default value was specified and that I should use the technique from question 2 (assuming there is one)?
This mailing list archive is Copyright 1997-2019 by
midrange.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 on our policy page. If you have questions about this, please contact