SQL DB GENERATED SEQUENCE

DB2 SQL DB GENERATED SEQUENCE : IDENTITY COLUMN


IDENTITY COLUMN

  1. An IDENTITY COLUMN is a numeric column defined in a table for which the column values can be generated automatically by DB2.
  2. Only columns of type SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC are allowed for identity columns.
  3. Maximum one identity column per table is allowed
  4. IDENTITY COLUMN is implicitly NOT NULL.


A new IDENTITY COLUMN can be added in table using

  1. CREATE TABLE Statement.
     CREATE TABLE INVOICE
    (invoice_number INTEGER 
                    GENERATED ALWAYS AS IDENTITY
    		  (START WITH 100001
    		     INCREMENT BY 1
                       CYCLE),
    order_number char(20))

    Here is complete syntax to add IDENTITY column

    
    column-name data-type GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY 
    (identity-attributes)
    
    ALWAYS SQL will ALWAYS generate a unique value for every INSERT statement. In this User defined value are not allowed.
    BY DEFAULT If value for IDENTITY COLUMN if given by user that value will be used else SQL will generate a unique value for it

    Identity Attributes details

    Attribute Dafault Value Details
    START WITH 1 [numeric-constant] Tells SQL starting value of counter.
    It can be positive or negative
    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.
  2. ALTER TABLE Statement
    1. Changing existing column to an identity column is not allowed.
    2. But can change existing identity column’s Attributes
    3. Also can add a new column of type identity column

    ALTER Table to add a new IDENTITY COLUMN.

    ALTER TABLE  ORDERS  ADD COLUMN             
     order_counter_number INTEGER NOT NULL      
                    GENERATED ALWAYS AS IDENTITY
                     (START with 1              
                      increment by 1) 

    ALTER Table to add change existing identity column’s Attributes.

    ALTER TABLE INVOICE ALTER COLUMN invoice_number 
    SET INCREMENT BY 3

    Here is complete syntax of ALTER TABLE statement to change Attributes of a IDENTITY COLUMN.

                                       
    ALTER TABLE table-name ALTER  COLUMN  
    {SET Identity-Attribute [Attribute-value] | RESTART WITH numeric-constant}
     

    Details for Identity-Attribute and Attribute-value

    Identity-Attribute Attribute-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

    Details on RESTART WITH

    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.
    ALTER TABLE INVOICE ALTER COLUMN invoice_number RESTART 
    ALTER TABLE INVOICE ALTER COLUMN invoice_number RESTART WITH 10
    

Pages: 1 2 3


IBM i developer.

View Comments
There are currently no comments.