Identity column in DDL table

Identity column in DDL table

Identity Column

The identity column in the DDL table is a column that can be auto-populated by the system.

In most cases, the identity column is used as the primary key of the table. But, can be used for other purposes also.

In this article, we will understand about identity column and about its attributes.

Identity Column with GENERATE ALWAYS

GENERATE ALWAYS keyword in identity column means the value for this column will be assigned by the only system.

But we can control some of the aspects of the auto-generated identity column.

GENERATE ALWAYS 

In the below shown DDL IDCOL column is set as identity column with GENERATE ALWAYS

CREATE TABLE DDL80(                         
  IDCOL BIGINT GENERATED ALWAYS AS IDENTITY,
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);    

DSFFFD of GENERATE ALWAYS

So, here we can see the column IDCOL is a GENERATE ALWAYS identity column and it also has some attributes with it with default values.

• START WITH set as 1
• INCREMENT BY set as 1
• MINVALUE set as 1
• MAXVALUE set as 9223372036854775807
• CYCLE set as NO

All the above-mentioned attributes can be changed as per our needs.

START WITH

CREATE TABLE DDL80(                         
  IDCOL BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 2), 
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);    

In the above table START WITH is used GENERATE ALWAYS.

DDSFFD of START WITH 

             Data        Field  Buffer    Buffer        Field    Column          
Field      Type       Length  Length  Position        Usage    Heading         
IDCOL      BINARY      18  0       8         1        Both     IDCOL           
  Identity column information:                                                 
    GENERATED . . . . . . . . . . . . . . . :  ALWAYS                          
    Original START WITH . . . . . . . . . . :                                2 
    Current START WITH  . . . . . . . . . . :                                2 
    INCREMENT BY  . . . . . . . . . . . . . :                                1 
    MINVALUE  . . . . . . . . . . . . . . . :                                2 
    MAXVALUE  . . . . . . . . . . . . . . . :              9223372036854775807 
    CYCLE (Yes or No) . . . . . . . . . . . :  No                              

Here we can see that the START WITH value along with MINVALUE has been changed to 2, hence now the auto-population of the Identity column will happen from 2 and it will be incremented by 1.

INCREMENT BY

CREATE TABLE DDL80(                                         
  IDCOL BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 2    
                                            INCREMENT BY 5),
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,   
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);                                                 
                                            

In the above table INCREMENT BY is used GENERATE ALWAYS.

DDSFFD of INCREMENT  

            Data        Field  Buffer    Buffer        Field    Column          
 Field      Type       Length  Length  Position        Usage    Heading         
 IDCOL      BINARY      18  0       8         1        Both     IDCOL           
   Identity column information:                                                 
     GENERATED . . . . . . . . . . . . . . . :  ALWAYS                          
     Original START WITH . . . . . . . . . . :                                2 
     Current START WITH  . . . . . . . . . . :                                2 
     INCREMENT BY  . . . . . . . . . . . . . :                                5 
     MINVALUE  . . . . . . . . . . . . . . . :                                2 
     MAXVALUE  . . . . . . . . . . . . . . . :              9223372036854775807 
     CYCLE (Yes or No) . . . . . . . . . . . :  No                                                                          

Now the INCREMENT BY value is set as 5 so the identity column will start with 2 and the next value will be 7 and so on.

MINVALUE

CREATE TABLE DDL80(                                       
  IDCOL BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 2  
                                            INCREMENT BY 5
                                            MINVALUE 0),  
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);       

In the above table, MINVALUE has used GENERATE ALWAYS.

But, the minimum value is set as 0, and the start by is set as 2...

This is possible here MINVALUE can be any value which less than equal to START WITH value but it can't be another way around.

DDSFFD of MINVALUE 

           Data        Field  Buffer    Buffer        Field    Column          
Field      Type       Length  Length  Position        Usage    Heading         
IDCOL      BINARY      18  0       8         1        Both     IDCOL           
  Identity column information:                                                 
    GENERATED . . . . . . . . . . . . . . . :  ALWAYS                          
    Original START WITH . . . . . . . . . . :                                2 
    Current START WITH  . . . . . . . . . . :                                2 
    INCREMENT BY  . . . . . . . . . . . . . :                                5 
    MINVALUE  . . . . . . . . . . . . . . . :                                0 
    MAXVALUE  . . . . . . . . . . . . . . . :              9223372036854775807 
    CYCLE (Yes or No) . . . . . . . . . . . :  No                                

MAXVALUE

CREATE TABLE DDL80(                                        
  IDCOL BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 2   
                                            INCREMENT BY 5 
                                            MINVALUE 0     
                                            MAXVALUE 7),   
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);         

In the above table, MAXVALUE has used GENERATE ALWAYS.

MAXVALUE will limit the number of records that can be inserted in the table as in this case MAXVALUE is set as 7 so, only 2 records can be inserted in the above table.

But, it can be resolved with the help of the CYCLE attribute of the identity column.

DDSFFD of MAXVALUE 

           Data        Field  Buffer    Buffer        Field    Column         
Field      Type       Length  Length  Position        Usage    Heading        
IDCOL      BINARY      18  0       8         1        Both     IDCOL          
  Identity column information:                                                
    GENERATED . . . . . . . . . . . . . . . :  ALWAYS                         
    Original START WITH . . . . . . . . . . :                                2
    Current START WITH  . . . . . . . . . . :                                2
    INCREMENT BY  . . . . . . . . . . . . . :                                5
    MINVALUE  . . . . . . . . . . . . . . . :                                0
    MAXVALUE  . . . . . . . . . . . . . . . :                                7
    CYCLE (Yes or No) . . . . . . . . . . . :  No                              

CYCLE

CREATE TABLE DDL80(                                        
  IDCOL BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 2   
                                            INCREMENT BY 5 
                                            MINVALUE 0     
                                            MAXVALUE 7 
                                            CYCLE),
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);         

In the above table, CYCLE has used GENERATE ALWAYS.

CYCLE attribute is used in the table when the maximum limit of the identity column is reached and to insert more records Identity column starts with the minimum value.

CYCLE is set as YES to enable the use of the existing identity column.

DDSFFD of CYCLE 

           Data        Field  Buffer    Buffer        Field    Column         
Field      Type       Length  Length  Position        Usage    Heading        
IDCOL      BINARY      18  0       8         1        Both     IDCOL          
  Identity column information:                                                
    GENERATED . . . . . . . . . . . . . . . :  ALWAYS                         
    Original START WITH . . . . . . . . . . :                                2
    Current START WITH  . . . . . . . . . . :                                2
    INCREMENT BY  . . . . . . . . . . . . . :                                5
    MINVALUE  . . . . . . . . . . . . . . . :                                0
    MAXVALUE  . . . . . . . . . . . . . . . :                                7
    CYCLE (Yes or No) . . . . . . . . . . . :  Yes                              

It is not recommended to CYCLE set as YES, as it will create the duplicate identity column in the table and the purpose of using the identity column as the primary key will fail

Identity Column with GENERATE AS DEFAULT

GENERATE AS DEFAULT keyword will enable the table to accept the user-defined identity column value allowing with auto-generated identity column value where the user-defined identity column value is not provided.

CREATE TABLE DDL80(                              
  IDCOL BIGINT GENERATED BY DEFAULT AS IDENTITY, 
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,
FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);    

DSPFFD of GENERATE AS DEFAULT

GENERATE AS DEFAULT will also have the same attributes as GENERATE ALWAYS but here identity column can accept any user-defined value.

MINVALUE is set as 1 but it can accept user-defined values as 0, also it can have any number of duplicate identity column values.

Related Post

Read also :

• CRTPF and Create Table in IBM i
• Primary Key in DDS and DDL tables
• Data Validation in DDS and DDL tables
• Column Heading in DDS Files and DDL tables
• Column Text in DDS and DDL tables
• Attributes of DDL table
• Auto-update Timestamp Column using ROW CHANGE TIMESTAMP in DDL table
• ADD Constraint in DDL tables
• Create Index and LF keyed in IBM i
• Create View and LF Non-Keyed in IBM i
• DDS to DDL Modernization: Level Check Error
• QSQGNDDL API - IBM to convert DDS file into DDL table
• QSQGNDDL API - IBM to convert DDS file into DDL table (Continuing...)