Help please: Native Stored Procedure erring with -514

Jay Bovard

Help please: Native Stored Procedure erring with -514

DB2 for Z/OS v11 (Conversion Mode)

-514: The cursor SELECT_CURSOR is not in a prepared state

The stored procedure has no result sets and no cursor processing (DB2 is identifying a CLOSE call type). All individual SELECT statements have been tested successfully, yet I cannot get the procedure to run at all. Can anyone help explain why I am getting this error?

  CREATE PROCEDURE F0DB2N.SPQ0005
(
  IN  I_ACCT      INTEGER
,OUT O_CNT_COST  SMALLINT
,OUT O_YESTERDAY DECIMAL(7,2)
,OUT O_REMAINING SMALLINT
,OUT O_LAST_BAL  DECIMAL(7,2)
,OUT O_LAST_TMS  TIMESTAMP
,OUT O_PND_PURCH CHAR(1)
)
VERSION V1
 NOT DETERMINISTIC
 MODIFIES SQL DATA
 CALLED ON NULL INPUT
 DYNAMIC RESULT SETS 0
 DISALLOW DEBUG MODE
 PARAMETER CCSID EBCDIC
 QUALIFIER F0DB2N
 PACKAGE OWNER F0DB2N
 ASUTIME NO LIMIT
 COMMIT ON RETURN NO
 INHERIT SPECIAL REGISTERS
 WLM ENVIRONMENT FOR DEBUG MODE DST_SP
 CURRENT DATA NO
 DEGREE 1
 DYNAMICRULES RUN
 APPLICATION ENCODING SCHEME EBCDIC
 WITHOUT EXPLAIN
 WITHOUT IMMEDIATE WRITE
 ISOLATION LEVEL CS
 WITHOUT KEEP DYNAMIC
 RELEASE AT COMMIT
 REOPT NONE
 VALIDATE RUN
 ROUNDING DEC_ROUND_HALF_EVEN
 DECIMAL(31)
 BEGIN
   DECLARE V_LOC_K INTEGER DEFAULT 0 ;
   DECLARE V_MTR_N CHAR(7) DEFAULT ' ' ;
   DECLARE V_MTR_BAL DECIMAL(7,2) DEFAULT 0 ;
   DECLARE V_MTR_TMS TIMESTAMP ;
   DECLARE V_PMT_BAL DECIMAL(7,2) DEFAULT 0 ;
   DECLARE V_PMT_TMS TIMESTAMP ;
   DECLARE V_SUM_COST DECIMAL(7,2) DEFAULT 0 ;
   SET O_CNT_COST  = 0 ;
     SET O_YESTERDAY = 0 ;
     SET O_REMAINING = 0 ;
     SET O_LAST_BAL  = 0 ;
     SET O_LAST_TMS  = NULL ;
     SET O_PND_PURCH = 'N' ;
   -- RETRIEVE LOCATION KEY
     SELECT LOCATN_K
       INTO V_LOC_K
       FROM TCUSTLOC
      WHERE BILACCT_K = I_ACCT ;
      -- RETRIEVE CURRENT METER NUMBER
        SELECT MTR_SRP_N
          INTO V_MTR_N
          FROM TLOCMTR
         WHERE LOCATN_K = V_LOC_K
           AND MTR_XCHG_OUT_D IS NULL
           AND MTR_RMV_D IS NULL ;
   -- RETRIEVE TOTALS FOR LAST 7 DAYS OF METER COSTS
     SELECT SUM(READ_Q)
           ,COUNT(READ_Q)
       INTO V_SUM_COST
           ,O_CNT_COST
       FROM TMTRDYPP
      WHERE LOCATN_K       = V_LOC_K
        AND MTR_SRP_N      = V_MTR_N
        AND RGSTR_TYP_C    = 704
        AND DATE(READ_TMS) > CURRENT_DATE - 6 DAYS ;
    -- RETRIEVE LAST METER COST
      SELECT READ_Q
        INTO O_YESTERDAY
        FROM TMTRDYPP P1
       WHERE P1.LOCATN_K       = V_LOC_K
         AND P1.MTR_SRP_N      = V_MTR_N
         AND P1.RGSTR_TYP_C    = 704
         AND P1.READ_TMS       = ( SELECT MAX(READ_TMS)
                                     FROM TMTRDYPP P2
                                    WHERE P2.LOCATN_K    = V_LOC_K
                                      AND P2.RGSTR_TYP_C = 704 ) ;
  -- RETRIEVE LAST MIDNIGHT READ METER BALANCE & TIMESTAMP
    SELECT READ_Q
          ,READ_TMS
      INTO V_MTR_BAL
          ,V_MTR_TMS
      FROM TMTRDYPP P1
     WHERE P1.LOCATN_K    = V_LOC_K
       AND P1.MTR_SRP_N   = V_MTR_N
       AND P1.RGSTR_TYP_C = 701
       AND P1.READ_TMS    = ( SELECT MAX(READ_TMS)
                                FROM TMTRDYPP P2
                               WHERE P2.LOCATN_K    = V_LOC_K
                                 AND P2.RGSTR_TYP_C = 701 ) ;
   -- RETRIEVE LAST PURCHASE METER BALANCE & TIMESTAMP
     SELECT MTR_BAL_A
           ,CR_XFER_TMS
       INTO V_PMT_BAL
           ,V_PMT_TMS
       FROM TPPFNRPT R1
      WHERE R1.BILACCT_K   = I_ACCT
        AND R1.MTR_BAL_A  IS NOT NULL
        AND R1.CR_XFER_TMS = ( SELECT MAX(CR_XFER_TMS)
                                 FROM TPPFNRPT R2
                                WHERE R2.BILACCT_K  = I_ACCT
                                  AND R2.MTR_BAL_A IS NOT NULL ) ;
   -- RETRIEVE LAST PURCHASE (IS IT PENDING A METER CONFIRMATION?)
     SELECT CASE WHEN CR_XFER_TMS IS NULL THEN 'Y' ELSE 'N' END
       INTO O_PND_PURCH
       FROM TPPFNRPT
      WHERE BILACCT_K          = I_ACCT
        AND MPA_EVNT_TYP_C     = 'PURCH'
        AND RCNCT_ATHZN_STTS_C = 'Y'
      ORDER BY ORIG_PST_TMS DESC
      FETCH FIRST 1 ROWS ONLY ;
    -- CALCULATE ESTIMATED DAYS REMAINING
        IF (O_CNT_COST > 0 AND V_SUM_COST > 0)
          THEN SET O_REMAINING = ABS(V_SUM_COST / O_CNT_COST) ;
        END IF ;
    -- DETERMINE LAST METER BALANCE AND TIMESTAMP BY COMPARING
    -- THE LAST VALUES FROM THE MIDNIGHT READ & THE LAST PURCHASE
        IF (V_MTR_TMS IS NULL AND V_PMT_TMS IS NULL)
            THEN SET O_LAST_TMS = NULL ;
        ELSEIF (V_MTR_TMS IS NOT NULL AND V_PMT_TMS IS NULL)
            THEN SET O_LAST_TMS = V_MTR_TMS ;
                 SET O_LAST_BAL = V_MTR_BAL ;
      ELSEIF (V_MTR_TMS IS NULL AND V_PMT_TMS IS NOT NULL)
          THEN SET O_LAST_TMS = V_PMT_TMS ;
               SET O_LAST_BAL = V_PMT_BAL ;
      ELSEIF V_MTR_TMS > V_PMT_TMS
          THEN SET O_LAST_TMS = V_MTR_TMS ;
               SET O_LAST_BAL = V_MTR_BAL ;
      ELSE SET O_LAST_TMS = V_PMT_TMS ;
           SET O_LAST_BAL = V_PMT_BAL ;
      END IF ;
  END

 

Edited By:
Jay Bovard[Organization Members] @ Jul 10, 2017 - 01:09 PM (America/Arizona)

Sam Baugh

RE: Help please: Native Stored Procedure erring with -514
(in response to Jay Bovard)

Are you sure the -514 error came from this stored procedure?  If you have Data Studio (free tool), you can debug native stored procedures (its especially nice to step through the procedure by each statement, set breakpoints, see variable values, etc).  Otherwise, I suggest adding a "debug message" out parameter, and use strategically placed SET O_DEBUG_MSG = 'whatever makes sense'; statements to let you know how far you are getting into the procedure.  If the -514 is from this procedure, I would guess that one of your SQL statements is returning more that 1 row, and may need a FETCH FIRST ROW ONLY.  There is also no exception handlers / logic.  Highly recommend watching Linda Claussen video called "Native SQLPL Error Logging Choices" available under YouTube.

Larry Kintisch

Help please: Native Stored Procedure erring with -514
(in response to Jay Bovard)
Hi Jay,
I have a few ideas, but I'm not an expert. If
you are running this from SPUFI, make sure that
in the SPUFI defaults panel you set the delimeter
to --other than-- a ";" and make sure that the
END statement has that delimeter.
Just check: Is it at all possible that one of
the SELECTs --COULD-- return more than one
result row? If so make it a MAX(col-name) or
something. Perhaps the processor is thinking
"multiple rows returned" in its interpretation of
the SQL into the package. I referenced the
Redbook SG24-7604-00 "DB2 9 for z/OS Stored
Procedures: Through the CALL and Beyond" in which
Chap 15 gives the details of Native SQL procedures.
Perhaps this helps.
Larry Kintisch Pres., ABLE Information Services [login to unmask email]

At 04:04 PM 7/10/2017, you wrote:

>-514: The cursor SELECT_CURSOR is not in a prepared state
>
>The stored procedure has no result sets and no
>cursor processing (DB2 is identifying a CLOSE
>call type). All individual SELECT statements
>have been tested successfully, yet I cannot get
>the procedure to run at all. Can anyone help
>explain why I am getting this error?
>
>Â CREATE PROCEDURE F0DB2N.SPQ0005
>......
> BEGIN ......



> END


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Jay Bovard

RE: Help please: Native Stored Procedure erring with -514
(in response to Larry Kintisch)

Thank you Sam and Larry.

I have modified one of the SELECT statements that may have returned multiple rows. I have also made a couple of other tweaks and am ready to test them.

Unfortunately, at our installation, native SQL procedures are new and we don't have a lot of training (including the DBA). I have tried the debug mode from the IBM Data Studio and run into firewall issues that nobody wanted to look at yet, so I am playing try-and-see.

I am not positive about the -514 error other than it was logged in BMC Apptune with my id at the time I attempted to run the procedure.

I appreciate you both responding so quickly. I hope that the next test is successful.

Larry Kintisch

Help please: Native Stored Procedure erring with -514
(in response to Jay Bovard)
Hi Jay,
It is good practice for question submitters like you to reply to
the list if you find the solution. Larry Kintisch

At 05:27 PM 7/10/2017, you wrote:

>Thank you Sam and Larry.
>
>I have modified one of the SELECT statements that may have returned
>multiple rows. I have also made a couple of other tweaks and am
>ready to test them.
>
>Unfortunately, at our installation, native SQL procedures are new
>and we don't have a lot of training (including the DBA). I have
>tried the debug mode from the IBM Data Studio and run into firewall
>issues that nobody wanted to look at yet, so I am playing try-and-see.
>
>I am not positive about the -514 error other than it was logged in
>BMC Apptune with my id at the time I attempted to run the procedure.
>
>I appreciate you both responding so quickly. I hope that the next
>test is successful.
>
>Site Links:
> http://www.idug.org/p/fo/st/?post=182079&anc=p182079#p182079 View
>post online http://www.idug.org/p/fo/si/?topic=19 View mailing
>list online <mailto:[login to unmask email]>Start new thread via
>email
><mailto:[login to unmask email]?Subject=Unsubscribe>Unsubscribe
>from this mailing list http://www.idug.org/p/us/to Manage your
>subscription This email has been sent to: [login to unmask email]
>
>Setup a data refresh task in less time than it takes to make a cup
>of coffee + save up to 90% in CPU ESAi's BCV5 & XDM fast data
>refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug http://www.ESAIGroup.com/idug
>
>
>Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2 http://www.idug.org/p/cm/ld/fid=2


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Jay Bovard

*SOLVED* RE: Help please: Native Stored Procedure erring with -514
(in response to Jay Bovard)

The errors that DB2 was producing did not accurately reflect the problem. There was an SQL PL coding error on a divide statement that was causing a S0C7 and a DB2 dump. Once this was discovered, it was simple to find and correct the offending statement.

Charles Brown

Help please: Native Stored Procedure erring with -514
(in response to Larry Kintisch)
From my experience, the CA InterTest debugs db2 Stored Procedure better than any other product in the market. Regrettably, CA InterTest is a licensed product. What's new!! Most mainframe products are licensed.
Hope this helps.

Sent from my iPhone

> On Jul 11, 2017, at 6:17 PM, Larry Kintisch <[login to unmask email]> wrote:
>
> Hi Jay,
> It is good practice for question submitters like you to reply to the list if you find the solution. Larry Kintisch
>
> At 05:27 PM 7/10/2017, you wrote:
>
>> Thank you Sam and Larry.
>>
>> I have modified one of the SELECT statements that may have returned multiple rows. I have also made a couple of other tweaks and am ready to test them.
>>
>> Unfortunately, at our installation, native SQL procedures are new and we don't have a lot of training (including the DBA). I have tried the debug mode from the IBM Data Studio and run into firewall issues that nobody wanted to look at yet, so I am playing try-and-see.
>>
>> I am not positive about the -514 error other than it was logged in BMC Apptune with my id at the time I attempted to run the procedure.
>>
>> I appreciate you both responding so quickly. I hope that the next test is successful.
>>
>> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription This email has been sent to: [login to unmask email]
>>
>> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See http://www.ESAIGroup.com/idug
>>
>>
>> Use of this email content is governed by the terms of service at:
>> http://www.idug.org/p/cm/ld/fid=2
>
> Virus-free. www.avast.com
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> ttp://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Philip Sevetson

Help please: Native Stored Procedure erring with -514
(in response to Charles Brown)
**please note my email address change**
Charles,

Have you assigned a WLM in that stored procedure definition? Or, alternately, created it as “DISABLE DEBUG MODE”?

Native SQL Stored Procedures default to DEBUG mode, and a WLM task must be assigned to the procedure if you’re using the default.

It’s been several years since I last implemented a native-SQL Stored Procedure, but I _think_ that SQLCODE -514 is what I got when I didn’t give my procedures a WLM-or-Disable setting. There’s a default WLM, but it’s a subsystem parameter and if the SYSADM hasn’t defined it, I think -514 is what you get.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Charles Brown [mailto:[login to unmask email]
Sent: Thursday, July 13, 2017 8:16 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Help please: Native Stored Procedure erring with -514

From my experience, the CA InterTest debugs db2 Stored Procedure better than any other product in the market. Regrettably, CA InterTest is a licensed product. What's new!! Most mainframe products are licensed.
Hope this helps.

Sent from my iPhone

On Jul 11, 2017, at 6:17 PM, Larry Kintisch <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hi Jay,
It is good practice for question submitters like you to reply to the list if you find the solution. Larry Kintisch

At 05:27 PM 7/10/2017, you wrote:


Thank you Sam and Larry.

I have modified one of the SELECT statements that may have returned multiple rows. I have also made a couple of other tweaks and am ready to test them.

Unfortunately, at our installation, native SQL procedures are new and we don't have a lot of training (including the DBA). I have tried the debug mode from the IBM Data Studio and run into firewall issues that nobody wanted to look at yet, so I am playing try-and-see.

I am not positive about the -514 error other than it was logged in BMC Apptune with my id at the time I attempted to run the procedure.

I appreciate you both responding so quickly. I hope that the next test is successful.

Site Links: View post online http://www.idug.org/p/fo/st/?post=182079&anc=p182079#p182079 View mailing list online http://www.idug.org/p/fo/si/?topic=19 Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription http://www.idug.org/p/us/to This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif] https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient&utm_term=icon

Virus-free. www.avast.com https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient&utm_term=link


-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Charles Brown

Help please: Native Stored Procedure erring with -514
(in response to Philip Sevetson)
Hello Philip ~~ to answer your question. Yes -- WLM was assigned. Db2 stored procedure runs in WLM assigned address space. I was able to circumvent the problem you're experiencing by increasing the WLM TCB. You may want touch base with your sys programmer for help with this.
Hope this helps.
Chas/b
NZ dba

Sent from my iPhone

> On Jul 14, 2017, at 7:09 AM, Sevetson, Phil <[login to unmask email]> wrote:
>
> **please note my email address change**
> Charles,
>
> Have you assigned a WLM in that stored procedure definition? Or, alternately, created it as “DISABLE DEBUG MODE”?
>
> Native SQL Stored Procedures default to DEBUG mode, and a WLM task must be assigned to the procedure if you’re using the default.
>
> It’s been several years since I last implemented a native-SQL Stored Procedure, but I _think_ that SQLCODE -514 is what I got when I didn’t give my procedures a WLM-or-Disable setting. There’s a default WLM, but it’s a subsystem parameter and if the SYSADM hasn’t defined it, I think -514 is what you get.
>
> Philip Sevetson
> Computer Systems Manager
> 5 Manhattan West (33rd St at 10th Ave)
> New York, NY 10001-2632
> 212-857-1688 w
> 917-991-7052 c
> 212-857-1659 f
>
>
> From: Charles Brown [mailto:[login to unmask email]
> Sent: Thursday, July 13, 2017 8:16 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Help please: Native Stored Procedure erring with -514
>
> From my experience, the CA InterTest debugs db2 Stored Procedure better than any other product in the market. Regrettably, CA InterTest is a licensed product. What's new!! Most mainframe products are licensed.
> Hope this helps.
>
> Sent from my iPhone
>
> On Jul 11, 2017, at 6:17 PM, Larry Kintisch <[login to unmask email]> wrote:
>
> Hi Jay,
> It is good practice for question submitters like you to reply to the list if you find the solution. Larry Kintisch
>
> At 05:27 PM 7/10/2017, you wrote:
>
>
> Thank you Sam and Larry.
>
> I have modified one of the SELECT statements that may have returned multiple rows. I have also made a couple of other tweaks and am ready to test them.
>
> Unfortunately, at our installation, native SQL procedures are new and we don't have a lot of training (including the DBA). I have tried the debug mode from the IBM Data Studio and run into firewall issues that nobody wanted to look at yet, so I am playing try-and-see.
>
> I am not positive about the -514 error other than it was logged in BMC Apptune with my id at the time I attempted to run the procedure.
>
> I appreciate you both responding so quickly. I hope that the next test is successful.
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription This email has been sent to: [login to unmask email]
>
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
> Virus-free. www.avast.com
>
> -----End Original Message-----
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
> Attachment Links: image001.png (3 k)
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> ttp://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>