Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

[Robert Clay <zreclay@xxxxxxxxx>]

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: