[原创]从db2move出来的备份里恢复含有identity字段的表的数据

Ray001

1. 原始表：
   
2. CREATE TABLE "EASYCORE"."TJYRZB" (
   
3. "BH" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
   
4. START WITH +1
   
5. INCREMENT BY +1
   
6. MINVALUE +1
   
7. MAXVALUE +999999999
   
8. CYCLE
   
9. NO CACHE
   
10. ORDER ),
    
11. "JGM" CHAR(4) NOT NULL ,
    
12. "GY" CHAR(8) NOT NULL ,
    
13. "GYLSH" INTEGER NOT NULL ,
    
14. "RQ" INTEGER NOT NULL ,
    
15. "SJ" CHAR(8) NOT NULL ,
    
16. "JYM" INTEGER NOT NULL ,
    
17. "BZ" CHAR(1) NOT NULL ,
    
18. "ZHKH" VARCHAR(20) NOT NULL ,
    
19. "ZHXH" INTEGER NOT NULL ,
    
20. "JYXX" VARCHAR(100) NOT NULL ,
    
21. "SQGY" CHAR(8) WITH DEFAULT ' ' ) ;
    
22. 
    
23. 我建的临时表：
    
24. CREATE TABLE "EASYCORE"."TJYRZB2" (
    
25. "BH" INTEGER NOT NULL ,
    
26. "JGM" CHAR(4) NOT NULL ,
    
27. "GY" CHAR(8) NOT NULL ,
    
28. "GYLSH" INTEGER NOT NULL ,
    
29. "RQ" INTEGER NOT NULL ,
    
30. "SJ" CHAR(8) NOT NULL ,
    
31. "JYM" INTEGER NOT NULL ,
    
32. "BZ" CHAR(1) NOT NULL ,
    
33. "ZHKH" VARCHAR(20) NOT NULL ,
    
34. "ZHXH" INTEGER NOT NULL ,
    
35. "JYXX" VARCHAR(100) NOT NULL ,
    
36. "SQGY" CHAR(8) WITH DEFAULT ' ' ) ;

复制代码

[ 本帖最后由 Ray001 于 2006-7-6 13:02 编辑 ]

Ray001

我的恢复数据脚本

1. #!/usr/bin/ksh
   
2. db2 connect to easytest
   
3. TABLE=$1 #表名
   
4. COUNT=$2 #记录数
   
5. grep \"$COUNT\" *msg|grep rows|cut -d: -f1|sort -u|sed "s/msg/ixf/g" >FILE_$$
   
6. while read FILE
   
7. do
   
8. db2 "load from /dev/null of del replace into EASYCORE.${TABLE}2"
   
9. db2 "load from ${FILE} of ixf insert into EASYCORE.${TABLE}2"
   
10. if [ $? -eq 0 ]
    
11. then
    
12. echo "load from ${FILE} of ixf insert into EASYCORE.${TABLE}2" SUCCESS
    
13. break
    
14. fi
    
15. done<FILE_$$
    
16. db2 "export to ${TABLE}.del of del select * from EASYCORE.${TABLE}2"
    
17. db2 "load from ${TABLE}.del of del modified by identityoverride replace into EASYCORE.${TABLE}"
    
18. db2 terminate

复制代码

[ 本帖最后由 Ray001 于 2006-7-6 05:35 编辑 ]

Ray001

处理思路：

1. 在db2move dbname import时，会有一个IMPORT.out的日志。在里面包含有Rejected某个表xx行的信息。据此可以得到TJYRZB的表名和行数。
   
2. 在export的tabxxx.msg文件中，包含有此表的行数。所以通过以下语句
   
3. grep \"$COUNT\" *msg|grep rows|cut -d: -f1|sort -u|sed "s/msg/ixf/g"
   
4. 得到可能包含此表的ixf文件。
   
5. 然后用穷举法从以上列出的几个文件中往TJYRZB2（结构相同，但不含identity字段的表）load。一旦成功则停止load。
   
6. 然后通过export命令用del格式导出。
   
7. 再通过import命令用del格式+identityoverride选项导入TJYRZB。

复制代码

lizhuo

load可以指定要导入的字段,不指定BH字段不就可以了吗
db2 load from filename of del method p(2,3,4,) insert into tabname(字段列表)

unixnewbie

method p(2,3,4)应该不行，因为所有not nullable的column必须在p()中出现.
P
Specifies the field numbers (numbered from 1) of the input data fields to be loaded. Each table column that is not nullable should have a corresponding entry in the METHOD P list. For example, given data fields F1, F2, F3, F4, F5, and F6, and table columns C1 INT, C2 INT NOT NULL, C3 INT NOT NULL, and C4 INT, method P (2, 1, 4, 3) is a valid request, while method P (2, 1) is not valid.