Table Columns added as NOT NULL
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 replied
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.