472,789 Members | 877 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,789 software developers and data experts.

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

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!

Nov 12 '05 #1
10 25885
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.
Nov 12 '05 #2
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
Nov 12 '05 #3
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.

Nov 12 '05 #4
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!

Nov 12 '05 #5
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.

Nov 12 '05 #6

"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

Nov 12 '05 #7
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.

Nov 12 '05 #8
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.

Nov 12 '05 #9
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.

Nov 12 '05 #10
SYSCAT.KEYCOLUSE is what you are really looking for.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Adam Fortuno | last post by:
Was in the midst of doing something today and I attempted to drop a number of columns in a table with the following: ALTER TABLE tmp DROP COLUMN col_1, col_2, col_3, col_4; Unfortunately MySQL...
3
by: M Simpson | last post by:
/* for the google index */ ALTER TABLE DEFAULT COLUMN DEFAULT VALUE I've worked out several stored procedures for altering the default column values in a table. They were compiled from books...
1
by: Rajesh Kumar Mallah | last post by:
Hi , Looks like ADD UNIQUE( some_fuc( some_feild) ) is not supported with add constraint. the only way is to add the constriant is using UNIQUE INDEX . Is it a bug or intended behaviour? ...
5
by: Mike L | last post by:
Hello all I'm trying to migrate a BE from Access to SQL Server. I've been making changes to the Access BE from the FE with SQL statements, and want to do the same with the BE in SQL Server. ...
1
by: NKN | last post by:
Hi all, Does anybody know how could I manage to do a sentence like this... ALTER TABLE Table1 DROP COLUMN Col1 after doing a DML sentence like and UPDATE, INSERT or DELETE. I'm using ADO...
4
by: @ndy | last post by:
I import an excel sheet to access and know i won't to delete some columns. This my code that doesn't work. strSQLAlter = "ALTER TABLE ImpTableB DROP COLUMN Dossier-beheerder" DoCmd.RunSQL...
2
by: RamaKrishna Narla | last post by:
In MS SQL Server, I have the following tables with some data in it. create table table1 ( column1 varchar(32), column2 int not null, column10 varchar(255), ..... primary key (column1,...
1
by: vasilip | last post by:
I'm testing out db2 for a project I'm starting that requires proper xml support and I can't seem to get both xml and spatial data to work well in the same table. Once having created a table...
3
by: Gregor Kovańć | last post by:
Hi! I have a table with a column of BIGINT and I want to change the type to SMALLINT. Command ALTER TABLE TABLE1 ALTER COLUMN COL1 SET DATA TYPE SMALLINT does not work since I cannot specify...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.