By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,449 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.

Getting SQLERRMC into DB2 for LUW Stored Procedure

P: n/a
I'm trying to get the SQLERRMC info from the SQLCA into my SP so that I
can use the information it provides. I'm successfully getting the
SQLCODE and SQLSTATE and so added the SQLERRMC to the code which
obtains these. It doesn't seem to work however -

create procedure dbair001.sp001testerr
(
,OUT p_sqlstate CHAR(5)
,OUT p_sqlcode INTEGER
,OUT p_sqlerrmc VARCHAR(70)
)
specific dbair001.sp001testerr
begin
--
-- Declare return codes
--
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLERRMC VARCHAR(70);
--
-- Define working storage
--
declare v_dynstmt VARCHAR(100);
--
-- Define error handlers
--
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT
SQLSTATE,
SQLCODE,
SQLERRMC
INTO
p_sqlstate,
p_sqlcode,
p_sqlerrmc
FROM
sysibm.sysdummy1;
--
-- Start of procedure logic
--
-- Initialize output parameters with defaults
--
VALUES (SQLSTATE,SQLCODE, SQLERRMC) INTO p_sqlstate, p_sqlcode,
p_sqlerrmc;
--
-- Set up SQL to unknown table
--
SET v_dynstmt = 'INSERT INTO DBAIR001.T0010TEST (COL1) VALUES
(1)';
PREPARE v_prepstmt FROM v_dynstmt;
EXECUTE v_prepstmt;
end#

This doesn't work however -

bash-2.05b$ db2 "call dbair001.sp001testerr(?,?,?)"

Value of output parameters
--------------------------
Parameter Name : P_SQLSTATE
Parameter Value : 42704

Parameter Name : P_SQLCODE
Parameter Value : -204

Parameter Name : P_SQLERRMC
Parameter Value : -

Return Status = 0

Any idea what I'm doing wrong ?

Thanks

Phil Nelson

Aug 23 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Use the GET_DIAGNOSTICS statement:
http://publib.boulder.ibm.com/infoce...n/r0005647.htm

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 23 '06 #2

P: n/a
Serge Rielau wrote:
Use the GET_DIAGNOSTICS statement:
http://publib.boulder.ibm.com/infoce...n/r0005647.htm
>
Cheers
Serge
Thanks Serge : I should have remembered that !!!

Phil
Aug 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.