Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Loading into table with generated identity column

    HI,

    I am trying to load data from 1 table to similar definition table both of them having 1 identity column defined as :

    "ILN_KEY" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (
    START WITH +5969613
    INCREMENT BY +1
    MINVALUE +5969613
    MAXVALUE +9223372036854775807
    NO CYCLE
    NO CACHE
    NO ORDER

    I am using a load from cursor. I have used all the file type mods like generatedoverride,ignor and missing but everytime I am getting the same error :

    SQL3526N The modifier clause "GENERATEDMISSING" is inconsistent with the
    current load command. Reason code: "3".

    Here are the cmds that I am using:

    db2 "declare loadcur cursor for select * from db2cods.A"
    db2 "load from loadcur of cursor modified by generatedmissing insert into db2cods.A_TEMP nonrecoverabl
    e"
    db2 "set integrity for db2cods.A_TEMP generated column immediate unchecked"
    db2 "select count(*) from db2cods.Awith ur"
    db2 "select count(*) from db2cods.A_TEMP with ur"

    Can anybody help me.

    Thanks,
    Anirban.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    try identity.... modifiers instead of generated.... modifier.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can also alter the table to set the indentity column to generated by default (depending on what version you have). I almost always use "generated by default" instead of "generated always".
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The other thing to do is remove the identity column from your load cursor and let DB2 do as you had already instructed and generate the identity number.
    Also, I completely agree with Marcus why would you use generated ALWAYS? This means you can never reload data you may have archived deleted/etc and keep the same number. You can't unload the entire contents from prod to load into a test system etc...
    Dave

Posting Permissions

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