开发者

Drop a DB2 view if it exists

开发者 https://www.devze.com 2022-12-23 23:29 出处:网络
Why doesn\'t this work in IBM Data Studio (Eclipse): IF EXISTS (SELECT 1 FROM SYSIBM.SYSVIEWS WHERE NAME = \'MYVIEW\' AND CREATOR = \'MYSCHEMA\') THEN

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.

  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.

0

精彩评论

暂无评论...
验证码 换一张
取 消