Created
September 23, 2020 00:52
-
-
Save forstie/762064534afab0def1e61094a8852c3d to your computer and use it in GitHub Desktop.
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 ; |
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;
`
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.?