By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,286 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,286 IT Pros & Developers. It's quick & easy.

Division by Zero or changed order of applying predicates in SQL

P: n/a
Hi Folks,

one of our developers uses a statement with a where clause which
usually does not match to any rows. In case of one or more rows found
she wrote a an expression in the select clause which evaluates to a
"division by zero" on purpose to raise an alarm (dirty)

This is the statement:

SELECT
1/VALUE(0,0) <-- this is the point of interest
FROM BIS.SVCPLAN
WHERE (PLANDATUM + 1 DAY) <> DATE('2005-07-13')
AND ANWENDUNG = 'XXX'

Prior DB2 Version 8 Fixpak 9a it worked but after applying the latest
fix it seems that DB2 evaluates the Division by zero expression in the
select clause before evaluting the where clause. Can anybody confirm
this?

I am not a fan of the above approach, however, the developer argues
that this has worked for years and does not work anymore after applying
Fixpak 9a. The developers have hundreds of batches with this way to
raise alarms in case of a matching where clause....

Cheers
Florian

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
fl***********@itgain.de wrote:
Hi Folks,

one of our developers uses a statement with a where clause which
usually does not match to any rows. In case of one or more rows found
she wrote a an expression in the select clause which evaluates to a
"division by zero" on purpose to raise an alarm (dirty)

This is the statement:

SELECT
1/VALUE(0,0) <-- this is the point of interest
FROM BIS.SVCPLAN
WHERE (PLANDATUM + 1 DAY) <> DATE('2005-07-13')
AND ANWENDUNG = 'XXX'

Prior DB2 Version 8 Fixpak 9a it worked but after applying the latest
fix it seems that DB2 evaluates the Division by zero expression in the
select clause before evaluting the where clause. Can anybody confirm
this?

I am not a fan of the above approach, however, the developer argues
that this has worked for years and does not work anymore after applying
Fixpak 9a. The developers have hundreds of batches with this way to
raise alarms in case of a matching where clause....

Cheers
Florian

*sigh* This has turned into a FAQ....
DB2 is rather agressive about evaluating constant expressions.
I.e. it tries to avoid re-computing the expression for every row in
BIS.SVCPLAN (which may, in general, be many).
As DB2 gets better and better about this optimization customers who have
written statements like yours get themselves into trouble.
I would appreciate if you could raise a PMR (call support). Have them
route straight to me (no busy loops with L1/L2 support please).
This needs to get fixed at the root - once and for all.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.