DB2 auto-increment column Import and Export Test

DB2 auto-increment column Import and Export test DB2 auto-increment column test 1 when you want to change a column in the table to auto-increment, run the following command: alter table <table name> alter column <column name> set not nullAlter 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. Www.2cto.com 2 when you modify the start 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 in this 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 many data options no cache and ORDER ensure that unused ID values are not discarded in the case of system failure. Test 1 insert into mermer_orders_t values (default, current date, 12, 12, 10.2, '2') -- insert into customer_orders_t values (1, current date, 12,12, 12, 10.2, '2') -- the error is returned because: the IDENTITY field cannot specify a value -- Solution: alter table customer_orders_t alter column order_id set generated by default -- CREATE the 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 INTO customer_orders_t VALUES (next value for orders_seq, current date, 12, 12, 12, 10.2, '2') www.2cto.com 1. Obtain sequence soc from the command line. the next value of nic_qian: db2 "values next value for soc. nic_qian "2. Command Line reset sequence soc. nic_qian: db2 "alter sequence soc. nic_qian restart ". The default value after resetting is MINVALUE 3 when SEQUENCE is created. The command line resets the sequence soc with the specified value 22. nic_qian: db2 "alter sequence soc. nic_qian restart with 22 "4. Life Reset the table KS for the row. the initial value of the IDENTITY field for CHECK_CONDITION is 20: db2 "alter table ks. CHECK_CONDITION alter column identity_column_name restart with 20 "5. If the sequence is reset as a command line, then, the VALID field of the BIND package of the embedded C program code that uses this sequence will be changed to N, so the next time this code is called, DB2 will automatically re-bind the bound package of this Code. This action will have unpredictable consequences for the application, such: if this code is re-bound in a frequently used period, it can easily cause deadlocks. The same problem occurs in the IDENTITY field. Www.2cto.com import and export of the DB2 auto-added field TABLE test create table employee (serialnumber bigint not null generated always as identity (start with 1, increment by 1), firstname char (64 ), lastname char (64), salary decimal (10, 2), primary key (SERIALNUMBER) create table EMPLOYEE1 (serialnumber bigint not null generated always as identity (start with 1, increment by 1), firstname char (64), lastname char (64), salary decimal (10, 2), primary key (SERIALNUMBER) www.2cto.com select * from EMPLOYEE; select * from EMPLOYEE1; identityignore ignore auto-incrementing identitymissing automatically generate auto-incrementing identityoverride use auto-incrementing insert into db2admin. EMPLOYEE (FIRSTNAME, LASTNAME, SALARY) values ('A', 'A', 1); insert into db2admin. EMPLOYEE (FIRSTNAME, LASTNAME, SALARY) values ('B', 'bb ', 2); insert into db2admin. EMPLOYEE (FIRSTNAME, LASTNAME, SALARY) values ('C', 'cc', 3); -- TEST full export db2 export to D: \ PRODUCTION. ixf of ixf select * from db2admin. EMPLOYEE -- direct insertion error -- db2 load CLIENT from D: \ PRODUCTION cannot be inserted. ixf of ixf replace into db2admin. EMPLOYEE1 NONRECOVERABLEdb2 import from D: \ PRODUCTION. ixf of ixf insert into db2admin. EMPLOYEE1 -- identityignore ignore insert from adding field -- insert normal db2 load CLIENT from D: \ PRODUCTION. ixf of ixf modified by identityignore replace into db2admin. EMPLOYEE1 NONRECOVERABLEdb2 import from D: \ PRODUCTION. ixf of ixf modified by identityignore commitcount 1000 insert into db2admin. EMPLOYEE1 -- identitymissing automatically generates the target auto-incrementing field to insert -- Insert the misplaced db2 load CLIENT from D: \ PRODUCTION. ixf of ixf modified by identitymissing replace into db2admin. EMPLOYEE1 NONRECOVERABLEdb2 import from D: \ PRODUCTION. ixf of ixf modified by identitymissing commitcount 1000 insert into db2admin. EMPLOYEE1 -- identityoverride inserts OKdb2 load CLIENT from D: \ PRODUCTION using the method from adding fields. ixf of ixf modified by identityoverride replace into db2admin. EMPLOYEE1 NONRECOVERABLE -- TEST part exports db2 export to D: \ PRODUCTION_1.ixf of ixf select FIRSTNAME, LASTNAME, SALARY from db2admin. EMPLOYEE -- partially insert OKdb2 load CLIENT from D: \ PRODUCTION_1.ixf of ixf replace into db2admin. EMPLOYEE1 (FIRSTNAME, LASTNAME, SALARY) NONRECOVERABLEdb2 import from D: \ PRODUCTION_1.ixf of ixf insert into db2admin. EMPLOYEE1 (FIRSTNAME, LASTNAME, SALARY) -- partially ignore the direct insertion of OKdb2 load CLIENT from D: \ PRODUCTION_1.ixf of ixf modified by identityignore replace into db2admin from the increment field. EMPLOYEE1 (FIRSTNAME, LASTNAME, SALARY) NONRECOVERABLEdb2 import from D: \ PRODUCTION_1.ixf of ixf modified by identityignore commitcount 1000 insert into db2admin. EMPLOYEE1 (FIRSTNAME, LASTNAME, SALARY) -- identitymissing automatically generates the target auto-increment field and inserts OKdb2 load CLIENT from D: \ PRODUCTION_1.ixf of ixf modified by identitymissing replace into db2admin. EMPLOYEE1 (FIRSTNAME, LASTNAME, SALARY) NONRECOVERABLEdb2 import from D: \ PRODUCTION_1.ixf of ixf modified by identitymissing commitcount 1000 insert into db2admin. EMPLOYEE1 (FIRSTNAME, LASTNAME, SALARY) -- identityoverride automatically generates auto-incrementing insert mode -- missing field error db2 load CLIENT from D: \ PRODUCTION_1.ixf of ixf modified by identityoverride replace into db2admin. EMPLOYEE1 (FIRSTNAME, LASTNAME, SALARY) NONRECOVERABLE