RE: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

Robert-

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)


-Tom Stieger
IT Manager
California Fine Wire




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Monday, September 16, 2013 9:20 AM
To: midrange-l@xxxxxxxxxxxx
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:

SELECT
Table_Schema
, Table_Name
, Number_Rows
, Number_Deleted_Rows
, CASE
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'
END
AS Algorithm_Used
, DECIMAL( CASE
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows = 0 )
THEN 0
WHEN ( Number_Rows > 0 ) AND ( Number_Deleted_Rows = 0 )
THEN 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 )
END
, 5 , 2 )
AS Percent_Deleted
, Data_Size
, COALESCE( CHAR( DATE( Last_Used_Timestamp ) ), ' Never' ) AS
Last_Used
, COALESCE( CHAR( DATE( Last_Change_Timestamp ) ), ' Never' ) AS
Last_Chgd
, COALESCE( CHAR( DATE( Last_Save_Timestamp ) ), ' Never' ) AS
Last_Saved
, COALESCE( CHAR( DATE( Last_Restore_Timestamp ) ), ' Never' ) AS
Last_Restored
FROM qsys2.systablestat
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,
Robert




--
"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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.