Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    4

    Angry Unanswered: How to load this Table ?

    Hi ,

    I'm trying to load this table and getting error.

    Here is Table Def.

    ------------------------------------------------
    -- DDL Statements for table "COS "."TCOSI010"
    ------------------------------------------------

    CREATE TABLE "COS "."TCOSI010" (
    "POLICY_NBR" DECIMAL(8,0) NOT NULL GENERATED ALWAYS AS IDENTITY (
    START WITH +1
    INCREMENT BY +1
    MINVALUE +1
    MAXVALUE +99999999
    NO CYCLE
    CACHE 20
    NO ORDER ) )
    IN "SCOSI010" ;

    Here is the error I'm getting

    SQL3550W The field value in row "F0-1" and column "1" is not NULL, but the
    target column has been defined as GENERATED ALWAYS.

    thanks for looking in to this,

    Sudheer

    sudheerkumars@yahoo.com

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Sudheer,
    Using "GENERATE ALWAYS" means that only DB2 can supply the values for the column. They cannot come from outside. If you change it to "GENERATE BY DEFAULT" then you should get what you want.

    HTH

    Andy

  3. #3
    Join Date
    Apr 2006
    Posts
    4

    Smile 'modified by identityoverride' option works

    Thanks for your help.

    I tried following option and it worked.

    load from /db2backup/db2move/COS_TCOSI010exp.ixf of ixf modified by identityoverride
    replace into COS.TCOSI010;

    Thanks,

    Sudheer

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Sudheer,
    After you load the data, you will need to alter the starting value of the identity. Otherwise when you try to insert new rows and have DB2 supply the value, you will get duplicate errors. Use ALTER TABLE Mytable ALTER column MyIdentityCol RESTART WITH x. Wherre x is some value larger that one already there.

    Andy

  5. #5
    Join Date
    Apr 2006
    Posts
    4
    Thanks Andy,,,, Now it works perfect

  6. #6
    Join Date
    Apr 2006
    Posts
    2
    Dear friends
    please tell me how we update datatype in db2.
    write me query

  7. #7
    Join Date
    Apr 2006
    Posts
    2
    hello friends
    i want to know how i update column datatype in db2.
    and any diffrence from oracle regarding to this question

    i wait for reply from any boddy

  8. #8
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Please let us know the OS and DB2 vesion. Prior to version 7 you will not be able to change from one data type to another. In version 8 you can do it with some limitations.

Posting Permissions

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