Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Question Unanswered: Stored procedure, date calculation

    Below is a portion of a stored procedure that I had written under DB2 V7.1 on z/OS.

    IF NPDD_SVS_ID IN (5, 6)
    AND NRATE_EFF_DT <= RATEBEGDT_1
    THEN
    SET BUDGETEDUNITS_1 = BUDGETEDUNITS_0;
    SET BUDGETEDUNITS_0 = 0;
    SET RATEENDDT_1 = (NRATE_EFF_DT - 1 DAY);

    The stored procedure was returning values for BUDGETEDUNITS_1, BUDGETEDUNITS_0, and RATEENDDT_1. Our provider recently began migration to V8. Currently we're in the limbo period before the final switch to V8. My issue is that the stored procedure no longer functions and I'm unable to determine why.

    I've tried rewriting and have used the following:

    IF NPDD_SVS_ID IN (5, 6)
    AND NRATE_EFF_DT < RATEBEGDT_0
    THEN
    SET RATEBEGDT_1 = DATE('2008-05-22') + 32 DAYS;
    or
    IF NPDD_SVS_ID IN (5, 6)
    AND NRATE_EFF_DT < RATEBEGDT_0
    THEN
    SELECT DATE('2008-05-22') + 32 DAYS
    INTO RATEBEGDT_1
    FROM SYSIBM.SYSDUMMY1;

    I have values for NRATE_EFF_DT & RATEBEGDT_0. I've tested both the set and the select statements in spufi and SQuirrel, both work. They just don't work in the stored procedure.

    If you have any idea(s) as to why the stored procedure won't function, please let me know.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    "stored procedure no longer functions" is not sufficient information. You need to post your error code/message. You might also need to use condition handlers to trap the specific SQL error code/message in the SP and return it to the calling program.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Your procedure looks fine and there is no reason why it shouldn't work. Could you possibly be a bit more specific on what you mean with "doesn't work"?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2007
    Posts
    56
    Output parms showed I had values for the stored procedure to perform the comparisons and then perform the SET statements. Previously, this step did function and produced results for the SET statements that I was able to view on output parms. I can't explain why the procedure went from working to not working as I was not getting sqlcode or sqlstate errors.

    The stored procedure had all of the END IF; statements grouped at the bottom. As this particular group of code statements were related to a different group of svsid's, I regrouped and placed the code below the END IF's. The procedure now runs fine.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I didn't understand a word of what you were saying. But if it is fine now, I guess you fixed some problem you had before (but probably didn't trigger it for some unknown reason).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •