DB2 SQL DB GENERATED SEQUENCE

Let say you add a column ID in your table, and you want an incremented value for this column every time a new record is inserted in Table, or later you want to re-sequence the same ID column starting from 1.

There can be multiple tricks to achieve this, but SQL provides multiple straightforward and handy option to do all these tasks where a sequence of numbers need to generate.

1. IDENTITY COLUMN
2. ROWID
3. SEQUENCE OBJECT
4. GENERATE_UNIQUE function

1. 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 to the table

1. using 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 the complete syntax to add the IDENTITY column

column-name data-type GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY 
(identity-attributes)

Identity Attributes details

2. using ALTER TABLE Statement

• Changing an existing column to an identity column is not allowed.
• But can change existing identity column’s Attributes
• Also can add a new column of the 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 the complete syntax of ALTER TABLE statement to change Attributes of an IDENTITY COLUMN.

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

Details for Identity-Attribute and Attribute-value

Details on RESTART WITH

ALTER TABLE INVOICE ALTER COLUMN invoice_number RESTART 
ALTER TABLE INVOICE ALTER COLUMN invoice_number RESTART WITH 10

Inserting a value into an identity column

In SQL INSERT Statement, there are multiple options to take care of IDENTITY COLUMN.

1. Without giving any value for IDENTITY COLUMN.

When INSERT statement is used with column name and map values using “VALUES” clause, Omit both IDENTITY COLUMN and corresponding value from query and SQL will assign value for IDENTITY COLUMN automatically.

INSERT INTO INVOICE (ORDER_NUMBER) VALUES('ORD1234')

2. With using the DEFAULT clause for the value of IDENTITY COLUMN.

When INSERT statement is used with IDENTITY COLUMN, “DEFAULT” can be used as the value for that column.

INSERT INTO INVOICE (INVOICE_NUMBER, ORDER_NUMBER) VALUES(DEFAULT, 'ORD2001')

Or simply

INSERT INTO INVOICE VALUES(DEFAULT, 'ORD2002')

3. With user given values.

Get Sumit goyal’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

When IDENTITY COLUMN is added with the “BY DEFAULT” clause other than the above 2 methods, there is one more option to insert value.

invoice_number INTEGER GENERATED BY DEFAULT AS IDENTITY "

The “BY DEFAULT” tells SQL to generate automatic value for IDENTITY COLUMN only if no user value is specified for that IDENTITY COLUMN. If user value is specified, use that value.

INSERT INTO INVOICE (INVOICE_NUMBER, ORDER_NUMBER) 
VALUES(200, 'ORD2001')

Get the last value generated for IDENTITY COLUMN.

Use function “IDENTITY_VAL_LOCAL” to retrieve the last value generated by SQL for IDENTITY COLUMN.

SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1

The IDENTITY_VAL_LOCAL function does not return a null value after multi-row inserts.

• It will return the last value that was generated.

2. ROWID

1. Using ROWID is another way to have the system assign a unique value to a column.
2. ROWID is similar to IDENTITY COLUMNS.

• But rather than being an attribute of a numeric column, it is a separate data type.

3. Same as IDENTITY COLUMNS, there are 2 options for GENERATED clause.

• ALWAYS
• BY DEFAULT

CREATE TABLE ORDERS (ORDERNO ROWID GENERATED ALWAYS, SHIPPED_TO 
VARCHAR (36) ,ORDER_DATE DATE)

3. SEQUENCE OBJECT

1. SEQUENCE OBJECT is similar to IDENTITY COLUMN in the way that they both generate unique values and use almost the same attributes.
2. However, sequences are objects that 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

CREATE [OR REPLACE] SEQUENCE sequence-name AS {data-type} 
{sequence-attributes}

Data-type allowed

Sequence-attributes details

Here is a sample CREATE SEQUENCE 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 if 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

DROP SEQUENCE statement

The syntax for the DROP SEQUENCE statement

DROP SEQUENCE sequence-name

Generate and Retrieve a SEQUENCE value

1. SQL provides two expressions, NEXT VALUE and PREVIOUS VALUE, to generate and retrieve a sequence value.
2. NEXT VALUE and PREVIOUS VALUE cannot be used in a WHERE clause.

You can use NEXT VALUE and PREVIOUS VALUE in the following expressions:

• Within the select-clause of a SELECT statement or SELECT INTO statement as long as the statement does not contain

— a DISTINCT keyword,

— a GROUP BY clause,

— an ORDER BY clause,

— a UNION keyword,

— an INTERSECT keyword,

— an EXCEPT keyword

• Within a VALUES clause of an INSERT statement
• Within the SELECT clause of the fullselect of an INSERT statement
• Within the SET clause of a searched or positioned UPDATE statement

INSERT INTO ORDERS (ORDERNO, CUSTNO) VALUES (NEXT VALUE FOR mySequence, 12)
INSERT INTO ORDERS (ORDERNO, CUSTNO) VALUES (PREVIOUS VALUE FOR mySequence, 12)

If more than one sequence expression for a single sequence object is used in a statement, DB2 will execute NEXT VALUE and PREVIOUS VALUE only once.

INSERT INTO ORDERS (ORDERNO, CUSTNO) VALUES (NEXT VALUE FOR mySequence, NEXT VALUE FOR mySequence)

In this case, both ORDERNO and CUSTNO will contain the same value as “NEXT VALUE FOR mySequence” for this query will be executed only once. So same value will be used for both columns.