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 questi开发者_如何学编程on 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.
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.
- use
Begin atomic
- 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.
精彩评论