1

Why doesn't this work in IBM Data Studio (Eclipse):

IF EXISTS (SELECT 1 FROM SYSIBM.SYSVIEWS WHERE NAME = 'MYVIEW' AND CREATOR = 'MYSCHEMA') THEN
    DROP VIEW MYSCHEMA.MYVIEW;
END IF;

I have a feeling it has to do with statement terminators (;) but I can't find a syntax that works.

Another similar question at How to check a procedure/view/table exists or not before dropping it in db2 9.1? suggests that they had to create a proc but this isn't a solution for us.

Community
  • 1
  • 1
grenade
  • 31,451
  • 23
  • 97
  • 126

1 Answers1

1

from IBM's Book: Getting Started with Data Studio for DB2:

Statement terminator: You can develop multiple SQL statements in a single SQL Editor window by ending each statement with a statement terminator character. The default terminator is a semi-colon. But you can change that to another character by right-clicking in the contents of the editor and selecting the context menu action Set Statement Terminator.

EDIT:

OK, the problem seems to be the syntax. Many people seem to have the same problem. Example see DROP TABLE, VIEW, ... only IF EXISTS.

In the example there are two solutions mentioned.

  1. use Begin atomic
  2. use the procedure db2perf_quiet_drop

For details read the liked page.

Second EDIT:

I just found a really good explanation for Begin ataomic on the IBM pages. See Advanced SQL Scripting PL from IBM. Example 2 is what you are looking for.

Peter Schuetze
  • 16,185
  • 4
  • 44
  • 58
  • Yeah, I think the problem is something along the lines of the sql processor doesn't like the statement terminator inside of the if...end if, but I'm only guessing. What I really need is a db2 valid sql statement that will check that a view exists before dropping it. – grenade Mar 22 '10 at 16:45
  • From what I can see of that Example 2, it describes how to feed an SQL statement stored in a text file, to the DB2 command line client. It's interesting but it doesn't answer my question. – grenade Mar 24 '10 at 13:22
  • It shows you how to wrap the `if` statement with `begin atomic`. It also states that you have to use a different Statement terminator. In addition it shows you that even the statement terminator was changed to `@` you need to use the `;` for the statement that is wrapped by `begin atomic`. – Peter Schuetze Mar 25 '10 at 13:05