Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
I was asked to provide an SQL approach that could be used to identify save file that are needlessly chewing up storage. The following examples find those pesky save file and provide some context as to their size and usage.
--
-- description: Find save file objects within QGPL, and order by size descending
--
select a.iasp_name, a.iasp_number, objname, objsize, max(objcreated, change_timestamp) as change_timestamp, last_used_timestamp, objowner, objdefiner
from table (
qsys2.object_statistics(object_schema => 'QGPL', objtypelist => '*FILE')
) a
where objattribute = 'SAVF'
order by objsize desc;
stop;
--
-- description: Find save file objects across all libraries, and order by size descending
--
select libs.iasp_name, libs.iasp_number, libs.objname as lib_name, savfs.objname as savf_name, savfs.objsize, max(savfs.objcreated, savfs.change_timestamp) as change_timestamp, savfs.last_used_timestamp,
savfs.objowner, savfs.objdefiner
from table (
qsys2.object_statistics(object_schema => '*ALLSIMPLE', objtypelist => '*LIB')
) libs, lateral (
select *
from table (
qsys2.object_statistics(object_schema => libs.objname, objtypelist => '*FILE')
)
) savfs
where savfs.objattribute = 'SAVF'
order by savfs.objsize desc;
stop;
--
-- Establish a table to hold the save file detail for *SYSBAS and every iASP
--
cl: crtlib coolstuff;
create or replace table coolstuff.savf_rpt as (
select libs.iasp_number, libs.objname as lib_name, savfs.objname as savf_name, savfs.objsize, max(savfs.objcreated, savfs.change_timestamp) as change_timestamp, savfs.last_used_timestamp,
savfs.objowner, savfs.objdefiner
from table (
qsys2.object_statistics(object_schema => '*ALLSIMPLE', objtypelist => '*LIB')
) libs, lateral (
select *
from table (
qsys2.object_statistics(object_schema => libs.objname, objtypelist => '*FILE')
)
) savfs
where savfs.objattribute = 'SAVF'
) with no data on replace delete rows;
-- Now, submit a batch job for SYSBAS
cl:SBMJOB CMD(runsql sql(' insert into coolstuff.savf_rpt
select libs.iasp_number, libs.objname as lib_name, savfs.objname as savf_name, savfs.objsize, max(savfs.objcreated, savfs.change_timestamp) as change_timestamp, savfs.last_used_timestamp,
savfs.objowner, savfs.objdefiner
from table (
qsys2.object_statistics(object_schema => ''*ALLAVL'', objtypelist => ''*LIB'')
) libs, lateral (
select *
from table (
qsys2.object_statistics(object_schema => libs.objname, objtypelist => ''*FILE'')
)
) savfs
where savfs.objattribute = ''SAVF'' ') commit(*NONE) naming(*SQL)) JOB(SAVF_RPT) INLASPGRP(*NONE);
-- Are there any available iASPs?
select *
from qsys2.syscatalogs
where catalog_type = 'LOCAL' and catalog_status = 'AVAILABLE' and catalog_aspgrp is not null;
stop;
cl:SBMJOB CMD(runsql sql(' insert into coolstuff.savf_rpt
select libs.iasp_number, libs.objname as lib_name, savfs.objname as savf_name, savfs.objsize, max(savfs.objcreated, savfs.change_timestamp) as change_timestamp, savfs.last_used_timestamp,
savfs.objowner, savfs.objdefiner
from table (
qsys2.object_statistics(object_schema => ''*ALLAVL'', objtypelist => ''*LIB'')
) libs, lateral (
select *
from table (
qsys2.object_statistics(object_schema => libs.objname, objtypelist => ''*FILE'')
)
) savfs
where savfs.objattribute = ''SAVF'' and libs.iasp_number <> 0') commit(*NONE) naming(*SQL)) JOB(SAVF_RPT) INLASPGRP(IASP33); -- <--- fill in the name from CATALOG_ASPGRP here
stop;
--
-- If you see rows returned by this query, the INSERTs have not completed
--
select *
from table (
qsys2.job_info(job_user_filter => '*ALL', job_submitter_filter => '*JOB')
)
where job_name like '%SAVF_RPT%';
stop;
-- Once the jobs have completed
select *
from coolstuff.savf_rpt
order by objsize desc;
@forstie

This comment has been minimized.

Copy link
Owner Author

@forstie forstie commented Nov 6, 2020

Updated on 11/6/2020 to fix a problem with the "run in batch" examples.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment