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.

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.

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.

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.

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

[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....

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,

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-)