How to create and alter Identity columns in DB2 for i SQL

How to create and alter Identity columns in DB2 for i SQL

If a table has an Identity column then whenever any new row inserted into that table system will automatically generate the identity column value for that new row.

Which datatype can be used to create an identity columns?

SMALLINT

INTEGER

BIGINT

DECIMAL

NUMERIC

Only columns of above data type can be set as an identity column.

How many identity column allowed per identity column?

Only one identity column per table.

Can we specify any existing table column as an identity column during changing table definition?

No, we cannot specify any existing table column as an identity column during table definition change. Only a new added column during change can be specified as an identity column.

How to create an identity column during table creation?

We can define an identity column as either GENERATED BY DEFAULT or GENERATED ALWAYS

If we define the column as GENERATED BY DEFAULT, we can insert/supply a value for an identity column and if we do not provide the value for identity column then system will generate it.

If we define the column as GENERATED ALWAYS, DB2 always generates a value for the column and we cannot insert/supply data to that column.

CREATE TABLE PF1
   (ROLLNO BIGINT NOT NULL
   GENERATED ALWAYS AS IDENTITY
   (START WITH 1 
   INCREMENT BY 1 
   CYCLE),
   NAME VARCHAR (30) ,
   CLASS CHAR(2))

Here, we created a table named PF1 with 3 fields namely ROLLNO of type BIGINT, NAME of type Varchar length 30, and class of type char length 2. ROLLNO is set as identity column and it is defined with a starting value of 1, incremented by 1 for every new inserted row and will cycle when the maximum value of bigint datatype(9,223,372,036,854,775,807) reached in this case and it will restart at 1 again.

Since we used keyword 'cycle' which means it will restart again from 1 and increment by 1, then there is a catch here if unique key is specified on the identity column as well then a duplicate key error occurred when it tries to assign 1 again and fails on all next inserts as well. For Unique values always please use NO CYCLE.

We can also decrease the value of the identity column by specifying a negative value for the INCREMENT BY option

CREATE TABLE PF1
   (ROLLNO BIGINT NOT NULL
   GENERATED ALWAYS AS IDENTITY
   (START WITH 5 
   INCREMENT BY -1 
   CYCLE),
   NAME VARCHAR (30) ,
   CLASS CHAR(2))

On inserting 3 rows to this table we see that the ROLLNO value is start with 5 and decrease by 1 on every next inserted row.

INSERT INTO PF1 (NAME, CLASS) VALUES('A', '1')  
INSERT INTO PF1 (NAME, CLASS) VALUES('B', '2')  
INSERT INTO PF1 (NAME, CLASS) VALUES('B', '2')  

ROLLNO   NAME                            CLASS
     5   A                                1   
     4   B                                2   
     3   B                                2   

We can alsospecify the exact range of values to be generated by specifying MINVALUE and MAXVALUE

CREATE TABLE PF1
   (ROLLNO BIGINT NOT NULL
   GENERATED ALWAYS AS IDENTITY
   (START WITH -4 
   INCREMENT BY 1 
   CYCLE,
   MINVALUE -5,
   MAXVALUE 5),
   NAME VARCHAR (30) ,
   CLASS CHAR(2))

Inserted below row 18 times start the ROLLNO value by -4 and increment by 1 and when reached to its maxvlue 5, restart the ROLLNO value from -5 and again increment by 1.

INSERT INTO PF1 (NAME, CLASS) VALUES('A', '1')

ROLLNO   NAME                            CLASS
     4-  A                                1   
     3-  A                                1   
     2-  A                                1   
     1-  A                                1   
     0   A                                1   
     1   A                                1   
     2   A                                1   
     3   A                                1   
     4   A                                1   
     5   A                                1   
     5-  A                                1   
     4-  A                                1   
     3-  A                                1   
     2-  A                                1   
     1-  A                                1   
     0   A                                1   
     1   A                                1   
     2   A                                1   

Modify the attributes of an existing identity column using the ALTER TABLE statement.

Just want to restart the identity column value with a new value.

ALTER TABLE PF1         
  ALTER COLUMN ROLLNO         
  RESTART WITH 5

How to drop the identity attribute from a column.

ALTER TABLE PF1         
  ALTER COLUMN ROLLNO         
  DROP IDENTITY

The ROLLNO column identity attribute will be dropped and now system will no longer generate values for this column.

Which function is used to return the most recently assigned value for an identity column?

The IDENTITY_VAL_LOCAL() function returns the most recently assigned value for an identity column and its present in schema SYSIBM. The function is not deterministic and has no input params and the return value from this function is of type DECIMAL(31,0)

The assigned value can be a value supplied by the user(GENERATED BY DEFAULT) was generated by Db2(GENERATED ALWAYS).

The IDENTITY_VAL_LOCAL value persists until the next insert in the current session into a table that has an identity column defined on it, or the application session ends. Otherwise the result would be NULL. Its the scope of this function.

select IDENTITY_VAL_LOCAL() from sysibm.sysdummy1