Error when trying to drop a table that doesn’t exist in RUNSQLSTM
If I try and DROP a table that does not exist the RUNSQLSTM will fail. Is it possible to monitor for this in the RUNSQLSTM so it won't fail?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 2, 2008 10:03 AM
UPDATED:
May 3, 2010 5:53 AM
Answer Wiki:
Hi,
I don't think you can monitor for it IN the statement itself. You do, however have several options :-
You could specify a higher severity level on the RUNSQLSTM.
You could monitor for the failure in the program that executes the RUNSQLSTM command.
You could check for the existence of the table before you execute the RUNSQLSTM.
Regards,
Martin Gilbert.
To see all answers submitted to the Answer Wiki: View Answer History.
RUNSQLSTM SRCFILE(&SRCLIB/&SRCFILE) SRCMBR(&SRCMEM)
COMMIT(&COMMIT) ERRLVL(21)
The ERRLVL(21) will cause it to ignore the DROP error.
rather than running a drop statement, call a stored procedure which does the drop. Inside the stored procedure check table exists before it drops it. If you have table to be dropped as a parameter passed into procedure you can re-use the procedure to drop other tables if needed in future.
Can you give a code example? I’m newbie enough that I can’t use either of those tips without some deep study and invention. Thanks.
I’m newbie enough that I can’t use either of those tips without some deep study and invention.
Numerous examples are in the Programming: SQL topic of the Information Center.
It’s difficult to give useful examples — a normal assumption would be that someone hired as a developer should already know SQL.
Giving SQL examples without including the fundamentals can be risky. It’s impossible to know how long an answer should be. Technically, the answer ought to be as long as a reference manual.
But perhaps you’re not a developer. You might be a user trying to get a task done without benefit of a development staff. Then how much should an answer about DROP TABLE include?
Or perhaps you are experienced in what Microsoft refers to as “SQL”? If so, then pointing you to the reference manuals and the Information Center is the answer that you need for now.
Tom