Skip to Content
author's profile photo Former Member
Former Member

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on May 26, 2011 at 10:47 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.