SQL PL NULL OUT Parameters

DJ Jones

SQL PL NULL OUT Parameters

When I run the following SQL PL proc, it returns null values
in both OUT parms.

I see these null values in Data Studio and in a calling JAVA script.

As coded below, to debug, I write the values to a DB2 Temp Table and see the
values populated. (I force a not found condtion by using other values
than '01' and '02' in the WHERE clause.)

I'm not getting a not found condition unless I code a FETCH. That is,
opening the cursor does not set SQlCODE or SQLSTATE.

I've tried just setting the output parameters inside and outside the
HANDLER blocks (SET O_SQLCODE to SQLCODE) and I get initialzied
values, zeroes, regardless of what is in the WHERE clause.

I've also tried moving the HANDLERs before the CURSOR declare. This
makes no difference.

As far as I can determine, SQL PL isnt' used in the shop, so
I can't determine if other scripts are working.

I'm thinking there is something going on at the DB2 subsystem
level, ie. a ZPARM or a missing PTF. We are running
z/OS DB2 V11.

Any help is greatly appreciated.

CREATE PROCEDURE DSSADU4.NFOSPNP1 (OUT O_SQLSTATE CHAR(5)
,OUT O_SQLCODE INTEGER)
VERSION V1
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
QUALIFIER DSSADU4
DYNAMIC RESULT SETS 1
SQL PATH "SYSIBM","SYSFUN","SYSPROC"
ASUTIME NO LIMIT
COMMIT ON RETURN NO
CURRENT DATA NO
DEGREE 1
DYNAMICRULES BIND
ISOLATION LEVEL UR

BEGIN

--DECLARE VARIABLES
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE O_SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE O_SQLCODE INTEGER DEFAULT '0';

DECLARE REQ_DATA CURSOR WITH RETURN FOR
SELECT A.REQ_STAT_CD

FROM NTRAC_SVC_REQ A

WHERE A.REQ_STAT_CD IN ('01' -- PENDING REQUEST
,'02') -- RETRY REQUESTED

FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
FOR FETCH ONLY;

--DECLARE HANDLERS
DECLARE CONTINUE HANDLER FOR NOT FOUND
SELECT SQLSTATE
,SQLCODE
INTO O_SQLSTATE
,O_SQLCODE
FROM SYSIBM.SYSDUMMY1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE
,SQLCODE
INTO O_SQLSTATE
,O_SQLCODE
FROM SYSIBM.SYSDUMMY1;

OPEN REQ_DATA;

FETCH REQ_DATA;

INSERT INTO WELZ281.DHTEST
VALUES (O_SQLCODE
,O_SQLSTATE)
;
COMMIT;

END #

 

Bill Gallagher

SQL PL NULL OUT Parameters
(in response to DJ Jones)
Just a guess, but I believe you’re defining O_SQLSTATE and O_SQLCODE twice . . . once in the parameter list, and then via the DECLARE statements in the procedure definition. That might be throwing something off when executing. I’m surprised that DB2 doesn’t throw an error or warning when you issue the CREATE PROCEDURE on this SP.

Try removing the DECLARE O_SQLSTATE and DECLARE O_SQLCODE statements. If you wish to initialize them, then insert the following two statements immediately before the OPEN REQ_DATA statement:

SET O_SQLSTATE = ‘00000’;
SET O_SQLCODE = 0;

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: DJ Jones [mailto:[login to unmask email]
Sent: Thursday, July 5, 2018 2:25 PM
To: [login to unmask email]
Subject: [DB2-L] - SQL PL NULL OUT Parameters


When I run the following SQL PL proc, it returns null values
in both OUT parms.

I see these null values in Data Studio and in a calling JAVA script.

As coded below, to debug, I write the values to a DB2 Temp Table and see the
values populated. (I force a not found condtion by using other values
than '01' and '02' in the WHERE clause.)

I'm not getting a not found condition unless I code a FETCH. That is,
opening the cursor does not set SQlCODE or SQLSTATE.

I've tried just setting the output parameters inside and outside the
HANDLER blocks (SET O_SQLCODE to SQLCODE) and I get initialzied
values, zeroes, regardless of what is in the WHERE clause.

I've also tried moving the HANDLERs before the CURSOR declare. This
makes no difference.

As far as I can determine, SQL PL isnt' used in the shop, so
I can't determine if other scripts are working.

I'm thinking there is something going on at the DB2 subsystem
level, ie. a ZPARM or a missing PTF. We are running
z/OS DB2 V11.

Any help is greatly appreciated.

CREATE PROCEDURE DSSADU4.NFOSPNP1 (OUT O_SQLSTATE CHAR(5)
,OUT O_SQLCODE INTEGER)
VERSION V1
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
QUALIFIER DSSADU4
DYNAMIC RESULT SETS 1
SQL PATH "SYSIBM","SYSFUN","SYSPROC"
ASUTIME NO LIMIT
COMMIT ON RETURN NO
CURRENT DATA NO
DEGREE 1
DYNAMICRULES BIND
ISOLATION LEVEL UR

BEGIN

--DECLARE VARIABLES
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE O_SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE O_SQLCODE INTEGER DEFAULT '0';

DECLARE REQ_DATA CURSOR WITH RETURN FOR
SELECT A.REQ_STAT_CD

FROM NTRAC_SVC_REQ A

WHERE A.REQ_STAT_CD IN ('01' -- PENDING REQUEST
,'02') -- RETRY REQUESTED

FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
FOR FETCH ONLY;

--DECLARE HANDLERS
DECLARE CONTINUE HANDLER FOR NOT FOUND
SELECT SQLSTATE
,SQLCODE
INTO O_SQLSTATE
,O_SQLCODE
FROM SYSIBM.SYSDUMMY1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE
,SQLCODE
INTO O_SQLSTATE
,O_SQLCODE
FROM SYSIBM.SYSDUMMY1;

OPEN REQ_DATA;

FETCH REQ_DATA;

INSERT INTO WELZ281.DHTEST
VALUES (O_SQLCODE
,O_SQLSTATE)
;
COMMIT;

END #



-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Bill Gallagher

SQL PL NULL OUT Parameters
(in response to Bill Gallagher)
A clarification on my previous post.

Variable scoping/visibility is coming into play. Any variables defined within a compound statement (defined by BEGIN and END statements) are visible only within the scope of the compound statement.

So essentially, the O_SQLSTATE and O_SQLCODE variable that you have defined within the procedure via the DECLARE statements are in fact separate variables from the ones you have defined as output parameters. Since the output variables are never actually referenced or set, they would contain (and return) NULL values.

My recommendation below should fix the problem. Please let us know if it does.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: Gallagher,Bill R [mailto:[login to unmask email]
Sent: Thursday, July 5, 2018 2:47 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: SQL PL NULL OUT Parameters

Just a guess, but I believe you’re defining O_SQLSTATE and O_SQLCODE twice . . . once in the parameter list, and then via the DECLARE statements in the procedure definition. That might be throwing something off when executing. I’m surprised that DB2 doesn’t throw an error or warning when you issue the CREATE PROCEDURE on this SP.

Try removing the DECLARE O_SQLSTATE and DECLARE O_SQLCODE statements. If you wish to initialize them, then insert the following two statements immediately before the OPEN REQ_DATA statement:

SET O_SQLSTATE = ‘00000’;
SET O_SQLCODE = 0;

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: DJ Jones [mailto:[login to unmask email]
Sent: Thursday, July 5, 2018 2:25 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - SQL PL NULL OUT Parameters


When I run the following SQL PL proc, it returns null values
in both OUT parms.

I see these null values in Data Studio and in a calling JAVA script.

As coded below, to debug, I write the values to a DB2 Temp Table and see the
values populated. (I force a not found condtion by using other values
than '01' and '02' in the WHERE clause.)

I'm not getting a not found condition unless I code a FETCH. That is,
opening the cursor does not set SQlCODE or SQLSTATE.

I've tried just setting the output parameters inside and outside the
HANDLER blocks (SET O_SQLCODE to SQLCODE) and I get initialzied
values, zeroes, regardless of what is in the WHERE clause.

I've also tried moving the HANDLERs before the CURSOR declare. This
makes no difference.

As far as I can determine, SQL PL isnt' used in the shop, so
I can't determine if other scripts are working.

I'm thinking there is something going on at the DB2 subsystem
level, ie. a ZPARM or a missing PTF. We are running
z/OS DB2 V11.

Any help is greatly appreciated.

CREATE PROCEDURE DSSADU4.NFOSPNP1 (OUT O_SQLSTATE CHAR(5)
,OUT O_SQLCODE INTEGER)
VERSION V1
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
QUALIFIER DSSADU4
DYNAMIC RESULT SETS 1
SQL PATH "SYSIBM","SYSFUN","SYSPROC"
ASUTIME NO LIMIT
COMMIT ON RETURN NO
CURRENT DATA NO
DEGREE 1
DYNAMICRULES BIND
ISOLATION LEVEL UR

BEGIN

--DECLARE VARIABLES
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE O_SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE O_SQLCODE INTEGER DEFAULT '0';

DECLARE REQ_DATA CURSOR WITH RETURN FOR
SELECT A.REQ_STAT_CD

FROM NTRAC_SVC_REQ A

WHERE A.REQ_STAT_CD IN ('01' -- PENDING REQUEST
,'02') -- RETRY REQUESTED

FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
FOR FETCH ONLY;

--DECLARE HANDLERS
DECLARE CONTINUE HANDLER FOR NOT FOUND
SELECT SQLSTATE
,SQLCODE
INTO O_SQLSTATE
,O_SQLCODE
FROM SYSIBM.SYSDUMMY1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE
,SQLCODE
INTO O_SQLSTATE
,O_SQLCODE
FROM SYSIBM.SYSDUMMY1;

OPEN REQ_DATA;

FETCH REQ_DATA;

INSERT INTO WELZ281.DHTEST
VALUES (O_SQLCODE
,O_SQLSTATE)
;
COMMIT;

END #



-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
-----End Original Message-----

DJ Jones

RE: SQL PL NULL OUT Parameters
(in response to Bill Gallagher)

That worked.

In short, the DECLARE PROCEDURE  OUT Parameters were not being populated, but the parameters within the BEGIN and END scope. with the same names,  were.

Apparently, without the double definitions, the OUT parameters have visibility within the scope, although they exist outside the scope. ie., they are GLOBAL.

Thanks very much for the help.

.