First you always should check if your SQL Documentation is for Db2 for i
(and not any other database not even for DB2 LUW or DB2 Z).
Here is the link for the Db2 for i documentation for GET DIAGNOSTICS in
SQL/PL:
https://www.ibm.com/docs/en/i/7.6.0?topic=pl-get-diagnostics-statement
SQLCODE and SQLSTATE can be defined as variables an can then be used for
examples in an Loop to check if the end of the end of the data is reached.
But IIRC they cannot be used to check or trap errors.
The GET DIAGNOSTICS is executed each time after the previous statement is
executed and not only if an error occurs. ... and instead of EXCEPTION you
have to specify CONDITION.
DB2_RETURNED_SQLCODE returns 0 if everything is executed correctly.
RETURNED SQLSTATE returns '00000' if everything is executed correctly.
So your EXECUTE IMMEDIATE statement was executed correctly.
If you only want to Check or Return the SQLCODE and/Or SQLSTATE if an error
occurs, you need an CONTINUE HANDLER (if you want to continue after the
error is handled) or an EXIT or UNDO HANLER (if you want to end the
procedure after the error is handled).
The Handlers have to be coded at the end of the DECLARE Section and get
activated whenever the appropriate SQLSTATE occurs. Instead of using the
SQLSTATE you may specify SQLEXCEPTION to trap any error that is not handled
before. So may be there is no need for receiving the SQLCODE or SQLSTATE
explicitly.
Begin
-- Declare Variables
-- Declare Cursor
-- Declare HANDLERS for specific errors (SQLSTATEs)
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -- Handler for any not yet
handled error
Begin
-- Do whatever you want if any error occurs
End;
-- your Statements to be executed
EXECUTE IMMEDIATE YourSQLStmt;
...
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Dan
Bale
Sent: Friday, 20 June 2025 16:53
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: How to get SQLCODE / SQLSTATE after EXECUTE IMMEDIATE in SQL stored
procedure?
... or is the system debugger from ACS' RSS itself buggy?
EXECUTE IMMEDIATE SQLstatement;
GET DIAGNOSTICS EXCEPTION 1
v_sqlcode = DB2_RETURNED_SQLCODE,
v_sqlstate = RETURNED_SQLSTATE;
It compiles fine, but it always returns zeros when it completes, at least in
the debugger (both the RSS GUI and the green screen). BTW, this is just the
latest iteration of many to try to figure out how to return a simple (ha!)
SQLCODE / SQLSTATE.
I am confounded by the options in GET DIAGNOSTICS. The SQL Reference has 25
pages on it, but nearly zero understanding.
- Dan Bale
*** CONFIDENTIALITY NOTICE: The information contained in this communication
may be confidential, and is intended only for the use of the recipients
named above. If the reader of this message is not the intended recipient,
you are hereby notified that any dissemination, distribution, or copying of
this communication, or any of its contents, is strictly prohibited. If you
have received this communication in error, please return it to the sender
immediately and delete the original message and any copy of it from your
computer system. If you have any questions concerning this message, please
contact the sender. ***
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
