Re: CREATE TABLE AS ... WITH DATA

Chuck

Just spoke with a couple of the database guys at that "fictional" IBM lab in Rochester - verified that the SELECT statement inside the CREATE TABLE statement would be cached. I'd asked about whether they could find a way to allow parameter markers in this situation - said they could, but it is not in the standards. Guess the answer to why it is this way is to read the SQL standards documents - not IBM's - the industry-standard ones.

OK, now literals in the subquery can be parameterized in the plan cache - so there would not be separate statements for each separate literal value in a built-up statement string. There might be different entries if the statistics on the column in question show a skew for some values, resulting in a different plan based on frequent values and histogram distribution and all that.

So it isn't likely to be so bad as you suggest - there might be hope, after all!!

Regards
Vern

CRPence wrote:

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
like

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.

Speculating again!!

CRPence wrote:

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.

Regards, Chuck