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

DB2 arithemtic division

P: n/a
Why do I have a zero result on this query for all non-zero rows? Do I
have to declare specifically the precision of ratio division? How do I
do that? And is there a way to make DB2 'ignore' division by zero and
just punch up a 0 result where such division is encountered so that I
can remove the where-clause?

Here's the query, I think it's very clear what I want to do:

select substr(tablespace_name, 1, 120) as TBSPC_NAME, used_pages,
free_pages, (used_pages/free_pages) as ratio
from table (snapshot_tbs_cfg ('MUTANT', -1)) as snapshot_tbs_cfg
where free_pages <0;

and here is the input set....

TBSPC_NAME USED_PAGES FREE_PAGES
---------------- -------------------- --------------------
SYSCATSPACE 0 0
TEMPSPACE1 0 0
USERSPACE1 0 0
SYSTOOLSPACE 0 0
TEMP 0 0
TABLESPACE_TS 31184 230944
SYSTOOLSTMPSPACE 0 0
Sep 19 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Pakna wrote:
Why do I have a zero result on this query for all non-zero rows?
Simple: you have integer arithmetic here because your input data is integer.

31184 / 230944 = 0.1350

And that results in 0 because the fractions are simply ignored. If you want
to have a the digits behind the decimal separator, you have to convert at
least one of the parameters to FLOAT or DOUBLE:

SELECT DOUBLE(used_pages) / DOUBLE(free_pages)
FROM ...
And is there a way to make DB2 'ignore' division by zero and
just punch up a 0 result where such division is encountered so that I
can remove the where-clause?
Depends on your DB2 version. AFAIK, DB2 for zOS supports that. LUW doesn't.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 19 '06 #2

P: n/a
Knut Stolze wrote:
Pakna wrote:
>Why do I have a zero result on this query for all non-zero rows?

Simple: you have integer arithmetic here because your input data is integer.

31184 / 230944 = 0.1350

And that results in 0 because the fractions are simply ignored. If you want
to have a the digits behind the decimal separator, you have to convert at
least one of the parameters to FLOAT or DOUBLE:

SELECT DOUBLE(used_pages) / DOUBLE(free_pages)
FROM ...
>And is there a way to make DB2 'ignore' division by zero and
just punch up a 0 result where such division is encountered so that I
can remove the where-clause?

Depends on your DB2 version. AFAIK, DB2 for zOS supports that. LUW doesn't.
Yes, now it works perfectly.

Thank you very much, herr Stolze. Your answers are, as always, much
appreciated.

Much abliged, sir!

With regards
Sep 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.