Introduction and use of DB2 Sequence

Introduction to and use of DB2 sequence 1. Creating sequence sequences is a numerical value produced according to certain rules. sequences play a very large role, such as sequential numbers in bank transactions, it is the key field that records each transaction. Use the create sequence statement to CREATE a SEQUENCE. The syntax is as follows:>-create -- + ------------ + -- sequence-name -- -----------> '-or replace -'. -as integer --------.> -- + ------------------- + -- + ------------------------------ + ---> '-AS -- | data-type |-''-start with -- numeric-constant -'. -increment by 1 ---------------.> -- + ---------------------------------- + -- ---------------------> '-increment by -- numeric-constant -'. -NO MINVA LUE --------------.> -- + ---------------------------- + -- ------------------------------> '-MINVALUE -- numeric-constant -'. -no maxvalue ----------------.. -no cycle-.> -- + -------------------------- + -- + ---------- + -- -------------> '-MAXVALUE -- numeric-constant-''-CYCLE ----'. -CACHE 20 ----------------.. -no order-.> -- + ------------------------- + -- + ---------- + -- -------------> <+-CACHE -- integer-cons Tant-+ '-ORDER ---- ''-no cache ----------------' simplified write as follows: create sequence seq_namestart with aincrement by bminvalue cmaxvalue dno cyclecache eorder parameter Introduction: or replace: if a sequence with duplicate names is created, it will be replaced (deleted first, then created) as datatype: Specifies the data type. Here, the sequence data type can only be numeric, for example, smallint, integer, bigint, decimal type without decimal point. Start with: Specifies the starting value of the sequence. By default, the ascending sequence is the minimum value of the currently specified data type, and the descending sequence is the maximum value of the currently specified type. Increment by: Specifies the value of growth. The default value is 1. A positive number indicates that the sequence is ascending, and a negative number indicates that the sequence is descending. Minvalue: Minimum value. If there is no cycle in the descending sequence, the sequence value will not be generated when this value is reached; no minvalue: For ascending sequence, this value is the value of start with. If the value of start with is not specified, it is 1. for a descending sequence, this value is the minimum value of the specified data type. Maxvalue: specifies the maximum value of the production sequence, no maxvalue: For the ascending sequence, this value is the maximum value of the specified data type; For the descending sequence, this value is the start with value, if the start with value is not specified, it is-1. cycle: uses data values cyclically. For ascending sequence, when the maximum value is reached, the next value will be its minimum value. For descending sequence, when the minimum value is reached, the next sequence value is its maximum value. No cycle: When the sequence boundary value is reached, the sequence value is no longer generated. The default option is. Cache: cache Sequence Value, indicating the sequence value that is pre-produced and stored in memory each time this sequence is applied. This effectively reduces the I/O operations for log writing. If a system error occurs during use, all these cache values will be lost. The minimum value is 2 and the default value is 20no cache. When this option is specified, no sequence value is stored in the memory, and no exception occurs, each time a new sequence value is generated, the I/O operation of the log is performed. Order: generate values in the order of requests. No order: the value is not generated according to the request order. Default Value. The following code creates a sequence seq_001: create sequence seq_001 start with 1 increment by 1 no maxvalue no cycle cache 21DB20000I SQL command. View the sequence seq_001 stored in the cataloguing table: select seqname, START, INCREMENT, MINVALUE, MAXVALUE, CYCLE, CACHE, order from syscat. sequences where seqname = 'seq _ 001' seqname start increment minvalue maxvalue cycle cache order --------- ---------- ----------- ---------- ------------ ------- -------- SEQ_001 1. 1. 1. 2147483647. N 21 N 1 records selected. Note: 1. Constant sequence, that is, a sequence that does not change its generated value. During creation, the specified growth value is 0, that is, increment by 0, but the start with value cannot exceed the maximum and minimum values of its data type. Another method is to specify the start with value and minvalue value, the value is equal to that of maxvalue. 2. When the sequence is defined as no cycle, you can use alter sequence to change its attributes so that it can continue to generate sequence values after reaching the boundary value, that is, change no cycle to cycle3. Specify cycle when defining a sequence. Except for increment by 1 or-1, the maximum value generated by the sequence is not the maximum value of the specified data type. For example, if a sequence is defined as start with = 1, increment = 2, and maxvalue = 10, the maximum value that can be generated is 9, instead of 10.4, the sequence definer has the alter and usage privileges of the sequence (with grant option, which can be granted to other users), and the sequence owner can delete the sequence. 5. The following two points are used with all DB2 database versions. Non-standard information: 1 ). A comma can be used to separate multiple sequence options 2 ). novinvalue, nomaxvalue, nocycle, nocache, noorder can replace no minvalue, no maxvalue, no cycle, no cache, no order. Ii. Modify the SEQUENCE syntax as follows:>-ALTER sequence -- SEQUENCE-name ------------------------------>. -----------------------------------------------. V (1) |> ---------- +-RESTART -- + ---------------------- +-+ -----------> <| '-WITH -- numeric-constant-' | +-increment by -- numeric-constant ------ + +- +-MINVALUE -- numeric-constant-+ ------ + | '-no minvalue ----------------' | +-MAXVALUE -- numeric-constant-+ ------ + | '-NO MAXVAL UE ---------------- '| +-CYCLE ---- + ------------------------ + |'-no cycle-'| +-CACHE -- integer-constant-+ --------- + |'-no cache ---------------- '|'-+-ORDER ---- + ---------------------- '-no order-' parameter introduction: restart: resets the sequence. If with n is not specified, the sequence will generate a value based on the parameter specified during create sequence. With: reset the sequence, and generate the sequence value according to the re-specified value. This can make any value. Other parameters are the same as those in create sequence. We can see from the syntax that the attributes that can be modified are as follows: 1. The actual sequence value (or reset) 2. increment value 3. maximum and minimum values 4. cache value 5. When the boundary value is reached, whether the sequence value is generated cyclically 6. Whether the sequence value is generated in the Request order. Note: 1. The data type of the sequence cannot be modified. to modify the sequence, you can only delete the current sequence and specify the desired data type during reconstruction. 2. When modified, all cache values will be lost. 3. When the sequence is changed to cycle, the sequence will generate duplicate values. The following is an example: alter sequence seq_001 increment by 2 maxvalue 20 cycle no cacheDB20000I SQL command is successfully completed. Select seqname, START, INCREMENT, MINVALUE, MAXVALUE, CYCLE, CACHE, order from syscat. sequences where seqname = 'seq _ 001' seqname start increment minvalue maxvalue cycle cache order --------- ---------- ----------- ---------- ------------ ------- -------- SEQ_001 1. 2. 1. 20. Y 1 N 1 records selected. We can see that no cache is actually cache 1. 3. Sequence values can be obtained through two expressions. next value gets the next value and previous value gets the current value. To maintain backward compatibility with DB2, you can also use nextval and prevval to obtain the next and current sequence values. When a sequence is referenced for the first time, only the next value can be used to obtain its first value; otherwise, the following error message will be obtained: db2 => insert into test1 (cid, cname, sex, age) values (previous value for seq_001, 'Scott ', 'M', 30) DB21034E this command is processed as an SQL statement, because it is an invalid "command line processor" command. During SQL processing, it returns SQL0845N. The PREVIOUS VALUE expression cannot be used before the NEXT VALUE expression generates a VALUE in the current session of the sequence "SEQID = 7. SQLSTATE = 51035 db2 => insert into test1 (cid, cname, sex, age) values (next value for seq_001, 'Scott ', 'M', 30) the DB20000I SQL command is successfully completed. Db2 => select * from test1 cid cname sex age ---------- -------------------- --- ----------- 1 scott M 30 1 record selected. The next value and previous value can be used in select, values, insert, or update statements, but cannot be used in where statements. -- The end --