By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,904 Members | 925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,904 IT Pros & Developers. It's quick & easy.

How to change a NOT NULL column to allow NULLs?

P: n/a
Either I missed something, or ALTER TABLE does not have this
capability. Is there any way of doing it except
DROPping all constraints which mention this table,
EXPORTing the data,
DROPping the table,
reCREATEing the table without the 'NOT NULL property,
reCREATEing the INDEXes,
reloading the data,
redefining all of DROPped constraints
reCREATE the view which were marked inactive by the above.

I think is all the steps, in order.

BTW, is there a way to reactivate a view after it has been marked
inactive? How about an SQL function or procedure?

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
ALTER TABLE does not seem to have this ability. A column that allows
NULL is one byte wider than a NOT NULL column, therefore you will need a
new page layout. So even if there was a command like ALTER TABLE...ALLOW
NULL, DB2 would have to do all the steps you described.

To reactivate the view, you have to CREATE it again, there is no other
way I am aware of.

PS: You will be faster if you load the data before creating the indexes.
Nov 12 '05 #2

P: n/a
DB2 V8.2 has a GUI tool that drived the process through, including
"reactivating" your views.
If you don't like GUI great care as been given in ensuring there is a
regular intreface. Take a look at the altobj() procedure.

Cheers
Serge
Nov 12 '05 #3

P: n/a
Changing a column from NOT NULL to nullable requires adding a "null
indicator" for the column in each existing row. This also changes the
row size! ALTER won't do this.

An alternative to export/import is to create a new, temporary, table to
hold the contents of your original one. Create it with the "NOT LOGGED
INITIALLY" clause and populate it with "INSERT INTO ... SELECT * FROM
....". This will avoid logging when populating the temporary table.

If you need to minimize the table's downtime; you can use the table copy
above, dropping the NOT NULL in the create. Lock the original table in
exclusive mode before you start then copy the table. After you drop the
original table; use the RENAME TABLE to change the name of the temporary
table. If you can't use RENAME TABLE because your tablespace setup won't
allow keeping two copies of the data then you'll have to do a second
table copy to get the data back where it belongs.

Your steps appear to be in a good order. Don't forget about triggers and
stored procedures that are dependent on the table.

Phil Sherman
Robert Stearns wrote:
Either I missed something, or ALTER TABLE does not have this
capability. Is there any way of doing it except
DROPping all constraints which mention this table,
EXPORTing the data,
DROPping the table,
reCREATEing the table without the 'NOT NULL property,
reCREATEing the INDEXes,
reloading the data,
redefining all of DROPped constraints
reCREATE the view which were marked inactive by the above.

I think is all the steps, in order.

BTW, is there a way to reactivate a view after it has been marked
inactive? How about an SQL function or procedure?


Nov 12 '05 #4

P: n/a
Is DB2 UDB v8.1.6 clever enough to have a swap tables which does exactly
that and no more? No deactivation of views, no requirement to drop
integrity constraints (they could be checked, if he felt it necessary),
etc.?

Philip Sherman wrote:
Changing a column from NOT NULL to nullable requires adding a "null
indicator" for the column in each existing row. This also changes the
row size! ALTER won't do this.

An alternative to export/import is to create a new, temporary, table to
hold the contents of your original one. Create it with the "NOT LOGGED
INITIALLY" clause and populate it with "INSERT INTO ... SELECT * FROM
...". This will avoid logging when populating the temporary table.

If you need to minimize the table's downtime; you can use the table copy
above, dropping the NOT NULL in the create. Lock the original table in
exclusive mode before you start then copy the table. After you drop the
original table; use the RENAME TABLE to change the name of the temporary
table. If you can't use RENAME TABLE because your tablespace setup won't
allow keeping two copies of the data then you'll have to do a second
table copy to get the data back where it belongs.

Your steps appear to be in a good order. Don't forget about triggers and
stored procedures that are dependent on the table.

Phil Sherman
Robert Stearns wrote:
Either I missed something, or ALTER TABLE does not have this
capability. Is there any way of doing it except
DROPping all constraints which mention this table,
EXPORTing the data,
DROPping the table,
reCREATEing the table without the 'NOT NULL property,
reCREATEing the INDEXes,
reloading the data,
redefining all of DROPped constraints
reCREATE the view which were marked inactive by the above.

I think is all the steps, in order.

BTW, is there a way to reactivate a view after it has been marked
inactive? How about an SQL function or procedure?


Nov 12 '05 #5

P: n/a
That's a great idea to add to a futures wish list. There's a lot of work
needed in the catalog to do a swap. Every one of the objects you've
mentioned has internal catalog relationships leading back to the table.
Performing a "table swap" would require concurrent modification of all
of these. At a minimum; this would lock up significant portions of the
catalog during the update and should also lock all dependent objects.
Many of today's shops, running web-initiated dynamic queries probably
wouldn't want the system-wide service interruption this would cause.

The existing mechanism for doing this forces the DBA to examine the full
impact of the change.

Database design principles expect that both current and future data
requirements are examined before defining physical attributes. Changing
a column from NOT NULL to nullable indicates that something was missed
in the original design. Nullable columns require additional programming
to process and require additional physical space to store. I've seen
many cases of a CHAR(1) column defined as nullable. This occurs even
when business rules allow a default, non-meaningful, value.

Variable length character data is another place where I've often raised
questions about nullable data. If you can specify that the length is
zero and limit the stored data to two bytes for the length field; why
define an additional byte for the nullable indicator? For a 100 row
table with 50% null values in a column; the null indicators will add 100
bytes to the data and will save 100 bytes of length fields. Every case
needs to be examined for alternatives and their impact.

Phil Sherman

Robert Stearns wrote:
Is DB2 UDB v8.1.6 clever enough to have a swap tables which does exactly
that and no more? No deactivation of views, no requirement to drop
integrity constraints (they could be checked, if he felt it necessary),
etc.?

Philip Sherman wrote:
Changing a column from NOT NULL to nullable requires adding a "null
indicator" for the column in each existing row. This also changes the
row size! ALTER won't do this.

An alternative to export/import is to create a new, temporary, table
to hold the contents of your original one. Create it with the "NOT
LOGGED INITIALLY" clause and populate it with "INSERT INTO ... SELECT
* FROM ...". This will avoid logging when populating the temporary table.

If you need to minimize the table's downtime; you can use the table
copy above, dropping the NOT NULL in the create. Lock the original
table in exclusive mode before you start then copy the table. After
you drop the original table; use the RENAME TABLE to change the name
of the temporary table. If you can't use RENAME TABLE because your
tablespace setup won't allow keeping two copies of the data then
you'll have to do a second table copy to get the data back where it
belongs.

Your steps appear to be in a good order. Don't forget about triggers
and stored procedures that are dependent on the table.

Phil Sherman
Robert Stearns wrote:
Either I missed something, or ALTER TABLE does not have this
capability. Is there any way of doing it except
DROPping all constraints which mention this table,
EXPORTing the data,
DROPping the table,
reCREATEing the table without the 'NOT NULL property,
reCREATEing the INDEXes,
reloading the data,
redefining all of DROPped constraints
reCREATE the view which were marked inactive by the above.

I think is all the steps, in order.

BTW, is there a way to reactivate a view after it has been marked
inactive? How about an SQL function or procedure?


Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.