Results 1 to 5 of 5

Thread: identity colums

  1. #1
    Join Date
    Apr 2004
    Posts
    36

    Exclamation Unanswered: identity colums

    V8.1 FP5
    Os : Sol 2.8


    I am migrating a db from one server to another.
    My database is having lot of identity colums tables.
    And these are generated alwasys.

    GENERATED ALWAYS AS IDENTITY (start with 0 etc).

    When i load the data , what are the things that i need to take care?

    I have loaded data with a regular LOAD statement without using "modified by identityignore"

    It got loaded . Is that a right process.

    Some where i read that we need to set counter after that.

    How do i do that?

  2. #2
    Join Date
    Apr 2004
    Posts
    36
    I am mistaken.

    Actually the columns are GENERATED BY DEFAULT AS IDENTITY.


    Not GENERATED ALWAYS.


    Do i have to start the count after loading the data.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should be fine with Genterated By Default.

    Use ALTER TABLE mytable alter MyColumn restart with x;

    HTH

    Andy

  4. #4
    Join Date
    Apr 2004
    Posts
    36
    AR ,

    Do i must restart the count.
    is n't that automatically take a new number , when you enter a next row.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    My assumptions:

    1) you created the table as Identity start at 0
    2) your Identity column is a primary key.

    When you load data and the Identity is generated by default, DB2 does NOT use up the values for the Identity column, they are being supplied by the load. THis is all perfectly legal, and there are numerous reasons why you want this behavior. The only drawback to this is that you can get duplicates for the Identity column, which will be rejected since it is a primary key. The workaround is to alter the starting value for the Identity to be larger than the largest value supplied in the load.

    With Generated by Default, the identity value is only generated when it is not supplied in the insert. If the data for the column is supplied, DB2 uses the value supplied, and does not use up the next values it would have used if the value was not supplied.

    HTH

    Andy

Posting Permissions

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