iSoftwerks.factory

When creating a table with SQL, a unique key may be created for the new table by using an identity column. This will generate a constraint when the table is built and automatically populate a value when records are inserted in the file. SQL server allows the value to be seeded, or provide a specific incremental value to use for the ID field.

IDENTITY - Specifies that the new column is an identity column. When a new row is added to the table, SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to a tinyint, smallint, int, decimal(p,0), or numeric(p,0) column. Only one identity column can be created per table. The DEFAULT keyword and bound defaults cannot be used with an identity column. Either both the seed and increment must be specified, or neither. If neither are specified, the default is (1,1).

Seed: the value that is used for the first row loaded into the table.) Increment: the incremental value that is added to the identity value of the previous row inserted.

  CREATE TABLE MYLIB.MYFILE ( 
  ID INTEGER GENERATED ALWAYS AS IDENTITY ( 
  START WITH 1 INCREMENT BY 1 
  NO MINVALUE NO MAXVALUE 
  NO CYCLE NO ORDER 
  CACHE 20), 
                              ; 

Managing the ID

It is convenient to allow the key to be generated automatically on a record insert. However, there is a drawback. On the IBM system I, when a table with an identity field is restored from one system to another (or one LPAR to another) the identity value may be reset to the initial value when the table object is restored. Subsequent attempts to insert a record in the file will fail ‘attempting to add duplicate record’, even though the data appears correct. The ID value may not be changed by simply changing the field. By clearing the table, the ID field will not be a problem (already at a value of 1) when attempting to insert new data. However, if you want to preserve the data in the restored file, ALTER TABLE may be used to reset the identity value to the last value on the file + 1.

ALTER TABLE RESCNF ALTER COLUMN ID RESTART WITH XXXX
Running the ALTER TABLE statement (where XXXX equals the last identity value on the file +1), will establish the correct ID for the next insert into the table.