ALTER SEQUENCE statement

Invocation for ALTER SEQUENCE

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for ALTER SEQUENCE

The privilege set that is defined below must include at least one of the following:

• Ownership of the sequence
• The ALTER privilege for the sequence
• The ALTERIN privilege on the schema
• SYSADM or SYSCTRL authority
• System DBADM
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

Installation SYSADM privilege is required to alter the SYSIBM.DSNSEQ_IMPLICITDB sequence (which specifies the maximum number of implicitly created databases).

The authorization ID that matches the schema name implicitly has the ALTERIN privilege on the schema.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package. If the statement is dynamically prepared, the privilege set is the union of the privilege sets that are held by each authorization ID and role of the process.

Description for ALTER SEQUENCE

sequence-name

Identifies the sequence. The combination of sequence name and the implicit or explicit qualifier must identify an existing sequence at the current server. sequence-name must not identify a sequence that is generated by Db2 for an identity column or a DB2_GENERATED_DOCID_FOR_XML column.

RESTART

Restarts the sequence. If numeric-constant is not specified, the sequence is restarted at the value specified implicitly or explicitly as the starting value on the CREATE SEQUENCE statement that originally created the sequence. RESTART does not change the original START WITH value.

WITH numeric-constant

Specifies the value at which to restart the sequence. The value can be any positive or negative value that could be assigned to the a column of the data type that is associated with the sequence without non-zero digits existing to the right of the decimal point. The range used for cycles is defined by MINVALUE and MAXVALUE. However, MAXVALUE and MINVALUE do not constrain the RESTART WITH numeric-constant value. That is, the RESTART WITH clause can be used to start the generation of values outside the range that is used for cycles. For more information, see Specifying RESTART WITH values outside the range for cycles.

If RESTART is not specified, the sequence is not restarted. Instead, it resumes with the current values in effect for all the options after the ALTER statement is issued.

After a sequence is restarted or changed to allow cycling, sequence numbers might be duplicates of values generated by the sequence previously.

INCREMENT BY numeric-constant

Specifies the interval between consecutive values of the sequence. The value can be any positive or negative value (including 0) that could be assigned to a column of the data type that is associated with the sequence without any non-zero digits existing to the right of the decimal point.

If INCREMENT BY numeric-constant is positive, the sequence ascends. If INCREMENT BY numeric-constant is negative, the sequence descends. If INCREMENT BY numeric-constant is 0, the sequence is treated as an ascending sequence.

The absolute value of INCREMENT BY can be greater than the difference between MAXVALUE and MINVALUE.

NO MINVALUE or MINVALUE

Specifies whether or not there is a minimum end point of the range of values for the sequence.

NO MINVALUE

Specifies that the minimum end point of the range of values for the sequence has not been specified explicitly. In such a case, the value for MINVALUE becomes one of the following:

• For an ascending sequence, the value is the original starting value.
• For a descending sequence, the value is the minimum of the data type that is associated with the sequence.

MINVALUE numeric-constant

Specifies the minimum value at which a descending sequence either cycles or stops generating values, or an ascending sequence cycles to after reaching the maximum value. The last value that is generated for a cycle of a descending sequence will be equal to or greater than this value. MINVALUE is the value to which an ascending sequence cycles to after reaching the maximum value.

The value can be any positive or negative value that could be assigned to the a column of the data type that is associated with the sequence without non-zero digits existing to the right of the decimal point. The value must be less than or equal to the maximum value.

NO MAXVALUE or MAXVALUE

Specifies whether or not there is a maximum end point of the range of values for the sequence.

NO MAXVALUE

Specifies either explicitly or implicitly that the minimum end point of the range of values for the sequence has not be set. In such a case, the default value for MAXVALUE becomes one of the following:

• For an ascending sequence, the value is the maximum value of the data type that is associated with the sequence
• For a descending sequence, the value is the original starting value.

If NO MAXVALUE is explicitly specified in the ALTER SEQUENCE statement, the value of the MAXVALUE column in the catalog table is reset to the maximum value of the data type associated with the sequence if the sequence is ascending or the value stored in the START column of the catalog table if the sequence is descending. Whether the sequence is ascending or descending depends on whether or not the INCREMENT BY option is reset. If it is, the new INCREMENT BY VALUE determines if the sequence is ascending or descending. If it is not explicitly reset, the value stored in the INCREMENT column of the catalog table determines if the sequence is ascending or descending.

MAXVALUE numeric-constant

Specifies the maximum value at which an ascending sequence either cycles or stops generating values or a descending sequence cycles to after reaching the minimum value. The last value that is generated for a cycle of an ascending sequence will be less than or equal to this value. MAXVALUE is the value to which a descending sequence cycles to after reaching the minimum value.

The value can be any positive or negative value that could be assigned to the a column of the data type that is associated with the sequence without non-zero digits existing to the right of the decimal point. The value must be greater than or equal to the minimum value.

NO CYCLE or CYCLE

Specifies whether or not the sequence should continue to generate values after reaching either its maximum or minimum value. The boundary of the sequence can be reached either with the next value landing exactly on the boundary condition or by overshooting it.

NO CYCLE

Specifies that the sequence cannot generate more values once the maximum or minimum value for the sequence has been reached.

CYCLE

Specifies that the sequence continue to generate values after either the maximum or minimum value has been reached. If this option is used, after an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum value, it generates its maximum value. The maximum and minimum values for the sequence defined by the MINVALUE and MAXVALUE options determine the range that is used for cycling.

When CYCLE is in effect, duplicate values can be generated by the sequence. When a sequence is defined with CYCLE, any application conversion tools for converting applications from other vendor platforms to Db2 should also explicitly specify MINVALUE, MAXVALUE, and START WITH values.

NO CACHE or CACHE

Specifies whether or not to keep some preallocated values in memory for faster access. This is a performance and tuning option.

NO CACHE

Specifies that values of the sequence are not to be preallocated. This option ensures that there is not a loss of values in the case of a system failure. When NO CACHE is specified, the values of the sequence are not stored in the cache. In this case, every request for a new value for the sequence results in synchronous I/O to the log.

CACHE integer-constant

Specifies the maximum number of sequence values that Db2 can preallocate and keep in memory. Preallocating values in the cache reduces synchronous I/O when values are generated for the sequence. The actual number of values that Db2 caches is always the lesser of the number in effect for the CACHE option and the number of remaining values within the logical range. Thus, the CACHE value is essentially an upper limit for the size of the cache.

In the event the system is shut down (either normally or through a system failure), all cached sequence values that have not been used in committed statements are lost (that is, they will never be used). The value specified for the CACHE option is the maximum number of sequence values that could be lost when the system is shut down.

The minimum value is 2.

In a data sharing environment, you can use the CACHE and NO ORDER options to allow multiple Db2 members to cache sequence values simultaneously.

NO ORDER or ORDER

Specifies whether the sequence numbers must be generated in order of request.

NO ORDER

Specifies that the sequence numbers do not need to be generated in order of request.

ORDER

Specifies that the sequence numbers are generated in order of request. Specifying ORDER might disable the caching of values. There is no guarantee that values are assigned in order across the entire server unless NO CACHE is also specified. ORDER applies only to a single-application process.

In a data sharing environment, if the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously, and the requests for next value assignments from different Db2 members might not result in the assignment of values in strict numeric order. For example, if members DB2A and DB2B are using the same sequence, and DB2A gets the cache values 1 to 20 and DB2B gets the cache values 21 to 40, the actual order of values assigned would be 1,21,2 if DB2A requested for next value first, then DB2B requested, and then DB2A again requested. Therefore, to guarantee that sequence numbers are generated in strict numeric order among multiple Db2 members using the same sequence concurrently, specify the ORDER option.

Notes for ALTER SEQUENCE

Altering a sequence

The changes to the attributes of a sequence take effect after the ALTER SEQUENCE statement is committed. Only future sequence numbers are affected by the ALTER SEQUENCE statement. If the ALTER SEQUENCE request results in an error or is rolled back, nothing is changed; however, unused cache values might be lost.

• The data type of a sequence cannot be changed. Instead, drop and re-create the sequence specifying the desired data type for the new sequence.
• All cached values are lost when a sequence is altered.
• After restarting a sequence or changing it to cycle, it is possible that a generated value will duplicate a value previously generated for that sequence.

Specifying RESTART WITH values outside the range for cycles

The RESTART WITH value is not constrained by the values of MINVALUE and MAXVALUE. That is, if the RESTART WITH value is greater than MAXVALUE, it has the following results:

• An ascending sequence generates the RESTART WITH value and then cycles to MINVALUE if CYCLE is in effect. If NO CYCLE is in effect, the sequence generates the RESTART WITH value one time, and the next attempt to generate a sequence value returns an error.
• A descending sequence generates the RESTART WITH value and then generates values according to the INCREMENT BY specification until it reaches MINVALUE. MAXVALUE does not constrain the generation of values for a descending sequence in this situation, so many values greater than MAXVALUE can potentially be generated.

Likewise, if the RESTART WITH value is less than MINVALUE, it has the following results:

• A descending sequence generates the RESTART WITH value and then cycles to MAXVALUE if CYCLE is in effect. If NO CYCLE is in effect, the sequence generates the RESTART WITH value one time, and the next attempt to generate a sequence value returns an error.
• An ascending sequence generates the RESTART WITH value and then generates values according to the INCREMENT BY specification until it reaches MAXVALUE. MINVALUE does not constrain the generation of values for an ascending sequence in this situation, so many values less than MINVALUE can potentially be generated.

Alternative syntax and synonyms

To provide compatibility with previous releases of Db2 or other products in the Db2 family, Db2 supports the following keywords:

• NOCACHE (single key word) as a synonym for NO CACHE
• NOCYCLE (single key word) as a synonym for NO CYCLE
• NOMINVALUE (single key word) as a synonym for NO MINVALUE
• NOMAXVALUE (single key word) as a synonym for NO MAXVALUE
• NOORDER (single key word) as a synonym for NO ORDER