By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,702 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

does drop table command also drop indexes on it?

P: n/a
Hello,
I always assumed that dropping table will drop everything associated
with it like indexes, references etc.
I just noticed that after dropping table A and recreating it and then
creating index on it gave me error

DROP TABLE tmp
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 "TMP" is an undefined name. SQLSTATE=42704

CREATE TABLE tmp ( a INTEGER, b CHAR(2), c CHAR(2) )
DB20000I The SQL command completed successfully.

CREATE UNIQUE INDEX tmp_ix ON tmp (a)
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 "tmp_ix" of type "INDEX". SQLSTATE=42710

please explain ?

Feb 9 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
db2admin wrote:
Hello,
I always assumed that dropping table will drop everything associated
with it like indexes, references etc.
I just noticed that after dropping table A and recreating it and then
creating index on it gave me error

DROP TABLE tmp
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 "TMP" is an undefined name. SQLSTATE=42704

CREATE TABLE tmp ( a INTEGER, b CHAR(2), c CHAR(2) )
DB20000I The SQL command completed successfully.

CREATE UNIQUE INDEX tmp_ix ON tmp (a)
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 "tmp_ix" of type "INDEX". SQLSTATE=42710

please explain ?
Check for that index. Maybe it belongs to another table and NOT the one
you just dropped.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 9 '07 #2

P: n/a
db2admin wrote:
Hello,
I always assumed that dropping table will drop everything associated
with it like indexes, references etc.
Yes they are dropped.
I just noticed that after dropping table A and recreating it and then
creating index on it gave me error

DROP TABLE tmp
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 "TMP" is an undefined name. SQLSTATE=42704

CREATE TABLE tmp ( a INTEGER, b CHAR(2), c CHAR(2) )
DB20000I The SQL command completed successfully.

CREATE UNIQUE INDEX tmp_ix ON tmp (a)
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 "tmp_ix" of type "INDEX". SQLSTATE=42710
Index names have to be unique within a schema - not at the table level. So
I guess you have a TMP_IX index on some other table.
$ db2 "create table t ( a int, b int)"
DB20000I The SQL command completed successfully.
$ db2 "create unique index idx on t(a)"
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from syscat.indexes where indname = 'IDX'"

1
-----------
1

1 record(s) selected.

$ db2 drop table t
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from syscat.indexes where indname = 'IDX'"

1
-----------
0

1 record(s) selected.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 9 '07 #3

P: n/a
On Feb 9, 9:50 am, Knut Stolze <sto...@de.ibm.comwrote:
db2admin wrote:
Hello,
I always assumed that dropping table will drop everything associated
with it like indexes, references etc.

Yes they are dropped.
I just noticed that after dropping table A and recreating it and then
creating index on it gave me error
DROP TABLE tmp
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 "TMP" is an undefined name. SQLSTATE=42704
CREATE TABLE tmp ( a INTEGER, b CHAR(2), c CHAR(2) )
DB20000I The SQL command completed successfully.
CREATE UNIQUE INDEX tmp_ix ON tmp (a)
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 "tmp_ix" of type "INDEX". SQLSTATE=42710

Index names have to be unique within a schema - not at the table level. So
I guess you have a TMP_IX index on some other table.

$ db2 "create table t ( a int, b int)"
DB20000I The SQL command completed successfully.
$ db2 "create unique index idx on t(a)"
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from syscat.indexes where indname = 'IDX'"

1
-----------
1

1 record(s) selected.

$ db2 drop table t
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from syscat.indexes where indname = 'IDX'"

1
-----------
0

1 record(s) selected.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Yes
I was wrong
it was index on different table
thanks for help

Feb 9 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.