Results 1 to 8 of 8

Thread: Identity Column

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: Identity Column

    Hi,

    I am using DB2 v7.2 on win2k.
    I have a PK column with identity property set and the table contains 100 rows. If I were to export these 100 rows and import it into another database, then will the identity column start generating/numbering from 101 or 1?

    If it starts from 1, then is there any way to alter an identity column to start from 101.

    Please help.

    Many thanks in advance.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure if you are importing to an existing table, or trying to create the table with the import (IXF). But in any case you can ALTER a table with "SET GENERATED RESTART WITH numeric-constant" to start at 101 if you need to do that.

  3. #3
    Join Date
    Sep 2003
    Posts
    218

    Identity Column

    Hi Marcus

    Many thanks! for your reply.

    Actually, I am creating the table structure using .ixf file into target db location.

    I will surely try out what you suggested and come back in case of further clarification. Thank you once again.

  4. #4
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Identity Column.

    Hi Suresh,

    Please go through the below article, it explains the usage of identity columns with details and examples.

    http://www7b.software.ibm.com/dmdd/l...2fielding.html

    Cheers!

    Nitin.

  5. #5
    Join Date
    Sep 2003
    Posts
    85
    If the target table that will get the exported data has GENERATED AS ALWAYS, then the IMPORT/LOAD would fail since one is supplying values for IDENTITY column. I would recommend defining GENERATED BY DEFAULT. So you could export/import to the target table and then any new inserts would use the values specified by the INDENTITY definitions. You would, as stated above, have to ALTER TABLE such that the IDENTITY column would start with the max value + whatever is desired.
    HTH,
    Ruby

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [mournful noises]
    Sad was the day that DB2 went down the IDENTITY route....
    [mournful noises]

    Think it was in OS/390 V6....

    Or maybe it's different across platforms....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Ruby, I believe that the data and the table definition were being imported all at the same time with IXF format. However I don't know if IXF format retains INDENTY DDL attributes on table creation with IXF.

    I assume (but don't know for sure) that IDENTITY is ANSI SQL, which is why IBM would go down that path. I am sure that customers asked for it also,

  8. #8
    Join Date
    Sep 2003
    Posts
    85
    If one didn't have the target table defined and then imported such that the DDL was used, then I would surmise that all should be well. However, I still think that the IDENTITY column starting point wuold have to be reset to the max identity column value + whatever increment.
    8-)

Posting Permissions

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