smythsit
asked on
Set isolation level in db2 luw stored procedure
Hi,
I'm new to DB2 and we're just starting to convert our sql stored procedures to db2 luw using the ibm data studio.
I just want to make sure i have the "set current isolation ur" satement in the correct place.
I can't seem to put the statement above the variable declaration. Would you know why this is?
Also, with the isolation statement placed here, is the whole stored procedure set to use read uncommitted?
The test stored procedure is below.
Thanks.
I'm new to DB2 and we're just starting to convert our sql stored procedures to db2 luw using the ibm data studio.
I just want to make sure i have the "set current isolation ur" satement in the correct place.
I can't seem to put the statement above the variable declaration. Would you know why this is?
Also, with the isolation statement placed here, is the whole stored procedure set to use read uncommitted?
The test stored procedure is below.
Thanks.
CREATE PROCEDURE TEMP_PROC1 (IN NAMEA VARCHAR(125))
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE currentDEPT varchar(10);
SET currentDEPT = 'Test';
SET CURRENT ISOLATION UR;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE
(COL1 VARCHAR(125)
) ON COMMIT PRESERVE ROWS NOT LOGGED WITH REPLACE;
INSERT INTO SESSION.TEMP_TABLE
SELECT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = NAMEA;
COMMIT;
BEGIN
DECLARE c2 CURSOR WITH RETURN FOR
SELECT COL1, currentDEPT
FROM SESSION.TEMP_TABLE;
OPEN c2;
END;
END P1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER