Drop a DB2 view if it exists...

Question

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 http://stackoverflow.com/questions/355687/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.

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