Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: How to check if a view does exist ?

    Hi,

    I need some IF THEN construct to drop some views at beginning of the scheduled job.

    For example I want to see if view exists and delete it before proceeding.

    IF EXIST VIEW ABC THEN
    DROP VIEW ABC
    END
    ;
    Anything in DB2 SQL that may allow me to drop view ABC.

    If I try straight
    DROP VIEW ABC ;
    then schedule will be terminated when view does not exit.

    Any Idea will be appreciated

    DBFinder

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Wrap the logic in a SP
    or if you are on 9.7 .. you can have execute immediate within compound stmts

    begin declare continue handler for sqlstate '42704' begin /* Ignore */ end@ - comp.databases.ibm-db2 | Google Groups
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks Rahul Singh

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You can use something like this:

    select 1 from sysibm.sysviews
    where name = '<<view name>>'
    and creator = '<<view creator>>'
    fetch first row only

    if sqlcode = 0
    drop view <<view name>>
    end-if
    ....
    Lenny

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Yes Lenny,

    This is great idea , SELECTing won't find anything and sqlcode will be non-zero.

    So I can use it in Stored Procedure.

    Can you suggest me how can I use it in Command Script box of Task Center.

    Thanks

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by DBFinder
    Yes Lenny,

    This is great idea , SELECTing won't find anything and sqlcode will be non-zero.

    So I can use it in Stored Procedure.

    Can you suggest me how can I use it in Command Script box of Task Center.

    Thanks
    You can do it. Just use this logics.

    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •