创建表失败报SQL0803N错，帮忙看看，谢谢。
数据库版本：DB2/LINUX 9.7.5
症状：创建表报SQL0803N错
db2 "create table DB2INST1.T2(id int,name char(20))"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "" constrains
table "" from having duplicate values for the index key. SQLSTATE=23505
db2diag日志：
2011-11-28-20.06.41.364011+480 I87866246G846 LEVEL: Info
PID : 4919 TID : 2942299040 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-509 APPID: *LOCAL.db2inst1.111128115304
AUTHID : DB2INST1
EDUID : 18 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:670
MESSAGE : ZRC=0x80090005=-2146893819=SQLI_DUPKEY "Duplicate key violation"
DIA8005C Duplicate key encountered, file token "" index token ""
record id "".
DATA #1 : String, 51 bytes
An error was detected during statement compilation.
DATA #2 : String, 163 bytes
Compiler error stack for rc = -2146893819:
sqlnn_cmpl[300]
sqlnp_main[250]
sqlnp_parser[510]
sqlnp_smactn[100]
sqlnq_table_defn1[210]
sqlnq_create_table_end[4700]
尝试检查：
(1)尝试select或drop都会报表未定义
db2 "select * from DB2INST1.T2"
SQL0204N "DB2INST1.T2" is an undefined name. SQLSTATE=42704
(2)使用db2 list tables for all|grep T2 可以查到对应表名
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
T2 DB2INST1 T 2011-11-25-17.00.42.742986
但是使用db2 list tables for schema db2inst1|grep T2 却查不到对应表名
(3)使用表名T2查询SYSCAT.TABLES都没查询到相关记录
请教，怎么解决这个问题？
症状：创建表报SQL0803N错
db2 "create table DB2INST1.T2(id int,name char(20))"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "" constrains
table "" from having duplicate values for the index key. SQLSTATE=23505
db2diag日志：
2011-11-28-20.06.41.364011+480 I87866246G846 LEVEL: Info
PID : 4919 TID : 2942299040 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-509 APPID: *LOCAL.db2inst1.111128115304
AUTHID : DB2INST1
EDUID : 18 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:670
MESSAGE : ZRC=0x80090005=-2146893819=SQLI_DUPKEY "Duplicate key violation"
DIA8005C Duplicate key encountered, file token "" index token ""
record id "".
DATA #1 : String, 51 bytes
An error was detected during statement compilation.
DATA #2 : String, 163 bytes
Compiler error stack for rc = -2146893819:
sqlnn_cmpl[300]
sqlnp_main[250]
sqlnp_parser[510]
sqlnp_smactn[100]
sqlnq_table_defn1[210]
sqlnq_create_table_end[4700]
尝试检查：
(1)尝试select或drop都会报表未定义
db2 "select * from DB2INST1.T2"
SQL0204N "DB2INST1.T2" is an undefined name. SQLSTATE=42704
(2)使用db2 list tables for all|grep T2 可以查到对应表名
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
T2 DB2INST1 T 2011-11-25-17.00.42.742986
但是使用db2 list tables for schema db2inst1|grep T2 却查不到对应表名
(3)使用表名T2查询SYSCAT.TABLES都没查询到相关记录
请教，怎么解决这个问题？
附件：
db2diag1.log (10.84 KB)
filename.flw (632.75 KB)
26同行回答
浏览3507
浏览3475
回复 23# cngd1
版本9.7.5，DDL可以进行回滚。
db2 "update db cfg for sample using LOGARCHMETH1 DISK:/backup/log"
db2stop force
db2start
db2 backup db sample to /backup
db2 connect to sample
db2 "create table test (ID int)"
db2 "insert into test values (1)"
;下面对照我在18楼贴出的步骤。
db2 backup db sample to /backup 备份taken at 20111125161722
db2 connect to sample
db2 "create table t2 (id int,name char(20))"收起
版本9.7.5，DDL可以进行回滚。
db2 "update db cfg for sample using LOGARCHMETH1 DISK:/backup/log"
db2stop force
db2start
db2 backup db sample to /backup
db2 connect to sample
db2 "create table test (ID int)"
db2 "insert into test values (1)"
;下面对照我在18楼贴出的步骤。
db2 backup db sample to /backup 备份taken at 20111125161722
db2 connect to sample
db2 "create table t2 (id int,name char(20))"收起
浏览3555
LZ用的DB2版本是多少？难道这个问题和DB2 9.X 的那个DDL也可以进行回滚的特性有关？另外还想知道在做第一次恢复之前，数据库是正常关闭还是直接通过db2stop force停止实例？收起
浏览3506
浏览3638
试了db2look -d -a -e -x 不能把表T2的DDL取出来。怎么确定是否是orphan row ？ 有没有什么工具可以检查系统表的orphan row？ 尝试用db2ckupgrade检查数据库看是否报SQL1344N Orphan rows found in the system catalogs.的错，执行不成功，好像是在migrate时使用的。
报错：
The db2ckupgrade command must be issued on the downlevel instance prior to upgrading the instance.
问题只是出现在自己学习的sample上的，所以没法找IBM support呀，问题出现背景：
前几天测试前滚后就发现这个问题了，之前的步骤：
db2 backup db sample to /backup 备份taken at 20111125161722
db2 connect to sample
db2 "create table t2 (id int,name char(20))"
db2 "begin atomic declare i int default 0; while (i<1000) do insert into t2 values(i,'name'||char(i)); set i=i+1 ; end while ; end"
第一次恢复，恢复前滚到最小恢复时间点
db2 restore db sample from /backup taken at 20111125161722
db2 rollforward db sample to 2011-11-25-16.17.30.000000 using local time and stop
第二次恢复，恢复到日志末尾：
db2 restore db sample from /backup taken at 20111125161722
db2 "rollforward db sample to end of logs and stop overflow log path (\"/backup/log/db2inst1/SAMPLE/NODE0000/C0000001\")"收起
报错：
The db2ckupgrade command must be issued on the downlevel instance prior to upgrading the instance.
问题只是出现在自己学习的sample上的，所以没法找IBM support呀，问题出现背景：
前几天测试前滚后就发现这个问题了，之前的步骤：
db2 backup db sample to /backup 备份taken at 20111125161722
db2 connect to sample
db2 "create table t2 (id int,name char(20))"
db2 "begin atomic declare i int default 0; while (i<1000) do insert into t2 values(i,'name'||char(i)); set i=i+1 ; end while ; end"
第一次恢复，恢复前滚到最小恢复时间点
db2 restore db sample from /backup taken at 20111125161722
db2 rollforward db sample to 2011-11-25-16.17.30.000000 using local time and stop
第二次恢复，恢复到日志末尾：
db2 restore db sample from /backup taken at 20111125161722
db2 "rollforward db sample to end of logs and stop overflow log path (\"/backup/log/db2inst1/SAMPLE/NODE0000/C0000001\")"收起
浏览3777