Error when trying to drop a table that doesn’t exist in RUNSQLSTM

Gingertosser

10 pts.

Tags:

DROP

RUNSQLSTM

tables

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?

Asked: July 2, 2008 10:03 AM Last updated: May 3, 2010 5:53 AM

Answer Wiki

Last updated: July 2, 2008 10:16 AM GMT

Gilly40023,730 pts.
History
Contributors
- Ordered by most recent
- Gilly40023,730 pts.

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4 Replies

There was an error processing your information. Please try again later.

Thanks. We'll let you know when a new response is added.

Send me notifications when members answer or reply to this question.

graybeard52 Jul 2, 2008 4:34 PM GMT

RUNSQLSTM SRCFILE(&SRCLIB/&SRCFILE) SRCMBR(&SRCMEM) COMMIT(&COMMIT) ERRLVL(21) The ERRLVL(21) will cause it to ignore the DROP error.

3,115 pointsBadges:

report
Ritchie1 Jul 4, 2008 8:35 AM GMT

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.

210 pointsBadges:

report
Spicetrader Feb 6, 2009 3:34 PM GMT

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.

10 pointsBadges:

report
TomLiotta May 3, 2010 5:53 AM GMT

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

125,585 pointsBadges:

report