Monitor Message in SQL for Delete File (aka DROP TABLE)

Recently a friend mentioned that I was being dumb about the IBM SQL CREATE or REPLACE TABLE statement. To my mind, this meant that if the TABLE already existed, it would be deleted ("DROPPED" in SQL terminology) and then (re)created. Apparently I was really dumb!

The CREATE or REPLACE TABLE statement effectively is a quasi "CREATE or ALTER TABLE" statement. That is if the table exists, it works like the CL CHGPF command, modifying the table's definition where needed. Obviously in the SQL context and assuming the file's definition hasn't changed, it is basically a NO-OP function.

CREATE or REPLACE TABLE QTEMP.MYJUNK (myname char(30) );

In some cases, this is fine. But in the situation where a utility script is running, you may want to delete the file and then create a new one. Normally you would do something like this:

DROP TABLE QTEMP.MYJUNK;
CREATE TABLE QTEMP.MYJUNK ( myname char(30) );

The problem is, when the file in QTEMP doesn't exist, the DROP will fail and the standard RUNSQLSTM will cancel the rest of the script from running.  My own SQL iQuery script tool will continue processing anyway, but let's stay with RUNSQLSTM for now.

To trap an error in an SQL Script processed by RUNSQLSTM or the new ACS (Client Access) SQL Scripts tool, you need to embed a continuation handle for the exception. This sounds ugly, it is, but if you simply do the SQL equivalent of MONMSG CPF0000, it isn't too bad. Here's the code:

BEGIN                                                    
   declare continue handler for sqlexception             
     begin end;                                          
  DROP TABLE QTEMP.MYJUNK;                              
END;                                                     
CREATE TABLE QTEMP.MYJUNK ( myname char(30) );

As you can see, the DECLARE CONTINUE HANDLER for SQLEXCEPTION statement creates a handler for the Table Not Found exception, and handles it. We simply embed an empty BEGIN/END block so it swallows the error and continues. Then the DROP statement is enclosed within another larger BEGIN/END block. If you put the DROP outside the BEGIN/END block, the exception is NOT trapped.

SQL iQuery Alternative

I've been using SQL iQuery's Scripting tool for a while now. It works great but is not a wholesale replacement for RUNSQLSTM or ACS SQL Scripts. However, for running a series of SQL statements (not creating a UDF or Stored Procedure) I prefer iQuery over RUNSQLSTM.

With SQL iQuery Scripts you have no issue with DROP failing as we build a monitor into the Script processor. However, why attempt to delete something and have it fail, trap that failure and continue? With iQuery's pre-processor we can test for the file's existence then delete it. Here's how you do that with SQL iQuery.

#ifExists QTEMP.MYJUNK         
  DROP TABLE QTEMP.MYJUNK;                              
#endif;                                                     
CREATE TABLE QTEMP.MYJUNK ( myname char(30) );

So now you know both ways to handle this situation. As more people upgrade to the iQuery scripting tool, things like this will become easier to handle.