Solved

Application received several SQL0437W Performance of this complex query might be sub-optimal.  Reason code “3”.  SQLSTATE=01602

Posted on 2013-11-22
5
1,571 Views
Last Modified: 2013-12-30
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!
0
Comment
Question by:data_bits
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39669458
probably still worth asking the developers what they've changed recently...

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?
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 39675624
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?
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 39680723
Post the code and we wil advise :-)
0
 

Author Comment

by:data_bits
ID: 39689805
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.
0
 
LVL 8

Accepted Solution

by:
mustaccio earned 500 total points
ID: 39689915
You may want to try generating the full explain plan of the statement in question, using db2exfmt -- it will likely contain information that might be useful for troubleshooting.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Find Value column 2 does not work 1 196
AS400 Forms 9 134
I am trying to building a ddl statement off a cobol layout 3 130
AS400 BRMS - none enrolled tape 6 65
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question