Hello Db2 people, 

we had a request to change a column from NULL to NOT NULL, at the beginning I doubt it could be possible thanks to that the table was still empty, so no rows, no null value to deal with.

Anyway, after trying seems that it's not possible, althought the error message is not clear about the problem:

ALTER TABLE XXX.DOCU_TABLE_TEST

         ALTER COLUMN FILE_TOSTORE SET NOT NULL;

-------------------------------

DSNT408I SQLCODE = -199, ERROR:   ILLEGAL USE OF KEYWORD NOT. TOKEN WITH

                   DEFAULT DATA INLINE CACHE MAXVALUE MINVALUE NOCACHE WAS EXPECTED

Just in case we tried other things ... ALTER TABLE ... ALTER COLUMN ... WITH DEFAULT 'whatever", or ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT ... but no success.

So when you go back to the deloper that requested this it's not easy to clarify where was the problem. I like to reply specifying where the problem is, but also adding some links to IBM site and/or some quote from IBM where it be specified.

But I was not able to find such a link, in fact, checking the IBM site I even thought there could be a way:

So this made me doubt if there was a way to do it, probably this is the reason why I am posting this (insecurity ... :-) ) 

Anybody has a link for confirming this assumption?

Thanks,

      Sole

The most solid argument I found to confirm that is not possible to change a column from NULL to NOT NULL, was this update in 2021 from my admired late Robert Catterall: DB2 for z/OS: Answering Some Questions About Adding and Dropping Table Columns

Hi!

 You are correct you cannot do this... I never figured out exactly why this is so badly documented but you can do nearly everything else in the whole world apart from this one ALTER! As Robert said the best (only!) way forward is to ALTER add a column NOT NULL, Update the column from the original changiikng and and all NULL vaules to a new value you choose, then ALTER DROP the column, REORG, ALTER rename the new column bnack to the old column, REORG... Phew! I would hope you could stack the last two ALTERs but you cannot...

DROP   TABLE ROYTEST ;                           
COMMIT ;                                         
CREATE TABLE ROYTEST (COL1 CHAR(1));             
COMMIT ;                                         
ALTER TABLE ROYTEST                              
  ADD COLUMN COL2 CHAR(1) WITH DEFAULT NOT NULL ;
COMMIT ;                                         
ALTER TABLE ROYTEST                              
  DROP COLUMN COL1 RESTRICT                      
;                                                
ALTER TABLE ROYTEST                              
  RENAME COLUMN COL2 TO COL1                     
;                                                
COMMIT;                                          

You get a pending REORG at DROP COLUMN which must be done before you can do the RENAME...

Sounds like an AHA Idea !

Hello Db2 people, 

we had a request to change a column from NULL to NOT NULL, at the beginning I doubt it could be possible thanks to that the table was still empty, so no rows, no null value to deal with.

Anyway, after trying seems that it's not possible, althought the error message is not clear about the problem:

ALTER TABLE XXX.DOCU_TABLE_TEST

         ALTER COLUMN FILE_TOSTORE SET NOT NULL;

-------------------------------

DSNT408I SQLCODE = -199, ERROR:   ILLEGAL USE OF KEYWORD NOT. TOKEN WITH

                   DEFAULT DATA INLINE CACHE MAXVALUE MINVALUE NOCACHE WAS EXPECTED

Just in case we tried other things ... ALTER TABLE ... ALTER COLUMN ... WITH DEFAULT 'whatever", or ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT ... but no success.

So when you go back to the deloper that requested this it's not easy to clarify where was the problem. I like to reply specifying where the problem is, but also adding some links to IBM site and/or some quote from IBM where it be specified.

But I was not able to find such a link, in fact, checking the IBM site I even thought there could be a way:

So this made me doubt if there was a way to do it, probably this is the reason why I am posting this (insecurity ... :-) ) 

Anybody has a link for confirming this assumption?

Thanks,

      Sole

The most solid argument I found to confirm that is not possible to change a column from NULL to NOT NULL, was this update in 2021 from my admired late Robert Catterall: DB2 for z/OS: Answering Some Questions About Adding and Dropping Table Columns

Fortunately for the OP the table is empty.  Which makes me think that a simple  DROP COLUMN followed by an ADD COLUMN might be sufficient.  Unless there is a requirement to have the columns in a particular order.

Hi!

 You are correct you cannot do this... I never figured out exactly why this is so badly documented but you can do nearly everything else in the whole world apart from this one ALTER! As Robert said the best (only!) way forward is to ALTER add a column NOT NULL, Update the column from the original changiikng and and all NULL vaules to a new value you choose, then ALTER DROP the column, REORG, ALTER rename the new column bnack to the old column, REORG... Phew! I would hope you could stack the last two ALTERs but you cannot...

DROP   TABLE ROYTEST ;                           
COMMIT ;                                         
CREATE TABLE ROYTEST (COL1 CHAR(1));             
COMMIT ;                                         
ALTER TABLE ROYTEST                              
  ADD COLUMN COL2 CHAR(1) WITH DEFAULT NOT NULL ;
COMMIT ;                                         
ALTER TABLE ROYTEST                              
  DROP COLUMN COL1 RESTRICT                      
;                                                
ALTER TABLE ROYTEST                              
  RENAME COLUMN COL2 TO COL1                     
;                                                
COMMIT;                                          

You get a pending REORG at DROP COLUMN which must be done before you can do the RENAME...

Sounds like an AHA Idea !

Hello Db2 people, 

we had a request to change a column from NULL to NOT NULL, at the beginning I doubt it could be possible thanks to that the table was still empty, so no rows, no null value to deal with.

Anyway, after trying seems that it's not possible, althought the error message is not clear about the problem:

ALTER TABLE XXX.DOCU_TABLE_TEST

         ALTER COLUMN FILE_TOSTORE SET NOT NULL;

-------------------------------

DSNT408I SQLCODE = -199, ERROR:   ILLEGAL USE OF KEYWORD NOT. TOKEN WITH

                   DEFAULT DATA INLINE CACHE MAXVALUE MINVALUE NOCACHE WAS EXPECTED

Just in case we tried other things ... ALTER TABLE ... ALTER COLUMN ... WITH DEFAULT 'whatever", or ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT ... but no success.

So when you go back to the deloper that requested this it's not easy to clarify where was the problem. I like to reply specifying where the problem is, but also adding some links to IBM site and/or some quote from IBM where it be specified.

But I was not able to find such a link, in fact, checking the IBM site I even thought there could be a way:

So this made me doubt if there was a way to do it, probably this is the reason why I am posting this (insecurity ... :-) ) 

Anybody has a link for confirming this assumption?

Thanks,

      Sole

The most solid argument I found to confirm that is not possible to change a column from NULL to NOT NULL, was this update in 2021 from my admired late Robert Catterall: DB2 for z/OS: Answering Some Questions About Adding and Dropping Table Columns

Hello Roy, 

thanks for the reply and the testing! Now I am really 100% sure of it :-)

Yes, I think this can be requested as a AHA.

Will you do it or do you want me to do it?

Thanks again!

------------------------------
Soledad Martinez
Db2 z/OS System Programmer
Volkswagen Group Services
Salinas - Asturias

Hello Roy, 

thanks for the reply and the testing! Now I am really 100% sure of it :-)

Yes, I think this can be requested as a AHA.

Will you do it or do you want me to do it?

Thanks again!

------------------------------
Soledad Martinez
Db2 z/OS System Programmer
Volkswagen Group Services
Salinas - Asturias

I do not see this as particularly useful. If the table is already populated, it may contain NULL data, making the NOT NULL constraint impossible to apply. If the table is not populated, you can either DROP and CREATE the table or ADD and DROP the column.

I can do it and then I will post the z- number here...whcih is DB24ZOS-I-1773

Please vote!

Hello Roy, 

thanks for the reply and the testing! Now I am really 100% sure of it :-)

Yes, I think this can be requested as a AHA.

Will you do it or do you want me to do it?

Thanks again!

------------------------------
Soledad Martinez
Db2 z/OS System Programmer
Volkswagen Group Services
Salinas - Asturias

I do not see this as particularly useful. If the table is already populated, it may contain NULL data, making the NOT NULL constraint impossible to apply. If the table is not populated, you can either DROP and CREATE the table or ADD and DROP the column.

I can do it and then I will post the z- number here...whcih is DB24ZOS-I-1773

Please vote!

Hello Roy, 

thanks for the reply and the testing! Now I am really 100% sure of it :-)

Yes, I think this can be requested as a AHA.

Will you do it or do you want me to do it?

Thanks again!

------------------------------
Soledad Martinez
Db2 z/OS System Programmer
Volkswagen Group Services
Salinas - Asturias

but then you are adding complexity on the internal DDL change process, and there is already too much complexity in some cases, as simply defining a defer yes non unique index, for example. 

Hi!

 That depends on how you look at it! I know a ton of tables at various sites with NULLable columns where not a single row actually has a NULL value. It was simply "forgotten" at table design decades ago as most people do not read docu and do not see that NULLable is the default... I agree that it is a niche case but the minimum should be updated docu in the "restrictions on ALTER" to say -> ALTER COLUMN cannot be used to switch NULL / NOT NULL

I do not see this as particularly useful. If the table is already populated, it may contain NULL data, making the NOT NULL constraint impossible to apply. If the table is not populated, you can either DROP and CREATE the table or ADD and DROP the column.

I can do it and then I will post the z- number here...whcih is DB24ZOS-I-1773

Please vote!

Hello Roy, 

thanks for the reply and the testing! Now I am really 100% sure of it :-)

Yes, I think this can be requested as a AHA.

Will you do it or do you want me to do it?

Thanks again!

------------------------------
Soledad Martinez
Db2 z/OS System Programmer
Volkswagen Group Services
Salinas - Asturias

but then you are adding complexity on the internal DDL change process, and there is already too much complexity in some cases, as simply defining a defer yes non unique index, for example. 

Hi!

 That depends on how you look at it! I know a ton of tables at various sites with NULLable columns where not a single row actually has a NULL value. It was simply "forgotten" at table design decades ago as most people do not read docu and do not see that NULLable is the default... I agree that it is a niche case but the minimum should be updated docu in the "restrictions on ALTER" to say -> ALTER COLUMN cannot be used to switch NULL / NOT NULL

I do not see this as particularly useful. If the table is already populated, it may contain NULL data, making the NOT NULL constraint impossible to apply. If the table is not populated, you can either DROP and CREATE the table or ADD and DROP the column.

I can do it and then I will post the z- number here...whcih is DB24ZOS-I-1773

Please vote!

Hello Roy, 

thanks for the reply and the testing! Now I am really 100% sure of it :-)

Yes, I think this can be requested as a AHA.

Will you do it or do you want me to do it?

Thanks again!

------------------------------
Soledad Martinez
Db2 z/OS System Programmer
Volkswagen Group Services
Salinas - Asturias

Hello Roy, 

thanks for the reply and the testing! Now I am really 100% sure of it :-)

Yes, I think this can be requested as a AHA.

Will you do it or do you want me to do it?

Thanks again!

------------------------------
Soledad Martinez
Db2 z/OS System Programmer
Volkswagen Group Services
Salinas - Asturias