Problem with SET_ROUTINE_OPTS()

[peteh]

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;

[jefftyzzer]

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;

[Tonkuma]

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.

[peteh]

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