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

checking existing before drop a table

P: n/a
Ken
how to check if a table is exist then drop it if true, false to ignore
the SQL statement, for example,

drop table person if exist;

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


P: n/a
Why just drop the table? If the drop works, then you have what you
wanted. If the drop fails, then you have what you wanted anyway.

Nov 12 '05 #2

P: n/a
Ken
when fails, I always get a warning message, it is a little annoyed.

--CELKO-- wrote:
Why just drop the table? If the drop works, then you have what you
wanted. If the drop fails, then you have what you wanted anyway.


Nov 12 '05 #3

P: n/a
you can query syscat.tables

Nov 12 '05 #4

P: n/a
Ken wrote:
when fails, I always get a warning message, it is a little annoyed.

Actually that would we an error. In the CLP you can easily suppress the
error by calling
UPDATE COMMAND OPTIONS USING <magicyouneedtolookupininformationcenter> OFF
DROP TABLE ..
UPDATE COMMAND OPTIONS USING ... ON

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

P: n/a
Serge Rielau wrote:
UPDATE COMMAND OPTIONS USING <magicyouneedtolookupininformationcenter> OFF


No magic needed.

UPDATE COMMAND OPTIONS USING o OFF;
DROP whatever you like;
UPDATE COMMAND OPTIONS USING o ON;

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin
Nov 12 '05 #6

P: n/a
There is no way to do that directly, but a lot of things can be done to
do the same, depending on your needs.

If you are executing statements:

SELECT COUNT(*) FROM SysCat.Tables WHERE TabName = <Name>

And if the result is not 0, DROP TABLE ....

This can be a PROCEDURE as well:

CREATE PROCEDURE Drop_Table (Name VARCHAR(0128))
BEGIN
DECLARE A INTEGER;
SELECT COUNT(*) INTO A FROM SysCat.Tables WHERE TabName = Name;
IF A = 1 THEN EXECUTE IMMEDIATE "DROP TABLE " || <tabname>; END IF;
END

The only issue with the above, is that if someone else DROPs the TABLE
in between the two statements, such as in a concurrent process, this
will still produce the error. If you were DELETEing a row from a TABLE,
you could LOCK the TABLE to guarantee a static state, but there is no
equivalent LOCK SCHEMA that i know of.

Instead, the more "apropriate" way to do the above would be to DROP it
and simple catch the error with a DECLARE CONTINUE HANDLER FOR
SQLEXCEPTION, or more specifically, SQL0204N, and just ignore the
error.

B.

Nov 12 '05 #7

P: n/a
Brian Tkatch wrote:
There is no way to do that directly, but a lot of things can be done to
do the same, depending on your needs.

If you are executing statements:

SELECT COUNT(*) FROM SysCat.Tables WHERE TabName = <Name>

And if the result is not 0, DROP TABLE ....

This can be a PROCEDURE as well:

CREATE PROCEDURE Drop_Table (Name VARCHAR(0128))
BEGIN
DECLARE A INTEGER;
SELECT COUNT(*) INTO A FROM SysCat.Tables WHERE TabName = Name;
IF A = 1 THEN EXECUTE IMMEDIATE "DROP TABLE " || <tabname>; END IF;
END

The only issue with the above, is that if someone else DROPs the TABLE
in between the two statements, such as in a concurrent process, this
will still produce the error. If you were DELETEing a row from a TABLE,
you could LOCK the TABLE to guarantee a static state, but there is no
equivalent LOCK SCHEMA that i know of.


Well, that's what isolation levels are for to prevent. Given that a DROP
TABLE actually amounts to a DELETE on the catalog (aside from the physical
changes), you won't run into any problem with isolation RR.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #8

P: n/a
Please explain further, i do not understand.

Can you give me an example of setting the isolation level to allow the
DROP of a TABLE when a concurrent process DROPs it first?

B.

Nov 12 '05 #9

P: n/a
Brian Tkatch wrote:
Please explain further, i do not understand.

Can you give me an example of setting the isolation level to allow the
DROP of a TABLE when a concurrent process DROPs it first?


Just do this:

SET ISOLATION LEVEL TO RR

Then all operations are done in what the SQL standard calls "serializable",
i.e. no concurrency issues. So the transaction that checks for the
existence of the table via the SELECT COUNT(*) FROM syscat.tables acquires
read locks on the catalog, and another transaction cannot simply drop the
table, disregarding those read locks. That's just the usual locking stuff.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #10

P: n/a
Kewl thanx.

I just tried this. The DBA CREATEd a simple TABLE called 'A' for me. I
SET a default SCHEMA.

In two different connections:

db2 => set isolation level rr
DB20000I The SQL command completed successfully.
db2 => select count(*) from syscat.tables where tabname = 'A'

1
-----------
1

1 record(s) selected.

After both were like this, i issued a DROP TABLE A in both of them. The
first timed out due to the deadlock with a SQL0911N.

The second one, after the first errored, successfuly DROPped the TABLE.

After i COMMITted the second transaction, i did a DROP in the first
connection, which errored out with SQL0204N.

B.

Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.