DB2 SQL PL advice

[PJackson]

DB2 UDB 8.X on Windows server

A couple of questions:
1.) I am trying to develop some DB2 SQL stored procedures. Dev
environment at UDB v 8.2,
production environment still at UDB v 8.1. Any issues with
migrating these things since the Dev environment does not need a C
compiler and production does?

2.) Main stored procedure calling other stored procedures. Called
SPs simply declare a cursor for
some SQL and open the cursor and return. Problem is iterating through
the result set from the calling SP.
For example, I declare this locator:
DECLARE v_MemberRSLocator RESULT_SET_LOCATOR VARYING;
and later execute the following:

CALL TFBUDB.SMACF_MBR_INFO(SP_POL_NBR_TXT,ERR_CD,ERR_MS G);
ASSOCIATE RESULT SET LOCATOR (v_MemberRSLocator)
WITH PROCEDURE TFBUDB.SMACF_MBR_INFO;
ALLOCATE v_MemberCursor CURSOR FOR RESULT SET v_MemberRSLocator;
WHILE (SQLSTATE = '00000') DO
FETCH FROM v_MemberCursor INTO v_MemberNbr;

-- do whatever with data here
END WHILE;

** It seems to loop once and exit. If I add any statements within the
loop it seems to ignore the stopping condition of SQLSTATE = '00000'
and loop forever.

I think perhaps there must be a better way to handle such a
situtation, as I will have many such loops within the SP , and each
will need to do a significant amount of work with the returned data.

I've got some references on order, but any thoughts and advice
appreciated.

Phil J.

[Serge Rielau]

PJ******@txfb-ins.com wrote:

DB2 UDB 8.X on Windows server

A couple of questions:
1.) I am trying to develop some DB2 SQL stored procedures. Dev
environment at UDB v 8.2,
production environment still at UDB v 8.1. Any issues with
migrating these things since the Dev environment does not need a C
compiler and production does?

In principle no. As long as you don't try to use the GET/PUT routine
procedures. Just running the DDL will be fine.
BUT: The removal of the C-Compiler was a MAJOR redesign. Your
test-environment shouldn't be fundamentally different from production.
In a mission critical production system I'd have a queasy stomach.

2.) Main stored procedure calling other stored procedures. Called
SPs simply declare a cursor for
some SQL and open the cursor and return. Problem is iterating through
the result set from the calling SP.
For example, I declare this locator:
DECLARE v_MemberRSLocator RESULT_SET_LOCATOR VARYING;
and later execute the following:

CALL TFBUDB.SMACF_MBR_INFO(SP_POL_NBR_TXT,ERR_CD,ERR_MS G);
ASSOCIATE RESULT SET LOCATOR (v_MemberRSLocator)
WITH PROCEDURE TFBUDB.SMACF_MBR_INFO;
ALLOCATE v_MemberCursor CURSOR FOR RESULT SET v_MemberRSLocator;
WHILE (SQLSTATE = '00000') DO
FETCH FROM v_MemberCursor INTO v_MemberNbr;

-- do whatever with data here
END WHILE;

** It seems to loop once and exit. If I add any statements within the
loop it seems to ignore the stopping condition of SQLSTATE = '00000'
and loop forever.

If you have any statement following the FETCH the SQLSTATE is reset.
Hence the endless loop. I'm unclear why you see the exit.
Typically cursor loops are written like this:

readresults: LOOP
FETCH ...
IF SQLCODE = 100 THEN LEAVE readresults; END
END LOOP;

For regular cursors (not resultsets) I prefer FOR:
FOR myrow AS (SELECT c1 FROM T) DO
SET x = myrow.c1 + x;
END FOR;

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[PJackson]

In principle no. As long as you don't try to use the GET/PUT routine
procedures. Just running the DDL will be fine.
BUT: The removal of the C-Compiler was a MAJOR redesign. Your
test-environment shouldn't be fundamentally different from production.
In a mission critical production system I'd have a queasy stomach.

1.) Ok, that is good to know and I will pass that info on to the
powers that be...

2.) This is my main stored procedure and will be calling about 10
other stored procedures,
iterating through the results fetched back, and operating on the data
returned. This is to replace a 2300 line COBOL stored procedure.
So how would I:
Call SP1(p1,p2,p3)
iterate through recordset returned from SP1 until EOF
do a whole lot of stuff with each row's columns
using a result set locator to handle the cursor left open by SP1
without resetting the
stopping case for the iteration

end iterate

Call SP2(p4,p7,p9)
iterate through rs returned from SP2
do a lot of stuff here
with each row returned
end iterate
thanks!

Phil Jackson

If you have any statement following the FETCH the SQLSTATE is reset.
Hence the endless loop. I'm unclear why you see the exit.
Typically cursor loops are written like this:

readresults: LOOP
FETCH ...
IF SQLCODE = 100 THEN LEAVE readresults; END
END LOOP;

For regular cursors (not resultsets) I prefer FOR:
FOR myrow AS (SELECT c1 FROM T) DO
SET x = myrow.c1 + x;
END FOR;

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

PJ******@txfb-ins.com wrote:

>In principle no. As long as you don't try to use the GET/PUT routine
procedures. Just running the DDL will be fine.
BUT: The removal of the C-Compiler was a MAJOR redesign. Your
test-environment shouldn't be fundamentally different from production.
In a mission critical production system I'd have a queasy stomach.

1.) Ok, that is good to know and I will pass that info on to the
powers that be...

2.) This is my main stored procedure and will be calling about 10
other stored procedures,
iterating through the results fetched back, and operating on the data
returned. This is to replace a 2300 line COBOL stored procedure.
So how would I:
Call SP1(p1,p2,p3)
iterate through recordset returned from SP1 until EOF
do a whole lot of stuff with each row's columns
using a result set locator to handle the cursor left open by SP1
without resetting the
stopping case for the iteration

end iterate

Call SP2(p4,p7,p9)
iterate through rs returned from SP2
do a lot of stuff here
with each row returned
end iterate

As noted in my previous email.
CALL ..
ALLOCATE/ASSOCIATE...
LOOP
FETCH ...;
IF SQLCODE = 100 THEN LEAVE END IF;
.. all your complex stuff..
END LOOP;
CLOSE..;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[PJackson]

Thanks, Serge,

That works like a charm. I now have an approx. 800+ line stored proc
that is calling about 10 other stored procs. My next task is to make
sure that any SQL errors are cascaded back to the client and any DB
work prior to the error is rolled back. Can I wrap up the whole main
stored proc within an "Atomic" wrapper and simply have a handler? A
little fuzzy on the methodology needed to make all of this bullet-
proof as possible.

thanks,

Phil Jackson

As noted in my previous email.
CALL ..
ALLOCATE/ASSOCIATE...
LOOP
FETCH ...;
IF SQLCODE = 100 THEN LEAVE END IF;
.. all your complex stuff..
END LOOP;
CLOSE..;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

PJ******@txfb-ins.com wrote:

That works like a charm. I now have an approx. 800+ line stored proc
that is calling about 10 other stored procs. My next task is to make
sure that any SQL errors are cascaded back to the client and any DB
work prior to the error is rolled back. Can I wrap up the whole main
stored proc within an "Atomic" wrapper and simply have a handler? A
little fuzzy on the methodology needed to make all of this bullet-
proof as possible.

That will work as long as you're not on an old version of DB2 (*squint*
pre-V8.2 ?). In older versions of DB2 you could not nest BEGIN ATOMIC.
Either way. BEGIN ATOMIC is syntactic sugar for using a SAVEPOINT.
If all you want is return the error to the client you need to do no more.
In the absence of a handler DB2 will pass the error up un handled trough
the CALL statement which will cause your main procedure to fail, roll
back the atomic block and again pass the original error back to your client.

Cheer
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab