Use of the DB2 auto-incrementing field IDENTITY

How should I use the DB2 auto-incrementing field IDENTITY? I believe this is a problem that many people have mentioned. The following describes the usage of the DB2 auto-incrementing field IDENTITY for your reference.

A. Generated always
The value is generated by DB2 and cannot be assigned directly.

 
 

  
  
Example：  
  
  
Create table t1  
  
  
(id int generated always as identity (start with 100 increment by 1),description char(10)）;  
  
  
      Commit;  
  
  
      Insert into t1 values (default,’a1’);                         //insert100 a1  
  
  
   Insert into t1(description) values (’a1’);                    //insert101 a1  
  
  
      Insert into t1 values (200,’a1’);                             //erro  
  
  
      Commit;  
  
  
      Insert into t1(description) values (’a1’);                    //insert102 a1  
  
  
      Rollback;  
  
  
      Insert into t1(description) values (’a1’);                    //insert103 a1  
  
  
      Commit;  
  
  
      Select * from t1;  
  
  
      100       a1  
  
  
      101       a1  
  
  
      103       a1 
 
 

B. Generated by default
Values can be generated by DB2 or assigned directly by the customer. However, DB2 cannot guarantee that the provided values are unique.

 
 

  
  
Example:
  
  
Create table t1
  
  
(Id int generated by default as identity (start with 100 increment by 1), description char (10) in userspace1
  
  
Commit;
  
  
Insert into t1 values (default, 'a1'); // insert100 a1
  
  
Insert into t1 (description) values ('a1'); // insert101 a1
  
  
Insert into t1 values (200, 'a1'); // insert200 a1
  
  
Insert into t1 values (102, 'a1'); // insert102 a1
  
  
Commit;
  
  
Insert into t1 (description) values ('a1'); // erro, so the current auto-increment value is 102, and the database already has a 102 primary key value.
  
  
Insert into t1 (description) values ('a1'); // insert103 a1
  
  
Commit;
  
  
Select * from t1;
  
  
100 a1
  
  
101 a1
  
  
102 a1
  
  
103 a1
  
  
200 a1
 
 

The usage of the DB2 auto-incrementing field IDENTITY is described above.

Introduction to DB2 datetime Data Types

Six common DB2 command lines

Four precautions for DB2 Data Import

DB2 Management page size limit

Policy Selection for DB2 environment variable Management