In general, SETIDCOLA only works correctly for the most typical scenario where all values for identity columns have been generated by the system, and no cycles are allowed. In other scenarios, it may not restart the identity column at a useful value.
Limited support for unplanned switch - Following an unplanned switch, the backup system may not be caught up with all the changes that occurred on the production system. Using the SETIDCOLA command on the backup system may result in the generation of identity column values that were used on the production system but not yet replicated to the backup system. Careful selection of the value of the INCREMENTS parameter can minimize the likelihood of this problem, but the value chosen must be valid for all tables in the data group. See Examples of choosing a value for INCREMENTS .
Not supported -The following scenarios are known to be problematic and are not supported. If you cannot use the SETIDCOLA command in your environment, consider the Alternative solutions.
-
Columns that have cycled - If an identity column allows cycling and adding a row increments its value beyond the maximum range, the restart value is reset to the beginning of the range. Because cycles are allowed, the assumption is that duplicate keys will not be a problem. However, unexpected behavior may occur when cycles are allowed and old rows are removed from the table with a frequency such that the identity column values never actually complete a cycle. In this scenario, the ideal starting point would be wherever there is the largest gap between existing values. The SETIDCOLA command cannot address this scenario; it must be handled manually.
-
Rows deleted on production table - An application may require that an identity column value never be generated twice. For example, the value may be stored in a different table, data area or data queue, given to another application, or given to a customer. The application may also require that the value always locate either the original row or, if the row is deleted, no row at all. If rows with values at the end of the range are deleted and you perform a switch followed by the SETIDCOLA command, the identity column values of the deleted rows will be re-generated for newly inserted rows. The SETIDCOLA command is not recommended for this environment. This must be handled manually.
-
No rows in backup table - If there are no rows in the table on the backup system, the restart value will be set to the initial start value. Running the SETIDCOLA command on the backup system may result in re-generating values that were previously used. The SETIDCOLA command cannot address this scenario; it must be handled manually.
-
Application generated values - Optionally, applications can supply identity column values at the time they insert rows into a table. These application-generated identity values may be outside the minimum and maximum values set for the identity column. For example, a table’s identity column range may be from 1 through 100,000,000 but an application occasionally supplies values in the range of 200,000,000 through 500,000,000. If cycling is permitted and the SETIDCOLA command is run, the command would recognize the higher values from the application and would cycle back to the minimum value of 1. Because the result would be problematic, the SETIDCOLA command is not recommended for tables which allow application-generated identity values. This must be handled manually.