db2 load 标识列生成列

最新推荐文章于 2025-06-18 11:17:50 发布

转载最新推荐文章于 2025-06-18 11:17:50 发布 · 234 阅读

本文详细介绍了如何使用 DB2 的 LOAD 命令加载数据到包含自增列和生成列的表中，并展示了如何处理不同情况下的数据加载，例如忽略、插入或覆盖已存在的数据。

标识列

列1：
   create table table1 (c1 varchar(30),
   c2 int generated by default as         identity,
   c3 int,
   c4 char(1))
   编辑一个1.txt数据文件
   Robert, 45, J
   Mike, 76, K
   Leo, 23, I

db2 load from 1.txt of del modified by identitymissing insert into table1;

列2：
   create table table2 (c1 varchar(30),
   c2 int generated by default as         identity,
   c3 int,
   c4 char(1))
   编辑一个2.txt的数据文件
   Robert, 1, 45, J
   Mike, 2, 76, K
   Leo, 3, 23, I
db2 load from 2.txt of del modified by identityignore insert into table2;

列3：
   create table table3 (c1 varchar(30),
   c2 int generated by default as         identity,
   c3 int,
   c4 char(1))
   编辑一个3.txt数据文件
   Robert, 3, 45, J
   Mike, 4, 76, K
   Leo, 5, 23, I
db2 load from 3.txt of del modified by identityoverride insert into table3;
这个参数只能用load,不能使用import.

It is also a table with a GENERATED ALWAYS identity column.
db2 select max(c2) from table3 with;

db2 alter table table3 alter c2 restart with maxnumber or maxnumber+cache
生成列

列1：

   create table table4 (c1 int,
   c2 int,
   g1 int generated always as (c1 + c2),
   g2 int generated always as (2 * c1),
   c3 char(1))
   编辑一个4.txt的数据文件
   1, 5, J
   2, 6, K
   3, 7, I

db2 load from 4.txt of del modified by generatedmissing insert into table4;

列2：

   create table table5 (c1 int,
   c2 int,
   g1 int generated always as (c1 + c2),
   g2 int generated always as (2 * c1),
   c3 char(1))
   创建一个5.txt的数据文件
   1, 5, 10, 15, J
   2, 6, 11, 16, K
   3, 7, 12, 17, I

db2 load from 5.txt of del modified by generatedignore insert into table5;

列6：

   create table table6 (c1 int,
   c2 int,
   g1 int generated always as (c1 + c2),
   g2 int generated always as (2 * c1),
   c3 char(1))
   创建一个6.txt的数据文件
   1, 5, 15, 15, J
   2, 6, 16, 16, K
   3, 7, 17, 17, I

db2 load from 6.txt of del modified by generatedoverride insert ino table6;

列6之后会出现integrity pending

约束检查

db2 set integrity for table6 immediate checked force generated

db2 select * from table6

1, 5, 6, 2, J
2, 6, 8, 4, K
3, 7, 10, 6, I

db2 set integrity for generated column unchecked

db2 select * from table6
1, 5, 15, 15, J
2, 6, 16, 16, K
3, 7, 17, 17, I