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

DROP TABLE, VIEW, ... only IF EXISTS

P: n/a
Hi!

Is it possible do to something like this:
DROP TABLE MY_TABLE ONLY IF EXISTS
and the same for views, indexes, procedures, .... ?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jul 6 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Gregor Kovac" <gr**********@mikropis.siwrote: news:6y********************@news.siol.net...
Is it possible do to something like this: DROP TABLE MY_TABLE ONLY IF EXISTS
and the same for views, indexes, procedures, .... ?
Long and cumbersome way. Just drop and ignore errors. But, if you insist...
For table:
--------------
Begin atomic
if( exists(
select 1 from syscat.tables where tabschema = 'MYSCHEMA' and tabname = 'MYTABLE'
)) then
drop table MYSCHEMA.MYTABLE;
end if;
End

--------------
For index: syscat.table -syscat.indexes + 2 extra conditions: indschema & indname.
.... and so on...

Cheers,
--
Konstantin Andreev.

Jul 6 '06 #2

P: n/a
"Gregor Kovac" <gr**********@mikropis.siwrote in message
news:6y********************@news.siol.net...
Hi!

Is it possible do to something like this:
DROP TABLE MY_TABLE ONLY IF EXISTS
and the same for views, indexes, procedures, .... ?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Use the db2perf_quiet_drop procedure. The source code is available on

ftp://ftp.software.ibm.com/ps/produc...perf-1.0.1.zip

In the file db2perf_utils.db2 there is the procedure db2perf_quiet_drop
which suppresses the 'not found' message.

The call :

CALL db2perf_quiet_drop( 'procedure db2perf_crmsg' )@

drops the procedure db2perf_crmsg if this procedure exists.

Kind regards,
Paul
Jul 6 '06 #3

P: n/a
Konstantin Andreev wrote:
Begin atomic
if( exists(
select 1 from syscat.tables where tabschema = 'MYSCHEMA' and tabname =
'MYTABLE' )) then
drop table MYSCHEMA.MYTABLE;
end if;
End
Hmm.. but it does not work for me.
I've saved this into a file TEST.SQL:
Begin atomic
* * if (exists(select 1 from syscat.tables where tabschema = 'MYSCHEMA' and
tabname = 'MYTABLE')) then
* * * * drop table MYSCHEMA.MYTABLE;
* * end if;
End
#

ran it like:
db2 -td# -f TEST.SQL

and got:
B21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "* *" was found following "Begin atomic ".
Expected tokens may include: "<space>". LINE NUMBER=2. SQLSTATE=42601

I've tried this approach, but to no avail.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jul 6 '06 #4

P: n/a
Paul Peters wrote:
>
Use the db2perf_quiet_drop procedure. The source code is available on

ftp://ftp.software.ibm.com/ps/produc...perf-1.0.1.zip

In the file db2perf_utils.db2 there is the procedure db2perf_quiet_drop
which suppresses the 'not found' message.

The call :

CALL db2perf_quiet_drop( 'procedure db2perf_crmsg' )@

drops the procedure db2perf_crmsg if this procedure exists.

Kind regards,
Paul
Cool, I like it :)) thanks.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jul 6 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.