If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Conditionally DROP INDEX in DB2

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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/
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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....
__________________
IBM Certified Database Associate, DB2 9 for LUW

Last edited by nick.ncs; 02-18-09 at 12:36.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On