ALTER TABLE <TABLE> DROP UNIQUE <INDNAME> doesn't recognize index name!

[BuddhaBuddy]

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!

[Gert van der Kooij]

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

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>' 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.

[Serge Rielau]

BuddhaBuddy wrote:

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!

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

[BuddhaBuddy]

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.

[BuddhaBuddy]

WooHoo! I didn't realize it was treated as a separate Constraint and
not just an Index.

I didn't see anything in the SYSCAT views, but SYSIBM.SYSTABCONST had
them:

select * from sysibm.systabconst where tbname='MYTEST'

Unfortunately, there is nothing there to indicate which Constraint is
attached to my Index. Fortunately, there is only one who's
CONSTRAINTYP='U' (as opposed to P for Primary and F for Foreign Key,
I'm assuming). After dropping that Constraint, my Index went away...
Thanks!

[BuddhaBuddy]

DROP INDEX won't work because it was a UNIQUE Index, or some such
thing. In any case, when I tried that, it gave an error.

[IBM][CLI Driver][DB2/NT] SQL0669N A system required index cannot be
dropped explicitly. SQLSTATE=42917

Everything I read about that error says that you have to use ALTER
TABLE DROP when this happens.

So, I'm happy that I finally got rid of my Index, but is there some
View in SYSCAT that displays Constraints and which Indexes are attached
to them? SYSIBM.SYSTABCONST really doesn't show much about what a
particular Constraint is really ABOUT, other than CONSTRAINTYP, but if
you have two FKs, there is no way to tell which FK a particular
Constraint is about.

[Matt Emmerton]

"BuddhaBuddy" <Bu*********@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

DROP INDEX won't work because it was a UNIQUE Index, or some such
thing. In any case, when I tried that, it gave an error.

[IBM][CLI Driver][DB2/NT] SQL0669N A system required index cannot be
dropped explicitly. SQLSTATE=42917

Everything I read about that error says that you have to use ALTER
TABLE DROP when this happens.

So, I'm happy that I finally got rid of my Index, but is there some
View in SYSCAT that displays Constraints and which Indexes are attached
to them? SYSIBM.SYSTABCONST really doesn't show much about what a
particular Constraint is really ABOUT, other than CONSTRAINTYP, but if
you have two FKs, there is no way to tell which FK a particular
Constraint is about.

What about sysibm.sqlforeignkeys?

--
Matt Emmerton

[BuddhaBuddy]

First off, that doesn't even exist in my DB. I don't see anything like
it in SYSIBM's Tables or SYSCAT's Views.

Even if it did, if it were like sysibm.sysindexes, it wouldn't name the
Constraint that the FK belongs to. My problem is that if I had more
than one Unique Constraint on my table (luckily, I did not), I would
not know which Constraint to drop in order to drop my problematic
Index. Since I DID only have one Unique Constraint, I knew which one to
drop, but if I had more, I wouldn't know how to decide which Unique
Constraint to drop.

Just so it is clear, my immediate problem is solved. Now, the
discussion (for me, anyway) is out of curiosity.

[Ian]

BuddhaBuddy wrote:

First off, that doesn't even exist in my DB. I don't see anything like
it in SYSIBM's Tables or SYSCAT's Views.

Even if it did, if it were like sysibm.sysindexes, it wouldn't name the
Constraint that the FK belongs to. My problem is that if I had more
than one Unique Constraint on my table (luckily, I did not), I would
not know which Constraint to drop in order to drop my problematic
Index. Since I DID only have one Unique Constraint, I knew which one to
drop, but if I had more, I wouldn't know how to decide which Unique
Constraint to drop.

Just so it is clear, my immediate problem is solved. Now, the
discussion (for me, anyway) is out of curiosity.

See SYSCAT.CONSTDEP. It contains the table name, constraint name and
the name of the index that the constraint requires.

Note, do you really care about name of the index when it's created by
the system as a result of a constraint (like a PK, unique constraint,
etc)? As you've found, you can't drop this index, and can only drop
the constraint.

[BuddhaBuddy]

No, I don't really care about the index name, but I DO care about what
the index DOES. If I have two of these Indexes, and I only want to drop
one of them, I can't tell which which one to drop by only looking at
the Constraint info. It only tells me the schema and table name. I
still don't have enough info to determine which Constraint to drop.

However, now that I know that SYSCAT.CONSTDEP links the two, I can run
the following query to find out which Constraint is associated with
which Index, and know exactly which Constraint to drop.

select i.colnames, c.constname from syscat.indexes i, syscat.constdep c
where i.tabname='MYTEST' and i.indname=c.bname and
i.indschema=c.bschema

That easily lets me see which Constraint is tied to which columns, and
when I drop that Constraint, the Index goes away, too.

[Serge Rielau]

SYSCAT.KEYCOLUSE is what you are really looking for.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab