Folks:

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)?

Thanks!

david


This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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 [javascript protected email address].