SQL DB GENERATED SEQUENCE

DB2 SQL DB GENERATED SEQUENCE : SEQUENCE OBJECT


SQL SEQUENCE OBJECT

  1. SEQUENCE OBJECT are similar to IDENTITY COLUMN in the way that they both generate unique values and use almost same attributes.
  2. However, sequences are objects are independent of any tables.
    • Sequences are not tied to a column in a table and are accessed separately.
    • Additionally, they are not treated as any part of a transaction’s unit of work.
    • A SEQUENCE OBJECT generates sequential values that can be used in any SQL statement.

  3. SQL use *DTAARA object to keep track of SEQUENCE OBJECT.

CREATE SEQUENCE statement

Syntax of the CREATE SEQUENCE statement.

CREATE [OR REPLACE] SEQUENCE sequence-name AS {data-type} 
{sequence-attributes}
data-type details
Default Other allowed values
INTEGER SMALLINT, INTEGER, BIGINT ,DECIMAL (with a scale of zero)
sequence-attributes details
Attribute Dafault Value Details
START WITH [numeric-constant] Tells SQL starting value of counter.
It can be positive or negative.
Unlike IDENTITY COLUMN There is no default value for this.
INCREMENT BY 1 [numeric-constant] This is the value counter should increment.
It can be positive or negative
MINVALUE NO MINVALUE [numeric-constant]It defines minimum side of value range It can be positive or negative
MAXVALUE NO MAXVALUE [numeric-constant]It defines maximum side of value range.It can be positive or negative
CYCLE NO CYCLE CYCLE means when IDENTITY COLUMN reached it’s range (MINVALUE or MAXVALUE) it will be reset to its MINVALUE and start counting again. NO CYCLE means not resetting and no recounting.
CACHE 20 It defines how many values for IDENTITY COLUMN system should keep in cache.Use NO CACHE if want not caching is required.
ORDER NO ORDER I am still looking what this means.

Here is sample create statement for

CREATE SEQUENCE mySequence AS SMALLINT
   START WITH 500
   INCREMENT BY 1
   MAXVALUE 1000
   CYCLE     
   CACHE 50 

ALTER SEQUENCE statement

Sequences can be altered in the following 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

Syntax of the ALTER SEQUENCE statement.

ALTER SEQUENCE sequence-name {sequence-alter-attributes}
sequence-alter-attributes
sequence-alter-attributes Attribute-value
RESTART [WITH numeric-constant] This is a very useful option to reset the counter to values specified at “START WITH” clause or to a given value.
INCREMENT BY numeric-constant
NO MINVALUE
MINVALUE numeric-constant
NO MAXVALUE
MAXVALUE numeric-constant
NO CYCLE
CYCLE
NO CACHE
CACHE numeric-constant
NO ORDER
ORDER

DROP SEQUENCE statement

Syntax for DROP SEQUENCE statement

DROP SEQUENCE sequence-name

Pages: 1 2


IBM i developer.

View Comments
There are currently no comments.