ITPub博客

首页 > Linux操作系统 > Linux操作系统 > db2 load 标识列 生成列

db2 load 标识列 生成列

原创 Linux操作系统 作者:jinlonglv 时间:2011-06-02 13:40:05 0 删除 编辑

标识列

列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
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24177460/viewspace-696968/,如需转载,请注明出处,否则将追究法律责任。

上一篇: crontab
下一篇: 去除重复记录
请登录后发表评论 登录
全部评论

注册时间:2010-07-01

  • 博文量
    14
  • 访问量
    29279