Db2 Drop Table If Exists Equivalent

DB2 Drop table if exists equivalent

First query if the table exists, like

select tabname from syscat.tables where tabschema='myschema' and tabname='mytable'

and if it returns something issue your

drop table myschema.mytable

Other possibility is to just issue the drop command and catch the Exception that will be raised if the table does not exist. Just put that code inside try {...} catch (Exception e) { // Ignore } block for that approach.

Drop table if it exists with DB2/400 SQL

Here is a CL answer to this question:

         PGM        PARM(&FILENAME)

         DCL        VAR(&FILENAME) TYPE(*CHAR) LEN(10)
         DCL        VAR(&NUMRECS) TYPE(*DEC) LEN(10 0)

         RTVMBRD    FILE(&FILENAME) NBRCURRCD(&NUMRECS)
         IF         COND(&NUMRECS > 0) THEN(DLTF +
                      FILE(&FILENAME))

OUT:     ENDPGM

This solution would have trouble if the physical file has dependencies such as indexes or logical files. Those dependencies would have to be deleted first.

The solution by @danny117 on the other hand does not work in all environments. For example I was unable to coerce it to work in SQuirreL client. But it does work in i Navigator. It also works in RUNSQLSTM, but I was unable to determine how to make it work with unqualified table references. If the tables are unqualified, RUNSQLSTM uses the default collection from DFTRDBCOL. The CURRENT_SCHEMA special register does not return the value from DFTRDBCOL.

Here is the if table has rows drop it solution using a compound statement:

begin
  if( exists(
    select 1 from qsys2.systables
    where table_schema = 'MYLIB'
      and table_name = 'MYTABLE'
  )) then
    if( exists(
      select 1 from mylib.mytable
    )) then
      drop table mylib.mytable;
    end if;
  end if;
end;

I am guessing at the reason you would want to do this, but if it is to allow creation of a new table, then best way may be with a CREATE OR REPLACE TABLE if you are at IBM i v7.2 or greater.

If all you want to do is make sure you have an empty table, TRUNCATE (v7.2+) or DELETE may be better options.

Syntax of if exists in IBM Db2

The way i did it is as follows

Begin atomic

  if( exists( SELECT 1 
              FROM SYSIBM.SYSTABLES 
              WHERE NAME='EMAIL' AND TYPE='T' AND creator = 'schema1' 
            )
    ) 
    then customStoredproc('drop table EMAIL'); 

  end if;

End

My customStoredProc just has one stmt execute immediate @dynsql;

How to drop a table if it exists?

Is it correct to do the following?

IF EXISTS(SELECT *
          FROM   dbo.Scores)
  DROP TABLE dbo.Scores

No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist).

Instead, for a permanent table you can use

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL 
  DROP TABLE dbo.Scores; 

Or, for a temporary table you can use

IF OBJECT_ID('tempdb.dbo.#TempTableName', 'U') IS NOT NULL
  DROP TABLE #TempTableName; 

SQL Server 2016+ has a better way, using DROP TABLE IF EXISTS …. See the answer by @Jovan.

Check if declared global temporary table exists in current session

Depending on your DB2 version, which you conveniently not indicated, you can use the WITH REPLACE option when declaring the temporary table, which will not only succeed even though the table has been previously declared, but will also take care of deleting the previously inserted rows.

DECLARE GLOBAL TEMPORARY TABLE mytab (
  ...
) NOT LOGGED WITH REPLACE

Drop table only if it exists, or ignore drop error

This is a FAQ

There's more than one way to do it.

You can use compound-SQL in your script with a continue-handler for the SQLSTATE corresponding to the error you get if the table is not found, but this requires that you also use an alternative statement delimiter like shown below

--#SET TERMINATOR @

set current schema myschema@

BEGIN
 DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
   BEGIN end;
 EXECUTE IMMEDIATE 'DROP TABLE MYLOG';
END @

CREATE TABLE MYLOG(... )@

You can also change the abort-on-first-error logic (if you use +s when running your script via the command line). You can udate the Db2 CLP options on the fly inside your script via update command options using s off (to continue on error) or update command options using s on to abort on error.

Create or replace table DB2?

For Db2-Linux/Unix/Windows up to version 11.1, there is no such syntax as "create or replace table" . Other Db2 platforms may vary (because syntax varies accross z/OS, i-series, and Linux/Unix/Windows).

From Db2 for Linux/Unix/Windows at v11.5, IBM added new syntax create table ... if not exists and drop table ... if exists.

For versions older than V11.5, you can drop the table first (and ignore the warning if it does not exist), and then create the table.
You can script to ignore the SQLSTATE corresponding to the failure of drop table if the table does not exist.

You can also you compound SQL to query if the table exists before dropping it. This is frequently asked. The mechanism can vary with you Db2 server operating-system platform also.

For the purpose of creating a backup-copy of a table, you can also consider adding a timestamp to the tablename (as suffix or prefix) before using CREATE TABLE LIKE. Then you are less likely to need to drop the table (except for manually later on when you know you no longer need the backup copy).

---

Related Topics