DB2 v8 FP5 WSE LUW
Red Hat Linux 2.1AS

I'm like to add a new column to an existing table that will be
NOT NULL and have a WITH DEFAULT value.

The table has a huge assortment of triggers, views, FK's, and
stored procs against it, so I don't want to take the usual steps
of creating a new table with the same structure plus the new
column, then copying all the data over and renaming the table.
(I would have to identify all the dependencies, drop them, do
the copy+rename, then recreate them).

Can I just SET INTEGRITY OFF for the table, add the NOT NULL WITH
DEFAULT column, populate the column, then SET INTEGRITY ON?

Any advice appreciated.

aj

aj wrote:[color=blue]
> DB2 v8 FP5 WSE LUW
> Red Hat Linux 2.1AS
>
> I'm like to add a new column to an existing table that will be
> NOT NULL and have a WITH DEFAULT value.
>
> The table has a huge assortment of triggers, views, FK's, and
> stored procs against it, so I don't want to take the usual steps
> of creating a new table with the same structure plus the new
> column, then copying all the data over and renaming the table.
> (I would have to identify all the dependencies, drop them, do
> the copy+rename, then recreate them).
>
> Can I just SET INTEGRITY OFF for the table, add the NOT NULL WITH
> DEFAULT column, populate the column, then SET INTEGRITY ON?
>
> Any advice appreciated.[/color]
ALTER TABLE T ADD COLUMN c1 INT NOT NULL WITH DEFAULT;
.... done... no SET INTEGRITY, no going for coffee, no worries...
DB2 for LUW had online schema evolution for this scenario since it's
inception.

Cheers
Serge



--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Well.......don't I feel like an idiot. I guess I can do that
with a simple:
alter table x add column1 int not null default 0

I *think* I discovered at one point that I could not add NOT NULLness
or WITH DEFAULT to an *existing* column (correct me if I'm wrong), so
I assumed I would see the same thing when adding a new column...

Can someone set me straight here?

TIA

aj

aj wrote:
[color=blue]
> DB2 v8 FP5 WSE LUW
> Red Hat Linux 2.1AS
>
> I'm like to add a new column to an existing table that will be
> NOT NULL and have a WITH DEFAULT value.
>
> The table has a huge assortment of triggers, views, FK's, and
> stored procs against it, so I don't want to take the usual steps
> of creating a new table with the same structure plus the new
> column, then copying all the data over and renaming the table.
> (I would have to identify all the dependencies, drop them, do
> the copy+rename, then recreate them).
>
> Can I just SET INTEGRITY OFF for the table, add the NOT NULL WITH
> DEFAULT column, populate the column, then SET INTEGRITY ON?
>
> Any advice appreciated.
>
> aj
>
>[/color]

aj wrote:[color=blue]
> Well.......don't I feel like an idiot. I guess I can do that
> with a simple:
> alter table x add column1 int not null default 0
>
> I *think* I discovered at one point that I could not add NOT NULLness
> or WITH DEFAULT to an *existing* column (correct me if I'm wrong), so
> I assumed I would see the same thing when adding a new column...
>
> Can someone set me straight here?[/color]
You are correct that you cannot ALTER nullability.
In V8.2 you can ADD/DROP/ALTER DEFAULT for existing columns directly.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Many thanks Serge. :)

aj

Serge Rielau wrote:
[color=blue]
> aj wrote:
>[color=green]
>> Well.......don't I feel like an idiot. I guess I can do that
>> with a simple:
>> alter table x add column1 int not null default 0
>>
>> I *think* I discovered at one point that I could not add NOT NULLness
>> or WITH DEFAULT to an *existing* column (correct me if I'm wrong), so
>> I assumed I would see the same thing when adding a new column...
>>
>> Can someone set me straight here?[/color]
>
> You are correct that you cannot ALTER nullability.
> In V8.2 you can ADD/DROP/ALTER DEFAULT for existing columns directly.
>
> Cheers
> Serge[/color]