By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,389 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

Strange Behaviour of SP due to GET DIAGNOSTICS position

P: n/a
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_PROC1
(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.SYSDUMMY1;

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_PROC1
(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.SYSDUMMY1;

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

Aug 17 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.SYSDUMMY1;

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/
Aug 17 '06 #2

P: n/a
Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Please
help by explaining this strange behaviour.

Aug 17 '06 #3

P: n/a
Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Please
help by explaining this strange behaviour.

Aug 18 '06 #4

P: n/a
Praveen_db2 wrote:
Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Please
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/
Aug 18 '06 #5

P: n/a
Sorry Serge.
Hadn't gone through your reply properly.
Silly mistake.

Thanks a lot
Praveen

Aug 18 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.