<< But performance-wise, including that predicate could be helpful; >>
I am surprised by your statement that performance of two statements
will be different. I am surprised that by adding statement A.UMNUM=0
to where clause for update will improve SQL performance. Quick check
on our test LPAR (7.1) confirmed that your analysis is correct.
The reason I am surprised is that on most modern databases both
statements perform same way. I checked access plans (explain) on
Oracle and DB2 LUW for similar statements. The access plans for both
statements are same on both databases. However, on "Db2 for i", they
are different. The SQL without repeating predicate "A.UMNUM=0" did
take more time on "DB2 for i" when millions of records are involved .
On Oracle and DB2 LUW both statements performed well (and equally).
We had issues with "DB2 for i" (5.4) due to inefficient optimizer. We
were hoping the optimizer on 7.1 will be better. But, it looks like
"DB2 for i" optimizer (7.1) still performs poorly compared to Oracle
and DB2 LUW. The SQL in question is not complicated, it is a simple
one. "DB2 for i" could not handle a simple SQL as well as Oracle/DB2
This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact