On 17-Oct-2016 21:02 -0500, MichaelQuigley wrote:
On 14-Oct-2016 13:23 -0500, dlclark wrote:
[…] We did not want to be qualifying our table and view names in
programs. We wanted the library list to control what is accessed.
They said that under the conditions specified that dynamic SQL will
tend to use the library list where static SQL will tend to use only
the current schema -- which defaults to the name of the current
user. We also did not want to be setting schema names in programs.
Again, we wanted the library list to control what is being
accessed.
Sorry, but that's just plain bonkers. (That's using a real technical
term, eh?)
That wishy-washy implication of "tend to use" was quite difficult to
read without letting out a huge sigh of disbelief. I essentially just
ignored that however; mostly because I figured that was not germane to
the issue. What the OP had to work with was already decided upon
[dynamically prepared vs static], and that was what had exhibited the
issue, and for which resolution was sought.
So to keep things clear in the archives:
I've coded static SQL for years and never had a problem with it
following the library list. It all comes down to a couple parameters
on the CRTSQLxxxx command (the options are the same for COBOL and
RPG). The precompiler OPTION parameter defaults to *SYS. This says
use system naming conventions.
Correct, and system-naming option causes a search for the file name
in the *LIBL, for the unqualified table-references; the SET OPTION
statement however, can be used to serve as an effective H-spec [of the
RPG], for which the parameter specifications are overridden. Works that
way with both prepared and static SQL; i.e. by default, *LIBL is
referenced for NAMING=*SYS, to establish the CURRENT SCHEMA.
Then there's the SQLPATH parameter. The default for this is *NAMING
for which the help text says:
The path used depends on the naming convention specified for the
Precompiler options (OPTION) parameter.
For *SYS naming, the path used is *LIBL, the current library
list at runtime.
It goes on to describe what happens for *SQL naming. But that should
be irrelevant to this discussion.
Quite accurate. But the PATH does not necessarily relate to the
discussion either; i.e. the issue seems to have been with unqualified
*table-references*, for which PATH plays no direct role. However just
as a change to the library list could require invalidation of an ODP
because the table-reference no longer resolves to the same file, so too
could a change in the library list be a legitimate reason to invalidate
the cursor [I suppose, as I do not recall ever having tested nor read
anything in that regard], because a reference to an unqualified function
invocation [or user defined type] would resolve to a different library.?
The issue is what you discovered.
You had set the CLOSQLCSR parameter to *ENDACTGRP (actually, that's
the default value). This means: "SQL cursors are closed and SQL
prepared statements are implicitly discarded, and LOCK TABLE locks
are released when the activation group ends." Since your DAO code
was running in the same activation group, the prepared statement,
etc. wasn't discarded.
That almost seems to imply that the /issues/ the OP had, were due
*not* to a defect, but per failing to choose the proper scoping of their
resources.? That the activation group does not end, should not be a
reason for the failures described by the OP; not from I can infer.
In a named activation, there would have been no desire nor any
benefit to have discarded the prepared statements and closed the cursors
in the scenario presented. Those resources would have been allocated to
that activation, and that activation persisted. For lack of that
activation ending, those resources *should* persist; only when a
resource is referenced and found to be incompatible, should the old
statement be discarded [to be replaced by the updated copy] and the old
cursor be full-closed [to be replaced by a new full-open]. That
essentially describes the whole point of pseudo-closed cursors; to
remain for re-use, given they remain compatible -- as an effective
caching technique.
There is however both a desire and an expectation, that when the
library list has changed, that the user-code still running in that named
activation, would /use/ a proper/compatible SQL cursor for that updated
library-list; i.e. the /issue/ that was discovered, appeared presumably,
to be a defect.
And what was implemented, seems merely to have been a circumvention.
Essentially, that after starting the explicitly named activation, a
Reclaim Activation Group (RCLACTGRP) could be performed explicitly, or
as was noted to have been implemented instead, the same reclaim effect
achieved implicitly, by having first established a[n implicitly named]
New Activation Group with ACTGRP(*NEW). By that additional reclaim
inserted into the process, that had merely ensured the close of the ODPs
[and discarding of prepared statements], thus preventing any possibility
of an error by the SQL to have reused a maintained pseudo-closed cursor
[an ODP] that instead should have been full-closed. By that
circumvention, they have necessarily eliminated the opportunity for the
SQL to establish reuse if/when the possibility both exists and the usage
would be proper; i.e. the choice to reuse would be non-defect per
producing the desired effect, and having done so /faster/ because the
request had effectively run a cached version of the request rather than
having built anew from scratch due to the resources having been reclaimed.
I'm glad you got the problem figured out, but I'm stunned that IBM
would provide such weak and inaccurate advice--especially when
you're getting it from them in paid training.
I was similarly disappointed with the implication about whence the
information/commentary was derived. However I am not totally convinced
that we have all of the information, or that the "tends to" maybe was
just a paraphrasing [or an accurate quote, but as a failed attempt to
give an accurate explanation to a complex situation] that may simply
have overshadowed the possibly that a legitimate course had been
established; i.e. perhaps gotten there by legitimate recommendations,
per details behind decisions of the getting there not being available to
readers of this topic. For example, what can not be changed for static
statements at run-time, is the choice for specification of a Default
Collection (DFTRDBCOL) [the CURRENT SCHEMA] and the SQL Path (SQLPATH)
[CURRENT PATH], both made at compile-time; those would override the
default of *LIBL established with the system-naming (*SYS) option, yet
the values as special-registers are available to be changed during
run-time for dynamic statements.
midrange.com
