asked on Application received several SQL0437W Performance of this complex query might be sub-optimal. Reason code “3”. SQLSTATE=01602
Our application has started to report SQL warnings when it runs:
DBD::DB2::st execute failed: (IBM) (CLI Driver) (DB2/LINUXX8664) SQL0437W Performance of this complex query might be sub-optimal. Reason code “3”. SQLSTATE=01602
For RC=3, it says to increase the size of the statement cache. We have STMM enabled and the sizes of the various memory parameters are all set to AUTOMATIC. The STMTHEAP should grow to accommodate the query as I understand it.
There is plenty of free memory on the database server.
I haven't been able to find the offending sql statement. Even if I do, I don't think the developer would be able to "fix it" quickly.
What can we do in the mean time?
Thanks!
DBD::DB2::st execute failed: (IBM) (CLI Driver) (DB2/LINUXX8664) SQL0437W Performance of this complex query might be sub-optimal. Reason code “3”. SQLSTATE=01602
For RC=3, it says to increase the size of the statement cache. We have STMM enabled and the sizes of the various memory parameters are all set to AUTOMATIC. The STMTHEAP should grow to accommodate the query as I understand it.
There is plenty of free memory on the database server.
I haven't been able to find the offending sql statement. Even if I do, I don't think the developer would be able to "fix it" quickly.
What can we do in the mean time?
Thanks!
DB2
Last Comment
mustaccio
For RC=3, it says to increase the size of the statement cache.
Not sure where you got this suggestion; the manual I'm looking at suggests these actions:
- Break the statement up into less complex SQL statements. (Reason codes 1, 2, 3, 4)
- Ensure predicates do not over-specify the answer set. (Reason code 3)
...
- Issue Runstats for the tables involved in the query. (Reason codes 3, 4)
How frequently do you update table and index statistics?
Post the code and we wil advise :-)
ASKER
mustaccio asked about the frequency of runstats: A complete set of statistics is generated everyday on all tables including SYSIBM.
STMTHEAP is set to AUTOMATIC so I assume it would grow as needed when the query is executed.
I'll try to get the offending SQL. The developer responsible for the application that is getting this error, "inherited" it and isn't very familiar with all of the SQL it contains.
STMTHEAP is set to AUTOMATIC so I assume it would grow as needed when the query is executed.
I'll try to get the offending SQL. The developer responsible for the application that is getting this error, "inherited" it and isn't very familiar with all of the SQL it contains.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
or if the know of any particularly complex/large sql statements that may have begun to be executed...
e.g. for an archive process, quarter end..., etc...
or is it possible that its a dynamic query which is growing overly complex?