DB2 自增长列导入、导出测试

亦凉 2022-03-11 04:38 410阅读 0赞

# DB2 自增长列测试 #

**1当想将表中一列修改为自动增长时，可用下面命令：**  
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)  
上面命令是在改一表中列的属性时，在网上找到的很有用。

**2当修改表中一列自动增长的开始值时，可用下面的命令：**  
ALTER TABLE <talbe\_name> ALTER COLUMN <column name> RESTART WITH 18;

**测试：**

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))

*注：该列中的以及它本身的 IDENTITY 属性并没有保证所生成的序列值是唯一的。  
但是， PRIMARY KEY 约束保证了表中行的唯一性。  
为了确保只将自动生成的值插入标识列，他们指定了 GENERATED ALWAYS 子句。  
使用最后一个生成的 order\_id 来确定多少数据  
选项 NO CACHE 和 ORDER 确保了在系统故障的情况下，不废弃未使用的标识值。*

测试1

插入数据

insert into customer\_orders\_t values (default,current date,12,12,12,10.2,'2')

\--成功

insert into customer\_orders\_t values (1,current date,12,12,12,10.2,'2')

\-- 报错 因为：IDENTITY字段不允许指定值  
  
\--解决方案  
ALTER TABLE customer\_orders\_t  
ALTER COLUMN order\_id  
SET GENERATED BY DEFAULT

\--创建orders\_seq对象  
CREATE SEQUENCE orders\_seq  
AS INT  
START WITH 1  
INCREMENT BY 1  
MINVALUE 1  
NO MAXVALUE  
NO CYCLE  
NO CACHE  
ORDER

\--插入数据  
INSERT INTO customer\_orders\_t VALUES (NEXT VALUE FOR orders\_seq, CURRENT DATE,12,12,12,10.2,'2')

1、命令行取sequence soc.nico\_qian的下一个值：  
db2 "values next value for soc.nico\_qian"

2、命令行重置sequence soc.nico\_qian：  
db2 "alter sequence soc.nico\_qian restart"，重置后的值默认为创建SEQUENCE时的MINVALUE

3、命令行以指定值22重置sequence soc.nico\_qian：  
db2 "alter sequence soc.nico\_qian restart with 22"

4、命令行重置表KS.CHECK\_CONDITION的IDENTITY字段初始值为20：  
db2 "ALTER TABLE KS.CHECK\_CONDITION ALTER COLUMN identity\_column\_name RESTART WITH 20"

5、如果sequence被以命令行的方式重置，那么用到这个sequence的嵌入式C程序代码的绑定包  
　　的VALID字段会被修改为N，那么在下一次这个代码被调用的时候，DB2会自动重新绑定  
　　 此代码的绑定包，这个动作会给应用程序带来不可预知的后果，比如：如果这段代码是在很  
　　 频繁的被用到的时间段内被重新绑定，那么极易造成死锁。  
　　 同样的问题会出现在IDENTITY字段上。

DB2自增加字段表 导入导出 测试

CREATE TABLE EMPLOYEE (SERIALNUMBERBIGINT NOT NULL  
GENERATED ALWAYS AS IDENTITY  
(START WITH 1, INCREMENT BY 1),  
FIRSTNAMECHAR(64),  
LASTNAMECHAR(64),  
SALARY DECIMAL(10, 2),  
PRIMARY KEY (SERIALNUMBER))

CREATE TABLE EMPLOYEE1(SERIALNUMBERBIGINT NOT NULL  
GENERATED ALWAYS AS IDENTITY  
(START WITH 1, INCREMENT BY 1),  
FIRSTNAMECHAR(64),  
LASTNAMECHAR(64),  
SALARY DECIMAL(10, 2),  
PRIMARY KEY (SERIALNUMBER))

select \* from EMPLOYEE;  
select \* from EMPLOYEE1;

identityignore 忽略自增identitymissing 自动生成自增identityoverride 使用自增

insert into db2admin.EMPLOYEE(FIRSTNAME,LASTNAME,SALARY)values ( 'A','AA',1);

insert into db2admin.EMPLOYEE(FIRSTNAME,LASTNAME,SALARY)values ( 'B','BB',2);

insert into db2admin.EMPLOYEE(FIRSTNAME,LASTNAME,SALARY)values ( 'C','CC',3);

\--TEST 全量导出  
db2 export to D:\\PRODUCTION.ixf of ixf select \* from db2admin.EMPLOYEE  
\--直接插入 报错 --不允许插入  
db2 load CLIENT from D:\\PRODUCTION.ixf of ixf replace into db2admin.EMPLOYEE1 NONRECOVERABLE  
db2 import from D:\\PRODUCTION.ixf of ixf insert into db2admin.EMPLOYEE1  
\--identityignore 忽略源自增字段 方式插入 --插入正常  
db2 load CLIENT from D:\\PRODUCTION.ixf of ixf modified by identityignore replace into db2admin.EMPLOYEE1 NONRECOVERABLE  
db2 import from D:\\PRODUCTION.ixf of ixf modified by identityignore commitcount 1000 insert into db2admin.EMPLOYEE1  
\--identitymissing 自动生成目标自增字段方式插入 --插入错位  
db2 load CLIENT from D:\\PRODUCTION.ixf of ixf modified by identitymissing replace into db2admin.EMPLOYEE1 NONRECOVERABLE  
db2 import from D:\\PRODUCTION.ixf of ixf modified by identitymissing commitcount 1000 insert into db2admin.EMPLOYEE1  
\--identityoverride 使用源自增字段方式插入 OK  
db2 load CLIENT from D:\\PRODUCTION.ixf of ixf modified by identityoverride replace into db2admin.EMPLOYEE1 NONRECOVERABLE

\--TEST 部分导出  
db2 export to D:\\PRODUCTION\_1.ixf of ixf select FIRSTNAME,LASTNAME,SALARY from db2admin.EMPLOYEE  
\--部分直接插入 OK  
db2 load CLIENT from D:\\PRODUCTION\_1.ixf of ixf replace into db2admin.EMPLOYEE1( FIRSTNAME,LASTNAME,SALARY) NONRECOVERABLE  
db2 import from D:\\PRODUCTION\_1.ixf of ixf insert into db2admin.EMPLOYEE1( FIRSTNAME,LASTNAME,SALARY)  
\--部分忽略源自增字段直接插入 OK  
db2 load CLIENT from D:\\PRODUCTION\_1.ixf of ixf modified by identityignore replace into db2admin.EMPLOYEE1( FIRSTNAME,LASTNAME,SALARY) NONRECOVERABLE  
db2 import from D:\\PRODUCTION\_1.ixf of ixf modified by identityignore commitcount 1000 insert into db2admin.EMPLOYEE1( FIRSTNAME,LASTNAME,SALARY)  
\--identitymissing 自动生成目标自增字段方式插入 OK  
db2 load CLIENT from D:\\PRODUCTION\_1.ixf of ixf modified by identitymissing replace into db2admin.EMPLOYEE1( FIRSTNAME,LASTNAME,SALARY) NONRECOVERABLE  
db2 import from D:\\PRODUCTION\_1.ixf of ixf modified by identitymissing commitcount 1000 insert into db2admin.EMPLOYEE1( FIRSTNAME,LASTNAME,SALARY)  
\--identityoverride 自动生成自增方式插入 --缺少字段错误  
db2 load CLIENT from D:\\PRODUCTION\_1.ixf of ixf modified by identityoverride replace into db2admin.EMPLOYEE1( FIRSTNAME,LASTNAME,SALARY) NONRECOVERABLE