467,104 Members | 1,054 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,104 developers. It's quick & easy.

Adding a generated column

I have two related questions. Why did I have to:

SET INTEGRITY FOR is3.animals OFF;

before doing:

alter table is3.animals
add column pseudo_id
generated always as
(coalesce(regnum, cast(bhid as char(10))));

It may be of importance that bhid is the primary key of animals.

Why are other <table>s which are dependent on animals, as a foreign key,
also require:

SET INTEGRITY FOR is3.animals IMMEDIATE CHECKED FORCE GENERATED;
SET INTEGRITY FOR is3.<table> IMMEDIATE CHECKED FORCE GENERATED;

Nov 12 '05 #1
  • viewed: 4348
Share:
1 Reply
Robert Stearns wrote:
I have two related questions. Why did I have to:

SET INTEGRITY FOR is3.animals OFF;

before doing:

alter table is3.animals
add column pseudo_id
generated always as
(coalesce(regnum, cast(bhid as char(10))));

It may be of importance that bhid is the primary key of animals If integrity for the table would not be turned off DB2 would need to do
the FORCE GENERATED processing right away in the ALTER TABLE statement.
If your table is of non trivial size this can mean a lot of logging.
Now - when expression-generated columns were added to DB2 log space was
fairly limited and we (DB2 Dev) felt that there is significant risk of
folks running out of log-space.
So we gave customers two options:
Use SET INTEGRITY to generate the values, or
use a little tool call db2gncol which generates the values using a
commit count. (and then use SET INTEGRITY to re-enable the table without
having to do FORCE GENERATED.
Now that was the past. Using SET INTEGRITY is still usefull however in
case you also want to do other changes to the table. You can batch them
up uf do all the work in one shot.
One could argue DB2 should now allow ALTER TABLE to do the whole work,
but so far it simply hasn't been more than a "yeah.. someday we'll fix
that".

Why are other <table>s which are dependent on animals, as a foreign key,
also require:

SET INTEGRITY FOR is3.animals IMMEDIATE CHECKED FORCE GENERATED;
SET INTEGRITY FOR is3.<table> IMMEDIATE CHECKED FORCE GENERATED;

If you didn't do more than add the geneated column to animals you can
take the <table> out of check pending
UNCHECKED. For sure you do NOT need FORCE GENERATED on <table>.

Cheers
Serge

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Chris Millar | last post: by
reply views Thread by Andrew | last post: by
reply views Thread by Sam Vanderstraeten | last post: by
16 posts views Thread by Geoff Jones | last post: by
7 posts views Thread by Miro | last post: by
3 posts views Thread by triumph | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.