How to change a NOT NULL column to allow NULLs?

[Robert Stearns]

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?

[Martin Neumann]

Re: How to change a NOT NULL column to allow NULLs?

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.

[Serge Rielau]

Re: How to change a NOT NULL column to allow NULLs?

DB2 V8.2 has a GUI tool that drived the process through, including
"reactivati ng" 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

[Philip Sherman]

Re: How to change a NOT NULL column to allow NULLs?

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:[color=blue]
> 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?
>[/color]

[Robert Stearns]

Re: How to change a NOT NULL column to allow NULLs?

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:[color=blue]
> 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:
>[color=green]
>> 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?
>>[/color]
>[/color]

[Philip Sherman]

Re: How to change a NOT NULL column to allow NULLs?

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:[color=blue]
> 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:
>[color=green]
>> 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:
>>[color=darkred]
>>> 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?
>>>[/color]
>>[/color]
>[/color]