Hi,
打算将DB2 9.7 下某数据库(h9) 的schema honcho通过 dbmove COPY的方式 copy到 DB2 10.5.0.3 的数据库honcho下,操作失败,语法如下:
[db2inst1@xuhz ~]$ db2move h9 copy -sn honcho -co target_db honcho -u db2inst1 -p db2inst1
报错信息为:
Application code page not determined, using ANSI codepage 1208
***** DB2MOVE *****
Action: COPY
Start time: Wed Jun 18 13:26:36 2014
All schema names matching: HONCHO;
Connecting to database H9 ... successful! Server : DB2 Common Server V9.7.3
Copy schema HONCHO to HONCHO on the target database HONCHO
Create DMT : "SYSTOOLS"."DMT_53a1f5fcbab9e"
Rolled back all changes from the create phase (debuginfo:136).
Error Message : sqlcode -204:select obj_qualifier, related_obj_info, sql_stmt,
db2move failed with -1 (debuginfo:50).
Files generated:
-----------------
COPYSCHEMA.20140618132636.msg
Please delete these files when they are no longer needed.
**Error occured -1
End time: Wed Jun 18 13:26:37 2014
db2diag.log日志如下:
2014-06-18-13.26.37.345292-420 I408702E1662 LEVEL: Severe
PID : 4530 TID : 140428167399200 PROC : db2move
INSTANCE: db2inst1 NODE : 000 DB : HONCHO
APPID : 192.168.0.30.45835.140618202637
HOSTNAME: xuhz
FUNCTION: <0>, <0>, <0>, probe:403
MESSAGE : ZRC=0xFFFFFF34=-204
SQL0204N "" is an undefined name.
DATA #1 : String, 99 bytes
[IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "SYSCAT.PERIODS" is an undefined name. SQLSTATE=42704
DATA #2 : String, 1063 bytes
select obj_qualifier, related_obj_info, sql_stmt, case when exists ( select 1 from SYSCAT.PERIODS where (OBJ_SCHEMA,OBJ_NAME) = (HISTORYTABSCHEMA,HISTORYTABNAME) and HISTORYTABSCHEMA <> TABSCHEMA) then 'H' when exists ( select 1 from SYSCAT.PERIODS where (OBJ_SCHEMA,OBJ_NAME) = (TABSCHEMA,TABNAME) and HISTORYTABSCHEMA is not NULL and HISTORYTABSCHEMA <> TABSCHEMA) then 'S' else 'N' end as skipped from SYSTOOLS.DB2LOOK_INFO where OP_TOKEN = ? AND SQL_OPERATION = 'CREATE' and OBJ_TYPE = ? and OBJ_NAME = ? and OBJ_SCHEMA = ?
DATA #3 : signed integer, 4 bytes
-204
DATA #4 : String, 5 bytes
42S02
整理后的语句为:
select obj_qualifier,
related_obj_info,
sql_stmt,
case
when exists (select 1
from SYSCAT.PERIODS
where (OBJ_SCHEMA, OBJ_NAME) =
(HISTORYTABSCHEMA, HISTORYTABNAME)
and HISTORYTABSCHEMA <> TABSCHEMA) then
'H'
when exists (select 1
from SYSCAT.PERIODS
where (OBJ_SCHEMA, OBJ_NAME) = (TABSCHEMA, TABNAME)
and HISTORYTABSCHEMA is not NULL
and HISTORYTABSCHEMA <> TABSCHEMA) then
'S'
else
'N'
end as skipped
from SYSTOOLS.DB2LOOK_INFO
where OP_TOKEN = ?
AND SQL_OPERATION = 'CREATE'
and OBJ_TYPE = ?
and OBJ_NAME = ?
and OBJ_SCHEMA = ?
发现SYSCAT.PERIODS catalog view 在DB2 10.5中才具有,所以想确认一下这种方式是否可行,谢谢~
收起