创建表失败报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同行回答
感谢大家的帮助，经过db2trc，发现原来在syscat.TABAUTH/sysibm.SYSTABAUTH里有表T2的记录，所以创建表的时候报错了。不过貌似系统表无法进行删除更改记录。谁有没有办法？
db2 "delete from sysibm.SYSTABAUTH where TTNAME like '%T2%' and GRANTEE='DB2INST1'"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0607N "DELETE" is not defined for system objects. SQLSTATE=42832
收起
db2 "delete from sysibm.SYSTABAUTH where TTNAME like '%T2%' and GRANTEE='DB2INST1'"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0607N "DELETE" is not defined for system objects. SQLSTATE=42832
2078 | | sqlrlCatalogScan::insert data [probe 60] 2079 | | | sqloclose entry [eduid 18 eduname db2agent] 2080 | | | sqloclose exit 2081 | | | sqloSigMask entry [eduid 18 eduname db2agent] 2082 | | | sqloSigMask exit 2083 | | | sqloSigMask entry [eduid 18 eduname db2agent] 2084 | | | sqloSigMask exit 2085 | pdLogInternal exit 2086 pdLogRC exit 2087 sqlrlCatalogScan::insert exit [rc = 0x80090005 = -2146893819 = SQLI_DUPKEY] 2088 sqlrlInsertTabAuth exit [rc = 0x80090005 = -2146893819 = SQLI_DUPKEY] 2089 sqlofmblkEx entry [eduid 18 eduname db2agent] 2090 sqlofmblkEx mbt [Marker:PD_OSS_FREED_MEMORY ] 2091 sqlofmblkEx exit 2092 sqlofmblkEx entry [eduid 18 eduname db2agent] 2093 sqlofmblkEx mbt [Marker:PD_OSS_FREED_MEMORY ] 2094 sqlofmblkEx exit 2095 sqlofmblkEx entry [eduid 18 eduname db2agent] 2096 sqlofmblkEx mbt [Marker:PD_OSS_FREED_MEMORY ] 2097 sqlofmblkEx exit附上db2trc及db2diag.log：
收起
浏览1460
db2inpe@dmsrvr:~> db2 " create table "T2"(id int) "
DB20000I The SQL command completed successfully.
db2inpe@dmsrvr:~> db2 "create table T2(id int) "
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "F12SITST.T2" of type "TABLE". SQLSTATE=42710
db2inpe@dmsrvr:~> db2 "select * from "T2" "
ID
-----------
0 record(s) selected.
db2inpe@dmsrvr:~> db2 " insert into T2(id) values(2) "
DB20000I The SQL command completed successfully.
db2inpe@dmsrvr:~> db2 "select * from "T2" "
ID
-----------
2
1 record(s) selected.
应该不是这个的缘故收起
DB20000I The SQL command completed successfully.
db2inpe@dmsrvr:~> db2 "create table T2(id int) "
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "F12SITST.T2" of type "TABLE". SQLSTATE=42710
db2inpe@dmsrvr:~> db2 "select * from "T2" "
ID
-----------
0 record(s) selected.
db2inpe@dmsrvr:~> db2 " insert into T2(id) values(2) "
DB20000I The SQL command completed successfully.
db2inpe@dmsrvr:~> db2 "select * from "T2" "
ID
-----------
2
1 record(s) selected.
应该不是这个的缘故收起
浏览1632
有可能第一次创建的表的表名 使用了“T2”，可以尝试select count(*) from "T2",看是否有查询结果 ...tree 发表于 2011-11-29 07:40
同意此观点，可以删除表，重建试试！