2008年3月27日 星期四

DB2, import identity column encounter SQL3550W

1. create table t1
create table t1
( orderno int generated always as identity
(start with 1)
, partno int
) ;

2. insert t1 data
insert into t1 values(default, 1234567)
insert into t1 values(default, 1234567)
insert into t1 values(default, 1234567)
insert into t1 values(default, 1234567)
insert into t1 values(default, 1234567)

3. check t1 data
select * from t1

4. export t1 data
export to t1.ixf of ixf select * from t1

5. delete t1 data
delete from t1

6. import t1.ixf into table t1
import from t1.ixf of ixf insert into t1

Number of rows read = 5
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 5
Number of rows committed = 5

7. load t1.ixf into table t1
load from t1.ixf of ixf modified by identityoverride insert into t1

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

alternate solution:
load from t1.ixf of ixf modified by identitymissing insert into t1
load from t1.ixf of ixf modified by identityignore insert into t1

沒有留言: