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