Debugging stored procedures?

Mr. K.V.B.L.

unread,

Mar 26, 2012, 5:16:55 PM3/26/12

to

Getting a little more detail into my stored procedure. One piece of logic I want is to detect a numerical overflow situation and reset the counter back down to one. If my counter field is 9(10) and its current value is 9,999,999,999 and you try to add 1, it bombs as it should. The SQLCODE should be -406 but I can't really tell whats going on. I am looking for that return code after my UPDATE and it its true, do and UPDATE with NUMBER=1. Are there print statements or something you can add to SQL/PL code to trace it? I've coded my routine in LANGUAGE SQL, for better or worse. Maybe I to learn to do it in embedded SQL with C.

Kelly Beard

Mr. K.V.B.L.

unread,

Mar 26, 2012, 5:24:34 PM3/26/12

to

On Monday, March 26, 2012 4:16:55 PM UTC-5, Mr. K.V.B.L. wrote:
> Getting a little more detail into my stored procedure. One piece of logic I want is to detect a numerical overflow situation and reset the counter back down to one. If my counter field is 9(10) and its current value is 9,999,999,999 and you try to add 1, it bombs as it should. The SQLCODE should be -406 but I can't really tell whats going on. I am looking for that return code after my UPDATE and it its true, do and UPDATE with NUMBER=1. Are there print statements or something you can add to SQL/PL code to trace it? I've coded my routine in LANGUAGE SQL, for better or worse. Maybe I to learn to do it in embedded SQL with C.
>
> Kelly Beard

I guess I could post my example code.

CREATE PROCEDURE BEAK/GetNextTransactionNumber(
IN startCode CHAR(10),
IN defaultTrans CHAR(10),
OUT transactionNumber CHAR(10))
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE CURS1 CURSOR FOR SELECT TRTRANS FROM DFTRANMSTP WHERE TRSCODE = startCode FOR UPDATE OF TRTRANS;
OPEN CURS1;
FETCH CURS1 INTO transactionNumber;
if SQLCODE <> 0 then
INSERT INTO DFTRANMSTP (TRTRANS, TRSCODE) VALUES (defaultTrans, startCode);
set transactionNumber = defaultTrans;
else
UPDATE DFTRANMSTP SET TRTRANS = TRTRANS + 1 WHERE CURRENT OF CURS1;
if SQLCODE = -406 then
UPDATE DFTRANMSTP SET TRTRANS = 1 WHERE CURRENT OF CURS1;
set transactionNumber = '1';
end if;
end if;
CLOSE CURS1;
END

CRPence

unread,

Mar 26, 2012, 6:10:19 PM3/26/12

to

On 26-Mar-2012 14:16 , Mr. K.V.B.L. wrote:
> Getting a little more detail into my stored procedure. One piece of
> logic I want is to detect a numerical overflow situation and reset

> the counter back down to one. If my counter field is DEC(10) and its

> current value is 9,999,999,999 and you try to add 1, it bombs as it

> should. The SQLCODE should be -406 but I can't really tell what's
> going on. I am looking for that return code after my UPDATE and if
> it's true, do and UPDATE with NUMBER=1. Are there print statements

> or something you can add to SQL/PL code to trace it? I've coded my
> routine in LANGUAGE SQL, for better or worse. Maybe I to learn to do
> it in embedded SQL with C.
>

I have not looked at the procedure [in the next message] yet.

FWiW I often use REXX SQL to test expressions for the effect as SQL
code and SQL state. I also recommend using the SQLstate instead of the
SQLCODE.

FWiW, I had used something similar to the following, for an effective
"print line" request invoked from within my SQL stored procedures, for
when I was testing from within STRSQL interactive SQL utiltity; e.g.:

In the SQL procedure I am writing and testing:

DECLARE DBG VARCHAR(150);
DBG='SQLCODE:' concat SQLCODE concat 'SQLSTATE:' concat SQLSTATE;
CALL REXSAY(DBGOUT);

That REXSAY procedure is defined as an external stored procedure
using the REXX language to present to STDOUT and the joblog, some string
of data [such as variable DBG from above]:

/* In a source member of *FILE named QREXSRC in library LIBNAME */
parse arg SayTxt
/* create procedure RexSay (in varchar(150)) */
/* language REXX */
/* parameter style general */
/* external name 'LIBNAME/QREXSRC(REXSAY)' */
/* */
say "<"SayTxt">"
address "*COMMAND"
msgtyp="*INFO"
"sndpgmmsg msg(&SayTxt) tomsgq(*topgmq)",
" topgmq(*prv (QSQXCUTE))",
" msgtype("msgtyp") "
return

Regards, Chuck

Mr. K.V.B.L.

unread,

Mar 27, 2012, 10:58:39 AM3/27/12

to

I've seen the word REXX is various 400-related posts for years but have never see one line of code. I'm checking it out via the REXX/400 Programmers Guide.
Thanks for the SQL debug tip. More stuff to learn.

Mr. K.V.B.L.

unread,

Mar 27, 2012, 2:43:06 PM3/27/12

to

On Monday, March 26, 2012 5:10:19 PM UTC-5, CRPence wrote:

Taken a heckuva long time to get to this point, but here is where I'm at now. I think this is pretty much done for me.

CREATE PROCEDURE BEAK/GetNextTransactionNumber(
IN startCode CHAR(10),
IN defaultTrans CHAR(10),

OUT transactionNumber CHAR(10),
OUT O_SQLCODE INTEGER,
OUT O_SQLSTATE CHAR(5))
LANGUAGE SQL
BEGIN
/* General variable and condition declarations. */

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE DBGOUT VARCHAR(150);
DECLARE overflow CONDITION FOR SQLSTATE '22003';

/* Cursor declaration(s) */

DECLARE CURS1 CURSOR FOR SELECT TRTRANS FROM DFTRANMSTP WHERE TRSCODE = startCode FOR UPDATE OF TRTRANS;

/* Handler and condition declaration(s) */
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET O_SQLCODE = SQLCODE;
SET O_SQLSTATE = SQLSTATE;
set DBGOUT='General SQLException: SQLCODE=' concat SQLCODE concat ', SQLSTATE:' concat SQLSTATE;
CALL REXSAY(DBGOUT);
END;

DECLARE CONTINUE HANDLER FOR overflow
BEGIN
set DBGOUT='OVERFLOW CONDITION! SQLCODE = ' concat SQLCODE
concat ', SQLSTATE = ' concat SQLSTATE concat
': attempting UPDATE DFTRANMSTP SET TRTRANS = 1 WHERE CURRENT OF CURS1';
CALL REXSAY(DBGOUT);

UPDATE DFTRANMSTP SET TRTRANS = 1 WHERE CURRENT OF CURS1;

CALL REXSAY('Overflow for DFTRANMSTP recovered.');
END;

/* Now onto the actual code */

OPEN CURS1;
FETCH CURS1 INTO transactionNumber;
if SQLCODE <> 0 then

INSERT INTO DFTRANMSTP (TRTRANS, TRSCODE) VALUES (defaultTrans + 1, startCode);

set transactionNumber = defaultTrans;
else

/* If TRTRANS = 9,999,999,999 then executing this statement will cause the 'overflow' exception to trip. */

UPDATE DFTRANMSTP SET TRTRANS = TRTRANS + 1 WHERE CURRENT OF CURS1;

Hau...@sss-software.de

unread,

Mar 28, 2012, 3:23:04 AM3/28/12

to

On Mar 26, 11:16 pm, "Mr. K.V.B.L." <kenverybigl...@gmail.com> wrote:
> Getting a little more detail into my stored procedure. One piece of logic I want is to detect a numerical overflow situation and reset the counter back down to one. If my counter field is 9(10) and its current value is 9,999,999,999 and you try to add 1, it bombs as it should. The SQLCODE should be -406 but I can't really tell whats going on. I am looking for that return code after my UPDATE and it its true, do and UPDATE with NUMBER=1. Are there print statements or something you can add to SQL/PL code to trace it? I've coded my routine in LANGUAGE SQL, for better or worse. Maybe I to learn to do it in embedded SQL with C.
>
> Kelly Beard

Kelly,

did you know you can debug your SQL code (NOT the C-code) with the
graphical debugger included in System i Navigator's - Run an SQL
Script (Run --> Debugger).
To debug your source code in SQL, you need to add an SET OPTION
Statement to your CREATE PROCEDURE Source Code:

CREATE PROCEDURE BEAK/GetNextTransactionNumber(
IN startCode CHAR(10),
IN defaultTrans CHAR(10),

OUT transactionNumber CHAR(10))
LANGUAGE SQL
Set Option DbgView = *SOURCE
BEGIN
-- Routine Body

Birgitta