DB2 identity column Reset, Reset/restart

In DB2, you can create an identity column for a column in a table that automatically populates the values, and in some cases (such as after deleting the data, you need to start with the minimum value and not repeat it, you can reset the identity column)

Grammar:

1 ALTER TABLE < table name>ALTERcolumn<IDENTITYcolumn > 2        with <Last Counter value>

The official explanatory information is as follows:

modifying identity column DefinitionsProcess

If you are recreating the table and then performing an import or mount operation, and you have an identity column in the table, the table is reset after the contents of the table are recreated to begin generating IDENTITY values, starting with 1. When you insert a new row into this re-created table, you do not want the IDENTITY column to start again from 1. You do not want to have duplicate values in the IDENTITY column. To prevent this situation from occurring, you should:

1. Re-create the table.
2. Use the MODIFIED by identityoverride clause to load the data into the table. The data is loaded into the table but no identity values are generated for the row.
3. Run the query to get the last counter value of the IDENTITY column:

   This query returns the value of the IDENTITY column value of the table.

4. Use the RESTART clause of the ALTER TABLE statement:

      Alter TABLE <table name> alter COLUMN <identity column>       

5. Inserts a new row into the table. The IDENTITY column value is generated based on the value specified in the RESTART with clause