Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    6

    Conditionally DROP INDEX in DB2

    Hi, new to DB2, how do I issue a drop index only if it exists? I thought I had worked it out (pretty similar to SQL Server, which I know well), but it fails. I have seen a number of questions about this but as yet no satisfactory answer. This is what I tried and the result - can anyone help?

    Thanks

    Product info -
    Microsoft Windows XP [Version 5.1.2600]

    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08024" with level identifier "03050106".
    Informational tokens are "DB2 v8.1.11.973", "s060120", "WR21365", and FixPak "11".

    Product Name = "DB2 Personal Edition"
    Product Identifier = "DB2PE"
    Version Information = "8.2"

    ------------------------------ Commands Entered -------------------------
    IF EXISTS (SELECT name FROM sysibm.sysindexes WHERE name = 'IX_D_EXP_01')
    THEN
    DROP INDEX IX_D_EXP_01;
    END IF
    --------------------------------------------------------------------------
    IF EXISTS (SELECT name FROM sysibm.sysindexes WHERE name = 'IX_D_EXP_01') THEN DROP INDEX IX_D_EXP_01
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "IF EXISTS (SELECT name FROM sysibm.sysindex"
    was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
    "<space>". SQLSTATE=42601

    END IF
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END IF".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END IF". Expected tokens may include: "JOIN <joined_table>
    Last edited by bventure; 01-09-09 at 11:40.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by bventure
    SQL0104N An unexpected token "IF EXISTS (SELECT name FROM sysibm.sysindex"
    SQLPL statements (like IF... ENDIF) are not supported in the CLP.
    I'd just do
    Code:
    DROP INDEX IX_D_EXP_01
    and ignore the negative sqlcode in case the index does not exist.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jan 2009
    Posts
    6
    Thanks for the advice, sorry it's taken me so long to get back, been busy elsewhere! I find this a big problem with DB2, that the CLP is so limited. I'm used to SQL server where anything you can do in a procedure you can do in a query window!

    One thing though, how do I go about 'ignoring the negative SQL code'?

    Thanks
    Martin

  4. #4
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Quote Originally Posted by bventure
    how do I go about 'ignoring the negative SQL code'?
    In a CLP you just ignore the negative SQL code
    In a script you donot exit for a negative SQL code
    In a Stored Procedure you specify DECLARE CONTINUE HANDLER FOR SQLSTATE '42601'

    UPDATE -
    Pls Note - when I say script I mean an external script like a bat file or shell script....
    Last edited by nick.ncs; 02-18-09 at 12:36.
    IBM Certified Database Associate, DB2 9 for LUW

  5. #5
    Join Date
    Jan 2009
    Posts
    6
    OK, thanks for that - again I'm tripped up by the fact that CLP, script & stored procedures don't all behave the same way! I'm used to them being essentially the same thing. Now, have you seen my BIG question....

    Cheers
    Martin

Posting Permissions

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