You are running into the rules DB2 uses for decimal precision with dividing. The rules are in the info center for 7.1 here : http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzdecarithmetic.htm
Basically, you need to cast you numerator and denominator to something like the following:
DEC ( (DEC( Number_Rows, 32, 5)/ DEC(Number_Deleted_Rows, 32, 5) ) * 100.00 , 5, 2)
California Fine Wire
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Monday, September 16, 2013 9:20 AM
Subject: Need assistance with SQL statement over QSYS2.SYSTABLESTAT
[OS v6.1.0, mostly current on PTFs. I apologize in advance if the formatting doesn't work but Thunderbird sometimes does its own thing.]
Using the following SQL statement and a 'Table_Schema' value that I know has the first four CASE WHEN clauses satisfied, I can see that the first
3 WHEN clauses work but the fourth one does not, returning a value of 0.00:
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows = 0 ) THEN '1'
WHEN ( Number_Rows > 0 ) AND ( Number_Deleted_Rows = 0 ) THEN '2'
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows > 0 ) THEN '3'
WHEN ( Number_Rows > Number_Deleted_Rows ) THEN '4'
WHEN ( Number_Deleted_Rows > Number_Rows ) THEN '5'
, DECIMAL( CASE
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows = 0 )
WHEN ( Number_Rows > 0 ) AND ( Number_Deleted_Rows = 0 )
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows > 0 ) THEN 100
WHEN ( Number_Rows > Number_Deleted_Rows )
THEN ( ( Number_Deleted_Rows/Number_Rows ) * 100.00 )
WHEN ( Number_Deleted_Rows > Number_Rows )
THEN ( ( Number_Rows/Number_Deleted_Rows ) * 100.00 )
, 5 , 2 )
, COALESCE( CHAR( DATE( Last_Used_Timestamp ) ), ' Never' ) AS
, COALESCE( CHAR( DATE( Last_Change_Timestamp ) ), ' Never' ) AS
, COALESCE( CHAR( DATE( Last_Save_Timestamp ) ), ' Never' ) AS
, COALESCE( CHAR( DATE( Last_Restore_Timestamp ) ), ' Never' ) AS
WHERE Table_Schema = 'MYLIB'
For example, there is one table that has Number_Rows = 776 and Number_Deleted_Rows = 694. The Algorithm_Used is returned correctly as '4' but the SQL statement returns 0.00 for Percent_Deleted.
(I added the Algorithm_Used column because I needed to see if the tests were activating correctly--and, they are except that I've yet to encounter an instance of '5').
I tried CASTing Percent_Deleted as DECIMAL but that still returns zero values.
Is it something obvious that I'm just overlooking?
Thanks in advance,
"Contrariwise, if it was so, it might be; and if it were so, it would be; but as it isn't, it ain't. That's logic."--Tweedledee
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l