Re: CPI4323 too often?

The customer has since been in contact with IBM about another issue and more PTFs are being installed so when I came across this issue the latest ones were not yet on the system.

It is possible that the same statement is coming in from an ODBC client via Client Access and from an AS/400 CLI program. Presumably the safest thing to do here is for the programs to set all the relevant settings and not rely on ODBC settings or job descriptions to be the same.

I presume SQE is being used but I don't know. How can I tell?

Can I look at the contents of the statement cache?

Thanks
Richard

Kent Milligan wrote:
Is it possible that someone else ran that same SQL statements in the CLI program via a different interface like STRSQL that would have different settings?

There's a SystemWide SQL Statement Cache for Dynamic SQL interfaces such as CLI and they only allow one entry per SQL statement. So if your attributes don't match, then the cached plan cannot be used.

Is the latest DB Group PTF installed on your server? Do you know if the SQL is being run by CQE or SQE?

Richard Hennessy wrote:
Hi

I have a SQL/CLI program running on the AS/400. The program is started via remote execution. In the joblog for the program there are lots of CPI4323 with these reason codes

9 - The value of one of the following is different in the current job: date format, date separator, time format, or time separator.

17 - The access plan was generated with a commitment control level that is different in the current job.


My program doesn't set any of the date/time attributes or commitment control, it relys on the defaults.
What could be the reason for the access plan rebuilds? Exactly which job
definition should I look at to compare the attributes that are different.

Richard Hennessy



.

Relevant Pages

  • Re: CPI4323 too often?
    ... You cannot view the content of the system cache, but you can get info from the Database Monitor data to see where the access plan came from as well as whether SQE is being used. ... There's a SystemWide SQL Statement Cache for Dynamic SQL interfaces such as CLI and they only allow one entry per SQL statement. ...
    (comp.sys.ibm.as400.misc)
  • Re: Monitoring SQL Statement Cache
    ... I can see that using the SMI tables, I can get a lot of what I want. ... the SQL Statement Cache. ... this is an undocumented feature, ... including the statement cache you are looking for. ...
    (comp.databases.informix)