470,591 Members | 1,869 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

DB2 arithemtic division

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
2 30310
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
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.

Similar topics

12 posts views Thread by Tim Rowe | last post: by
2 posts views Thread by Sebastian Haase | last post: by
5 posts views Thread by Jive | last post: by
9 posts views Thread by Marcin | last post: by
17 posts views Thread by seb.haase | last post: by
2 posts views Thread by kermit | last post: by
13 posts views Thread by jamesonang | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.