Stop SQL DROP statement object not found error

SQL's DROP statement is pretty universal. It is used to delete just about anything that can be created in SQL: tables, indexes, views, procedures, etc. As with all delete statements there will be an issue if you try to delete an object that does not exist. For example if I drop a table:

DROP TABLE QTEMP.DOES_NOT_EXIST

I will get an error message. This one is from ACS's "Run SQL Scripts":

SQL State: 42704 Vendor Code: -204 Message: [SQL0204] DOES_NOT_EXIST in QTEMP type *FILE not found.

While this is not a big deal in "Run SQL Scripts" as I can just ignore the message, it can be a significant issue if this situation is encountered in a program.

Fortunately this issue has been overcome in the latest Technology Refreshes released last week, IBM i 7.4 TR3 and 7.3 TR9, with the ability to add the words "IF EXISTS" to the Drop statement. For example if I use the following statement in "Run SQL Scripts":

DROP TABLE IF EXISTS QTEMP.DOES_NOT_EXIST

I receive the following message:

Statement ran successfully (294 ms)

Which is a successful result, even though the table does not exist.

What happens in a program? I am going to give you examples in RPG and CL.

RPG does not generate an error when a Drop fails, but I can retrieve the information about any successful and unsuccessful SQL statement using the Get Diagnostics statement.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt:*nounref) dftactgrp(*no) bnddir('TEST') ; 03 /copy devsrc,copybook 04 exec sql DROP TABLE QTEMP.DOES_NOT_EXIST ; 05 GetDiag = GetDiagnostics() ; 06 exec sql DROP TABLE IF EXISTS QTEMP.DOES_NOT_EXIST ; 07 GetDiag = GetDiagnostics() ; 08 *inlr = *on ;

I have my Get Diagnostics code in a procedure in a service program that I am binding to this program. For a full description of what GET DIAGNOSTICS does in this procedure please read: Using Get Diagnostic for SQL errors.

Line 3: The procedure prototype is included in the source member copied into the source of this program from the member COPYBOOK in the source file DEVSRC.

Line 4: I execute the Drop Table.

Line 5: I call the GetDiagnostics procedure and the results are returned into the GetDiag data structure, which is defined in COPYBOOK.

Before I go to the next line in the program I want to show the contents of the GetDiag data structure when I run the program in debug:

> EVAL GetDiag GETDIAG.ROWSCOUNT = 0 GETDIAG.SQLCODE = '-204 ' GETDIAG.SQLSTATE = '42704' GETDIAG.MSGID = 'SQL0204 ' GETDIAG.MSGID1 = ' ' GETDIAG.MSGID2 = ' ' GETDIAG.MSGLENGTH = 45 GETDIAG.MSGTEXT = ....5...10...15...20...25...30...35...40...45 1 'DOES_NOT_EXIST in QTEMP type *FILE not found.

As you can see there is a negative SQL code, SQLCODE, which shows that the SQL statement failed. I go straight to the last data structure subfield and see the text for the message, which tells me the file is not found. This message does not cause the program to error, and I have to admit I have used this to Drop a Table that may or may not exist.

Line 6: The program continues onto the next line. Here I have the IF EXISTS in the Drop statement.

Line 7: As I did before I call the GetDiagnostics procedure, and place the results in the GetDiag data structure.

When I check the contents of GetDiag data structure after line 7 I see the following:

> EVAL GetDiag GETDIAG.ROWSCOUNT = 0 GETDIAG.SQLCODE = ' ' GETDIAG.SQLSTATE = '00000' GETDIAG.MSGID = 'SQL7953 ' GETDIAG.MSGID1 = ' ' GETDIAG.MSGID2 = ' ' GETDIAG.MSGLENGTH = 29 GETDIAG.MSGTEXT = ....5...10...15...20...25...3 1 'Drop of *N in *LIBL complete.

No SQL Code, therefore, the statement completed successfully. It is only when I look at the message text do I see what happened as the file name Dropped is *N, which indicates there was no file to delete.

If I was doing the same in a CL program I would use the Run SQL command, RUNSQL.

01 PGM 02 RUNSQL SQL('DROP TABLE QTEMP.DOES_NOT_EXIST') + COMMIT(*NC) 03 RUNSQL SQL('DROP TABLE IF EXISTS QTEMP.DOES_NOT_EXIST') + COMMIT(*NC) 04 ENDPGM

This is copied from the job log of the job where I ran this program:

200 - RUNSQL SQL('DROP TABLE QTEMP.DOES_NOT_EXIST') COMMIT(*NC) DOES_NOT_EXIST in QTEMP type *FILE not found. RUNSQLSTM or RUNSQL command failed. Function check. SQL9010 unmonitored by TESTCL at statement 0000000200 instruction X'0000'. SQL9010 received by procedure TESTCL. (C D I R) I 300 - RUNSQL SQL('DROP TABLE IF EXISTS QTEMP.DOES_NOT_EXIST') COMMIT(*NC) - RETURN /* RETURN due to end of CL program */

When the first SQL statement failed it caused the CL program to error with a generic SQL9010 message. I have to look at the lines before that line to see the reason for the failure, which is the file does not exist. I answered the SQL9010 message with an "I", to ignore it, so that program would continue, and execute the second SQL statement. As the second statement contains the IF EXISTS it does not cause an error.

Why did I choose DROP TABLE for my examples?

It is the Drop statement I use the most. IF EXISTS works equally well with any other type of Drop statements. I tested with the following:

DROP VIEW IF EXISTS QTEMP.DOES_NOT_EXIST ; DROP INDEX IF EXISTS QTEMP.DOES_NOT_EXIST ; DROP ALIAS IF EXISTS QTEMP.DOES_NOT_EXIST ; DROP TYPE IF EXISTS MYLIB.DOES_NOT_EXIST ; DROP TRIGGER IF EXISTS QTEMP.DOES_NOT_EXIST ; DROP FUNCTION IF EXISTS QTEMP.DOES_NOT_EXIST ; DROP SEQUENCE IF EXISTS QTEMP.DOES_NOT_EXIST ;

And none of these produced an error. Good job IBM for adding this useful feature to Db2 for i

 

You can learn more about the DROP with IF EXISTS SQL statement from the IBM website here.

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.