RE: Debugging a UDF on V6.1 -- RPG400-L

Yes, that would be the case. You must be aware that, in the case of SQL
sentences, many times they process thousands or more records in a single
call. So sometimes you want to be sure of having the best performance you
can.

Regards,

Luis

Sent from my Nexus 7 tablet. Please excuse my brevity.
On Mar 2, 2014 10:28 AM, "Walter Bellisio" <wbellisio@xxxxxxxxxxx> wrote:

So, if we make something like iDate non-deterministic, we would call the

function every time, with the side affect of poor performance. Is that the
only trade-off?

Walter Bellisio
Sales and Distribution Developer
(631) 951 - 5375

Bimbo Bakeries USA
55 Paradise Lane
Bay Shore, NY 11706

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Luis Rodriguez

Sent: Sunday, March 02, 2014 7:54 AM
To: RPG programming on the IBM i / System, i
Subject: RE: Debugging a UDF on V6.1

Walter,

When you define an UDF as DETERMINISTIC DB2 can "Cache" both, your input

parameters and your results, so the next time the function is called the
results are returned without having to execute the function's code.

This works for things like iDate, math functions, etc., where for any

given input there is an only and invariant output.

HTH,

Luis Rodriguez

Sent from my Nexus 7 tablet. Please excuse my brevity.
On Mar 2, 2014 7:13 AM, "Walter Bellisio" <wbellisio@xxxxxxxxxxx> wrote:

What are the ramifications of making the UDF non-deterministic? The
IBM definition was confusing, could you clarify it. Also, why would a
developer make the UDF deterministic if it causes these debugging

issues?

Walter

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Alan Campin
Sent: Saturday, March 01, 2014 12:52 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Debugging a UDF on V6.1

If you want to call service program every time change the CREATE
FUNCTION statements to be non-deterministic and it will call every time.

On Sat, Mar 1, 2014 at 8:20 AM, Walter Bellisio <wbellisio@xxxxxxxxxxx

wrote:

Alan, Yes! It worked as follows:

Session 1: STRDBG UPDPROD(*YES) SRVPGM(QGPL/XVIDAT) and set a
"SBREAK line# USER UserProfileName" at the first statement of the
IDAT_Packed procedure.

Session 2: STRSQL and run the UDF. In this example: select
iDate(1140201) from someFile

Session 1: The CPI1903 message appears with the qualified job name
that the service job needs to be started for.

Session 3: STRSRVJOB JOB(qualified name from the above CPI1903

message).

STRDBG UPDPROD(*YES) SRVPGM(QGPL/XVIDAT) and set a BREAK at the
same first line of the IDAT_Packed procedure.

Session 1: Respond to the CPI1903 message by pressing Enter.

Session 3: Now it breaks on the first line of IDAT_Packed.

Alan, In response to your prior email, I changed the DLTPGM to
DLTSRVPGM, but the above worked in either case. Yes, we did qualify
the service program name QGPL/XVIDAT in the CREATE FUNCTION commands.

You were correct about the procedure being deterministic. If I use
the same date, it does not break, but if I use a different date, it
will

break.

I even signed off all three sessions and went through the whole
process again, and it still "remembered" not only the last date
used, but about a dozen or so I used before it. Even when I ran the
SQL as "select
iDate(dateFld) from fileWithDateFld" where the file has many
different dates it will only break when I roll to a date it has not

processed yet.

Weird.

Is there any way to prevent this memorization from happening? We
all like to debug the same value multiple times when looking for an

issue.

Many thanks to all who contributed. Not completely there yet, but
getting close.

Walter

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Alan Campin
Sent: Friday, February 28, 2014 6:52 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Debugging a UDF on V6.1

Walter, I figured it out. The procedure is deterministic which means
that if you run the query multiple times with the same value it does
not call the service program again. It just returns what it already
has so what has been happening is that we ran it for a given date
and we keep testing with that date and since it is the same date it
never

calls the service program.

We need to set the SBREAK <Line Number> USER <Your User Profile> and
then switch to another session and run the SQL with a new date value
and it will break. It seems to remember that that date was run
before even if you end the job and start a new one.

Also, a couple of other things. The CRTIDAT has a bug. I should say
DLTSRVPGM pgm, not DLTPGM.

Also, in the XVIDAT_SQL member did you it edit the source member to
indicate the procedure is in QGPL rather than the default of IDATE.
Since it is running, I would guess yes.

On Fri, Feb 28, 2014 at 3:58 PM, Alan Campin <alan0307d@xxxxxxxxx>

wrote:

Walter, I just don't know what to make of this. I have installed
iDate on my system and did what I always do and it will not
recognize it in debug. I tried using my user id and QUSER but
nothing works. The really weird one is that I put fatal error in
to cause it to crashes and nothing. It is like it not executing
the

service program.

I, also, went into RDI and set a service break point there and
nothing

The best thing is to open a ticket with IBM. Something must have
changed in V6R1M0 in how IBM is running the jobs. Very bizarre.

On Fri, Feb 28, 2014 at 1:16 PM, Walter Bellisio
<wbellisio@xxxxxxxxxxx
wrote:

Hi Peter,

These are the results ...

SLIB SNAME RLIB

RNAME

EXTERNAL_NAME
QGPL IDATE QGPL

IDATE

QGPL/XVIDAT(IDAT_PACKED_DEFAULT)
QGPL IDATE00001 QGPL

IDATE

QGPL/XVIDAT(IDAT_CHARACTER_DEFAULT)
QGPL IDATE00002 QGPL

IDATE

QGPL/XVIDAT(IDAT_INTEGER_DEFAULT)
QGPL IDATE00003 QGPL

IDATE

QGPL/XVIDAT(IDAT_DATE_DEFAULT)
QGPL IDATE00004 QGPL

IDATE

QGPL/XVIDAT(IDAT_PACKED)
QGPL IDATE00005 QGPL

IDATE

QGPL/XVIDAT(IDAT_CHARACTER)
QGPL IDATE00006 QGPL

IDATE

QGPL/XVIDAT(IDAT_INTEGER)
QGPL IDATE00007 QGPL

IDATE

QGPL/XVIDAT(IDAT_DATE)

Walter

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Peter Dow
Sent: Friday, February 28, 2014 3:05 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Debugging a UDF on V6.1

Hi Walter,

A little more information might help:

STRSQL

select substr(SPECIFIC_SCHEMA,1,20) slib,
substr(SPECIFIC_NAME,1,20) sname,
substr(ROUTINE_SCHEMA,1,20) rlib,
substr(ROUTINE_NAME,1,20) rname,
EXTERNAL_NAME
from QSYS2/SYSFUNCS
where lower(ROUTINE_NAME) = 'idate'

Or just leave off the WHERE clause and take a look at all the
functions on your system. If you see idate in there, and the
EXTERNAL_NAME is correct, expand the SELECT to show all fields,

i.e.

SELECT * FROM QSYS2/SYSFUNCS and look at some of the other
information, like the PARAMETER_STYLE and so on to make sure it
matches what you expect; or what Alan says it should show.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx

<mailto:

pdow@xxxxxxxxxxxxxx>

/
On 2/28/2014 11:44 AM, Walter Bellisio wrote:

Alan, No others, just the one in QGPL. Also, thank you for the
walk

through. I wish we had an answer.

Walter Bellisio
Sales and Distribution Developer
(631) 951 - 5375

Bimbo Bakeries USA
55 Paradise Lane
Bay Shore, NY 11706

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Friday, February 28, 2014 2:42 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Debugging a UDF on V6.1

Walter, try doing s WRKOBJ OBJ(*ALLUSR/XVIDAT) OBJTYPE(*SRVPGM)

See if there are multiple copies of XVIDAT around.

On Fri, Feb 28, 2014 at 11:55 AM, Alan Campin
<alan0307d@xxxxxxxxx>

wrote:

Walter, give me a call and I will walk you through it.
801-505-2707

On Fri, Feb 28, 2014 at 11:49 AM, Walter Bellisio <

wbellisio@xxxxxxxxxxx>wrote:

Hi Vernon,

We're on V6.1. I'm attempting to debug the iDate UDF that
Alan Campin has so kindly shared. I'm just running a simple
select statement from a STRSQL. I've attempted these two

scenarios:

I've tried it with BREAK as follows:

- Session 1: STRSRVJOB JOB(123456/MYUSERID/SESSION2)
- Session 1: STRDBG UPDPROD(*YES) SRVPGM(QGPL/XVIDAT)
- Session 1: BREAK on the first statement of each procedure
in both modules XVIDAT_M01 and XVIDAT_M02.
- Session 2: STRSQL
- Session 2: select idate(someDateField) from someTable

Dates are shown edited, but none of the breakpoints occur.

I also tried it with SBREAK as Alan had suggested as follows
and also no
luck:

- Session 1: STRDBG UPDPROD(*YES) SRVPGM(QGPL/XVIDAT)
- Session 1: "sbreak LINE# user MYUSERID" on the first
statement of each procedure in both modules XVIDAT_M01 and

XVIDAT_M02.

- Session 2: STRSQL
- Session 2: select idate(someDateField) from someTable

Alan states: " It will break on your previous statement and
tell you the STRSRVJOB command to use on another session.
Then issue STRDBG in that session."

It did not break. I also tried substituting sbreak LINE#
user MYUSERID in the first scenario, and no luck.

Walter

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Vernon Hamberg
Sent: Thursday, February 27, 2014 7:22 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Debugging a UDF on V6.1

Walter - if you're willing, tell us just what steps you took?
What release of the OS are you on? SBREAK would get you what
you want, but it is a relatively recent thing - v5r3 or v5r4.

If you used SBREAK, you have to set the user you want - that
is often QUSER for server jobs - so which user did you specify?

I forget now about UDFs, but I think SQL could be doing
things in a server job, not your own - again, you'd have a
hard time figuring which job to debug unless you can use

SBREAK.

Hope to help!
Vern

On 2/27/2014 3:01 PM, Walter Bellisio wrote:

Thanks for all of your responses. Still can't get a
breakpoint to come

up. I know the function is working because it is converting
the dates as described. In response to Peter, the service
program only exists in QGPL, and the CREATE FUNCTION
qualifies the service program. Was there a PTF that may have

been needed?

Walter

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan
Campin
Sent: Thursday, February 27, 2014 1:56 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Debugging a UDF on V6.1

I should clarify that more. In one session just do a STRDBG
and then

issue a SBREAK for the line you want to break and your user id.
Then in another session, run your SQL. It will break on your
previous statement and tell you the STRSRVJOB command to use
on another session. Then issue STRDBG in that session.

On Thu, Feb 27, 2014 at 11:46 AM, Walter Bellisio <

wbellisio@xxxxxxxxxxx>wrote:

I'm attempting to debug a User Defined Function that uses a
service program to do the work on a V6.1 release. This is
the iDate source downloaded from A. Campin, with the
modules, service program and functions created properly.
STRSRVJOB and STRDBG are done on one session, and a simple
STRSQL with a select statement that uses the UDF is done on
the other session that the STRSRVJOB points to. A break
point was set on the first statement of every procedure in
both modules and I can't get a module to stop at any

of the break points.

The RPGLE modules of the service program were also compiled
with
DBGVIEW(*ALL) and that did not work as well. Has anyone
encountered a

similar problem?

Walter
--
This is the RPG programming on the IBM i (AS/400 and
iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at

http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and
iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at

http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at

http://archive.midrange.com/rpg400-l.

---
This email is free from viruses and malware because avast!
Antivirus protection is active.
http://www.avast.com
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at

http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at

http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)

mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,

visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at

http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)

mailing list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.