All,

We are getting one error while drop/create(ing) index.
Sequence of execution is in following order.
1-Drop index indexname
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 "indexname" is an undefined name. SQLSTATE=42704

2-Create index indexname on tablename(columnname1,columnname2..)
Running successfully.

Would like to know the cause for the same.

As the statements executed on daily basis.Then how drop command is saying index undefined while create command has successfully created it.

I am new to DB2.Hence I am not having the detail knowledge of DB2 database.

Thank you

Regards
Baseet Ahmed
&&&&&&&&&&&&&&&&&&&&&&&&
Value of Parents:
Jannat(Heaven) is under the feet of Mother.
Father is the middle gate of Jannat(Heaven).
&&&&&&&&&&&&&&&&&&&&&&&&

The message on the first statement says the index named "indexname" doesn't exist. That's pretty clear. The creation succeeds, of course, because the index doesn't exist and no name collision exists.

I guess you have a different question, however: where has the index gone between the one execution of the script and the next (one day later). And that is something you can only figure out when looking at your environment. For example, it may be that:

• the index could not be created the previous day,
• someone else explicitly dropped the index,
• you run the script under different user-ids and do not use fully qualified table or index names (so that the user id is used for qualifying such names)
• ...

check whether the index you are trying to drop exists
check whether you are inputting the correct index name
check whether you are in the proper schema.... in this case you can try drop index schema.indexname

After looking at the database,the index is there.

But don't know why drop is giving such error even we have used proper index name and schema.



Thank you

Regards
Baseet Ahmed
&&&&&&&&&&&&&&&&&&&&&&&&
Value of Parents:
Jannat(Heaven) is under the feet of Mother.
Father is the middle gate of Jannat(Heaven).
&&&&&&&&&&&&&&&&&&&&&&&&

Please tell us how you verified that the index is there and what's the exact DROP statement that you tried along with the error message. (I think you may have abstracted from the reality in the initial post.)

The most likely reason is that the index was created in an alternate universe and does not exist in the universe where you are trying to drop it. Maybe when you get to Heaven you will be able to drop it.

Q:Please tell us how you verified that the index is there
A:We have checked the index existence through Control Center

Q:I think you may have abstracted from the reality in the initial post
A:May be your perception.

Q:The most likely reason is that the index was created in an alternate universe and does not exist in the universe where you are trying to drop it.
A:No,we are dropping and creating the index in the same database with the same Schema.

Q:Maybe when you get to Heaven you will be able to drop it
A: INSHA-ALLAH


Problem Explanation:
We are having one script which gets executed daily.
In this, we have written drop and create commands for the same index name with same schema connecting with one single Database.

After looking at the output of script,it gives error log after drop command but not after create command. This is happening daily.

Now, would like to know what could be the problem ?


Thank you

Regards
Baseet Ahmed
&&&&&&&&&&&&&&&&&&&&&&&&
Value of Parents:
Jannat(Heaven) is under the feet of Mother.
Father is the middle gate of Jannat(Heaven).
&&&&&&&&&&&&&&&&&&&&&&&&

Probably you script is written to drop each object before it is created. If you get an error on the drop, then it does not exist at that point in time (even if it existed before the script ran). Maybe the reason is that you dropped the table (which drops all the indexes) earlier in the script.

The problem is that you refuse to give us the answers for the questions we raised: please explain in detail what you have done, i.e. show the script, show screenshots how you verified that the index exists, etc.

Also, with the script you should scale it down as much as possible to still exhibit the problem but not include things irrelevant to your question.

Please find attached the screenshot of control center for showing index existence.(As stolze thought that, I am refusing the question).
Secondly, about problem once again,we have made one sql script which is kept in the schedular(for daily execution).
But as I said the drop command problem.If it happened only first time,then I can understand that due to index absense,it is saying undefined name. But as create command is perfectly creating it.Then,why next day it is not dropping it.
Note that we are not firing any drop statements apart from this script.
Code snippets from the script:
......
DROP INDEX "Schema1"."REOPNED_ON_IX"
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 "Schema1.REOPNED_ON_IX" is an undefined name. SQLSTATE=42704

create index "Schema1"."REOPNED_ON_IX" on Schema1.Table1 (Col1)
DB20000I The SQL command completed successfully.
......

Hope this time,I have not refused anything.

Thank you

Regards
Baseet Ahmed
Information Technologist

&&&&&&&&&&&&&&&&&&&&&&&&
Value of Parents:
Jannat(Heaven) is under the feet of Mother.
Father is the middle gate of Jannat(Heaven).
&&&&&&&&&&&&&&&&&&&&&&&&

When u running drop statement please omit the double quotes and try to re run the script.
DROP INDEX Schema1.REOPNED_ON_IX
create index Schema1.REOPNED_ON_IX on Schema1.Table1 (Col1)

No, the double-quotes are fine! They tell DB2 that you are using delimited identifiers and that the case of characters matters (or that you use special characters). That's standard SQL.

The real issue here is that something else drops the index. Either some other process is messing around, or - as Marcus already pointed out - you drop the table on which the index is created and, thus, the index gets dropped as well. But without having a complete, scaled-down scenario we are just in the dark.