Drop table if it exists with DB2/400 SQL

Question

My goal is pretty straightforward - if table has rows, drop it. Despite the fact that currently there are several similar answers none of them worked for me.

1. DB2 Drop table if exists equivalent

Suggested solution:

IF EXISTS (SELECT name FROM sysibm.systables WHERE name = 'mylib.mytable') THEN
DROP TABLE mylib.mytable;END IF;

Result:

SQL State: 42601 Vendor Code: -199 Message: [SQL0199] Keyword IF not expected.
Valid tokens: ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN

2. Drop DB2 table if exists

Suggested solution:

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

Result:

Message: [SQL0104] Token HANDLE was not invalid. Valid tokens: HANDLER or, if replace handle with handler:

Message: [SQL0199] Keyword STATEMENT not expected. Valid tokens: SQL PATH RESULT SCHEMA CURRENT CONNECTION DESCRIPTOR.

3. From answer about views

Suggested solution:

DROP TABLE MY_TABLE ONLY IF EXISTS source.

Result:

Message: [SQL0104] Token ONLY was not invalid. Valid tokens: RESTRICT CASCADE

So, I wonder if an alternate solution exists. CL solution is also interesting.

Answer 1

I'm assuming you may want to do this more than once, so a procedure might be in order.

CREATE or replace PROCEDURE   DROP_LIVE_TABLE
  (in  @table        varchar(10)   
  ,in  @library      varchar(10)
  )
BEGIN
  declare @stmt      varchar(100);
  declare @cnt       int;

  IF exists( select * from systables
               where sys_dname = @library
                 and sys_tname = @table
                 and table_type in ('P','T')
           ) THEN
    SELECT int(sum(number_rows))
      INTO @cnt
      from SYSTABLESTAT
        where sys_dname = @library
          and sys_tname = @table
      ;
    IF @cnt > 0 THEN
      set @stmt = 'DROP TABLE '||@library||'.'||@table||' CASCADE';
      execute immediate @stmt; 
    END IF;
  END IF;
  RETURN;
END;  

The CASCADE keyword causes any dependent objects such as indexes, logical files, views, or such to be deleted as well.

Answer 2

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.

Answer 3

Drop table if exists using atomic statement.

BEGIN ATOMIC                  
IF( EXISTS(                   
SELECT 1 FROM TABLES           
WHERE TABLE_SCHEMA = 'MYLIB'   
AND TABLE_NAME = 'MYTABLE' 
)) THEN                       
DROP TABLE MYLIB/MYTABLE;   
END IF;                       
END;                          

Answer 4

try this:

BEGIN    
   IF EXISTS (SELECT NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'YOURLIBINUPPER' AND TABLE_NAME = 'YOURTABLEINUPPER') THEN           
      DROP TABLE YOURLIB.YOURTABLE;                             
   END IF;                                                        
END  ;