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?
