CREATE SEQUENCE (Define sequence generator)
Function
CREATE SEQUENCE defines a sequence generator.
Privileges
Format
CREATE SEQUENCE [authorization-identifier.]sequence-generator-identifier |
Operands
Specifies that the sequence generator being generated is to be usable by all users.
If this is omitted, the sequence generator can only be used by the owner.
Specifies the name of the user RDAREA used to store the sequence generator.
An RDAREA using the inner replica facility cannot be specified for the RDAREA name.
If the RDAREA name is omitted, the RDAREA used to store the sequence generator is determined according to the following priority:
An option cannot be repeatedly specified in sequence-generator-option-column.
Specifies the data type of the value generated by a sequence generator.
INTEGER, SMALLINT, or DECIMAL of decimal scaling position 0 can be specified in the data type.
If this option is omitted, INTEGER is assumed.
Specifies an integer value to be used as the starting value for the sequence generator.
When the first NEXT VALUE expression is executed after a sequence generator is defined, the sequence generator returns the starting value.
Specify an integer value that is between the minimum and maximum values specified for the sequence generator in the sequence generator start option.
If this option is omitted, the minimum value specified for the sequence generator is assumed for an ascending sequence generator, and the maximum value specified for the sequence generator is assumed for a descending sequence generator.
For details about ascending and descending sequence generators, see sequence-generator-increment-option.
Specifies the value to be added when updating the value generated by the sequence generator (current value).
If the increment is positive, this is an ascending sequence generator, and if the increment is negative, this is a descending sequence generator.
Specify an integer other than 0 that satisfies the following conditions in sequence-generator-increment-option:
If this option is omitted, 1 is assumed.
Specifies the maximum value that can be generated by a sequence generator (sequence number).
When specifying the maximum value in sequence-generator-maxvalue-option, the following conditions must be satisfied:
If this option is omitted or NO MAXVALUE is specified, the maximum value of the sequence generator data type is assumed.
Specifies the minimum value that can be generated by the sequence number.
When specifying the minimum value in sequence-generator-minvalue-option, the following conditions must be satisfied:
If this option is omitted or NO MINVALUE is specified, the minimum value of the sequence generator data type is assumed.
Specifies whether to cycle the sequence numbers when the next value that would be generated by the sequence generator exceeds the maximum or minimum value. The operation when CYCLE is specified in sequence-generator-cycle-option are as follows:
Note that once the sequence generator is cycled, duplicate numbers will be generated.
If this option is omitted or NO CYCLE is specified, the operation is as follows:
Specifies the interval for outputting sequence generator logs.
Specifying a large output interval reduces the number of logs output, thereby increasing the processing performance. However, if a system failure occurs and 2 or more is specified for the output interval, there may be missing numbers in the sequence of numbers for up to the number of output intervals specified here. No missing numbers will occur if 1 is specified for the output interval or if this option is omitted.
The output interval has a range of 1 to 231-1 and must satisfy the following condition:
Output interval absolute value of (
(sequence generator maximum value - sequence generator minimum value)
increment
)
If this option is omitted, 1 is assumed.
For details about the sequence generator log output interval, see HiRDB Version 9 UAP Development Guide.
Common rules
Notes
Examples
A sequence generator (SEQ1) is defined using the following conditions:
CREATE SEQUENCE USER1.SEQ1
AS INTEGER
START WITH 1
INCREMENT BY 1
MAXVALUE 999
MINVALUE 1
NO CYCLE
LOG INTERVAL 20
IN RDA1