9.2 fp4a AIX
OK So I have some ludicrously long, complex and unsurprisingly poorly performing reporting SQLs I'm trying to (unsuccessfully) tune and I'm getting the old SQL0437W Performance of this complex query may be sub-optimal. Reason code: "1".

I've increased the STMTHEAP to a whopping 163920*4k pages and reduced the Query Opt to 2.

One of the statements is 1000 lines long with lots of CASE and sub-SELECTS etc. but I would have thought a 60MB Statement Heap should cover it, or am I mistaken?

Any ideas?

Seems that not only 1000 lines (wow), but also multiple joins in the query.

Without having looked at the query, I may suggest to try split it in some small queries, try to reduce joins if possible

Yea I usually make the "Can you please write better SQL" request but often it's either Application generated code or they simply say they can't do any better... You should seen the EXPLAIN on this sucker.... db2advisor recommended 30 indexes yieling only a 17% projected improvement. Gonna look at MDC and stuff now I think, see if I can get any boost there.

FML :P

Did you try REORG/Runstats on the table. See if it helps.