Results 1 to 7 of 7

Thread: SET statement

  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: SET statement

    I have the following SET statements in a stored procedure. The stored procedure runs up to the point that it encounters the SET statements and then stops. The results panel in Stored Procedure Builder shows that the parameters have the appropriate values based on test data supplied for the run but the SET parameters (in Bold) a NULL value is returned.

    SET OLDBDGTUNITS = ((MTHLYUNITS_0) * (MONTHS_BETWEEN));
    SET NEWBDGTUNITS = ((BUDGETEDUNITS_0) - (OLDBDGTUNITS));
    SET SERVICECOST1 = ((NEWBDGTUNITS) * (NPDD_SVS_RATE));
    SET SERVICECOST2 = ((OLDBDGTUNITS) * (SVSRATE1));
    SET TOTAL_AMOUNT = ((SERVICECOST1) + (SERVICECOST2) + (AMOUNT));

    The DECLARE statements are shown below:

    DECLARE BUDGETEDUNITS_0 SMALLINT;
    DECLARE OLDBDGTUNITS SMALLINT;
    DECLARE NEWBDGTUNITS SMALLINT;
    DECLARE SERVICECOST1 DEC(7,2);
    DECLARE SERVICECOST2 DEC(7,2);
    DECLARE MONTHS_BETWEEN SMALLINT;
    DECLARE MTHLYUNITS_0 SMALLINT;
    DECLARE AMOUNT DEC(7,2);
    DECLARE TOTAL_AMOUNT DEC(7,2);
    DECLARE SVSRATE1 DEC(7,2);
    DECLARE NPDD_SVS_RATE DECIMAL(6,2);


    I created a separate stored procedure with the SET statements. When I run the stored procedure, I insert the values in the "specify parameter values" window, the statements return the expected value.

    What am I missing here? I don't understand why the statements will work in one stored procedure but not another.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You care to tell us which version of DB2 you are using on which platform?

    What exactly does "and then stops" mean? Do you get an error message? If so, which one?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2007
    Posts
    56
    I'm using DB2 V7.1 on Z/OS

    >What exactly does "and then stops" mean?

    All I receive are NULL in the RESULTS screen for the parameters used in the SET statements. The PARAMETERS on the right hand side of the equation do have valid values in the RESULTS screen. All statements after the SET statements do not appear to execute as their values are also NULL.

    >Do you get an error message?

    No, I don't receive an error. I am using Stored Procedure Builder. The Output View window shows Action: Run with a Status: Success.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where is the strored procedure builder?

    On the mainframe? Or a client side tool?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It may just be that the procedure builder doesn't fully work with DB2 z/OS V7. (I don't know.)

    I suggest you remove everything after the first SET statement and then return the value being set as OUT parameter of the procedure. Create the procedure and run it via a CALL statement (not through the procedure builder). There you can see exactly the value that the variable got, i.e. whether the SET worked. Going from there, add in all the other code step by step and ensure that things are working fine.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Aug 2007
    Posts
    56
    Quote Originally Posted by Brett Kaiser
    Where is the strored procedure builder?

    On the mainframe? Or a client side tool?
    The stored procedure builder is a client side tool and resides on my PC.

  7. #7
    Join Date
    Aug 2007
    Posts
    56
    Quote Originally Posted by stolze
    It may just be that the procedure builder doesn't fully work with DB2 z/OS V7. (I don't know.)

    I suggest you remove everything after the first SET statement and then return the value being set as OUT parameter of the procedure. Create the procedure and run it via a CALL statement (not through the procedure builder). There you can see exactly the value that the variable got, i.e. whether the SET worked. Going from there, add in all the other code step by step and ensure that things are working fine.

    Thanks, I will give that a try.

Posting Permissions

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