SET GENERATOR
Go Up to Statement and Function Reference (Language Reference Guide)
Sets a new value for an existing generator. Available in gpre, DSQL, and isql.
SET GENERATOR name TO <int>;
- Important: In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in
isql, the semicolon is a terminating symbol for the statement, so it must be included.
| Argument | Description |
|---|---|
|
<name> |
Name of an existing generator |
|
<int> |
Value to which to set the generator, an integer from –263 to 263 – 1 |
Description: SET GENERATOR initializes a starting value for a newly created generator, or resets the value of an existing generator. A generator provides a unique, sequential numeric value through the GEN_ID() function. If a newly created generator is not initialized with SET GENERATOR, its starting value defaults to zero.
<int> is the new value for the generator. When the GEN_ID() function inserts or updates a value in a column, that value is <int> plus the increment specified in the GEN_ID()step parameter. Any value that can be stored in a DECIMAL(18,0) can be specified as the value in a SET GENERATOR statement.
Generators return a 64-bit value, and wrap around only after 264 invocations (assuming an increment of 1). Use an ISC-INT64 variable to hold the value returned by a generator.
- Tip: To force a generator’s first insertion value to 1, use SET GENERATOR to specify a starting value of 0, and set the step value of the GEN_ID() function to 1.
- Important: When resetting a generator that supplies values to a column defined with
PRIMARY KEYorUNIQUEintegrity constraints, be careful that the new value does not enable duplication of existing column values, or all subsequent insertions and updates will fail.
Example: The following isql statement sets a generator value to 1,000:
SET GENERATOR CUST_NO_GEN TO 1000;
If GEN_ID() now calls this generator with a step value of 1, the first number it returns is 1,001.