Vern Hamberg wrote:
Chuck - I'm not completely sure of this, but I was thinking that
the statement executed with EXECUTE IMMEDIATE would still be
cached - it would not be parameterized, of course - so it'd be
create table lib/table as (select * from otherlib/othertable
where somefield = 'somevalue')
If that runs through SQE, isn't it still cached? There may be
some additional criteria for inclusion. And is it possible for a
plan to be effectively parameterized at the low level? There's
some setting in QAQQINI to control some of that.
But back to the original limitation... Would a cached plan be
parameterized, if it could not be originally? If not, then
every separate invocation would have its own unique plan;
cluttering the cache needlessly. Using an INSERT INTO after the
CREATE without data, at least allows both host variables and
parameterized dynamic for which one copy is maintained.
The given statement describes my point quite well if, indeed it
were cached with the literal. When considering that the /same/
statement might then run sixty other times with 'somevalue01' to
'somevalue60', then there would be sixty copies of unique
statements, for a failure to convert the 'somevalue' into a
parameter marker. But that was conjecture, based on the original
failure which disallows a variable there; i.e. does that restriction
imply also, that the query can not then be parameterized for
caching? Not sure, but...
To clarify one issue for both the original and the
[theoretically] cached statement, they are CREATE not SELECT. That
is, as a CREATE statement it is not DML, and thus it is just never
cached. It would only be the SELECT, the DML portion of the
statement would be cached; and if it could\would, cached parameterized.
Since the DML portion of the CREATE statement must flow the same
path as any other DML, it is conceivable that the SELECT is both
parameterized and cached just as when not part of a CREATE. Is each
subquery cached separately, for example? Then any variation of the
given CREATE [noting that obviously in an application the "lib/file"
would likely be changing], its SELECT could remain unchanged or
varied only for its literal [where a variable is allowed]. In that
case, then the cache could be searched for a matching parameterized
SELECT and use it. At this point I am actually suspecting this
might very well be the case. So maybe the cached dynamic statement,
with a parameterized SELECT, could fair almost as well as an INSERT
with its access plan encapsulated in the *PGM.
p.s. I recall something of the INI option for parameter markers,
but in thinking on it, there is nothing popping into my brain that
would cause me to think there is any relevance to this scenario.