SEO

Hi All;
Am running DB2 LUW v8.2 (FP10) on AIX and having trouble with SQL
stored procedure bind options.

I have run the following code expecting to be able to change the
isolation level for this proc from the default of cursor stability to
repeatable read. I have experimented with placing COMMITS between the
statements and the procedure package is always created with the default
isolation of CS. For the life of me, I cannot get the
set_routine_opts() procedure to work. Help!? (and thanks in advance...)

Pete H
--------------------------------------------------------------------------------------------------------------------------------------------------
call set_routine_opts('ISOLATION RR');
CREATE PROCEDURE "SPPCH"."EDWUTIL_VALID_SYSIDS2" ( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0 ;

-- use WITH RETURN TO CLIENT in DECLARE CURSOR to always
-- return a result set to the client application
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
select SYS_ID as value, SYS_DESC as meaning
from dd.SYSTEM
order by sys_id;
-- to return result set, do not CLOSE cursor
OPEN c1;

END;

Hi, Pete:

I appreciate that you want to know why the SET_ROUTINE SP isn't working
as you think it should (sorry I can't help you there), but if all else
fails, consider adding WITH RR after your ORDER BY, i.e.,

CREATE PROCEDURE "SPPCH"."EDWUTIL_VALID_SYSIDS2" ( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0 ;

-- USE WITH RETURN TO CLIENT IN DECLARE CURSOR TO ALWAYS
-- RETURN A RESULT SET TO THE CLIENT APPLICATION
DECLARE C1 CURSOR WITH RETURN TO CALLER FOR
SELECT SYS_ID AS VALUE, SYS_DESC AS MEANING
FROM DD.SYSTEM
ORDER BY SYS_ID
WITH RR;
-- TO RETURN RESULT SET, DO NOT CLOSE CURSOR
OPEN C1;

END;

--Jeff

peteh wrote:
> Hi All;
> Am running DB2 LUW v8.2 (FP10) on AIX and having trouble with SQL
> stored procedure bind options.
>
> I have run the following code expecting to be able to change the
> isolation level for this proc from the default of cursor stability to
> repeatable read. I have experimented with placing COMMITS between the
> statements and the procedure package is always created with the default
> isolation of CS. For the life of me, I cannot get the
> set_routine_opts() procedure to work. Help!? (and thanks in advance...)
>
> Pete H
> --------------------------------------------------------------------------------------------------------------------------------------------------
> call set_routine_opts('ISOLATION RR');
> CREATE PROCEDURE "SPPCH"."EDWUTIL_VALID_SYSIDS2" ( )
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> READS SQL DATA
> BEGIN
> DECLARE SQLCODE INTEGER DEFAULT 0 ;
>
> -- use WITH RETURN TO CLIENT in DECLARE CURSOR to always
> -- return a result set to the client application
> DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
> select SYS_ID as value, SYS_DESC as meaning
> from dd.SYSTEM
> order by sys_id;
> -- to return result set, do not CLOSE cursor
> OPEN c1;
>
> END;

How about this?
------------------------------ Commands Entered
------------------------------
--#SET TERMINATOR !
CALL SET_ROUTINE_OPTS('ISOLATION RR')!
CREATE PROCEDURE TEST (OUT outVALUE INT)
LANGUAGE SQL
BEGIN
SET outVALUE = 1;
END!
------------------------------------------------------------------------------
CALL SET_ROUTINE_OPTS('ISOLATION RR')

Return Status = 0


CREATE PROCEDURE TEST (OUT outVALUE INT)
LANGUAGE SQL
BEGIN
SET outVALUE = 1;
END
DB20000I The SQL command completed successfully.

Tonkuma wrote:
> How about this?
> ------------------------------ Commands Entered
> ------------------------------

Thanks Tonkuma (and Jeffyzzer);
I can get this to work as long as I use the CLP. I can't find the right
syntax to run the set and create from a Windows (Quest) client and am
still not sure why that is... CLP will get me where I need to go for
now. Thanks!

Pete H