On 20-Dec-2013 03:52 -0800, PAPWORTH Paul wrote:
I'm trying to use deterministic with an external stored procedure
written in RPG in order to optimize the processing time. The info
returned by the procedure hardly ever changes.
FWiW the topic is not specific to the RPG; the routines could be any
language, including the SQL.
As noted in another reply, the DETERMINISTIC has nothing to do with
the returned data being /nearly static/. To be truly DETERMINISTC the
routine must have *purely static* results\output-values for *every*
invocation with an identical set of input-values. The specification of
DETERMINISTIC is a _contract between the routine definer and the SQL_
database feature; i.e. that the definer of the routine assures the DB
that the routine is written to produce purely deterministic results.
While the use of DETERMINISTIC should literally hold true across
space and time, legitimately it need only hold true across the scope in
which cached results are ever maintained. Thus if there were
partition-wide or iASP-group caching of the inputs+outputs of a routine,
then the outputs need be static only within every invocation on each
LPAR or each iASP-group.
Note however that "every invocation" is possibly applicable only per
an effective unit of work, or per statement; i.e. the scope of the
definition of DETERMINISTIC may be fungible, and still be valid given
the results hold static within that known\predictable scope. At some
point with the DB2 for IBM i 7.1, 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).
According to what I understand deterministic specifies whether or
not the procedure will always return the same result from repeated
calls containing the same input values.
Exactly. DETERMINISTIC implies that for each invocation of the
routine [per above, within the defined scope] for which _all inputs_ are
identical, then *you will ensure* that _all outputs_ must be identical
also. The onus is on the routine definer, because *if* the database
wants to cache the effects for any specific\known inputs, then the
results returned will be the cached results. Thus if the routine
definer tells the database that the routine will effect DETERMINISTIC
results, then to avoid getting unpredictable [possibly stale or a
mixture of stale and fresh] output, do not break that contract; i.e.
always return identical results for each invocation with the same inputs
[across the known\defined scope].
If the answer is yes, DB2 will cache the results and the next time a
call is made using the same input parameters, the result will be
returned to the caller without the external program ever being
called.
To be clear, not that the "DB2 will cache". The DB2 *may cache* the
inputs and the results such that if a lookup of the inputs on a later
invocation is found to match the prior\cached invocation, then the DB2
is allowed to return the cached results. FWiW the database did not
actually implement any caching until v5r3, and only for SQE queries, and
I believe only for User Defined Function (UDF) routines versus all
routines; i.e. CQE queries can not benefit from routine caching, and
very possibly even by v5r3 a PROCEDURE may not have any support for
caching. The implementation of the caching of the non-query routines is
likely done by the OS SQL (SQ component) rather than by the OS Query
feature which implements the UDF routine caching.
It should be intuitively obvious why not every possible set of input
values [and corresponding deterministic outputs] could or even would be
cached. While a programmer might hope that the DB2 would always cache
the first or first several, in anticipation of many identical
invocations matching those first few, that would have potential
performance implications. More likely, the caching would be something
that happens only after repeated invocations, and then only after
several repeated static inputs across those repeating invocations,
before the DB2 activates the caching; i.e. seems very unlikely that the
_next invocation_ would already be able to experience the effects of
caching, because there is probably some algorithm trying to evaluate
with minimal impact, the benefit of trying to cache vs continuing to
effect the actual invocation across the repeated calls.
FWiW: If the invoking program already knows that the inputs [and thus
the deterministic outputs] are going to be identical for multiple [esp.
if most] invocations, then that program might do well to implement its
own caching rather than allowing the database to possibly do so. When
invoking the SP against data from an ordered result set, effecting that
caching may be very simplistic.
Testing under version 7 using the iSeries Navigator I notice that
the programme seems to be called with each request even though the
input parameters have not changed. I have my 2 parameters declared as
Input/Output. Could this be the problem. ?
Entirely possible that the [current level of] caching capabilities
available, are limited to only IN vs both IN and INOUT for the inputs.
However I suspect the issue is more likely related to the caching not
having been activated for some other reason; e.g. the parameters are
deemed large enough to discourage [aggressive] caching. Or for lack of
recalling any articles covering DETERMINISTIC PROCEDURE routines vs
those covering DETERMINISTIC FUNCTION routines, there is even possibly
still no caching algorithm\enablement at-all, as provided by the SQL for
its CALL statement.
CREATE PROCEDURE ITG849/RXL020G
( INOUT IO_DRXL020 CHAR ( 49)
, INOUT IO_DRXCPA CHAR (287)
)
LANGUAGE RPG
DETERMINISTIC
EXTERNAL NAME RXL020G
PARAMETER STYLE GENERAL
FWiW I have never even verified that any caching was ever taking
place in any of my routines [functions or procedures] because I have
never had any case for which the performance was not sufficiently
acceptable; never having had an impetus to look, beyond mere curiosity.
I do admit to that curiosity, albeit too mild to move me to any
action. I seem to recall, articles Kent Milligan and possibly also by
Mike Cain about use of DETERMINISTIC.
The following used as a Google web search, for example; possibly try
adjusting, to find similar about [SQL or External] PROCEDURE:
"kent milligan" caching deterministic routine OR function
midrange.com
