I am new to db2. Here is the procedure.
/* Produce outfile first via this command on IBMi:*/;
CL: DSPOBJD OBJ(SCRUBLIB/*ALL) OBJTYPE(*ALL) DETAIL(*BASIC) OUTPUT(*OUTFILE) OUTFILE(HERAPERM/SCRUBLIB);
/* Build objects in SCRUBLIB not in Activity from Journal Dump summary file */;
drop table HERAPERM.SCRUBLITST;
CREATE TABLE HERAPERM.SCRUBLITST AS
(
/* Build Activity from Journal Dump summary file */
WITH scrub_activity AS (
SELECT joobj,
jouser,
entry_count,
first_entry,
last_entry,
CASE
WHEN jocode = 'J' -- start / end journalling.
OR jouser IN ('JGP212', 'KXK632', 'DJY881', 'S_DBAADMIN', 'SYSADMIN', 'S_PRDSUPT') THEN 'admin'
WHEN jouser IN ('QUICK', 'QSYS')
AND -- ignore replication & other background activity
((jocode = 'F'
AND joentt IN ('CB', 'CL', 'OP'))
-- OP = open, CL = closed, CB = member changed (generally for stats info)
OR (jocode = 'D'
AND joentt IN ('DH', 'DW')) -- save activitiy
) THEN 'system'
WHEN
jouser IN ('MISRBTAZ',
'MISRBTEA', 'MISRBTCZ', 'MISRBT', 'MISRBTNW', 'MISRBTCT', 'MISRBTLR', 'MISRBTPA', 'MISRBTUS', 'MISRBTCW',
'MISRBTLF', 'MISRBTCA', 'MISRBTMS', 'MISRBTFT') THEN 'production jobs'
WHEN
jouser IN ('TURNOVER',
'PGMRADMIN', 'MXE325', 'MXE325', 'ELP847', 'KVP548', 'WMC577', 'CKK315', 'TDM450', 'AAG907', 'RAV995', 'AGG582',
'GDD759', 'CDJ667', 'MBB128', 'JHH085', 'AAA249', 'AGC549', 'JID202', 'V_KNN648', 'FSS256', 'DHT776', 'XPP203',
'RNF185', 'KRR842', 'KOT500', 'RKK970', 'KNN152', 'SRR886', 'PPP228', 'V_GVV273', 'JFF869', 'KSS648', 'KKK588',
'JFF449', 'JAC000', 'DWW693', 'SSS865', 'RPK746', 'V_FSS633', 'SAA508') THEN 'developer activity'
ELSE 'other'
END AS activity_type
FROM techcmds.scrubjrn1
WHERE joobj <> '' -- only interested in object changes, not receiver switches, etc.
AND SUBSTR(last_entry, 1, 2) = to_char(current_date - 2 MONTH, 'MM')
-- Only objects activity (2 months ago)
OR joobj <> '' -- only interested in object changes, not receiver switches, etc.
AND SUBSTR(last_entry, 1, 2) = to_char(current_date - 1 MONTH, 'MM')
-- Only objects activity (1 month ago)
OR joobj <> '' -- only interested in object changes, not receiver switches, etc.
AND SUBSTR(last_entry, 1, 2) = to_char(current_date, 'MM')
-- Only objects activity (Current month)
),
scrub_activity_sum AS (
SELECT joobj,
activity_type,
SUM(entry_count) entry_count,
MIN(first_entry) first_entry,
MAX(last_entry) last_entry
FROM scrub_activity
GROUP BY joobj,
activity_type
)
/* Compare SCRUBLIB objects against journal activity to find objects in Scrublib*/
/* that have not been opened/closed/etc for prod purposes */
/* Scrublib Inventory created from EndDr SQL Analysis script */
SELECT x.ODOBNM,
x.ODOBTP,
x.ODOBAT,
x.ODCDAT,
x.ODUDAT
FROM HERAPERM.SCRUBLIB AS x
WHERE x.ODOBNM NOT IN (SELECT y.JOOBJ
FROM scrub_activity_sum AS y
WHERE activity_type NOT IN ('admin', 'system')
/* Filters objects created in current year and less than filter month */
AND y.JOOBJ IS NULL)
AND x.ODCRTS = 'ENDEAVOR'
/* Ignore objects created on EndDr/DPITest for Endeavor cleanup */
/* 12-2020 */
AND x.ODOBNM <> 'QDFTJRN' /* Ignore journal manager dtaara */
AND x.ODOBTP <> 'QMQRY' /* Ignore Query Objects // 12-2020 */
AND x.ODOBTP <> 'QRYDFN' /* Ignore Query Objects // 12-2020 */
)
WITH DATA;
