Drop DB2 table if exists

Question

In my script I have to do a lot of selects to a joined table, so instead I decided to put this join into a temporal table.

First I thought:

1. Create table
2. Put the data from the join into a table
3. Drop the table

But then I thought, what if the script fails before I dropped the table?

So I decided to go with:

1. Drop the table
2. Create the table
3. Put the data from the join into a table

I don't really mind if the table is left there until the next time I run the script, so the second option works too.

But what if somebody had already dropped the table?

I saw some systems have a "drop if exists" but unfortunately not DB2. I would like to do something that won't make the script die when the drop table fails.

Ideas? On any of this? Thanks!

EDIT: I forgot to say this is in a PERL script!

You can check for the existence of a given table using [`$dbh->tables`](http://search.cpan.org/~hoymich/DBD-DB2-0.78-5.8.4/lib/DBD/DB2.pod#Getting_Table_information) — Borodin, Mar 13 '15 at 13:48

score 1 · Answer 1 · answered Mar 15 '15 at 03:17

The best way to do this is by using an annonymous block like in this code

You need to call the drop table in a dynamic sql, and catch the exception in the block.

--#SET TERMINATOR @
begin
  declare statement varchar(128);
  declare continue handle for sqlstate '42710' BEGIN END;
  SET STATEMENT = 'DROP TABLE MYTABLE';
  EXECUTE IMMEDIATE STATEMENT;
end @

This code will run normally in DB2. It does not need to be part of a procedure nor function.

we should learn to copy paste without syntax errors (HANDLER) — U.V., Mar 31 '20 at 15:41

score 0 · Answer 2 · answered Mar 13 '15 at 13:29

0

Why not look for the table first? If you find it, it needs to be dropped; if you don't, it doesn't.

answered Mar 13 '15 at 13:29

Garry

109
4

Manaswi B · Answer 3 · 2015-03-13T13:38:20.670

db2perf_quiet_drop that might works the way you want.. Its a free add-on :)

You can look into this post too..

http://www.dbforums.com/showthread.php?1609047-DB2-equivalent-for-mysql-s-DROP-TABLE-IF-EXISTS

If this doesn't work for you please let me know what error you are getting so I can try to help :)

Or this might work
if( NOT exists( create table detailval
(
id int,
detaildeptNo int,
info varchar(255)
);
insert into detailval(1,1, 'detail values A');
insert into detailval(2,1, 'detail values B');
insert into detailval(3,1, 'detail values C');
insert into detailval(4,2, 'detail values D');

        )  
)   
then customStoredproc('droptable');

end if;

End

score 0 · Answer 4 · answered Mar 17 '15 at 09:45

I think you should look into working with temporary tables (DECLARE GLOBAL TEMPORARY TABLE). They are stored in the temporary table space and are dropped automatically after commit.

You can easily also query syscat.tables like this:

select COUNT(*) from SYSCAT.TABLES where TRIM(TABNAME) = '<some_table_name>'

if this query returns 0 then the table does not exists.

Drop DB2 table if exists

4 Answers4

Linked