drop table orig0_table;
create table orig0_table
attrib#1 int not null generated always as identity primary key
, attrib#2 date not null default current_date
, attrib#3 date not null
, attrib#4 date generated always as (case when year(attrib#2) < 1980
then attrib#2 + 1 year
else attrib#3 - 3 months
insert into orig0_table (attrib#2 , attrib#3) values ('1959-04-26' , '1985-09-22' );
insert into orig0_table (attrib#2 , attrib#3) values ('1985-09-22' , current_date );
insert into orig0_table (attrib#2 , attrib#3) values ('2000-01-01' , '2112-12-21' );
insert into orig0_table (attrib#3) values ( '1111-11-11');
insert into orig0_table (attrib#3) values ( '1212-12-12');
select * from orig0_table;
drop table copy1_table;
create table copy1_table
declare x1 cursor for select * from orig0_table ;
load from x1 of cursor insert into copy1_table nonrecoverable ;
insert into copy1_table (attrib#3) values ( '111-11-11');
insert into copy1_table (attrib#3) values ( current_date);
select * from copy1_table;
Try for yourself and see whay happens.... The inserts in copy1_table are not executed. Why?
The "generate always" logic did not survive the "create table A like B" syntax. Okay, it was a nonsence rule, but DB2 accepted it for the orig0_table, didn't it?
attrib#4 is not a generated field anymore but a "normal" column which did remember the "not null" property.
Right: when you dig deep into the documentation I do not doubt that this is "expected behaviour" so I will not issue a PMR, but I do not LIKE this LIKE syntax which forgets parameters without any warning
You all know this already, but when there is only 1 soul out there who did not realize this (like me): this thread is for you.