Wednesday, January 6, 2021

Stop SQL DROP statement object not found error

sql drop with if exists stops errors

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.

3 comments:

  1. Thank you Simon !
    It's good to know
    Just in case I have a question about other DROP - DROP column :
    Does exists any method ( except the ADDRPYLE ) to suppress/prevent inuiry message ?

    ReplyDelete
  2. slromano@us.ibm.comJanuary 8, 2021 at 2:43 PM

    For altering a table, a control was added about a year ago to suppress the inquiry message: https://www.ibm.com/support/pages/node/1116729

    ReplyDelete
  3. Sergio Luis Puentes-ValladaresJune 18, 2021 at 7:46 AM

    Thank you, for share

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.