Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2008
    Posts
    10

    Unanswered: Problem in Drop Index -- DB2 8.2

    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).
    &&&&&&&&&&&&&&&&&&&&&&&&

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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)
    • ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    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

  4. #4
    Join Date
    Jun 2008
    Posts
    10
    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).
    &&&&&&&&&&&&&&&&&&&&&&&&

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dbforumsB
    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).
    &&&&&&&&&&&&&&&&&&&&&&&&
    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.
    Last edited by Marcus_A; 07-11-08 at 23:36.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jun 2008
    Posts
    10
    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).
    &&&&&&&&&&&&&&&&&&&&&&&&

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jun 2008
    Posts
    10
    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).
    &&&&&&&&&&&&&&&&&&&&&&&&
    Attached Thumbnails Attached Thumbnails ccenterimage.bmp  

  11. #11
    Join Date
    Jul 2008
    Posts
    94
    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)

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •