Adding a generated column

[Robert Stearns]

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(regnu m, 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;

[Serge Rielau]

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(regnu m, 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