Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: generated column with load

    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?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why do you need to override the generated column values? I would let LOAD do the job by specifying "generatedmissing".

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i View Post
    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.

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about specifying column names explicitly?

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •