Warning :- The main SQL statement may run for many minutes.  In my case just over 1:30 elapsed and 35 CPU seconds, but I only have 222,000 objects in my IFS on a Power 9.  I have run the QSRSRV (see below) on a Power 7+ with around a million objects in the IFS and as I remember it took about 12 minutes.

I’ve decided to start a series of articles about managing your IFS.  There are many ways of doing this.  I found that looking for large directories or directories with many objects, helped in this process.  Also looking for directories with frequent changes, particularly additions, more of this next time.

This is a follow on from Steve Bradshaw’s excellent article https://powerwire.wpengine.com/what-is-eating-my-ifs.  Now that QSYS2.IFS_OBJECT_STATISTICS() has been available for nearly 18 months, I assume most of you have it installed, in addition to the very useful SQL Regular Expressions, (although they are a bit geeky) which is why you can just use these examples.  The latter also requires the OS option 39 International Components for Unicode (included with the OS).  I believe it can be installed without an IPL.

So to get down to it, below is an SQL statement that will go through your IFS and return the number of objects, along with the data size and allocated size totals in bytes.

You can use Run SQL Scripts in ACS, RUNSQL in a CL program or any other SQL with a connection to your IBM i.

Using ACS the table will be created in a library of the same name as the user, unless it is adjusted.  To change it, use library.ifsstats or library/ifsstats in the SQL statement or change the library list on the Connection  –  ‘Edit JDBC Configuration – Default’.

First create a table to hold the results 

CREATE TABLE ifsstats (

            dir_name VARCHAR(16384) ALLOCATE (128),

            data_size BIGINT,

            allocated_size BIGINT,

            link_count BIGINT,

            date_time TIMESTAMP ) ;

Now populate the table  –  the   start_path_name => ‘/’    towards the bottom can be changed to another level if you wish e.g. ‘/QIBM’ or ‘/QIBM/UserData’ etc.

INSERT INTO ifsstats (

      dir_name,

      data_size,

      allocated_size,

      link_count,

      date_time)

   WITH ifsdirs AS (

      SELECT

         CASE object_type

            WHEN ‘*DIR’ THEN SUBSTR(path_name, 1, LENGTH(path_name))

            WHEN ‘*DDIR’ THEN SUBSTR(path_name, 1, LENGTH(path_name))

            ELSE REGEXP_REPLACE(path_name, ‘\/[^/]*$’, ”, 1, 1)

            END AS dir_name,

         object_type,

         data_size,

         allocated_size,

         path_name

      FROM TABLE ( qsys2.ifs_object_statistics(

         start_path_name => ‘/’,

         subtree_directories => ‘YES’,

         object_type_list => ‘*NOQDLS *NOQOPT *NOQSYS’)

      )

   )

   SELECT dir_name,

      SUM(data_size) AS data_size,

      SUM(allocated_size) AS allocated_size,

      COUNT(*) AS link_count,

      current_timestamp as date_time

   FROM ifsdirs

   GROUP BY dir_name ;

Now you can analyse the results.

List everything

SELECT * FROM ifsstats ;

List the top 25 by allocated size

SELECT *  FROM ifsstats

   ORDER BY allocated_size DESC

   FETCH FIRST 25 ROWS ONLY ;

List all records with QIBM as the first level of directory  –  change the last 1 to 3 on the REGEXP and ‘QIBM’ to ‘HTTPA’ to list the third level directory of both /QIBM/ProdData and /QIBM/UserData etc.

WITH selection AS (

   SELECT REGEXP_SUBSTR(dir_name, ‘([^/]*)[^/]’, 1, 1) AS sub_dir,

         a.*

      FROM ifsstats a

   )

   SELECT *

      FROM selection

      WHERE sub_dir = ‘QIBM’

      ORDER BY dir_name;

I hope you get the idea by now – two levels of directory

WITH selection AS (

   SELECT REGEXP_SUBSTR(dir_name, ‘([^/]*)[^/]’, 1, 1) AS sub_dir,

         REGEXP_SUBSTR(dir_name, ‘([^/]*)[^/]’, 1, 2) AS sub_dir2,

         a.*

      FROM ifsstats a

   )

   SELECT *

      FROM selection

      WHERE sub_dir = ‘QIBM’ and sub_dir2 = ‘UserData’

      ORDER BY dir_name;

Summarise by the top level directories, including the number of sub-directories

WITH selection AS (

   SELECT REGEXP_SUBSTR(dir_name, ‘([^/]*)[^/]’, 1, 1) AS sub_dir,

         a.*

      FROM ifsstats a

   )

   SELECT sub_dir,

         SUM(data_size) AS data_size,

         SUM(allocated_size) AS allocated_size,

         COUNT(*) AS no_of_directories

      FROM selection

      WHERE sub_dir IS NOT NULL

      GROUP BY sub_dir

      ORDER BY COUNT(*) DESC;

Quicker report in spool file only – QSRSRV

If you don’t have option 39 of the OS (there are other ways without REGEXP) or you can’t, for some reason, use SQL, there is another solution.  You have to have *ALLOBJ authority and it’s hundreds of pages (660 pages for my 222,000 objects), but it runs much faster than the SQL.

Override the spool file to the widest possible, to get the longer directory names

OVRPRTF FILE(QSRSRV) TOFILE(QTEMP/QSRSRV) DEV(QSRSRV) DEVTYPE(*SCS) PAGESIZE(*N 378)

Run an IBM Must Gather report – change the ‘/’ to collect a subset of all directories.  The  ‘/EPFS’ parameter is  to not process QNTC, QNETWARE, or QLANSRV directories.

CALL       PGM(QSYS/QSRSRV) PARM(METRICS ‘/’ ‘EPFS’)

The nice thing about this is the summary at the bottom of the report.  The allocated size may be slightly different from the SQL, I don’t know why.  The sizes are in KB not bytes.  The number of directories is different by 1, I assume that it does not include the top level directory.

More to come next month.