RE: Need MONMSG-thingy for SQL stored procedures

Hi Dan,

I recently was working on putting error handling into stored procedures. My SQL version of "MonMsg CPF0000" can be found here:
http://archive.midrange.com/midrange-l/201304/msg00063.html

This IBM redbook on SQL procedures and functions has been helpful. See chapter 8 for error handling. 8.2 gets into specifics.
http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf

-Kurt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Thursday, April 11, 2013 2:23 PM
To: Midrange Systems Technical Discussion
Subject: Re: Need MONMSG-thingy for SQL stored procedures

Thanks for the reply, Gary.

In the example I tried, there were four messages in the job log, none were escape messages:

CPF5009 Diagnostic "Duplicate record key in member CVGDESP."
CPF5034 Sender copy "Duplicate key on access path."
CPF5034 Notify "Duplicate key on access path."
SQL0803 Diagnostic "Duplicate key value specified."

BTW, I think I found something in the SQL Reference:
CREATE PROCEDURE UPDATE_SALARY_IF
(IN employee_number CHAR(6), INOUT rating SMALLINT)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR not_found
SET rating = -1;

However, I would hate to think that I would have to define all of the possible error conditions to use this. I'm still researching whether there's any "global" error condition that can be utilized.

- Dan

On Thu, Apr 11, 2013 at 3:11 PM, Monnier, Gary <Gary.Monnier@xxxxxxxxx>wrote:

You may need to search the program and/or job messages. What is
CPF5034 being sent as? Diagnostic? Escape? Is there any message sent
after CPF5034?


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Thursday, April 11, 2013 11:23 AM
To: Midrange Systems Technical Discussion
Subject: Need MONMSG-thingy for SQL stored procedures

In our shop, we use SQL stored procedures to update our control tables
at each step in development, QA, system testing, and production deployment.
(FWIW, our control tables do most of the "driving" in our application
and, as a result, some of our projects will have anywhere from a few
hundred to a few thousand records added to our control tables.)

We use separate SQL stored procedures for each table, and the
collection of these for a given project is called from a CL program.
(Code for a very simple project is shown below.) We would like an
exception error thrown in the CL program when any statement in a
stored procedure fails. I tried MONMSG CPF0000 and MONMSG SQL0000 in
the CL program, but apparently none of the errors (i.e. CPF5034 "Duplicate key on access path") are "returned" to
the CL program. I've found the "SQL control statements" chapter in the
SQL reference, but still can't make heads or tails on how to do this.
IF POSSIBLE, I would prefer a "global" at-beginning-of-source
MONMSG-thingy; I would like to avoid having to add code to every
statement that tests the results of a statement.

Member Type Text
RUNDTASCR CLLE Data Deployment Script
@CATUPD SQLPRC SQL Data UPDATE Script - CATMSTP
@CVGINS SQLPRC SQL Data Insert Script - CVGDESP

CL program RUNDTASCR:
Pgm
Call @CATUPD
Call @CVGINS
Commit
EndPgm

SQL proc @CATUPD:
CREATE PROCEDURE @CATUPD
LANGUAGE SQL BEGIN
UPDATE CATMSTP
Set CVCGDESC = 'UNDERINSURED MOTORIST BODILY INJURY'
where CVCGCVGGRP = 'UNDERMBI' ;
END;

CREATE PROCEDURE @CVGINS
LANGUAGE SQL BEGIN
--
DELETE FROM CVGDESP
WHERE (CDST, CDCO, CDCVGCD, CDCVGLC, CDCVGDEDC) =
('OH',24,'BI','100/300','');
INSERT INTO CVGDESP VALUES
('OH',24,'BI','100/300','',
'BODILY INJURY', 'LIABILITY', '$100,000 EA PERSON', '$300,000 EA
ACCIDENT';
--
DELETE FROM CVGDESP
WHERE (CDST, CDCO, CDCVGCD, CDCVGLC, CDCVGDEDC) =
('OH',24,'BI','50/100','');
INSERT INTO CVGDESP VALUES
('OH',24,'BI','50/100','',
'BODILY INJURY', 'LIABILITY', '$50,000 EA PERSON', '$100,000 EA
ACCIDENT';
--
END;
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.