On 10/28/13 2:35 PM, James Rich wrote:
On Mon, 28 Oct 2013, CRPence wrote:
On 10/28/13 1:10 PM, James Rich wrote:
<<SNIP>> There is something unusual happening when *any* file
named COCTLP is placed into library DMSIDEV. Whether I move the
file, create it new from source, or copy it from another location
the second row of results contains invalid data *even though*
*the data is correct when queried or viewed with DSPPFM*.
Perhaps an issue related to [prepared statement] caching. I would
try specifying a column-list for the SELECT instead of using the
asterisk to test if the problem is circumvented. FWiW I also would
not ignore close errors.
Fascinating. Changing the prepared statement did cure the problem.
I'm doing my testing from a laptop that has been turned off over the
weekend, so the caching that is occurring must be happening on the
System i. Any suggestions on how I can instruct the System i to
clear the cache besides an IPL?
Yes. Caching of SQL at the server.
Perhaps reporting the issue to the service provider is best... to
determine if an improper utilization of the Plan Cache [or statement
cache] is at fault; presumably so, given the results, as expressed. The
IBM support should be able to provide docs about how to dump the cache
and any supporting docs that would assist them to diagnose the issue.
However see the following doc snippet [at the bottom] which shows some
means to remove a specific statement; doing that of course would just
circumvent the issue, and whatever is the origin remains undiagnosed and
leaves the system with no preventive for further encounters of the same
difficulty:
IBM i 7.1 Information Center -> Database -> Performance and query
optimization -> Query engine overview
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzajq/rzajqcache.htm
_i Plan cache i_
"The plan cache is a repository that contains the access plans for
queries that were optimized by SQE.
Access plans generated by CQE are not stored in the plan cache; instead,
they are stored in SQL packages, the system-wide statement cache, and
job cache.
...
... The plan cache is cleared when a system Initial Program Load (IPL)
is performed.
Multiple access plans for a single SQL statement can be maintained in
the plan cache. Although the SQL statement is the primary key into the
plan cache, different environmental settings can cause additional access
plans to be stored. ...
...
Currently, the plan cache can maintain a maximum of three different
access plans for the same SQL statement. As new access plans are created
for the same SQL statement, older access plans are discarded to make
room for the new access plans. There are, however, certain conditions
that can cause an existing access plan to be invalidated. Examples of
these conditions include:
* Specifying REOPTIMIZE_ACCESS_PLAN(*YES) or (*FORCE) in the QAQQINI
table or in Run SQL Scripts
* Deleting or recreating the table that the access plan refers to
* Deleting an index that is used by the access plan
..."
midrange.com
