Table Columns added as NOT NULL

[Former Member]

I'm asking the question in this forum as I don't know if the answer will be database specific and we are on DB2 z/OS.

I added a column to a table and did not check the "Initial Values" box. As I understand it, if the "Initial Value" box is checked that will force NOT NULL for the column. As I did not check the box I expected the column to be added allowing NULL values (which is what we wanted) but the column in the database has the NOT NULL attribute.

Our production table has 200 million rows and we are concered about how long it will take for transport to complete as all rows will have to be updated with the default value.

Is there a reason it appears most columns are created as NOT NULL? Is there a way to add a column allowing NULL values?

Thank you for any input.

[bernhard_glaser · Accepted solution]

Hi Dana,

this is general SAP DDIC behaviour. If you are in change mode in SE11, please type NULL into the OK-Code and then maintain the new field. There will then be the option "Null Values Alllowed" in the pop-up window.

This will add the column allowing NULL values.

But also the ALTER TABLE ADD COLUMN with a column having a default value will not cause an update of the rows you just will have 2 versions of the rows until at some point in time you run a REORG to materialize this.

Regards

Bernhard

SAP

[Former Member · Accepted solution]

Hello Bernhard,

This is exactly what I need in order to solve my problems with fields being defined with NOT NULL in DB2, but I cannot get it to work.

I do not get the pop-up dialogue you describe even though I enter NULL in the OK-code / transaction field and press enter before I add a new field to the table.

Will the pop-up dialogue be shown upon saving the table or activating it?

Does the table  have to be of a special type?

Any help will be appreciated.

Thanks in advance.

Regards,

  Hans

[Former Member · Accepted solution]

Hello,

I found the answer:
In SE11: After entering NULL in the OK-code field you double click on the field name of the field to change.

Here you will find the option "NULL values allowed" in the "Database Options" section.

Best regards,

   Hans