Strange Behaviour of SP due to GET DIAGNOSTICS position

[Praveen_db2]

Hi All
db2 8.1.3 Windows
I have folowing table structures
CREATE TABLE tb_RTB(
EMP_ID INTEGER,
DESC VARCHAR(20));

CREATE TABLE tb_ERROR(
SQL_STATE CHAR(5),
SQL_DESC VARCHAR(20),
REFER varchar(20));

Following entries are inserted in tb_RTB

INSERT INTO tb_RTB VALUES(1,'MGR') ,(2,'CLK'),(3,' CLK')

I have a stored procedure USP_PROC1

CREATE PROCEDURE WSACES.USP_PROC 1
(IN iEMP_ID INTEGER,
OUT SQLDESC_OUT VARCHAR(20),
OUT SQLSTATE_OUT CHAR(5))
SPECIFIC PROC1
LANGUAGE SQL
P1: BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE NO_DATA_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE vSQL_MSG VARCHAR(4096) DEFAULT 'SUCCESS';

DECLARE EXIT HANDLER FOR NO_DATA_FOUND
BEGIN
--Get the error message text
GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

SELECT SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY 1;

SELECT SQL_DESC
INTO SQLDESC_OUT
FROM tb_ERROR
WHERE SQL_STATE = SQLSTATE_OUT
AND REFER = 'PROC1';

END;

UPDATE tb_RTB
SET DESC = 'MGR'
WHERE EMP_ID = iEMP_ID;
END P1

This procedure updates the table tb_RTB properly.
Now I update the value back to 'CLK' for EMP_ID 2.

I change the GET DIAGNOSTICS position and place it out of Begin - End
Block of handler.
Now the SP is

CREATE PROCEDURE WSACES.USP_PROC 1
(IN iEMP_ID INTEGER,
OUT SQLDESC_OUT VARCHAR(20),
OUT SQLSTATE_OUT CHAR(5))
SPECIFIC PROC1
LANGUAGE SQL
P1: BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE NO_DATA_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE vSQL_MSG VARCHAR(4096) DEFAULT 'SUCCESS';

DECLARE EXIT HANDLER FOR NO_DATA_FOUND

--Get the error message text
GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

BEGIN
--Get the error message text
-- GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

SELECT SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY 1;

SELECT SQL_DESC
INTO SQLDESC_OUT
FROM tb_ERROR
WHERE SQL_STATE = SQLSTATE_OUT
AND REFER = 'PROC1';

END;

UPDATE tb_RTB
SET DESC = 'MGR'
WHERE EMP_ID = iEMP_ID;
END P1
This SP doesnot update the table tb_RTB.
Can any gurus explain why is this happening?

Regards
Praveen

[Serge Rielau]

Praveen_db2 wrote:

DECLARE EXIT HANDLER FOR NO_DATA_FOUND

--Get the error message text
GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

This is the end of of handler

>
BEGIN

A nested compound statement, nothing more

--Get the error message text
-- GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

SELECT SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY 1;

SELECT SQL_DESC
INTO SQLDESC_OUT
FROM tb_ERROR
WHERE SQL_STATE = SQLSTATE_OUT
AND REFER = 'PROC1';

My bet: You're getting a 02000 right here. Exit handler, game over.

END;
UPDATE tb_RTB
SET DESC = 'MGR'
WHERE EMP_ID = iEMP_ID;
END P1

If you drive this through the debugger it should show what happens.
Note that Developer Workbench works against DB2 V8.2, in case you don't
like Developer Center V8.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Praveen_db2]

Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY 1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Pleas e
help by explaining this strange behaviour.

[Praveen_db2]

Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY 1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Pleas e
help by explaining this strange behaviour.

[Serge Rielau]

Praveen_db2 wrote:

Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY 1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Pleas e
help by explaining this strange behaviour.

I did. Please reread my post.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Praveen_db2]

Sorry Serge.
Hadn't gone through your reply properly.
Silly mistake.

Thanks a lot
Praveen