By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,007 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,007 IT Pros & Developers. It's quick & easy.

Serge......Help me

P: n/a
Hi,
We have "if exists" and "create or replace view" clause to check
for the existence of a table or view in sybase and Oracle. Just wanna
know is there any way we can do this in DB2?
For some purpose, I want to drop the views on a table only if it
exists and then create a new view for that table with different column
definitions.
It would be great help if anyone could show me a way in this direction.
TIA

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
pa***************@yahoo.co.in wrote:
Hi,
We have "if exists" and "create or replace view" clause to check
for the existence of a table or view in sybase and Oracle. Just wanna
know is there any way we can do this in DB2?
For some purpose, I want to drop the views on a table only if it
exists and then create a new view for that table with different column
definitions.
It would be great help if anyone could show me a way in this direction.
TIA

The naive translation is (I'm assuming a procedure):
IF EXISTS(SELECT 1 FROM SYSCAT.VIEWS WHERE viewschema = 'SRIELAU'
AND viewname = 'V') THEN
DROP VIEW "SRIELAU"."V";
END IF;
CREATE VIEW "SRIELAU"."V"(c1) AS VALUES 1;

Here is what I prefer though:
CREATE PROCEDURE proc()
BEGIN
DECLARE txt VARCHAR(1000);
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DROP VIEW "SRIELAU"."V";
END;
CREATE VIEW "SRIELAU"."V"(c1) AS VALUES 1;
END%

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Thanks for the reply Serge. Is there any way to do this thro CLP?

Nov 12 '05 #3

P: n/a
Hi!
Since in compound SQL block only following statement can be used:
full select, insert, update, delete, set, to execute any DDL statements
you have to enclosed it in SP, i.e.:
CREATE PROCEDURE akalicki.exec(IN p_command VARCHAR(4000))
BEGIN
-- dummy avoid NOT FOUND exception
DECLARE dummy SMALLINT DEFAULT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET dummy = NULL ;
EXECUTE IMMEDIATE p_command ;
END
#

Then you can use it in your CLP script with a compound SQL:
BEGIN ATOMIC
IF EXISTS(SELECT 1 FROM SYSCAT.VIEWS WHERE viewschema = 'SRIELAU'
AND viewname = 'V') THEN
call akalicki.exec('DROP VIEW "SRIELAU"."V"');
END IF;
END
#

HTH
Adrian

pa***************@yahoo.co.in napisal(a):
Thanks for the reply Serge. Is there any way to do this thro CLP?


Nov 12 '05 #4

P: n/a
Pankaj,

in CLP scripts I just drop objects before I create them.

the script will complete without problems even if it does not exist.

Nov 12 '05 #5

P: n/a
pa***************@yahoo.co.in wrote:
Thanks for the reply Serge. Is there any way to do this thro CLP?

What speaks against simply ignoring the error output of the script?
Also using UPDATE COMMAND OPTIONS you can twiddle on and of CLPs
reaction to errrors/warnings dump the error to nowheere....
I don't really see the issue with CLP....
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a
thanx for helping. I think i got what i wanted. thax again.

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.