Topic
16 replies Latest Post - ‏2013-03-15T21:29:16Z by krmilligan
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

‏2013-02-19T16:26:17Z |
I am trying to debug a UDF. I have used the correct two session methodology to accomplish this, much like debugging a batch job, and am able to get the process in debug and add breakpoints as normal.

The problem is, once a breakpoint is hit, I cannot eval any internal SQL variables. In fact, if I eval incoming parameters, I get a SSP:xxxxxxxx as a return value for the eval.

Help! Debug is nearly useless without being able to see variable values!

Thanks,
Brent Dunn
Updated on 2013-03-15T21:29:16Z at 2013-03-15T21:29:16Z by krmilligan
  • krmilligan
    krmilligan
    441 Posts
    ACCEPTED ANSWER

    Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

    ‏2013-02-19T16:55:38Z  in response to SystemAdmin
    This is covered in the Graphical Debugger for SQL Procedures white paper (link at: http://ibm.com/systems/i/db2/awp.html)

    If you use the graphical debugger, you can just check the variable values graphically. The key is adding a label to your BEGIN clause. With that label in place, you can check contents of variables like ratecalc with this EVAL statement - EVAL SP.RATECALC

    Routine parameters need to be prefixed with the procedure name (EVAL SHIP_IT.ORDNUM)

    CREATE PROCEDURE myschema.ship_it(IN ordnum INTEGER, IN ordtype CHAR(1),
    IN ordweight dec(3,2))
    LANGUAGE SQL
    SET OPTION DBGVIEW =*SOURCE
    sp: BEGIN
    DECLARE ratecalc DECIMAL(5,2);

    IF ordtype='I' THEN
    SET ratecalc = ordweight * 5.50;
    INSERT INTO wwshipments VALUES(ordnum,ordweight,ratecalc);
    ELSE
    SET ratecalc = ordweight * 1.75;
    INSERT INTO shipments values(ordnum,ordweight,ratecalc);
    END IF;
    END
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

      ‏2013-02-19T17:40:17Z  in response to krmilligan
      So you're saying, in the case of this UDF, I should define the BEGIN like this, I will be able to EVAL the information. Originally I was having problems with this UDF do to the use of CHAR. (CHAR(01) = '1', DIGITS(01) = '01') Once I changed the CHAR to DIGITS it corrected the problem. Unfortunately, EVAL would have made this correction MUCH easier to see and correct.

      In the case of the incoming parameters, I should use "EVAL GSDATE.CCYY" and for a internal variable I should use "EVAL SP:ASSEMBLEDATE". Is this correct?

      Thank you SO much for the assistance. I do not have anyone to bounce this stuff off of because I am the only one of my associates trying to make the transition to high level, effective SQL processing.

      /*-------------------------------------------------------------------*/
      /* Program: GSDATE */
      /* Description: Return a Date field from CCYY, MM, DD decimal */
      /* input parameters */
      /* Date Written. 02/19/13 */
      /* Programmer: Brent Dunn */
      /* */
      /*===================================================================*/
      /* Date Programmer Request Synopsis of Modification */
      /*
      ---------------
      -------------------------------*/
      /* */
      /*-------------------------------------------------------------------*/
      DROP function RDUNN/GSDATE;

      CREATE function RDUNN/GSDATE
      (CCYY DEC (4,0), MM DEC(2,0), DD DEC(2,0))
      returns DATE
      LANGUAGE SQL
      /*-------------------------------------------------------------------*/
      /* This UDF should be not fenced to insure that this UDF is not run */
      /* in a separate thread. Also this UDF should be deterministic due */
      /* to its probable use within both SELECT and WHERE clauses. */
      /* These options will ensure the most efficient processing */
      /*-------------------------------------------------------------------*/
      NOT FENCED
      DETERMINISTIC
      /*-------------------------------------------------------------------*/
      /* Begin Processing */
      /*-------------------------------------------------------------------*/
      SP:BEGIN
      /*-------------------------------------------------------------------*/
      /* Work Field Declarations */
      /*-------------------------------------------------------------------*/
      DECLARE WORKDATE DATE;
      DECLARE ASSEMBLEDATE CHAR(10);
      /*-------------------------------------------------------------------*/
      /* Error Handler Declarations */
      /* Exist Handler: This will handle invalid incoming CCYY, MM, DD */
      /*-------------------------------------------------------------------*/
      DECLARE EXIT HANDLER for SQLEXCEPTION
      RETURN NULL;
      /*-------------------------------------------------------------------*/
      /* Assemble the three portions of the date into a single field and */
      /* convert it to a date */
      /*-------------------------------------------------------------------*/
      SET ASSEMBLEDATE = SUBSTR(DIGITS(CCYY),1,4) ||
      '-' || SUBSTR(DIGITS(MM),1,2) ||
      '-' || SUBSTR(DIGITS(DD),1,2);
      SET WORKDATE = DATE(ASSEMBLEDATE);
      /*-------------------------------------------------------------------*/
      /* Return the assembled date in date format */
      /*-------------------------------------------------------------------*/
      RETURN WORKDATE;
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

    ‏2013-02-19T18:45:58Z  in response to SystemAdmin
    I still cannot get internal variables to EVAL. See below.

    Also, I have breakpoints on lines 5 - 11, yet only lines 5, and 8 breakpoint.

    Thanks,
    Brent



    Display Module Source

    Program: GSDATE Library: RDUNN Module: GSDATE
    1 CREATE FUNCTION RDUNN / GSDATE ( CCYY DEC ( 4 , 0 ) , MM DEC ( 2 , 0 )
    2 OPTION DBGVIEW = * SOURCE
    3 UDF : BEGIN
    4 DECLARE WORKDATE DATE;
    5 DECLARE ASSEMBLEDATE CHAR ( 10 );
    6 DECLARE EXIT HANDLER FOR SQLEXCEPTION
    7 RETURN NULL;
    8 SET ASSEMBLEDATE = SUBSTR ( DIGITS ( CCYY ) , 1 , 4 ) || '-' || SUBSTR
    9 SET WORKDATE = DATE ( ASSEMBLEDATE );
    10 RETURN WORKDATE;
    11 END;
    Bottom
    Debug . . . EVAL UDF:ASSEMBLEDATE

    F3=End program F6=Add/Clear breakpoint F10=Step F11=Display variable
    F12=Resume F17=Watch variable F18=Work with watch F24=More keys
    Syntax error occurred.
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

    ‏2013-02-19T19:01:16Z  in response to SystemAdmin
    Btw, It appears we dont have the graphical debugger, so I am using standard STRSRVJOB/STRDBG type debugging that I would use for a RPG free program.

    To be honest, my client has determined that stored procedures are unmanageable and are replacing them with socket processing. I just don't believe that UDFs and Stored Procedures are unmanageable. I do have to say that making the transition from "Native" processing to SQL processing is made very difficult by the lack of good, accessible documentation.
    • krmilligan
      krmilligan
      441 Posts
      ACCEPTED ANSWER

      Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

      ‏2013-02-19T20:07:24Z  in response to SystemAdmin
      Graphical debugger is included with operating system - easiest way to install is to do just do a full install of System i Navigator.
      A colon(:) is used to define your routine label. The period must be used on the EVAL command to append the routine label to the variable name.

      EVAL UDF.ASSEMBLEDATE
      • krmilligan
        krmilligan
        441 Posts
        ACCEPTED ANSWER

        Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

        ‏2013-02-19T20:12:30Z  in response to krmilligan
        Since ASSEMBLEDATE is defined as character string, you need to add * to EVAL command.

        EVAL *UDF.ASSEMBLEDATE

        Documentation is one vehicle to build SQL knowledge - education is another. There's a DB2 for i Advanced SQL Workshop that covers this debug topic.
        http://www-304.ibm.com/jct03001c/services/learning/ites.wss/us/en?pageType=course_description&courseCode=OL390
        • SystemAdmin
          SystemAdmin
          3129 Posts
          ACCEPTED ANSWER

          Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

          ‏2013-02-19T20:49:40Z  in response to krmilligan
          Kent:

          Thank you for your responses.

          Unfortunately, I am still unable to debug this UDF because it only breakpoints on lines 5 and 8. Also, it appears the UDF can execute dozens of times without any breakpoints being hit. If I run a SQL (simple select) using the UDF, it may output a page or two of results BEFORE a breakpoint is hit. This, of course, is impossible. I understand the implecations of DETERMINISTIC in debugging, but for the very first record not to breakpoint, I just don't understand.

          Kent, this is driving me crazy. I am a contractor trying to convince my client not to give up on SQL and other modern processing techniques, but to be honest, it is a difficult thing to accomplish. How am I supposed to sell this as the future, when I can't even get it to work??

          As far as taking classes, It is very difficult for a consultant to absorb the cost, and especially, to take the time off to take a class. I, personnally, am MUCH better served with a complete comprehesive manual.

          I VERY much appreaciate you assistance, however. Please don't take any of my comments as negative towards SQL functionality, it's just the road to get to be an expert in that functionality can be challenging.

          Thanks,
          Brent
          • krmilligan
            krmilligan
            441 Posts
            ACCEPTED ANSWER

            Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

            ‏2013-02-19T21:46:42Z  in response to SystemAdmin
            If you're having issues with the cached calls to a Deterministic UDF, then you should recreate and debug it as a NOT DETERMINISTIC UDF to ensure that it gets called on every execution. Cached values for DETERMINISTIC UDF's are shared system-wide, so it's not surprising for the first record to skip a call.

            Not sure what's causing your breakpoint issue. Sounds like the job may be using an old version of the program object, I'd try restarting the job. You also should ensure a recent version of the Database Group PTF is installed(https://ibm.biz/BdxSGC) - loading PTF Cume packages is not good enough.

            A person is not going to be able to become an SQL expert by just reading the manuals. At some point, an investment in training to learn best practices is required.
            • SystemAdmin
              SystemAdmin
              3129 Posts
              ACCEPTED ANSWER

              Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

              ‏2013-02-19T22:44:51Z  in response to krmilligan
              I have dropped the UDF and recreated it without DETERMINISTIC.

              In debug, this is what the UDF looks like:

              Display Module Source

              Program: GSDATE Library: RDUNN Module: GSDATE
              1 CREATE FUNCTION RDUNN / GSDATE ( CCYY DEC ( 4 , 0 ) , MM DEC ( 2 , 0 )
              2 = * SOURCE
              3 UDF : BEGIN
              4 DECLARE WORKDATE DATE;
              5 DECLARE ASSEMBLEDATE CHAR ( 10 );
              6 DECLARE EXIT HANDLER FOR SQLEXCEPTION
              7 RETURN NULL;
              8 SET ASSEMBLEDATE = SUBSTR ( DIGITS ( CCYY ) , 1 , 4 ) || '-' || SUBSTR
              9 SET WORKDATE = DATE ( ASSEMBLEDATE );
              10 RETURN WORKDATE;
              11 END;

              There are breakpoints on lines 4-11.

              Only lines 4,5,8 breakpoint. Here is the line 8 breakpoint:

              Display Module Source

              Program: GSDATE Library: RDUNN Module: GSDATE
              1 CREATE FUNCTION RDUNN / GSDATE ( CCYY DEC ( 4 , 0 ) , MM DEC ( 2 , 0 )
              2 = * SOURCE
              3 UDF : BEGIN
              4 DECLARE WORKDATE DATE;
              5 DECLARE ASSEMBLEDATE CHAR ( 10 );
              6 DECLARE EXIT HANDLER FOR SQLEXCEPTION
              7 RETURN NULL;
              8 SET ASSEMBLEDATE = SUBSTR ( DIGITS ( CCYY ) , 1 , 4 ) || '-' || SUBSTR
              9 SET WORKDATE = DATE ( ASSEMBLEDATE );
              10 RETURN WORKDATE;
              11 END;
              Bottom
              Debug . . . EVAL *UDF:ASSEMBLEDATE

              F3=End program F6=Add/Clear breakpoint F10=Step F11=Display variable
              F12=Resume F17=Watch variable F18=Work with watch F24=More keys
              Pointer type error occurred.


              As you can see the eval of ASSEMBLEDATE does not work. (Pointer type error occurred).

              Also, The field I would be trying to debug is ASSEMBLEDATE after it is updated by line 8. Since there is no breakpoint occurring on line 9, as defined, I cannot see ASSEMBLEDATE.

              I have not checked on the Database Group PTF yet. I will make sure that it is loaded.

              Thanks for the assistance!
              -Brent
              • SystemAdmin
                SystemAdmin
                3129 Posts
                ACCEPTED ANSWER

                Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

                ‏2013-02-20T15:19:24Z  in response to SystemAdmin
                Sorry about the last post. It should have been EVAL *UDF.ASSEMBLEDATE.

                I am still unable to get the UDF to breakpoint on any relevant lines other than line 8.

                What I would really like is to have the UDF breakpoint on line 9 and 10. Does anyone know how I can accomplish this? Is green screen debugging just not compatible with UDFs?

                Im at a loss.

                Thanks for the assistance!
                • krmilligan
                  krmilligan
                  441 Posts
                  ACCEPTED ANSWER

                  Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

                  ‏2013-02-20T16:19:52Z  in response to SystemAdmin
                  You may have uncovered a bug in the debugger, I will need to investigate.

                  You should find that if you remove your handler that all the expected breakpoints will be hit. Another option is switching to the ILE C output listing in the debugger and sitting breakpoints on lines with if (memcmp(SQLSTATE,"00000",5) != 0)

                  The EVAL command works fine with your SQL function. You can increase the number of characters displayed with the :S option.
                  EVAL UDF.ASSEMBLEDATE :S 10
                  • SystemAdmin
                    SystemAdmin
                    3129 Posts
                    ACCEPTED ANSWER

                    Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

                    ‏2013-02-20T16:38:57Z  in response to krmilligan
                    Kent, thank you SO much for the assistance. Also, thank you for reading my mind on the EVAL length thing. It was on my list of things to figure out later!

                    I look forward to seeing what you come up with in your investigation.

                    You have the source code for the UDF, but if you need anything else from me, please don't hesitate to ask.

                    Again, thank you Kent!
                    -Brent
                    • krmilligan
                      krmilligan
                      441 Posts
                      ACCEPTED ANSWER

                      Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

                      ‏2013-02-20T17:20:45Z  in response to SystemAdmin
                      The development team that confirmed that this is a defect that will need to be fixed with a PTF. What release level are you running with?
                      • SystemAdmin
                        SystemAdmin
                        3129 Posts
                        ACCEPTED ANSWER

                        Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

                        ‏2013-02-20T21:50:19Z  in response to krmilligan
                        We just completed the upgrade to V7R1 which includes PTF SF99701 Group 21 enhancements for DB2 for i V7R1.
                        • SystemAdmin
                          SystemAdmin
                          3129 Posts
                          ACCEPTED ANSWER

                          Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

                          ‏2013-02-21T16:00:22Z  in response to SystemAdmin
                          Not sure what happened last night, but my previous user ID no longer works (password no longer valid). It shows up under my profile, but I cannot use it, and the IBM support line says my email address is no longer in the system.

                          Please use this User ID in the future for me.
                        • krmilligan
                          krmilligan
                          441 Posts
                          ACCEPTED ANSWER

                          Re: Unable to see SQL variable values in Debugging a UDF (F11 - Eval)

                          ‏2013-03-15T21:29:16Z  in response to SystemAdmin
                          Test PTF SI49512 available as a fix to this problem. You would need to open a PMR with IBM Support if you wish to try the Test PTF.