Simplify your SQL script processing.

Best practice dictates that an SQL database creation script would have the following pair of statements duplicated for each SQL object:

DROP TABLE table1;

CREATE TABLE table1 (col1 INT, col2 CHAR(10));

The pairing of the DROP and CREATE statements is done so that the script can remove old versions of the database objects and avoid failing with an “Object Already Exists” error. The problem with this approach is the DROP statement will fail with an “Object Not Found” error the first time you try to create this database on a system or partition.

Extra processing needs to be added to your scripts to ensure that DROPs of non-existent objects don’t cause your database creation script to fail. Unfortunately, this extra processing has to be done outside of the SQL script, giving developers another thing to manage. The following is a list of the external controls that are commonly employed:

• Use a wrapper CL program with the appropriate MONMSG command.
• Specify a value of 20 for the Severity Level parameter on the RUNSQLSTM CL command.
• Use the Options menu (Figure 1) on the IBM i Access Client Solutions (ACS) Run SQL Scripts

Figure 1: Ignore “Object Not Found” DROP error on Run SQL Scripts Options menu.

Even though these extra processing options allowed your SQL script to run, the job log would still be littered with all the “File Not Found” error messages from DROP statements that were ignored or swallowed.

All these headaches and challenges go away with the new IF EXISTS clause for the SQL DROP statement. This new clause is part of the IBM i Technology Refresh that was announced on October 6, 2020.

The IF EXISTS clause allows your SQL database creation script to add additional processing to the DROP statement. In the following updated SQL script, the DROP statement will now run successfully whether or not TABLE1 exists in the target schema.

DROP TABLE IF EXISTS table1;

CREATE TABLE table1 (col1 INT, col2 CHAR(10));

No extra processing is needed with commands or parameters; you’re able to control the situation by adding a simple clause to your SQL script. In addition, the IF EXISTS clause eliminates the error message noise from your job log as shown in Figure 2. The results of the DROP statement will always be successful due to the IF EXISTS clause. Db2 attempts to drop the specified table, and if the table is not found, Db2 just ends processing for the DROP statement.

Figure 2: This is a successful result from the new IF EXISTS clause.

The IF EXISTS clause is not limited to just the SQL DROP TABLE statement; it’s available to use with any SQL DROP statement (DROP INDEX, DROP PROCEDURE, etc.).

The new IF EXISTS clause for the SQL DROP statement will be available for the IBM i 7.3 and 7.4 releases on November 13, 2020 by applying the latest Db2 PTF Group. More details on these PTF Groups can be found here. Happy SQL Scripting!

Kent Milligan is a Senior Db2 for i Consultant in the IBM Lab Services Power Systems Delivery Practice.  Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the DB2 for i Lab Services practice in 2020, Kent spent 5 years working on healthcare solutions powered by IBM Watson technologies. Kent is a sought-after speaker and author on Db2 for i & SQL topics.