Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: SQL0437W Reason Code 1

    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?

  2. #2
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by DBFinder View Post
    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

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Did you try REORG/Runstats on the table. See if it helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •