I've never had that, but I'm pretty sure it's saying that you have, by
definition, resulted in a zero set of rows by making a "where" clause
which by definition rules out a solution with any rows, or where perhaps
you do indeed get the "right" number of rows (whether zero or not) but
you're using identically the same qualifications in at least two places
(i.e., redundantly) which adds nothing informationally and which could
at least theoretically slow down the optimizer. On this last point -
portions of the predicate which are identical - you'd think the
optimizer might weed them out, but for various kinds of really hairy
correlated subqueries, I've found myself essentially "adding code" which
"merely" slowed down the query without being really needed for the
result set. Unfortunately, I got no such message as you're getting now,
and had to figure out on my own how to get a query which originally had
15 instantiations of the same table and ran in 9 minutes down to 9
tables which ran in 9 seconds and accomplished the same thing.

As for the "official" verbiage given in the "help" portion, here it is
(they call it "Optimizer cost underflow"):

SQL0437W Performance of this complex query may be sub-optimal.
Reason code: "<reason-code>".

Explanation:

The statement may achieve sub-optimal performance since the
complexity of the query requires resources that are not available
or optimization boundary conditions were encountered. The
following is a list of reason codes:


1 The join enumeration method was altered due to memory
constraints

2 The join enumeration method was altered due to query
complexity

3 Optimizer cost underflow

4 Optimizer cost overflow

5 Query optimization class was too low

6 Optimizer ignored an invalid statistic

The statement will be processed.

User Response:

One or more of the following:

o Increase the size of the statement heap (stmtheap) in the
database configuration file. (Reason code 1)

o Break the statement up into less complex SQL statements.
(Reason codes 1,2,3,4)

o Ensure predicates do not over-specify the answer set (Reason
code 3)

o Change the current query optimization class to a lower value
(Reason codes 1,2,4)

o Issue Runstats for the tables involved in the query (Reason
codes 3,4)

o Change the current query optimization class to a higher value
(Reason code 5)

o Reissue RUNSTATS for both the tables involved in the query
and their corresponding indexes, that is, use the AND INDEXES
ALL clause so that table and index statistics are consistent
(Reason code 6)



sqlcode : +437

sqlstate : 01602


-----Original Message-----
From: Thompson Keith
[mailto:db2udbdba-ezmlmshield-x32797101.[Email address protected]
Sent: Monday, April 28, 2008 3:40 PM
To: LazyDBA Discussion
Subject: SQL0437W

In tuning queries I see a lot of SQL0437W reason code 3 which is:

Ensure predicates do not over-specify the answer set (Reason code 3)

Can anyone tell me what exactly does that mean?

Keith


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



DB2 & UDB email list listserv db2-l LazyDBA home page