generated column with load

dr_te_z · #1 (**permalink**) 09-09-10, 08:45

Getting very confused:

db2 v9.5 on win2003

I've created a table with a generated column based upon another NOT NULL column.
the new generated column is part of 1 or more indexes
the new table is populated by means of a load
I'm on a time-critical path, so I would not like time-consuming "set integrity's" after the load has finished

simplyfied example:

Code:

create table nw 
 ( nw_id int not null
 , nw_ts timestamp not null
 , nw_gen  int generated always as ((year(nw_ts) * 100) + month(nw_ts))
 );
create index xx on table nw (nw_id asc , nw_gen asc);
declare x1 cursor for select a , b from another_table;
load from x1 of cursor modified by ???? insert into nw;

So I concider:
- load with "modified by generatedmissing"
- load with "modified by generatedignore"
- load with "modified by generatedoverride"

I cannot work out what is the best option for me to use. It could be my bad google-skills, or my bad english or my limited intelligence or the "set integrity" can never by avoided.

Who knows the best syntax to use in this case?

n_i · #2 (**permalink**) 09-09-10, 10:19

Generated column load considerations - IBM DB2 9.7 for Linux, UNIX, and Windows

dr_te_z · #3 (**permalink**) 09-09-10, 12:26

See if I understand:

Code:

load with "modified by generatedoverride"
SET INTEGRITY FOR  nw GENERATED COLUMN IMMEDIATE UNCHECKED

The "set integrity" will not take long to process and the table is ready for use and the index is properly built. Correct?

n_i · #4 (**permalink**) 09-09-10, 12:59

Why do you need to override the generated column values? I would let LOAD do the job by specifying "generatedmissing".

dr_te_z · #5 (**permalink**) 09-09-10, 13:30

Quote:

Originally Posted by n_i

Why do you need to override the generated column values? I would let LOAD do the job by specifying "generatedmissing".

That was my iniital idea, but then the table was left in "integrity pending state" and was trying to avoid that.
Can I use the "generated missing" in combination with "inmediate unchecked"?
This area is the most "blurry part of db2" to me.

dr_te_z · #6 (**permalink**) 09-10-10, 09:48

Does not work

The

Code:

set integrity for ... check immediate unchecked

does not take away the

Code:

SQL0668N  Operation not allowed for reason code "1" on table 
...  SQLSTATE=57016

ERGO: "set integrity for ... immediate checked" can NOT be avoided

tonkuma · #7 (**permalink**) 09-10-10, 14:03

How about specifying column names explicitly?

Code:

load from x1 of cursor METHOD P(1 , 2) insert into nw(nw_id , nw_ts);

Internet Services

Web Development

Digital Marketing

Consumer Tech