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--]

Re: checking existing before drop a table

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]

Re: checking existing before drop a table

when fails, I always get a warning message, it is a little annoyed.

--CELKO-- wrote:[color=blue]
> 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.[/color]

[Artur]

Re: checking existing before drop a table

you can query syscat.tables

[Serge Rielau]

Re: checking existing before drop a table

Ken wrote:[color=blue]
> when fails, I always get a warning message, it is a little annoyed.[/color]
Actually that would we an error. In the CLP you can easily suppress the
error by calling
UPDATE COMMAND OPTIONS USING <magicyouneedto lookupininforma tioncenter> OFF
DROP TABLE ..
UPDATE COMMAND OPTIONS USING ... ON



--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bernd Hohmann]

Re: checking existing before drop a table

Serge Rielau wrote:
[color=blue]
> UPDATE COMMAND OPTIONS USING <magicyouneedto lookupininforma tioncenter> OFF[/color]

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ämpf e
des 18. März in Berlin

[Brian Tkatch]

Re: checking existing before drop a table

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]

Re: checking existing before drop a table

Brian Tkatch wrote:
[color=blue]
> 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.[/color]

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]

Re: checking existing before drop a table

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]

Re: checking existing before drop a table

Brian Tkatch wrote:
[color=blue]
> 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?[/color]

Just do this:

SET ISOLATION LEVEL TO RR

Then all operations are done in what the SQL standard calls "serializab le",
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]

Re: checking existing before drop a table

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.