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

Adding a generated column

P: n/a
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
Share this Question
Share on Google+
1 Reply


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