Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

Progress report

I've adjusted the SQL statement to use SYSPARTIONSTAT:

SELECT

Table_SchemaAS Table_Schema

, Table_NameAS Table_Name

, System_Table_Member AS Member_Name

, Number_RowsAS Number_Rows

, Number_Deleted_Rows AS Number_Deleted_Rows

, CAST(CASE

WHEN ( Number_Deleted_Rows = 0 )

THEN0

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

, Days_Used_CountAS Days_Used_Count

FROMqsys2.syspartitionstat

WHERETable_Schema = 'MyLIB'

ORDER BY

Table_Schema

, Table_Name

, System_Table_Member

;

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 the project.

Here is the way I normally determine source physical files:

SELECT DBXLIBLibrary

, DBXFILFile

, DBXOWNOwner

, DBXTXTDesc

, DBXATRAttrib

, DBXTYPType

FROMQSYS.QADBXREF

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 their equivalents.

Does anyone know of a view suitable for this task?

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