CREATE SEQUENCE (Define sequence generator)

Function

CREATE SEQUENCE defines a sequence generator.

Privileges

Users who own a schema
These users can define a sequence generator in a private-use RDAREA with RDAREA usage privileges or in a public RDAREA.

Format

CREATE SEQUENCE [authorization-identifier.]sequence-generator-identifier
              [FOR PUBLIC USAGE]
              [sequence-generator-option-column]
              [IN sequence-generator-storage-RDAREA-name]

sequence-generator-option-column::=sequence-generator-option[sequence-generator-option]
sequence-generator-option::={sequence-generator-option-data-type-option
              |common-sequence-generator-option-column}
common-sequence-generator-option-column::=common-sequence-generator-option
              [common-sequence-generator-option]...
common-sequence-generator-option::={sequence-generator-start-option
              |common-sequence-generator-option}
basic-sequence-generator-option::={sequence-generator-increment-option
              |sequence-generator-maxvalue-option
              |sequence-generator-minvalue-option
              |sequence-generator-cycle-option
              |sequence-generator-log-output-interval-option

sequence-generator-data-type-option ::=AS data-type
sequence-generator-start-option ::=START WITH start-value
sequence-generator-increment-option ::=INCREMENT BY increment
sequence-generator-maxvalue-option  ::={MAXVALUE maxvalue|NO MAXVALUE}
sequence-generator-minvalue-option  ::={MINVALUE minvalue|NO MINVALUE}
sequence-generator-cycle-option  ::={CYCLE|NO CYCLE}
sequence-generator-log-output-interval-option ::=LOG INTERVAL output-interval

Operands

authorization-identifier
Specifies the authorization identifier of the user who is the owner of the sequence generator being defined.
If this is omitted, the authorization identifier of the executing user is assumed.
sequence-generator-identifier
Specifies the identifier of the sequence generator.

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:

  1. Public RDAREA with the smallest total number of defined tables and sequence generators from among the RDAREAs not using the inner replica facility.
  2. If there is more than one RDAREAs meeting condition 1, the first RDAREA found by HiRDB.
  3. An error occurs if an RDAREA cannot be determined using the above criteria.

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[Figure] absolute value of ([Figure](sequence generator maximum value - sequence generator minimum value) [Figure] increment[Figure])

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

  1. Up to 500 sequence generators can be defined together with a table in an RDAREA.
  2. The rules for generating sequence numbers are as follows:

Notes

  1. CREATE SEQUENCE cannot be specified from an X/Open-compliant UAP running under OLTP.

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