Re: New DB2 external Stored Procedure question - setting sqlcode/sqlstate
On Thu, 9 Feb 2023 18:01:24 + Sri h Kolusu wrote: :>>>2. How can I specify the SQLSTATE so that NOT FOUND will be the condition? I am writing the SP itself in COBOL and want to return an SQLCODE. :>Assuming that you are calling the Stored procedure from COBOL, here is an example of how the SQLCODES are handled. :>https://www.ibm.com/docs/en/db2-for-zos/12?topic=examples-example-cobol-program-that-calls-stored-procedure The SP GETPRML can only return to the LIST. The +466 is not done by the SP itself, it is done by DB2 SP processing because cursors are returned. -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: New DB2 external Stored Procedure question - setting sqlcode/sqlstate
On Thu, 9 Feb 2023 13:01:00 -0500 "Lance D. Jackson" wrote: :>The -818 SQLCode is a timestamp disparity between the load module and the :>DBRM. Make sure that the WLM which the SP runs in has been properly :>refreshed since the SP was last built. When the SQL in the SP gets SQLCODE, the SP returns the data - so the SP works and is bound. When the SP gets +100, the SP sets SQLSTATE (in the extended plist) to '02000' The CALLer gets the -443 and the -443 description says -443 ROUTINE routine-name (SPECIFIC NAME specific-name) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT msg-text Explanation An SQLSTATE was returned by routine routine-name, along with message text msg-text. System action Execution of the SQL statement is terminated. :>-Original Message- :>From: IBM Mainframe Discussion List On Behalf Of :>Binyamin Dissen :>Sent: Thursday, February 9, 2023 12:03 :>To: IBM-MAIN@LISTSERV.UA.EDU :>Subject: New DB2 external Stored Procedure question - setting :>sqlcode/sqlstate :> :>I am trying to return a NOT FOUND condition, so I am setting the SQLSTATE :>parameter to '02000' :> :>However, the calling program is getting :> :>DSNT408I SQLCODE = -443, ERROR: ROUTINE SP01 (SPECIFIC NAME SP01) HAS :>RETURNED :> AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT DSNT418I SQLSTATE = 02000 :>SQLSTATE RETURN CODE DSNT415I SQLERRP= DSNXRRTN SQL PROCEDURE DETECTING :>ERROR DSNT416I SQLERRD= -818 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION :>DSNT416I SQLERRD= X'FCCE' X'' X'' X'' :>X'' X'' SQL DIAGNOSTIC INFORMATION 1. How can I specify the :>SQLCODE (that is returned to the CALLer)? Return-code doesn't work. :> :>2. How can I specify the SQLSTATE so that NOT FOUND will be the condition? :> :>SIGNAL does not appear to work for COBOL code. -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: New DB2 external Stored Procedure question - setting sqlcode/sqlstate
>>2. How can I specify the SQLSTATE so that NOT FOUND will be the condition? Binyamin Dissen, Assuming that you are calling the Stored procedure from COBOL, here is an example of how the SQLCODES are handled. https://www.ibm.com/docs/en/db2-for-zos/12?topic=examples-example-cobol-program-that-calls-stored-procedure Thanks, Kolusu -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: New DB2 external Stored Procedure question - setting sqlcode/sqlstate
Binyamin, The -818 SQLCode is a timestamp disparity between the load module and the DBRM. Make sure that the WLM which the SP runs in has been properly refreshed since the SP was last built. -Original Message- From: IBM Mainframe Discussion List On Behalf Of Binyamin Dissen Sent: Thursday, February 9, 2023 12:03 To: IBM-MAIN@LISTSERV.UA.EDU Subject: New DB2 external Stored Procedure question - setting sqlcode/sqlstate I am trying to return a NOT FOUND condition, so I am setting the SQLSTATE parameter to '02000' However, the calling program is getting DSNT408I SQLCODE = -443, ERROR: ROUTINE SP01 (SPECIFIC NAME SP01) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT DSNT418I SQLSTATE = 02000 SQLSTATE RETURN CODE DSNT415I SQLERRP= DSNXRRTN SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD= -818 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD= X'FCCE' X'' X'' X'' X'' X'' SQL DIAGNOSTIC INFORMATION 1. How can I specify the SQLCODE (that is returned to the CALLer)? Return-code doesn't work. 2. How can I specify the SQLSTATE so that NOT FOUND will be the condition? SIGNAL does not appear to work for COBOL code. -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
New DB2 external Stored Procedure question - setting sqlcode/sqlstate
I am trying to return a NOT FOUND condition, so I am setting the SQLSTATE parameter to '02000' However, the calling program is getting DSNT408I SQLCODE = -443, ERROR: ROUTINE SP01 (SPECIFIC NAME SP01) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT DSNT418I SQLSTATE = 02000 SQLSTATE RETURN CODE DSNT415I SQLERRP= DSNXRRTN SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD= -818 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD= X'FCCE' X'' X'' X'' X'' X'' SQL DIAGNOSTIC INFORMATION 1. How can I specify the SQLCODE (that is returned to the CALLer)? Return-code doesn't work. 2. How can I specify the SQLSTATE so that NOT FOUND will be the condition? SIGNAL does not appear to work for COBOL code. -- Binyamin Dissen http://www.dissensoftware.com Director, Dissen Software, Bar & Grill - Israel -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
