Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
The request from a client was to provide an SQL approach to search all of the IFS, finding any object that has "log4j" in its name, and producing an SQL table with the search results.
--
-- Subject: Find objects that have the string "log4j" in their name (on IBM i 7.1 or IBM i 7.2)
-- Author: Scott Forstie
-- Date : December 17, 2021
-- What to do on IBM i releases when ifs_object_statistics, and other SQL services do not exist
--
--
stop;
--
--
-- Note 1, if you have iASPs, you would need to repeat the search for each iASP, specifying the iASP namespace on the SBMJOB
-- So, instead of searching from the root ('/'), you would initiate the search from '/IASPNAME' and have INLASPGRP(IASPNAME)
--
-- Note 2, this search avoids QSYS (library based) objects. If you have a requirement to include those objects in the search, remove the OMIT parameter.
--
-- Note 3, this search is intended to be initiated by someone who has *ALLOBJ special authority.
--
-- Note 4, this search will likely be long running, which is why is it submitted to QBATCH.
-- As the search will touch all objects in the IFS, it is recommended that the search be done outside of a performance critical window.
--
-- Establish a library to hold the search results
--
cl: crtlib IFSSEARCH;
cl:GRTOBJAUT OBJ(IFSSEARCH) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*EXCLUDE);
--
-- If you've run this before, you have to delete this work files before running them again
--
drop table IFSSEARCH.IFSINFO2O;
drop table IFSSEARCH.IFSINFO2D;
--
-- Submit a job to batch that does the searching
--
cl: SBMJOB CMD(RTVDIRINF DIR('/') INFFILEPFX(IFSINFO2) INFLIB(IFSSEARCH) OMIT('/QSYS')) JOB(IFSSEARCH) INLASPGRP(*CURRENT) JOBMSGQFL(*PRTWRAP) LOG(4 00 *SECLVL);
--
-- Use WRKACTJOB SBS(QBATCH) to follow the job execution... wait for it to complete
--
stop;
--
-- Establish a table to hold the search results
--
drop table ifssearch.results;
create table ifssearch.results as
(select QEZDIRNAM1 as IFS_DIRECTORY,
QEZOBJNAM as IFS_OBJECT_NAME,
QEZOBJTYPE as IFS_OBJECT_TYPE,
qezown as owner,
o.*
from IFSSEARCH.IFSINFO2O O
inner join IFSSEARCH.IFSINFO2D D
on O.QEZDIRIDX = D.QEZDIRIDX
where upper(QEZOBJNAM) like '%LOG4J%')
with data;
cl:GRTOBJAUT OBJ(IFSSEARCH/RESULTS) OBJTYPE(*FILE) USER(*PUBLIC) AUT(*EXCLUDE);
drop table IFSSEARCH.IFSINFO2O; -- delete work file
drop table IFSSEARCH.IFSINFO2D; -- delete work file
--
-- View the results
--
select *
from ifssearch.results;
--
-- Subject: Find objects that have the string "log4j" in their name
-- Author: Scott Forstie
-- Date : December 13, 2021
-- Features Used : This Gist uses ifs_object_statistics, job_info, RUNSQL, and SBMJOB
--
--
stop;
--
--
-- Note 1, if you have iASPs, you would need to repeat the search for each iASP, specifying the iASP namespace on the SBMJOB
-- So, instead of searching from the root ('/'), you would initiate the search from '/IASPNAME' and have INLASPGRP(IASPNAME)
--
-- Note 2, this search avoid QSYS (library based) objects. If you have a requirement to include those objects in the search, remove *NOQSYS.
--
-- Note 3, this search is intended to be initiated by someone who has *ALLOBJ special authority.
--
-- Note 4, this search will likely be long running, which is why is it submitted to QBATCH.
-- As the search will touch all objects in the IFS, it is recommended that the search be done outside of a performance critical window.
--
-- Establish a library to hold the search results
--
cl: crtlib IFSSEARCH;
cl:GRTOBJAUT OBJ(IFSSEARCH) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*EXCLUDE);
--
-- Establish a table to hold the search results
--
create table ifssearch.results as (
select current timestamp as timeOfSearch, a.*
from table (
qsys2.ifs_object_statistics(
start_path_name => '/',
subtree_directories => 'YES',
object_type_list => '*ALLDIR *ALLSTMF *NOQSYS',
ignore_errors => 'YES'
)) a
where upper(path_name) like '%LOG4J%'
) with no data;
cl:GRTOBJAUT OBJ(IFSSEARCH/RESULTS) OBJTYPE(*FILE) USER(*PUBLIC) AUT(*EXCLUDE);
--
-- Submit a job to batch that does the searching
--
cl: SBMJOB CMD(RUNSQL SQL('insert into ifssearch.results
select current timestamp as timeOfSearch, a.*
from table (
qsys2.ifs_object_statistics(
start_path_name => ''/'',
subtree_directories => ''YES'',
object_type_list => ''*ALLDIR *ALLSTMF *NOQSYS'',
ignore_errors => ''YES''
)) a
where upper(path_name) like ''%LOG4J%'' ')
commit(*NONE)) JOB(IFSSEARCH) INLASPGRP(*CURRENT) JOBMSGQFL(*PRTWRAP) LOG(4 00 *SECLVL);
stop;
--
-- If you see rows returned by this query, the batch job has not yet completed
--
select *
from table (
qsys2.job_info(job_user_filter => '*ALL', job_submitter_filter => '*JOB')
);
--
-- Finally, review the results of the search
--
select * from ifssearch.results;
@forstie
Copy link
Author

forstie commented Dec 18, 2021

Nice going Matt.
I had several clients ask me for a technique that could be used on IBM i 7.2 and IBM i 7.1.
They could use your solution, or here is my alternate solution. (code shared above)

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