Enter Default Values for all columns in all tables except Primary Keys

[serge]

How can i enter Default Values of " " to all the columns of type character
of all the tables (excluding system tables) and Default Values of 0
of all columns of type numbers. Excluding all primary key columns.

Thank you

[Serge Rielau]

serge wrote:

How can i enter Default Values of " " to all the columns of type character
of all the tables (excluding system tables) and Default Values of 0
of all columns of type numbers. Excluding all primary key columns.

Thank you

A little SQL procedure browsing SYSCAT.COLUMNS and firing an ALTER TABLE
... ALTER COLUMN .. SET DEFAULT should do it.
The ALTER TABLE statement needs to be glued together as a string and
then fired with EXECUTE IMMEDIATE.

Cheres
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[aj]

Hey Serge.

Re: the ALTER TABLE /. ALTER COLUMN .. SET DEFAULT you mention

I have DB2 WSE LUW v8.1 FP5
Can you give me an example of the SQL to do this? I want to set
a default value for *existing* columns in existing tables. I didn't
think I could do this in DB2 yet..???

Is this possible? How?

TIA
aj
Serge Rielau wrote:

serge wrote:

How can i enter Default Values of " " to all the columns of type
character
of all the tables (excluding system tables) and Default Values of 0
of all columns of type numbers. Excluding all primary key columns.

Thank you

A little SQL procedure browsing SYSCAT.COLUMNS and firing an ALTER TABLE
.. ALTER COLUMN .. SET DEFAULT should do it.
The ALTER TABLE statement needs to be glued together as a string and
then fired with EXECUTE IMMEDIATE.

Cheres
Serge

[Serge Rielau]

aj wrote:

Hey Serge.

Re: the ALTER TABLE /. ALTER COLUMN .. SET DEFAULT you mention

I have DB2 WSE LUW v8.1 FP5
Can you give me an example of the SQL to do this? I want to set
a default value for *existing* columns in existing tables. I didn't
think I could do this in DB2 yet..???

Is this possible? How?

DB2 V8.2 (FP7).
You can add/alter/drop:
* defaults,
* expression generated column property,
* identity column property

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

[Sean McKeough]

You're talking about changing the values from an alter add column?

You can't change the exists default other than by an update
statement...it's immutable once the column is added.

aj wrote:

Hey Serge.

Re: the ALTER TABLE /. ALTER COLUMN .. SET DEFAULT you mention

I have DB2 WSE LUW v8.1 FP5
Can you give me an example of the SQL to do this? I want to set
a default value for *existing* columns in existing tables. I didn't
think I could do this in DB2 yet..???

Is this possible? How?

TIA
aj
Serge Rielau wrote:

serge wrote:

How can i enter Default Values of " " to all the columns of type
character
of all the tables (excluding system tables) and Default Values of 0
of all columns of type numbers. Excluding all primary key columns.

Thank you

A little SQL procedure browsing SYSCAT.COLUMNS and firing an ALTER
TABLE .. ALTER COLUMN .. SET DEFAULT should do it.
The ALTER TABLE statement needs to be glued together as a string and
then fired with EXECUTE IMMEDIATE.

Cheres
Serge

[Serge Rielau]

Sean McKeough wrote:

You're talking about changing the values from an alter add column?

You can't change the exists default other than by an update
statement...it's immutable once the column is added.

Uh... lots of confusion. Especially since DB2 Dev. lingo is now in the mix.
Let me try to consolidate:

You cannot alter the DEFAULT property before DB2 V8.2.
You can alter the property in V8.2 and higher.
Now, changing the default property does not affect any existing values
in the table, even if they were added using the DEFAULT keyword.
So far so good and presumably uncontested.
Sean now is talking about the "exist default" for added columns.
That is, if a column is added to an existin table with existing rows,
then there must be some sort of default which is chosen for those
existing rows.
DB2 does not acually update the table when you add a column.
the ALTER TABLE returns instantanious (well, pretty much, ...)
Instead DB2 logs a separate "exist default" for those rows in the
descriptors for the table.
Whenever a row is updated the exist default will be materialized.
It is independent from the "external" semantics of changing teh default
for new rows.

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