ALTER TABLE< TABLE> DROP UNIQUE< INDNAME>无法识别索引名称！ [英] ALTER TABLE <TABLE> DROP UNIQUE <INDNAME> doesn't recognize index name!

问题描述

平台是DB2 / NT 7.2.9

表格是这样创建的：

CREATE TABLE MYTEST（

MYTESTOID bigint not null主键，

FK_OTHEROID bigint not null引用其他，

FK_ANOTHEROID bigint not null引用另一个，

FK_LASTLYOID bigint not null引用最后，

唯一（FK_OTHEROID，FK_ANOTHEROID））


所以我现在在SYSCAT.INDEXES中显示两个自动命名的索引：

INDSCHEMA INDNAME DEFINER TABSCHEMA TABNAME COLNAMES UNIQUERULE

SYSIBM SQL040102143652040管理员MYSCHEMA MYTEST + MYTESTOID P

SYSIBM

SQL040102143652150管理员MYSCHEMA MYTEST + FK_OTHEROID + FK_ANOTHEROID几个月后，我们发现OOPS，FK_LASTLYOID也应该已经包含在这个独特的约束中了！


好​​了，所以我们无法修改索引，我们必须将ALTER TABLE删除它，然后再添加一个新的
。我很好。


ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150

错误：COM.ibm.db2.jdbc.DB2Exception：[IBM ] [CLI驱动程序] [DB2 / NT]

SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704


嗯。也许它不喜欢我当前的架构？


ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SYSIBM.SQL040102143652150

错误：COM.ibm.db2.jdbc .DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT]

SQL0108N名称SQL040102143652150有错误数量的

资格赛。 SQLSTATE = 42601


好​​的，不能限定架构，我会将当前架构设置为SYSIBM。


SET CURRENT SCHEMA SYSIBM


ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150

错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI Driver] [DB2 / NT]

SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704


SET CURRENT SCHEMA MYSCHEMA


ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150

错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT]

SQL0204NSQL040102143652150是一个未定义的名称。 SQLSTATE = 42704


没有区别。我猜它应该能够推断出指数

架构。


也许它被视为约束？


ALTER TABLE MYSCHEMA.MYTEST DROP CONSTRAINT SQL040102143652150

错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT]

SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704

大鼠。我究竟做错了什么？我很确定这是正确的索引

名称。


我不是数据库管理员，所以请给出SQL示例！如果唯一的答案是

是删除表格，修改索引，然后重新加载 （而且我真的希望

它不是），告诉我如何做到这一点！ :-)我只是一个愚蠢的程序员...


谢谢！

Platform is DB2/NT 7.2.9

The table was created like this:
CREATE TABLE MYTEST (
MYTESTOID bigint not null primary key,
FK_OTHEROID bigint not null references other,
FK_ANOTHEROID bigint not null references another,
FK_LASTLYOID bigint not null references lastly,
unique (FK_OTHEROID,FK_ANOTHEROID))

So I now have two auto-named indexes showing in SYSCAT.INDEXES:
INDSCHEMA INDNAME DEFINER TABSCHEMA TABNAME COLNAMES UNIQUERULE
SYSIBM SQL040102143652040 ADMINISTRATOR MYSCHEMA MYTEST +MYTESTOID P
SYSIBM
SQL040102143652150 ADMINISTRATOR MYSCHEMA MYTEST +FK_OTHEROID+FK_ANOTHEROID U

A few months later, we find out that, OOPS, FK_LASTLYOID should ALSO
have been included in the unique constraint!

OK, so we cannot modify the index, we must ALTER TABLE to drop it, then
add a new one. I''m fine with that.

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704

Hmmm. Maybe it doesn''t like my current schema?

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SYSIBM.SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0108N The name "SQL040102143652150" has the wrong number of
qualifiers. SQLSTATE=42601

OK, can''t qualify the schema, I''ll set current schema to SYSIBM.

SET CURRENT SCHEMA SYSIBM

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704

Try setting current schema to the table schema?

SET CURRENT SCHEMA MYSCHEMA

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704

No difference. I guess it is supposed to be able to infer the index
schema.

Maybe it is treated as a Constraint?

ALTER TABLE MYSCHEMA.MYTEST DROP CONSTRAINT SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704

Rats. What am I doing wrong? I''m pretty sure that is the correct index
name.

I am NOT a DB admin, so please, give SQL examples! If the only answer
is "Drop the table, modify the index, and reload it" (and I REALLY hope
it isn''t), show me HOW to do that! :-) I''m just a dumb programmer...

Thanks!

推荐答案

在文章< 11 ********************** @ g44g2000cwa.googlegroups .com> ;,

BuddhaBuddy（Bu * ********@hotmail.com）说...

In article <11**********************@g44g2000cwa.googlegroups .com>,
BuddhaBuddy (Bu*********@hotmail.com) says...

平台是DB2 / NT 7.2.9

表是这样创建的：
CREATE TABLE MYTEST（
MYTESTOID bigint not null主键，
FK_OTHEROID bigint not null引用其他，
FK_ANOTHEROID bigint not null引用另一个，
FK_LASTLYOID bigint not null引用最后，
独特（FK_OTHEROID，FK_ANOTHEROID））

Platform is DB2/NT 7.2.9

The table was created like this:
CREATE TABLE MYTEST (
MYTESTOID bigint not null primary key,
FK_OTHEROID bigint not null references other,
FK_ANOTHEROID bigint not null references another,
FK_LASTLYOID bigint not null references lastly,
unique (FK_OTHEROID,FK_ANOTHEROID))

''alter table mytest drop unique< indexname>''应该有效，它确实是

从命令行工作。也许这是java驱动程序中的一个错误？

也许安装最新的fixpack会有所帮助。


我更喜欢定义命名约束，比如

''约束FK_CONSTRAINT唯一（FK_OTHEROID，FK_ANOTHEROID）''

你可以放弃它而没有问题

''alter table mytest drop constraint FK_CONSTRAINT''

所以你不必知道系统生成了哪个名字。

''alter table mytest drop unique <indexname>'' should work, it does
work from the command line. Maybe it''s a bug in the java driver?
Maybe installing the latest fixpack will help.

I prefer to define a named constraint, something like
''constraint FK_CONSTRAINT unique (FK_OTHEROID,FK_ANOTHEROID)''
You can drop it without a problem with
''alter table mytest drop constraint FK_CONSTRAINT''
so you don''t have to find out which name was generated by the system.

BuddhaBuddy写道：

BuddhaBuddy wrote:

平台是DB2 / NT 7.2.9

表是这样创建的：
CREATE TABLE MYTEST（
MYTESTOID bigint not null主键， FK_OTHEROID bigint not null引用其他，
FK_ANOTHEROID bigint not null引用另一个，
FK_LASTLYOID bigint最后不引用引用，
唯一（FK_OTHEROID，FK_ANOTHEROID））

所以我现在在SYSCAT.INDEXES中显示两个自动命名的索引：
INDSCHEMA IN DNAME DEFINER TABSCHEMA TABNAME COLNAMES UNIQUERULE
SYSIBM SQL040102143652040管理员MYSCHEMA MYTEST + MYTESTOID P
SYSIBM
SQL040102143652150管理员MYSCHEMA MYTEST + FK_OTHEROID + FK_ANOTHEROID U

几个月后，我们要弄清楚，OOPS，FK_LASTLYOID也应该包含在唯一的约束中！

好的，所以我们无法修改索引，我们必须将ALTER TABLE删除它，然后
添加一个新的。我没关系。

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150
错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI Driver] [DB2 / NT]
SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704

嗯。也许它不喜欢我当前的架构？

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SYSIBM.SQL040102143652150
错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT]
SQL0108N名称SQL040102143652150有多少
限定符。 SQLSTATE = 42601

好的，不能限定架构，我会将当前架构设置为SYSIBM。

SET CURRENT SCHEMA SYSIBM

> ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150
错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT]
SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704

尝试将当前架构设置为表架构？

SET CURRENT SCHEMA MYSCHEMA

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150 SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704

没有区别。我猜它应该能够推断索引
架构。

也许它被视为约束？

ALTER TABLE MYSCHEMA.MYTEST DROP CONSTRAINT SQL040102143652150
错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT]
SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704

老鼠。我究竟做错了什么？我很确定这是正确的索引
名称。

我不是数据库管理员，所以请给出SQL示例！如果唯一的答案是删除表，修改索引，然后重新加载。 （我真的希望
不是），告诉我如何做到这一点！ :-)我只是一个愚蠢的程序员...

谢谢！

Platform is DB2/NT 7.2.9

The table was created like this:
CREATE TABLE MYTEST (
MYTESTOID bigint not null primary key,
FK_OTHEROID bigint not null references other,
FK_ANOTHEROID bigint not null references another,
FK_LASTLYOID bigint not null references lastly,
unique (FK_OTHEROID,FK_ANOTHEROID))

So I now have two auto-named indexes showing in SYSCAT.INDEXES:
INDSCHEMA INDNAME DEFINER TABSCHEMA TABNAME COLNAMES UNIQUERULE
SYSIBM SQL040102143652040 ADMINISTRATOR MYSCHEMA MYTEST +MYTESTOID P
SYSIBM
SQL040102143652150 ADMINISTRATOR MYSCHEMA MYTEST +FK_OTHEROID+FK_ANOTHEROID U

A few months later, we find out that, OOPS, FK_LASTLYOID should ALSO
have been included in the unique constraint!

OK, so we cannot modify the index, we must ALTER TABLE to drop it, then
add a new one. I''m fine with that.

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704

Hmmm. Maybe it doesn''t like my current schema?

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SYSIBM.SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0108N The name "SQL040102143652150" has the wrong number of
qualifiers. SQLSTATE=42601

OK, can''t qualify the schema, I''ll set current schema to SYSIBM.

SET CURRENT SCHEMA SYSIBM

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704

Try setting current schema to the table schema?

SET CURRENT SCHEMA MYSCHEMA

ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704

No difference. I guess it is supposed to be able to infer the index
schema.

Maybe it is treated as a Constraint?

ALTER TABLE MYSCHEMA.MYTEST DROP CONSTRAINT SQL040102143652150
Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]
SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704

Rats. What am I doing wrong? I''m pretty sure that is the correct index
name.

I am NOT a DB admin, so please, give SQL examples! If the only answer
is "Drop the table, modify the index, and reload it" (and I REALLY hope
it isn''t), show me HOW to do that! :-) I''m just a dumb programmer...

Thanks!

你需要找出唯一约束的名称（这是可能

与索引名称不同）。然后ALTER TABLE DROP约束。

我的猜测是DB2也将丢弃唯一索引。如果没有，你可以使用

DROP INDEX摆脱它。


干杯

Serge

-

Serge Rielau

DB2 SQL编译器开发

IBM多伦多实验室

You need to find out the name of the unique constraint (which is likely
different than the index name). Then ALTER TABLE DROP the constraint.
My guess is that DB2 will also drop the unique index. If not you can use
DROP INDEX to get rid of it.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

我同意它应该有效，但事实并非如此。它不是我的Java DB工具

（SQuirreL SQL）。以下是我的CLP会话的副本：


（c）版权所有IBM Corporation 1993,2001

DB2 SDK 7.2.9的命令行处理器


您可以从

命令

提示中发出数据库管理器命令和SQL语句。例如：

db2 =>连接样品

db2 =>绑定sample.bnd


如需一般帮助，请键入：？。

要获得命令帮助，请键入：？命令，其中命令可以是数据库管理器命令的前几个关键字。例如：

？ CATALOG DATABASE获取有关CATALOG DATABASE命令的帮助

？ CATALOG获取有关所有CATALOG命令的帮助。


要退出db2交互模式，请在命令提示符下键入QUIT。在外部

交互模式下，所有命令都必须以''db2'为前缀。

要列出当前命令选项设置，请键入LIST COMMAND OPTIONS。


如需更详细的帮助，请参阅在线参考手册。


db2 =>连接到mydb


数据库连接信息

数据库服务器= DB2 / NT 7.2.9

SQL授权ID = ADMINISTRATOR

本地数据库别名= MYDB


db2 => create table other（otheroid bigint not null primary key，data

varchar（10）

）

DB20000I SQL命令成功完成。 />
db2 =>另一个创建表（另一个bigint不是null主键，

数据varchar

（10））

DB20000I SQL命令成功完成。 />
db2 =>最后创建表（lastlyoid bigint not null主键，数据

varchar（1

0））

DB20000I SQL命令成功完成。

db2 => create table mytest（mytestoid bigint not null主键，

fk_otheroid bi

gint not null引用其他，fk_anotheroid bigint not null

引用另一个

，fk_lastlyoid bigint最后不是null引用，

unique（fk_otheroid，fk_anothero

id））

DB20000I SQL命令成功完成。

db2 =>选择indschema，indname，definer，tabschema，tabname，

colnames，uniquer

ule from syscat.indexes where tabname =''MYTEST''

>
INDSCHEMA

INDNAME

DEFINER


TABSCHEMA


TABNAME

COLNAMES



UNIQUERULE

------------ -------------------------------------------------- ------------------

------------------------ ------------------------ ------------------

------------

------------------------------ --------------------------------------------------

------------------------------------

-------------------------------------------

----------------------------------------------- ---------------------------------

-----

---------------------------------------------- ----------------------------

-------------- ----------------------------------------

- -----------------------

------------------- -------------------------------------------------- -----------

------------------------------- -------------------------------------------------

------------------------------------------- -------------------------------------

----- -------------------------------------------------- -------------------------

----------------- -------------------------------------------------- -------------

----------------------------- -------------------------------------------------- -

----------------------------------------- ---------------------------------------

--- -------------------------------------------------- - ----------

SYSIBM

SQL051026135325270

ADMINISTRATOR


管理员


MYTEST

+ MYTESTOID



P

SYSIBM

SQL051026135325490

ADMINISTR ATOR


管理员


MYTEST


+ FK_OTHEROID + FK_ANOTHEROI

D


U


2条记录被选中。


db2 => alter table mytest drop unique SQL051026135325490

DB21034E该命令作为SQL语句处理，因为它是

而不是
有效的命令行处理器命令。在SQL处理期间它返回了
：

SQL0204N" SQL051026135325490"是一个未定义的名称。 SQLSTATE = 42704

我同意使用命名约束会让名字更容易找到名字

，但我找到了名字，我知道它是正确的，但我不能

放弃了这个糟糕的东西！


以下是我用来测试它的命令：


create table other（otheroid bigint not null主键，数据

varchar（10））

另外创建表格（anotheroid bigint not null主键，数据

varchar（10））


最后创建表（lastlyoid bigint not null主键，数据

varchar（10））


创建表mytest（mytestoid bigint not null主键，fk_otheroid

bigint not null引用其他，fk_anotheroid bigint not null

引用另一个，fk_lastlyoid bigint not not最后为null引用，

unique（fk_otheroid，fk_anotheroid））

从syscat.indexes中选择indname，其中tabname =''MYTEST''和

colnames =''+ FK_OTHEROID + FK_ANOTHEROID''


alter table mytest drop unique<使用上一个查询的结果>


如果有效，请告知我们系统。如果确实如此，那么我们的DB2

安装就会被搞砸了。我相信我们运行1.7.2与

FP14_WR21354。

I agree that it should work, but it isn''t. It isn''t my Java DB tool
(SQuirreL SQL) either. Here is a copy of my CLP session:

(c) Copyright IBM Corporation 1993,2001
Command Line Processor for DB2 SDK 7.2.9

You can issue database manager commands and SQL statements from the
command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with ''db2''.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to mydb

Database Connection Information

Database server = DB2/NT 7.2.9
SQL authorization ID = ADMINISTRATOR
Local database alias = MYDB

db2 => create table other(otheroid bigint not null primary key, data
varchar(10)
)
DB20000I The SQL command completed successfully.
db2 => create table another(anotheroid bigint not null primary key,
data varchar
(10))
DB20000I The SQL command completed successfully.
db2 => create table lastly(lastlyoid bigint not null primary key, data
varchar(1
0))
DB20000I The SQL command completed successfully.
db2 => create table mytest(mytestoid bigint not null primary key,
fk_otheroid bi
gint not null references other, fk_anotheroid bigint not null
references another
, fk_lastlyoid bigint not null references lastly,
unique(fk_otheroid,fk_anothero
id))
DB20000I The SQL command completed successfully.
db2 => select indschema, indname, definer, tabschema, tabname,
colnames, uniquer
ule from syscat.indexes where tabname=''MYTEST''

INDSCHEMA
INDNAME
DEFINER

TABSCHEMA

TABNAME
COLNAMES



UNIQUERULE
--------------------------------------------------------------------------------
------------------------------------------------ ------------------
------------
--------------------------------------------------------------------------------
------------------------------------
-------------------------------------------
--------------------------------------------------------------------------------
-----
--------------------------------------------------------------------------
------------------------------------------------------
-------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------- ----------
SYSIBM
SQL051026135325270
ADMINISTRATOR

ADMINISTRATOR

MYTEST
+MYTESTOID



P
SYSIBM
SQL051026135325490
ADMINISTRATOR

ADMINISTRATOR

MYTEST

+FK_OTHEROID+FK_ANOTHEROI
D


U

2 record(s) selected.

db2 => alter table mytest drop unique SQL051026135325490
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "SQL051026135325490" is an undefined name. SQLSTATE=42704
I agree that using named constraints would make finding the name
easier, but I have FOUND the name, I KNOW it is correct, but I cannot
drop the darn thing!

Here are the commands I used to test this:

create table other(otheroid bigint not null primary key, data
varchar(10))

create table another(anotheroid bigint not null primary key, data
varchar(10))

create table lastly(lastlyoid bigint not null primary key, data
varchar(10))

create table mytest(mytestoid bigint not null primary key, fk_otheroid
bigint not null references other, fk_anotheroid bigint not null
references another, fk_lastlyoid bigint not null references lastly,
unique(fk_otheroid,fk_anotheroid))

select indname from syscat.indexes where tabname=''MYTEST'' and
colnames=''+FK_OTHEROID+FK_ANOTHEROID''

alter table mytest drop unique <use results of previous query>

Please let me know if it works on your system. If it does, then our DB2
installs are royally messed up. I believe we run 1.7.2 with
FP14_WR21354.

这篇关于ALTER TABLE&lt; TABLE&gt; DROP UNIQUE&lt; INDNAME&gt;无法识别索引名称！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！