checking existing before drop a table

[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;

[--CELKO--]

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.

[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.

[Artur]

you can query syscat.tables

[Serge Rielau]

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

[Bernd Hohmann]

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

[Brian Tkatch]

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.

[Knut Stolze]

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

[Brian Tkatch]

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.

[Knut Stolze]

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

[Brian Tkatch]

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.