On 20-Dec-2013 05:38 -0800, Charles Wilt wrote:
<<SNIP>>
Also note that "hardly ever changes" isn't the right place to use
deterministic.
Generally. However with a known\predictable scope for the caching,
and knowing when the /changes/ can occur, the possibility may exist.
See my /calendar/ example noted later.
Nor is a function with either "READS SQL DATA" or "MODIFIES SQL
DATA". As anything accessing a table by definition is NOT
DETERMINISTIC.
An EXTERNAL ACTION would seem a more likely cause to deem a routine
to be NOT DETERMINISTIC. The database query does enforce that already,
AFaIK, by always invoking such a routine irrespective of the /claim/
that the routine was created as DETERMINISTIC; i.e. I do not recall
there being any error on CREATE of the routine, but the database query
feature will AFaIK always invoke a FUNCTION rather than use any cached
value, if that routine was defined with EXTERNAL ACTION... and I have no
idea about PROCEDUREs.
While I have never done so... I expect there are valid cases of a
MODIFIES SQL DATA being deterministic, at least given the change access
is restricted to only those application(s) that are aware of the rules.
I understand that [regardless that a routine might act as part of a
unit of work for which any modifications should participate within an
actual transaction] an UPDATE or INSERT or DELETE do _seem_ very similar
to an EXTERNAL ACTION. However knowing the effect [of the update
activity] would be the same for each identical invocation, I am under
the impression that even with MODIFIES SQL DATA, a procedure still can
be DETERMINISTIC. For [a contrived] example, I might have a procedure
NEW_PRICE(IN, IN, OUT, OUT) that updates a PRICING file to reflect a new
price for a particular item according to new pricing data, whereby the
routine returns the new price [extracted from some price-change
authorization data], and if the update has not already occurred, the
procedure inserts the new price with that authorization code along with
the current_date, and the date that the item was updated to that new
price is returned. If I invoke that procedure many times with the same
two inputs using the SQL CALL
NEW_PRICE(:ITEM_NBR,:PRICE_CHG_AUT_CODE,:ITEM_PRICE,:DATED) then the
result seems deterministic enough to me; i.e. each time invoked, I
should get the same item-price and date for the two output values,
whether my current invocation performed the INSERT or any other prior
invocation [even by some other job] had done so, using the same item and
price\authorization values. The cached price and updated-date values
are just as valid as having looked it up from the TABLE; i.e. they are
static. Obviously though, if somebody is making changes to that same
data outside of the NEW_PRICE routine [or outside of any other interface
necessarily operating by the same rules as the NEW_PRICE routine], then
all bets are off.
But "READS SQL DATA" can be even more easily deterministic. Any
TABLE accessed merely for READ is totally acceptable for a deterministic
result *if* there are [effective] constraints for which the referenced
/TABLE/ is ensured to be static; e.g. incapable of DELETE, UPDATE, or
INSERT which could be enforced by a TRIGGER or in part by AlwDlt and
AlwUpd attributes [although those "Allow" capability attributes are
still something only available to non-SQL TABLE]. Like with MODIFIES
SQL DATA, the capability for a truly DETERMINISTIC result within the
scope of caching, depends on everybody playing according to some set of
rules.
A /read/ of anything, TABLE or otherwise, SQL or otherwise, has a
similar potential for changes. IMO it is merely the natural inference
that the TABLE data is inherently dynamic\update-capable, that makes its
use seem more likely to be contrary to the concept of deterministic.
Other stored data may be harder to change, with fewer and possibly more
controlled access to the interfaces to make the changes, but changes are
still possible; e.g. a compile-time array could be the source of the
data, and if someone places a new copy of the compiled program into play
when the database routine caching was enabled, the possibility for
negative effects is no different than if the changed data had come from
a TABLE.
I would have minimal concern for implementing a routine that accesses
an effectively-static /calendar table/ using DETERMINISTIC; accepting
the unlikely possibility that the _results could be unpredictable_ *if*
someone were allowed to bypass the intended /restrictions/ preventing
data changes within the scope of the caching. I would presume in that
scenario, that the caching does not survive an IPL, and having ensured
the effectively-static table will be updated only during such
[restricted-state] maintenance, that all would function well; i.e.
assumed to be truly static, where\when required, to prevent
incorrect\unpredictable output.
midrange.com
