数据迁移碰到Identity数据的处理

字数 4916阅读 3024评论 6赞 0

在数据加载过程中，可能会遇到identity标识的列，DB2提供了两组修饰符来解决：

identity组修饰符：identityoverride，identitymissing和identityignore；

generated组修饰符：generatedoverride，generatedmissing和generatedignore。用于当表字段有generated always as <expression>时的数据加载方法。

（1）identityoverride：表示使用数据文件中提供的值，特别适用于系统间数据迁移时，需要保留identity列值得情况；

identityoverride修饰符只适用于generated always，不支持generated by default ，不能在import中使用，只能是load中受支持；

[db2inst1@db2@two ~]$ create table identity_t1 (id integer generated always by identity(start with 100,increment by 1),name char(20));

[db2inst1@db2@two ~]$ create table identity_exp like identity_t1;

[db2inst1@db2@two ~]$ vi ident1.del

1,"Tom"
2,"jerry"
3,"Mikey"
4,"Snow"
100,"Stark"
101,"Lance"

[db2inst1@db2@two ~]$ db2 "load from ident1.del of del modified by identityoverride messages msg1.log insert into identity_t1 for exception identity_exp nonrecoverable"

Number of rows read         = 6
Number of rows skipped      = 0
Number of rows loaded       = 6
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 6
db2inst1@db2@two ~]$ db2 "select * from indentity_t1"

ID          NAME
----------- -------------
          1 Tom
          2 jerry
          3 Mikey
          4 Snow
        100 Stark
        101 Lance -------------- 保留了原来identity列的值

6 record(s) selected.

（2）identitymissing：表示数据文件中不包含自增值时，import/load数据产生自增值；

[db2inst1@db2@two ~]$ more ident_2.del
KG
Allen
[db2inst1@db2@two ~]$ db2 "load from ident_2.del of del modified by identitymissing messages msg1.log insert into identity_t1 for exception identity_exp nonrecoverable"

Number of rows read         = 2
Number of rows skipped      = 0
Number of rows loaded       = 2
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 2
[db2inst1@db2@two ~]$ db2 "select * from identity_t1"

ID          NAME
----------- ---------------
        125 KG
        126 Allen ------------------ 自动生成identity列的值

2 record(s) selected.

（3）identityignore：表示当数据文件包含自增列时，import/load命令忽略该值，并生产自增值。

[db2inst1@db2@two ~]$ vi ident_3.del

1,"KG"
2,"Allen"
[db2inst1@db2@two ~]$ db2 "load from ident_3.del of del modified by identityignore messages msg1.log insert into indentity_t1 for exception identity_exp nonrecoverable"

Number of rows read         = 2
Number of rows skipped      = 0
Number of rows loaded       = 2
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 2

[db2inst1@db2@two ~]$ db2 "select * from indentity_t1"

ID          NAME
----------- ----------------
        127 KG
        128 Allen ----------------- identity忽略文件中的值，重新生成。

2 record(s) selected.

对于含有generated by default as identity自增列的数据导入，可能出现加载完成后，再插入数据时产生重复值的情况，处理办法就是重置开始值如下：

db2 "select MAX(<identity_colname>) as maxcounter from tablename"

db2 "alter table tablename alter column <identity)colname> restart with <maxcounter+1>"

数据库数据迁移

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！