How to check if a view does exist ?

DBFinder · #1 (**permalink**) 08-17-09, 21:17

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.

Quote:

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

Quote:

DROP VIEW ABC ;

then schedule will be terminated when view does not exit.

Any Idea will be appreciated

DBFinder

rahul_s80 · #2 (**permalink**) 08-18-09, 01:52

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

DBFinder · #3 (**permalink**) 08-19-09, 06:02

Thanks Rahul Singh

Lenny77 · #4 (**permalink**) 08-19-09, 17:41

You can use something like this:

Quote:

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

DBFinder · #5 (**permalink**) 08-20-09, 09:37

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

Lenny77 · #6 (**permalink**) 08-21-09, 15:20

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

Internet Services

Web Development

Digital Marketing

Consumer Tech