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 ?
3  20305 
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Rod Davis |
last post by:
Once a week, we run a script to drop all the indexes on tables in our
db, followed by another to add those indexes back (we insert every
day, so we do this to prevent index fragmenttation).
The database is named BX, and the script runs these two commands:
c:\mysql\bin\mysql BX < dropIndex.sql
c:\mysql\bin\mysql BX < addIndex.sql
We're running version 3.23.29 on Windows 2000.
|
by: Jay |
last post by:
Hi I have a huge table with over 100million records and on regular
basis ineed to delete nearly a million records and insert a million
records.
Currently I delete indexes before going through the process and
recreate the indexes which takes a very very long time.
IS there a way to disable indexes and re enable them after doing
insert and delete by reindexing or anything of that sort?
OR
Is there an approach to append two tables with...
|
by: BuddhaBuddy |
last post by:
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))
|
by: Rajesh Kumar Mallah |
last post by:
Hi,
Looks like alter table does not tells about the indexes it dropped
PG version: 7.4.3
Regds
mallah.
|
by: ruben |
last post by:
Hi:
I'm running a large database on PostgreSQL 7.1.3. 20 days ago the
database failed with a threatening and not too descriptive error like:
pg_exec() query failed: server closed the connection unexpectedlyThis
probably means the server terminated abnormally before or while
processing the request.
I lost some data and had to recreate a table, without knowing the reason
| | |
by: Challenge |
last post by:
Hi,
I have a table creation script that generated by ERWin. An index name
is too long and has been truncated as "XPKsp_prj_stk_ord_". Now I want
to drop and recreate it as another name. I got error when I drop it:
SQL0204N "SPARADM.XPKsp_prj_stk_ord_" is an undefined name.
SQLSTATE=42704
How can I drop it?
|
by: Gregor Kovač |
last post by:
Hi!
Is it possible do to something like this:
DROP TABLE MY_TABLE ONLY IF EXISTS
and the same for views, indexes, procedures, .... ?
Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
|
by: rcamarda |
last post by:
Hi,
I found this SQL in the news group to drop indexs in a table. I need a
script that will drop all indexes in all user tables of a given
database:
DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)
DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
|
by: dsdevonsomer |
last post by:
Hello all,
I am new to managing indexes on large tables and need some help.
Hopefully, I am not repeating question here. I searched as much as I
can, but not finding relatively best answer..
Here is my scenario.
I have 2 tables with more than 4 mil rows (1 - 2.8 Mil, 2 2.1 mil). Of
these tables, there are about 25 ( select ) queries run to help
generate reports every week.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
