IFS Storage Analysis

IFSStorageAnalysis.sql

/***********************************************************************************/
/* Look at files taking up space in IFS                                            */
/*                                                                                 */
/* Returns similar information to RTVDIRINF, but at the (relative) speed of light! */
/***********************************************************************************/

-- Get raw info about files from IFS_OBJECT_STATISTICS
WITH ALL_OBJS AS (
         SELECT PATH_NAME,
                OBJECT_TYPE,
                DATA_SIZE AS FILESIZE,
                OBJECT_OWNER
             FROM TABLE (
                     QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/home', -- Set "root" directory for analysis
                                                 SUBTREE_DIRECTORIES => 'YES',
                                                 OMIT_LIST => '/QSYS.LIB /QFileSvr.400')
                 )
     ),
     -- Get the total size of all data underneath root
     TOTAL_DATA_SIZE AS (
         SELECT CAST(SUM(FILESIZE) AS DECFLOAT) AS DATA_SIZE
             FROM ALL_OBJS
             WHERE OBJECT_TYPE != '*DIR'
     ),

    -- Get path names to files, also calculate percent storage used (relative to total storage under root).
    PATHS_AND_FILES AS (
        SELECT SUBSTRING(PATH_NAME, 1, LOCATE_IN_STRING(PATH_NAME, '/', -1)) AS PATHNAME,
               SUBSTRING(PATH_NAME, LOCATE_IN_STRING(PATH_NAME, '/', -1) + 1) AS FILENAME,
               FILESIZE,
               OBJECT_TYPE,
               (CAST(FILESIZE AS DECFLOAT) / (SELECT DATA_SIZE FROM TOTAL_DATA_SIZE)) * 100 AS PERCENT_STORAGE
            FROM ALL_OBJS
            WHERE OBJECT_TYPE != '*DIR'
    ),
    
    -- Sum file size by directory.
    FILES_AND_DIRS AS (
        SELECT PATHNAME, 
               COUNT(*) AS NUM_FILES,
               CAST(CAST(SUM(FILESIZE) AS DECFLOAT) / 1000000 AS DEC(12, 2)) AS STORAGE_USED_MB, 
               CAST(SUM(PERCENT_STORAGE) AS DEC(5, 2)) AS PERCENT_STORAGE_USED_OF_RELATIVE_ROOT
        FROM PATHS_AND_FILES
        GROUP BY PATHNAME)

SELECT * FROM FILES_AND_DIRS
ORDER BY PERCENT_STORAGE_USED_OF_RELATIVE_ROOT DESC;

cool stuff here...
should the "order by" at the end be PERCENT_STORAGE_USED_OF_RELATIVE_ROOT ?

Hi Bryan, it should be PERCENT_STORAGE_USED_OF_RELATIVE_ROOT - I've fixed it up. I've been shuffling some names around and missed that change. Thanks for catching it.