加载中…db2 sequence 修改
(2013-12-17 16:11:43)
标签：
it |
分类： DB2 |
ALTER SEQUENCE statement
The ALTER SEQUENCE statement can be used to change a
sequence in any of these ways:
- Restarting the sequence
- Changing the increment between future sequence values
- Setting or eliminating the minimum or maximum values
- Changing the number of cached sequence numbers
- Changing the attribute that determines whether the sequence can cycle or not
- Changing whether sequence numbers must be generated in order of request
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Authorization
The privileges held by the authorization ID of the
statement must include at least one of the following:
- ALTER privilege on the sequence to be altered
- ALTERIN privilege on the schema implicitly or explicitly specified
- DBADM authority
Syntax
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/c.gifsequence 修改" />
>>-ALTER SEQUENCE--sequence-name-------------------------------->
.-----------------------------------------------.
V (1) |
>----------+-RESTART--+------------------------+-+-+-----------><
| '-WITH--numeric-constant-' |
+-INCREMENT BY--numeric-constant------+
+-+-MINVALUE--numeric-constant-+------+
| '-NO MINVALUE----------------' |
+-+-MAXVALUE--numeric-constant-+------+
| '-NO MAXVALUE----------------' |
+-+-CYCLE----+------------------------+
| '-NO CYCLE-' |
+-+-CACHE--integer-constant-+---------+
| '-NO CACHE----------------' |
'-+-ORDER----+------------------------'
'-NO ORDER-'
Notes:
- The same clause must not be specified more than once.
Description
- sequence-name
- Identifies the sequence that is to be changed. The name, including the implicit or explicit schema qualifier, must uniquely identify an existing sequence at the current server. If no sequence by this name exists in the explicitly or implicitly specified schema, an error (SQLSTATE 42704) is returned. sequence-name must not be a sequence generated by the system for an identity column (SQLSTATE 428FB).
- 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.
- WITH numeric-constant
- Restarts the sequence with the specified value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA).
- INCREMENT BY numeric-constant
- Specifies the interval between consecutive values
of the sequence. This value can be any positive or
negative value that could be assigned to a column of the data type
associated with the sequence (SQLSTATE 42815). The value must not
exceed the value of a large integer constant (SQLSTATE 42820) and
must not contain nonzero digits to the right of the decimal point
(SQLSTATE 428FA).
If this value is negative, then this is a descending sequence. If this value is 0 or positive, this is an ascending sequence after the ALTER statement.
- MINVALUE or NO MINVALUE
- 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.
- MINVALUE numeric-constant
- Specifies the numeric constant that is the minimum value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be less than or equal to the maximum value (SQLSTATE 42815).
- NO MINVALUE
- For an ascending sequence, the value is the original starting value. For a descending sequence, the value is the minimum value of the data type associated with the sequence.
- MAXVALUE or NO MAXVALUE
- 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.
- MAXVALUE numeric-constant
- Specifies the numeric constant that is the maximum value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be greater than or equal to the minimum value (SQLSTATE 42815).
- NO MAXVALUE
- For an ascending sequence, the value is the maximum value of the data type associated with the sequence. For a descending sequence, the value is the original starting value.
- CYCLE or NO CYCLE
- Specifies whether 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
the value.
- CYCLE
- Specifies that values continue to be generated
for this sequence after 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; or after
a descending sequence reaches its minimum value, it generates its
maximum value. The maximum and minimum values for the sequence
determine the range that is used for cycling.
When CYCLE is in effect, then duplicate values can be generated by DB2® for the sequence.
- NO CYCLE
- Specifies that values will not be generated for the sequence once the maximum or minimum value for the sequence has been reached.
- CACHE or NO CACHE
- Specifies whether to keep some preallocated
values in memory for faster access. This is a performance and
tuning option.
- CACHE integer-constant
- Specifies the maximum number of sequence values
that are preallocated and kept in memory. Preallocating and storing
values in the cache reduces synchronous I/O to the log when values
are generated for the sequence.
In the event of 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 in case of system failure.
The minimum value is 2 (SQLSTATE 42815).
- NO CACHE
- Specifies that values of the sequence are not to be preallocated. It ensures that there is not a loss of values in the case of a system failure, shutdown or database deactivation. When this option 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.
- ORDER or NO ORDER
- Specifies whether the sequence numbers must be
generated in order of request.
- ORDER
- Specifies that the sequence numbers are generated in order of request.
- NO ORDER
- Specifies that the sequence numbers do not need to be generated in order of request.
Notes
- Only future sequence numbers are affected by the ALTER SEQUENCE statement.
- The data type of a sequence cannot be changed. Instead, drop and recreate 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 to CYCLE, it is possible for sequence numbers to be duplicate values of ones generated by the sequence previously.
- Syntax alternatives: The following are
supported for compatibility with previous versions of DB2 and with
other database products. These alternatives are non-standard and
should not be used.
- A comma can be used to separate multiple sequence options.
- NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, and NOORDER can be specified in place of NO MINVALUE, NO MAXVALUE, NO CYCLE, NO CACHE, and NO ORDER, respectively
Examples
Example 1: A possible reason
for specifying RESTART without a numeric value would be to reset
the sequence to the START WITH value. In this example, the goal is
to generate the numbers from 1 up to the number of rows in the
table and then inserting the numbers into a column added to the
table using temporary tables. Another use would be to get results
back where all the resulting rows are numbered:
ALTER SEQUENCE ORG_SEQ RESTART
SELECT NEXT VALUE FOR ORG_SEQ, ORG.* FROM ORG
alter sequence seq_test restart with 1000;-- seq_test 从1000开始
alter sequence seq_test restart;-- seq_test 从seq_test创建的开始值重新计数
alter sequence seq_test increment by 1;-- 更改seq_test 的步长(每次变化的值)
alter sequence seq_test minvalue 10 ;-- 更改seq_test 的最小值 为10 alter sequence seq_test no minvalue; -- 更改seq_test 没有最小值
alter sequence seq_test maxvalue 10000 ;-- 更改seq_test 的最大值 为10000 alter sequence seq_test no minvalue; -- 更改seq_test 没有最大值
alter sequence seq_test no cycle ;-- seq_test 不循环计数
alter sequence seq_test cycle ;-- seq_test 在达到最大值后会重新开始计数
alter sequence seq_test cache 500 ;-- seq_test 每次缓存500个
alter sequence seq_test no cache ;-- seq_test 不使用cache
alter sequence seq_test order;-- seq_test sequence值顺序生成
alter sequence seq_test no order;-- seq_test sequence值不按顺序生成
喜欢
0
赠金笔
前一篇：db2 sequence 创建