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>

SQLPL statements (like IF... ENDIF) are not supported in the CLP.
I'd just doand ignore the negative sqlcode in case the index does not exist.

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

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

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