I've adjusted the SQL statement to use SYSPARTIONSTAT:
, Table_NameAS Table_Name
, System_Table_Member AS Member_Name
, Number_RowsAS Number_Rows
, Number_Deleted_Rows AS Number_Deleted_Rows
WHEN ( Number_Deleted_Rows = 0 )
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows > 0 )
ELSE ( 100.00 *Number_Deleted_Rows
/ ( Number_Rows + Number_Deleted_Rows ) )
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
, Days_Used_CountAS Days_Used_Count
WHERETable_Schema = 'MyLIB'
The problem with this is that certain schemas contain source physical
files and they (and ALL of their members) are included in the results.
I want to exclude source physical files because they are not relevant to
Here is the way I normally determine source physical files:
WHERE( ( substr( DBXLIB,1,1 ) not in ( '#' , 'Q' ) ) OR DBXLIB = 'QGPL' )
ANDDBXATR = 'PF'
ANDDBXTYP = 'S'
ORDER BY DBXLIB, DBXFIL
I believe that one needs *ALLOBJ authority to use QADBXREF.I would
prefer to use a view but I can't find one that uses DBXATR and DBXTYP or
Does anyone know of a view suitable for this task?
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