Re: Ignoring error messages in SQL scripts

On 21-Oct-2011 07:53 , Alan Shore wrote:

I have an SQL script run by the command

RUNSQLSTM SRCFILE(libname/QSQLSRC) SRCMBR(MBRNAME)

Within the script MBRNAME I have

DROP VIEW library/viewname
;
CREATE VIEW library/viewname
;
Etc.

In this particular instance the view library/viewname had already
been dropped, so my RUNSQLSTM failed on the DROP

As already suggested by others, the ERRLVL(30) is intended to resolve that situation. The more severe errors which should stop a script typically will have a severity level greater than 30. I have never encountered an undesirable case of an error being ignored; although before some specific release, some DROP actions failed even with ERRLVL(30) so I created a stored procedure to do the DROP.

My question is
I knew why the view wasn't there, so I wasn't bothered about the
drop in the SQL script, so how would I ignore such an error?

As above, ERRLVL(30). Some newer syntax, IIRC referred to as CREATE OR REPLACE may be available for some SQL objects; eliminating the DROP statement before the CREATE request.

Is there the equivalent to a MONMSG within SQL scripts?

RUNSQLSTM script processor is very simplistic, much like CL streams; i.e. //BCHJOB. With CL programming comes the ability to MONMSG. So like

Is there a reference manual that I could read regarding SQL scripts

I am not aware of much other than the help text for the RUNSQLSTM command. But being such a simplistic processor, having no macro or logic-flow control or variable-replace capabilities, I am not sure there is much to be documented.

On 21-Oct-2011 09:09 , Alan Shore wrote:

I was looking into this as part of a change management system

If more specific granularity is required for statement processing, like changing a CL script to a CLP to get MONMSG, the SQL can be embedded or dynamic to an alternate, perhaps a custom, statement processor which could be invoked from CL or CLP. For such use a processor that accepts a single statement might suffice. However fully replacing the RUNSQLSTM with STRREXPRC can maintain each object to a source member, while allowing great flexibility by combining the full capabilities of the REXX interpreted language [with variables and flow logic control including response to errors] and the REXX SQL support.

FWiW the CMS I used in development at IBM, for generating SQL objects, the implementation was RUNSQLSTM source. However IIRC, I believe the DROP CASCADE for the object implemented by that source member was performed as part of the run-time, and the actual [the first] DROP statement in the source was commented-out by a pre-processor. In some other cases an SQL object was moved into the installation or the an activation of or the runtime of the product, rather than being created and moved into the production environment.

Regards, Chuck