On 02-Mar-2014 03:42 -0800, Walter Bellisio wrote:
Alan Campin on Saturday, March 01, 2014 12:52 PM wrote:
On Sat, Mar 1, 2014 at 8:20 AM, Walter Bellisio wrote:
<<SNIP>>
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.
<<SNIP>>
If you want to call service program every time change the CREATE
FUNCTION statements to be non-deterministic and it will call every
time.
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?
I am unsure what was found to be /confusing/ about the definition;
i.e. what was confusing, was unstated. What is stated in the docs,
seems pretty clear to me; i.e. "Specifies whether the function returns
the same results each time that the function is invoked with the same
input arguments" per:
<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcfsce.htm>
_CREATE FUNCTION_ (External Scalar)
But what I do know, what was not previously well-described by that
IBM documentation for the [NOT] DETERMINISTIC, is the _scoping_ of any
cached results. For that, the following discussion [also not specific
to the RPG] that took place here not that long ago, may be of help to
clarify. However, the Notes section at the bottom of the page of
documentation at the above doc link, was at some point updated to
include the QQ initialization option I mentioned in the archived message
at the link below, and even mentions another use-case that may be of
interest of which to be aware.
<
http://archive.midrange.com/rpg400-l/201312/msg00240.html>
Notably, rather than using NOT DETERMINISTIC while testing and then
re-creating the FUNCTION after testing is complete, the possibility to
limit the *scope* of the caching should exist if for not other reason
than to assist with debugging. However the support may not exist in
v6r1. From the above archived message, I noted that "the scope may be
defined by the QAQQINI option DETERMINISTIC_UDF_SCOPE with possible
values of *ALWAYS (the default) and *OPEN (implying the scope of a query
ODP; or effectively, as I understand would be the case, scoped to the
SQL statement)." That of course requires that the SQL query also is not
pseudo-closed, so the use of RCLACTGRP may be required.
FWiW:
Regardless [though no better I suppose, than re-creating the routine
as NOT DETERMINISTIC to enable debugging], I would expect that DROP
FUNCTION should effectively clear the cache for that function. That is,
after a new CREATE [OR REPLACE] FUNCTION with the same signature and
specific name, caching for invocations of that function should be
renewed. Anyhow, the DROP FUNCTION documentation does not have any
conspicuous reference to either of /cache/ or /deterministic/ allowing
one to easily locate and determine if that should be the effect; though
IMO, a seemingly intuitive effect for the DROP statement:
<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzdropst.htm>
Or possibly [but hopefully not, as it would be a daft requirement], a
more complicated sequence of actions could be required: DROP FUNCTION,
CREATE FUNCTION [effecting a new signature due to a different
return-type], DROP FUNCTION; i.e. after a second new CREATE [OR REPLACE]
FUNCTION, caching for invocations of the original function should be
renewed because the more recent CREATE FUNCTION with a conflicting
signature would have informed the database that the cache must be
cleared. This is mentioned only because, if the DROP and CREATE alone
are not corrective [as I would expect], then this would necessarily have
to ensure cached values would not be used; i.e. would merely be a
circumvention if functional and the simple DROP+CREATE was not effective.
Thus, in a debug scenario such as was described, the scripted actions
to effect the DROP and CREATE to create the function anew, inserted as a
step prior to each new iteration of testing the function, would be [IMO]
expected to ensure that no old\stale cached results could exist; or at
least not be returned, even if the database might not literally /clear/
them. The option to re-create the function _once_ as NOT DETERMINISTIC
and then re-create the function only _once more_ after testing is
complete, is probably nicer [per being less overall work both for the
programmer and for the system], even if not as consistent; of course,
also taking great care to remember that the final re-create is required,
to avoid accidentally leaving the function defined as NOT DETERMINISTIC
and thus losing the capability for better performance due to cached results.
midrange.com
