Someone asked if you could count the stream file objects, by directory. This solution gets the job done and includes the total size count as well.

IFS breakdown objects by subdir.sql

-- One time setup
cl:ADDDIRE USRID(<user-profile> RST) USRD('Your name') USER(<user-profile>);

--
-- description: Count the stream file objects, by subdirectory
--              Include the total size of all stream files in each subdirectory
--              Order the result by largest object count, descending
--
-- Note: Please note that this isn't a fast query and is meant to be run once in a while
--       and not During a performance critical period.
-- minvrm: v7r3m0

with all_ifs_dirs as
(select path_name as dir_path
  from table (
      qsys2.ifs_object_statistics(
        start_path_name => '/',
        subtree_directories => 'YES',
        object_type_list => '*ALLDIR *NOQSYS',
        ignore_errors => 'YES'
        )) )
 select dir_path, count(*) as stmf_count, sum(data_size) as total_size from
  all_ifs_dirs, table ( qsys2.ifs_object_statistics(
        start_path_name => dir_path,
        subtree_directories => 'NO',
        object_type_list => '*STMF',
        ignore_errors => 'YES'
        ))
   group by dir_path     
   order by 2 desc ;

This is going to useful for the analysis of our IFS that we need to do, but the issue is that our IFS is huge and running this might take a long time. Is there a way to allow us to run wild cards on the path to break it down into manageable pieces? Say all the B folders, etc.?

Hello, I want this query for QDLS ...

I have included in first object_type_list to *ALLSTMF (Select all stream file object types. This includes *MBR, *DOC, *STMF, *DSTMF, and *USRSPC object types.) and object_type_list => '*DOC' the second .. but 0 is the result (I hace a lot of DOCs ...)

Any idea???

I wasn't able to get this done in one statement,...

`-- Create ifs tables.
-- ---------------------------------------------------
if not exists(select '1' from table(QSYS2.OBJECT_STATISTICS('DTSYSDTA', 'FILE', 'UTIFSDTL'))) then 
   call systools.lprintf('  create table UTIFSDTL');

   create table dtsysdta.UTifsdtl
          (sysname         char(8),
           collected       date,
           root_dir        varchar(4096),
           object_type     varchar(10),
           file_ext        varchar(10),
           dir_path        varchar(4096), 
           allocated_size  dec(15),
           created         date,
           last_used       date);
end if;


if not exists(select '1' from table(QSYS2.OBJECT_STATISTICS('DTSYSDTA', 'FILE', 'UTIFSSUM'))) then 
   call systools.lprintf('  create table UTIFSSUM');

   create table dtsysdta.UTifssum
          (sysname       char(8),
           collected     date,
           root_dir      varchar(4096),
           obj_count     dec(11), 
           total_size    dec(15),
           maxCreated    date, 
           maxLastUsed   date,
           objtxt        varchar(50));
end if;




-- Load ifs level detail.
-- ---------------------------------------------------
call systools.lprintf('  load ifs level detail.');

insert into dtsysdta.UTifsdtl
 select CURRENT_SERVER, CURRENT_DATE, 
        regexp_substr(path_name, '^(\/.*?)(\/|$).*$',1,1,'i',1),
        object_type, 
        lower(substr(coalesce(regexp_substr(path_name, '\.([0-9a-z]+)(?:[\?#]|$)', 1, 1, 'i', 1),' '),1,10)),
        path_name, 
        allocated_size,
        date(create_timestamp),
        date(last_used_timestamp)
   from table(qsys2.ifs_object_statistics(start_path_name     => '/',
                                          subtree_directories => 'YES',
                                          object_type_list    => '*NOQDLS *NOQOPT *NOQSYS',
                                          ignore_errors       => 'YES')) as IOS;


-- Load ifs level summary.
-- ---------------------------------------------------
call systools.lprintf('  load ifs level summary.');

insert into dtsysdta.UTifssum                            
select sysname, collected, dtl.root_dir, 
       count(*),
       sum(coalesce(allocated_size,0)), 
       max(coalesce(Created, '1900-01-01')), 
       max(coalesce(Last_Used, '1900-01-01')),
       coalesce(txt.objtext, ' ')
  from dtsysdta.UTifsdtl      as dtl
  left join dtsysdta.UTifstxt as txt
    on (dtl.root_dir = txt.root_dir) 
 group by sysname, collected, dtl.root_dir, txt.objtext
 order by sysname, collected, dtl.root_dir;

`