debugging SQL function - EVAL character field

[Steve Richter]

using the STRDBG and DSPMODSRC debug commands to debug an SQL function. Where the SQL function field names are not recognized by the EVAL command because the debugger is debugging the ILE C program that the SQL function was compiled as.

so I use EVAL %localvars to see the actual names of the variables.

then to see the value of a character field named CH10 I run this command:

EVAL SQLP_L2.CH10:x 10
00000 F2F0F1F4 60F1F260 F0F1.... ........ - 2014-12-01......

which is ok. But is there an easier way?

Does rational developer for I do a better job debugging SQL procedures and functions?

thanks,

[Buck]

I haven't used the green debugger in nearly 20 years. Having said that I haven't debugged a LANGUAGE SQL UDF yet; I tend to write them in RPG rather than SQL. I'll write one tomorrow and see what it's like to debug it with RDi.
--buck

[Steve Richter]

On Wednesday, March 2, 2016 at 8:31:06 PM UTC-5, Buck wrote:
>
> I haven't used the green debugger in nearly 20 years. Having said that I haven't debugged a LANGUAGE SQL UDF yet; I tend to write them in RPG rather than SQL. I'll write one tomorrow and see what it's like to debug it with RDi.
> --buck

here is source code of an sql function.

CREATE OR REPLACE FUNCTION date_Construct (
inYearNum decimal(5,0),
inMonthNum decimal(5,0),
inDayNum decimal(5,0))
RETURNS date
language sql
BEGIN atomic

declare chYear char(4) ;
declare chMonth char(2) ;
declare chDay char(2) ;
declare chDate char(10) ;
declare vDate date ;

set chYear = SUBSTR(digits(inYearNum),2,4) ;
set chMonth = substr(digits(inMonthNum),4,2) ;
set chDay = substr(digits(inDayNum),4,2) ;

set chDate = chYear || '-' || chMonth || '-' || chDay ;
set vDate = date(chDate,iso) ;

return vDate ;
END

[Buck]

On Wednesday, March 2, 2016 at 12:20:28 PM UTC-5, Steve Richter wrote:

This turned interesting.
I used your example; had to make 2 changes. The first was to add SET OPTION DBGVIEW = *SOURCE and the other was to eliminate the ', iso' from the date() function call. Now the SEP debugger comes right up when I invoke the function.

SEP sees all of the variables in the Variables View, which is what I tend to use more than Monitors. However, the display is odd compared to RPG. The input parameters are all visible in a structure called DATE_CONSTRUCT. Each input parameter has a corresponding null indicator. So far, so good. I can right click on any of those variables and Monitor Expression works exactly as expected.

It so happens that there are three more variables named INYEARNUM, etc. Only these are pointers. I can Monitor them, but to see the value I additionally need to dereference them once in the Monitor (another right click from the Monitor).

The local variables are very unusual compared to RPG. I can see them in a structure called SQLP_L2. Once again, they have associated indicator variables. I can right click them from the Variables view and Monitor them. If I want to right click and Monitor directly from the source, it fails with a 'CRRDG3137E Failed to monitor the given expression'. I can Add Monitor SQLP_L2.CHYEAR and that works, but it must be typed in all caps. So knowing the internal structure seems a prerequisite.

Everything else seems to work. Stepping, Run to location, even changing the value of variables in Monitors or Variables view. All told, I would live with the weirdness rather than switch back to the green debugger.

For what it's worth, the weirdness is almost certainly due to the options DB2 applies to the C compiler that creates this code. The debuggers can only look into the code where the compiler lets them.

One more thing; there is a graphical IBM i debugger that doesn't require RDi, see http://www.ibm.com/developerworks/ibmi/library/i-debug/index.html It's been around a while, but few seem to know about it. An alternative name is 'System Debugger'.

Hope this was helpful.
--buck

[lae...@gmail.com]

On Monday, March 7, 2016 at 8:31:38 PM UTC-5, Buck wrote:

> I used your example; had to make 2 changes. The first was to add SET OPTION DBGVIEW = *SOURCE and the other was to eliminate the ', iso' from the date() function call. Now the SEP debugger comes right up when I invoke the function.
>

....

>
> One more thing; there is a graphical IBM i debugger that doesn't require RDi, see http://www.ibm.com/developerworks/ibmi/library/i-debug/index.html It's been around a while, but few seem to know about it. An alternative name is 'System Debugger'.
>

Buck,

that was terrific. And thanks for the link to the debugger tool.

-Steve

[CRPence]

On 02-Mar-2016 11:20 -0600, Steve Richter wrote:
> Using the STRDBG and DSPMODSRC debug commands to debug an SQL

> function. Where the SQL function field names are not recognized by
> the EVAL command because the debugger is debugging the ILE C program
> that the SQL function was compiled as.
>

> So I use EVAL %localvars to see the actual names of the variables.
>
> Then to see the value of a character field named CH10 I run this
> [debugger] command:

>
> EVAL SQLP_L2.CH10:x 10
> 00000 F2F0F1F4 60F1F260 F0F1.... ........ - 2014-12-01......
>

> which is OK. But is there an easier way?
>
> <<SNIP>>

When using the green-screen debugger, in place of [to replicate] what
is shown being done in the above example, but without having to prefix a
variable name with the actual label, use the following debug command
wherein the SQL-generated label [previously specified as a prefix
including a period] has been replaced by two consecutive percent-signs
as prefix to the variable name:

EVAL %%CH10:X 10

For details, see:
[http://www.mcpressonline.com/sql/techtip-enhanced-debug-experience-for-sql-routines-in-ibm-i.html]
TechTip: Enhanced Debug Experience for SQL Routines in IBM i
Written by Kent Milligan
Friday, 05 December 2014 01:00
"...
Clearly, the old EVAL support was cumbersome. With the recent
enhancements, a developer just needs to enter EVAL %%V1. This
improvement eliminates the need for a developer to create, find, or
enter a label-name! (If you're already in the practice of adding labels
and using those labels in the EVAL command, [then] that approach will
still work.)

Accessing these enhancements for the green-screen debugger is as simple
as loading the Database Group PTF level 32 for the 7.1 release and level
3 for the 7.2 release. ...
..."

Otherwise, as already noted elsewhere, the "no-charge IBM i graphical
debugger" should be an alternative. As well, there is the IBM® Data
Studio graphical debugger. Some more links included below; and the
"white paper" in the prior link, I believe means to reference the first
of the following links:

[https://www.ibm.com/partnerworld/wps/servlet/ContentHandler/stg_ast_sys-wp-graphical-debugging]
Graphical debugging makes procedural SQL debugging on IBM i even easier
"Overview: This white paper ..."

[http://www.ibm.com/developerworks/ibmi/library/i-debugger-db2-i/]
IBM Data Studio debugger and IBM DB2 for i
Graphically debug your SQL stored procedures and functions

--
Regards, Chuck

[CRPence]

On 03-Mar-2016 07:21 -0600, Steve Richter wrote:
> here is source code of an sql function.
>
> CREATE OR REPLACE FUNCTION date_Construct (
> inYearNum decimal(5,0),
> inMonthNum decimal(5,0),
> inDayNum decimal(5,0))
> RETURNS date
> language sql
> BEGIN atomic
>
> declare chYear char(4) ;
> declare chMonth char(2) ;
> declare chDay char(2) ;
> declare chDate char(10) ;
> declare vDate date ;
>
> set chYear = SUBSTR(digits(inYearNum),2,4) ;
> set chMonth = substr(digits(inMonthNum),4,2) ;
> set chDay = substr(digits(inDayNum),4,2) ;
>
> set chDate = chYear || '-' || chMonth || '-' || chDay ;
> set vDate = date(chDate,iso) ;
>
> return vDate ;
> END

OT [and old\out-of-date per retrieved from my drafts folder] and FWiW:

The above source would not compile everywhere due to use of the /bar/
character '|' and failure to precede commas with a blank if the
following character is a digit.

Also, while certainly not very helpful for debugging, quite the
opposite in fact, the following scalar User Defined Function (UDF)
sources are probably much more efficient at effecting the same data
conversion from decimal inputs. And AIUI can be /in-lined/ since some
somewhat recent release, due to each being composed without a BEGIN\END
pair, effectively implying there are no declares, and nothing but the
RETURN. They both depend on the fact that there is a 14-character form
of a TimeStamp string-representation:

CREATE OR REPLACE FUNCTION date_Construct
( inYearNum decimal(5, 0)
, inMonthNum decimal(5, 0)
, inDayNum decimal(5, 0)
) RETURNS date
language sql

return
date( digits(
dec( ( (inYearNum * 10000000000)
+ (inMonthNum * 100000000 )
+ (inDayNum * 1000000 )
) , 14 )
) )

CREATE OR REPLACE FUNCTION date_Construct
( inYearNum decimal(5, 0)
, inMonthNum decimal(5, 0)
, inDayNum decimal(5, 0)
) RETURNS date
language sql

return
date(
digits( dec( ( ( inYearNum * 10000 )
+ ( inMonthNum * 100 )
+ ( inDayNum )
) , 8 )
) concat '000000' )


--
Regards, Chuck