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>;
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.
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.
When resetting a generator that supplies values to a column defined with
PRIMARY KEY or UNIQUE integrity 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.