DB2 auto-increment column Import and Export Test

DB2 auto-increment column test
1. To change a column in a table to auto-increment, run the following command:
Alter table <table name> alter column <column name> set not null
Alter table <table name> alter column <column name> set generated always as identity (start with 1, increment by 1)
The above command is useful online when you change the attributes of a column in a table.

2. When you modify the starting value of an auto-increment column in a table, run the following command:
Alter table <talbe_name> alter column <column name> restart with 18;

Test:

Create table customer_orders_t (
Order_id INT NOT NULL GENERATED ALWAYS AS IDENTITY
(Start with 1
Increment by 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER ),
Order_date date not null,
Cust_id int not null,
Product_id int not null,
Quantity int not null,
Price DECIMAL (10, 2) not null,
Status CHAR (9) not null,
Primary key (order_date, order_id ))

Note: The IDENTITY attribute of the column and its own does not guarantee that the generated sequence value is unique.
However, the primary key constraint ensures the uniqueness of the row in the table.
To ensure that only automatically GENERATED values are inserted into the ID column, they specify the generated always clause.
Use the last generated order_id to determine how much data
The no cache and ORDER options ensure that unused ID values are not discarded in case of system failure.

 

Test 1

Insert data

Insert into customer_orders_t values (default, current date, 10.2, 12, '2 ')

-- Successful

Insert into customer_orders_t values (1, current date, 12, 12, 10.2, '2 ')

-- An error is returned because the IDENTITY field cannot be specified.

-- Solution
Alter table customer_orders_t
Alter column order_id
SET GENERATED BY DEFAULT

-- Create an orders_seq object
Create sequence orders_seq
AS INT
Start with 1
Increment by 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER

-- Insert data
Insert into customer_orders_t VALUES (next value for orders_seq, current date, 12,12, 12, 10.2, '2 ')

 

1. Use the command line to obtain the next value of sequence soc. nic_qian:
Db2 "values next value for soc. nic_qian"

2. Run the command line to reset sequence soc. nic_qian:
Db2 "alter sequence soc. nic_qian restart", the reset value is the MINVALUE when the SEQUENCE is created by default.

3. the command line resets sequence soc. nic_qian with the specified value 22:
Db2 "alter sequence soc. nic_qian restart with 22"

4. the initial value of the IDENTITY field of KS. CHECK_CONDITION in the command line reset table is 20:
Db2 "alter table ks. CHECK_CONDITION alter column identity_column_name restart with 20"

5. If sequence is reset as a command line, bind the Embedded C program code using this sequence package
The VALID field of is changed to N, so DB2 will automatically rebind the next time this code is called.
Bind the package of this Code, this action will bring unpredictable consequences to the application, for example, if the code is
It is easy to cause a deadlock if it is rebound frequently during the time range used.
The same problem occurs in the IDENTITY field.

1 2 Next Page