DB2 arithemtic division

Pakna

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

Knut Stolze

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

Pakna

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