Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

Hi, Jim

Well, as Rob pointed out, I'm using SYSTABLESTAT for now but will need to think about SYSPARTITIONSTAT in the near future.

Anyway, here is what I have so far:

SELECT
Table_Schema AS Table_Schema
, Table_Name AS Table_Name
, Number_Rows AS Number_Rows
, Number_Deleted_Rows AS Number_Deleted_Rows
, CAST(CASE
WHEN ( Number_Deleted_Rows = 0 ) THEN 0
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows > 0 ) THEN 100.00
ELSE ( 100.00 * Number_Deleted_Rows / ( Number_Rows + Number_Deleted_Rows ) )
END
AS DECIMAL( 5 , 2 )
) AS Percent_Deleted
, Data_Size AS Data_Size
, COALESCE( CHAR( DATE( Last_Used_Timestamp ) ), ' ' ) AS Last_Used
, COALESCE( CHAR( DATE( Last_Change_Timestamp ) ), ' ' ) AS Last_Chgd
, COALESCE( CHAR( DATE( Last_Save_Timestamp ) ), ' ' ) AS Last_Saved
, COALESCE( CHAR( DATE( Last_Restore_Timestamp ) ), ' ' ) AS Last_Restored
FROM qsys2.systablestat
WHERE Table_Schema = 'MyLIB' -- Use your actual schema name here, etc.


It takes a looooooooong time to run for large schemas, though, so be warned.


"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
On 09/17/2013 2:30 PM, Jim Oberholtzer wrote:

Robert,

Any chance you could post that to the code scratch pad? I'm trying to follow the changes you made and messing it up.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 9/17/2013 7:09 AM, Robert Clay wrote:

Thanks, Birgitta!

Unless someone else has input, I'll go with this: