473,218 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

How to change a NOT NULL column to allow NULLs?

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
5 50519
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
3
by: A.M. de Jong | last post by:
Reading a lot about Nulls right now I still can't find a Technical reason to use it or not. For what I've understand is this: In an Ingres database a Null column has a standard extra storage...
10
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed...
3
by: Trevor Best | last post by:
Is there a *simple* way to change a collumn from allowing null to not null? I just unchecked "allow nulls" in EM and the SQL it generates to do this one thing is astonishing, create table, drop...
3
by: serge | last post by:
Someone was explaining me the following: 1- NULL values is NOT good for a column that has an index 2- Therefore, all columns have default values of : - 0 for numbers - " " for strings I...
3
by: binder | last post by:
I am designing a new table with a few columns that may or may not have a value on each row that is inserted. What issues determine whether to allow a NULL value to be inserted for that column or...
4
by: Eric Layman | last post by:
Hi everyone, Im puzzled by a NULL behaviour in SQL 2000 server. There is a column in the table that does not allow NULL. During data mining, the staff noted that, for that particular column,...
9
by: =?Utf-8?B?UGV0ZXJX?= | last post by:
I have a TabControl on a Windows form in which I have various tab pages each with a DataGridView, the first column of which is a DataGridViewCheckBoxColumn and subsequent columns being...
6
by: BobLewiston | last post by:
When I try to save a new (inserted) record in an SQL database, I get the following System.Runtime.InteropServices.ExternalException message: I have to either find out how to insert an...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.