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
midrange.com
