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

SET INTEGRITY question

P: n/a
aj
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
aj wrote:
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.

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
Nov 12 '05 #2

P: n/a
aj
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:
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

Nov 12 '05 #3

P: n/a
aj wrote:
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?

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
Nov 12 '05 #4

P: n/a
aj
Many thanks Serge. :)

aj

Serge Rielau wrote:
aj wrote:
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?


You are correct that you cannot ALTER nullability.
In V8.2 you can ADD/DROP/ALTER DEFAULT for existing columns directly.

Cheers
Serge

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.