More SQL than you want.... or so much fine SQL that you keep coming back? Time will tell.
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
-- | |
-- ====================================================================== | |
-- iSee - Using SQL to Manage IBM i | |
-- ====================================================================== | |
-- | |
-- ====================================================================== | |
-- Scott Forstie | |
-- forstie@us.ibm.com | |
-- Date: November, 2020 | |
-- Disclaimer: These examples are provided without warranty, yada yada | |
-- ====================================================================== | |
-- | |
-- | |
stop; | |
-- ====================================================================== | |
-- ====================================================================== | |
-- ====================================================================== | |
cl: crtlib coolstuff; | |
set schema coolstuff; | |
set path coolstuff, system path; | |
stop; | |
-- ====================================================================== | |
-- ====================================================================== | |
-- | |
-- description: Unused objects | |
-- | |
-- Beware: last_used_timestamp is not updated for all object types. | |
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rbam6/detob.htm | |
-- | |
-- | |
-- definition: Find objects created > 6 months ago that have | |
-- not been used or changed in the last 6 months | |
-- ====================================================================== | |
-- ====================================================================== | |
-- ====================================================================== | |
-- | |
-- description: Find objects not used within a specified timeframe | |
-- | |
-- ====================================================================== | |
-- ====================================================================== | |
stop; | |
select * from | |
table(qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA')) | |
where (last_used_timestamp is NULL or | |
last_used_timestamp < current timestamp - 1 months) and | |
(change_timestamp < current timestamp - 1 months); | |
stop; | |
select * from | |
table(qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA')) | |
where (last_used_timestamp is NULL or | |
last_used_timestamp < current timestamp - 1 months) and | |
(change_timestamp < current timestamp - 1 months) | |
order by objsize desc; | |
stop; | |
select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size, | |
objs.* from | |
table(qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA')) objs | |
where (last_used_timestamp is NULL or | |
last_used_timestamp < current timestamp - 1 months) and | |
(change_timestamp < current timestamp - 1 months) | |
order by objsize desc; | |
stop; | |
-- formatter time | |
create table Nov10objs as (select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size, objs.* from | |
table(qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA')) objs | |
where (last_used_timestamp is NULL or last_used_timestamp < current timestamp - 1 months) and | |
(change_timestamp < current timestamp - 1 months) order by objsize desc) with data; | |
select * from Nov10objs; | |
stop; | |
-- | |
-- 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; | |
-- ====================================================================== | |
-- ====================================================================== | |
-- | |
-- Generate the column list for a UDTF! | |
-- | |
-- ====================================================================== | |
-- ====================================================================== | |
select * from qsys2.sysroutine where routine_name = 'OBJECT_STATISTICS'; | |
select * from qsys2.sysparms c | |
where specific_schema = 'QSYS2' and | |
specific_name = (select specific_name from qsys2.sysroutine | |
where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2'); | |
stop; | |
-- debug what's wrong here | |
select | |
listagg(cast(qsys2.delimit_name(parameter_name) as clob(1m)), ', ') within group (order by | |
ordinal_position) as parameter_list | |
from qsys2.sysparms c | |
where specific_schema = 'QSYS2' and | |
specific_name = (select specific_name from qsys2.sysroutine | |
where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2') | |
parameter_mode = 'OUT'; -- Don't include input parameters (doh) | |
stop; | |
select | |
listagg(cast(qsys2.delimit_name(parameter_name) as clob(1m)), ', ') within group (order by | |
ordinal_position) as parameter_list | |
from qsys2.sysparms c | |
where specific_schema = 'QSYS2' and | |
specific_name = (select specific_name from qsys2.sysroutine | |
where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2') and | |
parameter_mode = 'OUT'; -- Don't include input parameters (doh) | |
stop; | |
-- how do we overcome this? | |
create table Nov10objs as | |
(select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size, | |
objname, objtype, objowner, objdefiner, objcreated, objsize, objtext, | |
objlongname, last_used_timestamp, days_used_count, last_reset_timestamp, | |
iasp_number, objattribute, objlongschema, text, sql_object_type, objlib, | |
change_timestamp, user_changed, source_file, source_library, source_member, | |
source_timestamp, created_system, created_system_version, licensed_program, | |
licensed_program_version, compiler, compiler_version, | |
user_defined_attribute, compressed, object_domain, object_audit, | |
object_signed, system_trusted_source, multiple_signatures, save_timestamp, | |
restore_timestamp, save_while_active_timestamp, save_command, save_device, | |
save_file_name, save_file_library, save_volume, save_label, | |
save_sequence_number, last_save_size, journaled, journal_name, | |
journal_library, journal_images, omit_journal_entry, remote_journal_filter, | |
journal_start_timestamp, apply_starting_receiver, | |
apply_starting_receiver_library | |
from table ( | |
qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA') | |
) objs | |
where (last_used_timestamp is null or | |
last_used_timestamp < current timestamp - 1 months) and | |
(change_timestamp < current timestamp - 1 months) | |
order by objsize desc) | |
with data; | |
stop; | |
create or replace table Nov10objs as | |
(select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size, | |
objname, objtype, objowner, objdefiner, objcreated, objsize, objtext, | |
objlongname, last_used_timestamp, days_used_count, last_reset_timestamp, | |
iasp_number, objattribute, objlongschema, text, sql_object_type, objlib, | |
change_timestamp, user_changed, source_file, source_library, source_member, | |
source_timestamp, created_system, created_system_version, licensed_program, | |
licensed_program_version, compiler, compiler_version, | |
user_defined_attribute, compressed, object_domain, object_audit, | |
object_signed, system_trusted_source, multiple_signatures, save_timestamp, | |
restore_timestamp, save_while_active_timestamp, save_command, save_device, | |
save_file_name, save_file_library, save_volume, save_label, | |
save_sequence_number, last_save_size, journaled, journal_name, | |
journal_library, journal_images, omit_journal_entry, remote_journal_filter, | |
journal_start_timestamp, apply_starting_receiver, | |
apply_starting_receiver_library | |
from table ( | |
qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA') | |
) objs | |
where (last_used_timestamp is null or | |
last_used_timestamp < current timestamp - 1 months) and | |
(change_timestamp < current timestamp - 1 months) | |
order by objsize desc) | |
with data on replace delete rows; | |
stop; | |
-- default ownership | |
select * from Nov10objs where objowner = 'QDFTOWN'; | |
stop; | |
-- journal insight | |
select journaled, count(*) as journaled_count from Nov10objs | |
group by journaled | |
order by 2 desc; | |
-- transformative grouping | |
select journal_library concat '/' concat journal_name, | |
count(*) as journal_count | |
from Nov10objs | |
group by journal_library concat '/' concat journal_name | |
order by 2 desc; | |
-- | |
-- description: Which files in a library, are not being journaled? | |
-- | |
SELECT * | |
FROM TABLE ( | |
QSYS2.OBJECT_STATISTICS('TOYSTORE', '*ALL') | |
) X | |
WHERE JOURNAL_LIBRARY IS NULL AND | |
OBJTYPE = '*FILE' | |
ORDER BY OBJNAME ASC; | |
stop; | |
-- | |
-- Multiple libraries | |
-- | |
-- Dawn's libraries :-) | |
-- | |
select * | |
from table ( | |
qsys2.object_statistics('QSYS ', '*LIB') | |
) libs | |
where libs.objdefiner = 'DAWNM'; | |
stop; | |
-- not quite there yet | |
create or replace table Nov10objs as | |
(select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size, | |
objname, objtype, objowner, objdefiner, objcreated, objsize, objtext, | |
objlongname, last_used_timestamp, days_used_count, last_reset_timestamp, | |
iasp_number, objattribute, objlongschema, text, sql_object_type, objlib, | |
change_timestamp, user_changed, source_file, source_library, source_member, | |
source_timestamp, created_system, created_system_version, licensed_program, | |
licensed_program_version, compiler, compiler_version, | |
user_defined_attribute, compressed, object_domain, object_audit, | |
object_signed, system_trusted_source, multiple_signatures, save_timestamp, | |
restore_timestamp, save_while_active_timestamp, save_command, save_device, | |
save_file_name, save_file_library, save_volume, save_label, | |
save_sequence_number, last_save_size, journaled, journal_name, | |
journal_library, journal_images, omit_journal_entry, remote_journal_filter, | |
journal_start_timestamp, apply_starting_receiver, | |
apply_starting_receiver_library | |
from table ( | |
qsys2.object_statistics('QSYS ', '*LIB') | |
) libs, lateral ( | |
select * | |
from table ( | |
qsys2.object_statistics( | |
libs.objname, '*PGM *SRVPGM *FILE *USRSPC *DTAARA') | |
) x | |
) objs | |
where libs.objdefiner = 'DAWNM' and | |
(last_used_timestamp is null or | |
last_used_timestamp < current timestamp - 1 months) and | |
(change_timestamp < current timestamp - 1 months) | |
order by objsize desc) | |
with data | |
on replace delete rows; | |
stop; | |
-- improve the select list | |
select | |
listagg(cast('objs.' concat qsys2.delimit_name(parameter_name) as clob(1m)), ', ') within group (order by | |
ordinal_position) as parameter_list | |
from qsys2.sysparms c | |
where specific_schema = 'QSYS2' and | |
specific_name = (select specific_name from qsys2.sysroutine | |
where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2') and | |
parameter_mode = 'OUT'; -- Don't include input parameters (doh) | |
stop; | |
create or replace table Nov10objs as | |
(select varchar_format(objs.objsize, '999G999G999G999G999G999G999G999') as obj_size, | |
objs.objname, objs.objtype, objs.objowner, objs.objdefiner, objs.objcreated, | |
objs.objsize, objs.objtext, objs.objlongname, objs.last_used_timestamp, | |
objs.days_used_count, objs.last_reset_timestamp, objs.iasp_number, | |
objs.objattribute, objs.objlongschema, objs.text, objs.sql_object_type, | |
objs.objlib, objs.change_timestamp, objs.user_changed, objs.source_file, | |
objs.source_library, objs.source_member, objs.source_timestamp, | |
objs.created_system, objs.created_system_version, objs.licensed_program, | |
objs.licensed_program_version, objs.compiler, objs.compiler_version, | |
objs.user_defined_attribute, objs.compressed, objs.object_domain, | |
objs.object_audit, objs.object_signed, objs.system_trusted_source, | |
objs.multiple_signatures, objs.save_timestamp, objs.restore_timestamp, | |
objs.save_while_active_timestamp, objs.save_command, objs.save_device, | |
objs.save_file_name, objs.save_file_library, objs.save_volume, | |
objs.save_label, objs.save_sequence_number, objs.last_save_size, | |
objs.journaled, objs.journal_name, objs.journal_library, | |
objs.journal_images, objs.omit_journal_entry, objs.remote_journal_filter, | |
objs.journal_start_timestamp, objs.apply_starting_receiver, | |
objs.apply_starting_receiver_library | |
from table ( | |
qsys2.object_statistics('QSYS ', '*LIB') | |
) libs, lateral ( | |
select * | |
from table ( | |
qsys2.object_statistics( | |
libs.objname, '*PGM *SRVPGM *FILE *USRSPC *DTAARA') | |
) x | |
) objs | |
where libs.objdefiner = 'DAWNM' and | |
(objs.last_used_timestamp is null or | |
objs.last_used_timestamp < current timestamp - 1 months) and | |
(objs.change_timestamp < current timestamp - 1 months) | |
order by objs.objsize desc) | |
with data | |
on replace delete rows; | |
stop; | |
-- something seems to be missing...? | |
select * from Nov10objs; | |
stop; | |
-- defeated??? no way jose | |
create or replace table Nov10objs as | |
(select libs.objname, objs.objname, | |
varchar_format(objs.objsize, '999G999G999G999G999G999G999G999') as obj_size, | |
objs.objtype, objs.objowner, objs.objdefiner, objs.objcreated, objs.objsize, | |
objs.objtext, objs.objlongname, objs.last_used_timestamp, | |
objs.days_used_count, objs.last_reset_timestamp, objs.iasp_number, | |
objs.objattribute, objs.objlongschema, objs.text, objs.sql_object_type, | |
objs.objlib, objs.change_timestamp, objs.user_changed, objs.source_file, | |
objs.source_library, objs.source_member, objs.source_timestamp, | |
objs.created_system, objs.created_system_version, objs.licensed_program, | |
objs.licensed_program_version, objs.compiler, objs.compiler_version, | |
objs.user_defined_attribute, objs.compressed, objs.object_domain, | |
objs.object_audit, objs.object_signed, objs.system_trusted_source, | |
objs.multiple_signatures, objs.save_timestamp, objs.restore_timestamp, | |
objs.save_while_active_timestamp, objs.save_command, objs.save_device, | |
objs.save_file_name, objs.save_file_library, objs.save_volume, | |
objs.save_label, objs.save_sequence_number, objs.last_save_size, | |
objs.journaled, objs.journal_name, objs.journal_library, | |
objs.journal_images, objs.omit_journal_entry, objs.remote_journal_filter, | |
objs.journal_start_timestamp, objs.apply_starting_receiver, | |
objs.apply_starting_receiver_library | |
from table ( | |
qsys2.object_statistics('QSYS ', '*LIB') | |
) libs, lateral ( | |
select * | |
from table ( | |
qsys2.object_statistics( | |
libs.objname, '*PGM *SRVPGM *FILE *USRSPC *DTAARA') | |
) x | |
) objs | |
where libs.objdefiner = 'DAWNM' and | |
(objs.last_used_timestamp is null or | |
objs.last_used_timestamp < current timestamp - 1 months) and | |
(objs.change_timestamp < current timestamp - 1 months) | |
order by objs.objsize desc) | |
with data | |
on replace delete rows; | |
stop; | |
-- improve the select list | |
select '(lib,' concat | |
listagg(cast(qsys2.delimit_name(parameter_name) as clob(1m)), ', ') within group (order by | |
ordinal_position) concat ')' as column_list | |
from qsys2.sysparms c | |
where specific_schema = 'QSYS2' and | |
specific_name = (select specific_name from qsys2.sysroutine | |
where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2') and | |
parameter_mode = 'OUT'; | |
stop; | |
create or replace table Nov10objs ( | |
lib, objname, obj_size, objtype, objowner, objdefiner, objcreated, objsize, objtext, | |
objlongname, last_used_timestamp, days_used_count, last_reset_timestamp, | |
iasp_number, objattribute, objlongschema, text, sql_object_type, objlib, | |
change_timestamp, user_changed, source_file, source_library, source_member, | |
source_timestamp, created_system, created_system_version, licensed_program, | |
licensed_program_version, compiler, compiler_version, user_defined_attribute, | |
compressed, object_domain, object_audit, object_signed, system_trusted_source, | |
multiple_signatures, save_timestamp, restore_timestamp, save_while_active_timestamp, | |
save_command, save_device, save_file_name, save_file_library, save_volume, | |
save_label, save_sequence_number, last_save_size, journaled, journal_name, | |
journal_library, journal_images, omit_journal_entry, remote_journal_filter, | |
journal_start_timestamp, apply_starting_receiver, apply_starting_receiver_library) | |
as | |
(select libs.objname, objs.objname, | |
varchar_format(objs.objsize, '999G999G999G999G999G999G999G999') as obj_size, | |
objs.objtype, objs.objowner, objs.objdefiner, objs.objcreated, objs.objsize, | |
objs.objtext, objs.objlongname, objs.last_used_timestamp, | |
objs.days_used_count, objs.last_reset_timestamp, objs.iasp_number, | |
objs.objattribute, objs.objlongschema, objs.text, objs.sql_object_type, | |
objs.objlib, objs.change_timestamp, objs.user_changed, objs.source_file, | |
objs.source_library, objs.source_member, objs.source_timestamp, | |
objs.created_system, objs.created_system_version, objs.licensed_program, | |
objs.licensed_program_version, objs.compiler, objs.compiler_version, | |
objs.user_defined_attribute, objs.compressed, objs.object_domain, | |
objs.object_audit, objs.object_signed, objs.system_trusted_source, | |
objs.multiple_signatures, objs.save_timestamp, objs.restore_timestamp, | |
objs.save_while_active_timestamp, objs.save_command, objs.save_device, | |
objs.save_file_name, objs.save_file_library, objs.save_volume, | |
objs.save_label, objs.save_sequence_number, objs.last_save_size, | |
objs.journaled, objs.journal_name, objs.journal_library, | |
objs.journal_images, objs.omit_journal_entry, objs.remote_journal_filter, | |
objs.journal_start_timestamp, objs.apply_starting_receiver, | |
objs.apply_starting_receiver_library | |
from table ( | |
qsys2.object_statistics('QSYS ', '*LIB') | |
) libs, lateral ( | |
select * | |
from table ( | |
qsys2.object_statistics( | |
libs.objname, '*PGM *SRVPGM *FILE *USRSPC *DTAARA') | |
) x | |
) objs | |
where libs.objdefiner = 'DAWNM' and | |
(objs.last_used_timestamp is null or | |
objs.last_used_timestamp < current timestamp - 1 months) and | |
(objs.change_timestamp < current timestamp - 1 months) | |
order by objs.objsize desc) | |
with data | |
on replace delete rows; | |
stop; | |
-- show how to save in IFS | |
drop table Nov10objs; | |
select * from Nov10objs; | |
-- Use prompting ... /home/SCOTTF/capture dawns object info.sql | |
cl:RUNSQLSTM; | |
stop; | |
cl:RUNSQLSTM SRCSTMF('/home/SCOTTF/capture dawns object info.sql') COMMIT(*NONE) NAMING(*SQL); | |
select * from Nov10objs; | |
stop; | |
drop table Nov10objs; | |
cl: SBMJOB CMD(RUNSQLSTM SRCSTMF('/home/SCOTTF/capture dawns object info.sql') COMMIT(*NONE) NAMING(*SQL)) JOB(DAWNSPACE) INLASPGRP(*CURRENT) JOBMSGQFL(*PRTWRAP) LOG(4 0 *SECLVL); | |
stop; | |
cl: ADDJOBSCDE JOB(DAWNSPACE) CMD(RUNSQLSTM SRCSTMF('/home/SCOTTF/capture dawns object info.sql') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(23550) ; | |
stop; | |
select * | |
from qsys2.scheduled_job_info | |
where scheduled_job_name = 'DAWNSPACE' | |
order by scheduled_by; | |
select * from Nov10objs ; | |
stop; | |
select * from Nov10objs order by obj_size desc limit 10; | |
-- ====================================================================== | |
-- ====================================================================== | |
-- | |
-- The following approach can be used for any multiple member file. | |
-- | |
-- ====================================================================== | |
-- ====================================================================== | |
-- description: Find all members within the Collection Services JOB WAIT BUCKET DESCRIPTION file | |
-- | |
select table_partition as membername, a.* | |
from qsys2.syspartitionstat a | |
where tabpart is not null and table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD' | |
order by create_timestamp desc; | |
stop; | |
-- | |
-- description: Find the second most recently added member | |
-- | |
WITH ct1(member_number, member_name) AS | |
( | |
select ROW_NUMBER() OVER (ORDER BY CREATE_TIMESTAMP DESC) AS MBR_NUMBER, | |
TABLE_PARTITION AS MBRNAME | |
from qsys2.syspartitionstat A | |
where tabpart is not null and table_schema = 'QPFRDATA' and TABLE_NAME = 'QAPMJOBWTD' | |
AND TABLE_PARTITION <> TABLE_NAME | |
) | |
SELECT member_name FROM CT1 WHERE member_number = 2; | |
stop; | |
-- | |
-- description: Find the second most recently added member (improved) | |
-- | |
select table_partition as membername, a.* | |
from qsys2.syspartitionstat a | |
where tabpart is not null and table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD' | |
order by create_timestamp desc | |
limit 1 offset 1; | |
stop; | |
create or replace variable select1 clob(1M); | |
select MIN(count(*), 10) from qsys2.syspartitionstat | |
where table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD'; | |
select table_partition from qsys2.syspartitionstat | |
where table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD'; | |
stop; | |
select * from QPFRDATA.QAPMJOBWTD(Q301000002); | |
create or replace alias qtemp.tryit for QPFRDATA.QAPMJOBWTD(Q301000002); | |
select * from qtemp.tryit; | |
stop; | |
begin | |
declare query_sqlstmt clob(1G) default ''; | |
declare sqlstmt clob(1G); | |
declare lc integer default 0; | |
declare alias_count integer; | |
declare v_partition_name varchar(10); | |
declare disk_members cursor for | |
select table_partition from qsys2.syspartitionstat | |
where table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD' | |
order by create_timestamp desc | |
limit 20; | |
select MIN(count(*), 10) into alias_count from qsys2.syspartitionstat | |
where table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD'; | |
open disk_members; | |
while (lc<alias_count) do | |
set lc = lc + 1; | |
fetch from disk_members into v_partition_name; | |
set sqlstmt = 'create or replace alias DISK' concat lc concat | |
' FOR QPFRDATA.QAPMJOBWTD(' concat v_partition_name concat ')'; | |
execute immediate sqlstmt; | |
set query_sqlstmt = query_sqlstmt concat ' SELECT * FROM DISK' concat lc; | |
if (lc < alias_count) then | |
set query_sqlstmt = query_sqlstmt concat ' UNION ALL '; | |
end if; | |
end while; | |
set select1 = query_sqlstmt; | |
close disk_members; | |
end; | |
stop; | |
-- | |
-- Place the results of this query into the query below | |
-- | |
values select1; | |
select * | |
from disk1 | |
union all | |
select * | |
from disk2 | |
union all | |
select * | |
from disk3 | |
union all | |
select * | |
from disk4 | |
union all | |
select * | |
from disk5 | |
union all | |
select * | |
from disk6 | |
union all | |
select * | |
from disk7 | |
union all | |
select * | |
from disk8 | |
union all | |
select * | |
from disk9 | |
union all | |
select * | |
from disk10; | |
stop; | |
-- | |
-- description: Storage breakdown for a specific user | |
-- | |
cl: crtlib stgstudy; | |
create or replace variable stgstudy.sqltext varchar(100) default | |
('SBMJOB CMD(RUNSQL SQL(''create table stgstudy.stg' concat lpad(month(current date), 2, 0) | |
concat lpad(day(current date), 2, 0) | |
concat lpad(right(year(current date), 2), 2, 0) | |
concat ' as (select b.objlongschema, b.objname, b.objtype, | |
b.objattribute, b.objcreated, b.objsize, | |
b.objtext, b.days_used_count, b.last_used_timestamp | |
from table ( | |
qsys2.object_statistics(''''*ALLUSRAVL '''', ''''*LIB'''') | |
) as a, table ( | |
qsys2.object_statistics(a.objname, ''''ALL'''') | |
) as b | |
where b.objowner = ''''TIMMR'''' | |
order by b.objsize desc | |
fetch first 10 rows only) with data '') COMMIT(*NONE)) JOB(STGSTUDY)'); | |
values; | |
call qsys2.qcmdexc(stgstudy.sqltext); | |
select * from STGSTUDY.STG051719; | |
stop; | |
-- insert from examples | |
-- category: IBM i Services | |
-- description: PTF - Group PTF Currency | |
-- | |
-- Derive the IBM i operating system level and then | |
-- determine the level of currency of PTF Groups | |
-- | |
with ilevel (iversion, irelease) as ( | |
select os_version, | |
os_release | |
from sysibmadm.env_sys_info | |
) | |
select p.* | |
from ilevel, | |
systools.group_ptf_currency p | |
where ptf_group_release = 'R' concat iversion concat irelease concat '0' | |
order by ptf_group_level_available - ptf_group_level_installed desc; | |
stop; | |
-- category: IBM i Services | |
-- description: Spool - Output queue basic detail | |
-- | |
-- 10% better than perfect? Wow | |
-- | |
select user_name, sum(size) as total_spool_space | |
from qsys2.output_queue_entries_basic | |
where user_name in (select user_name from qsys2.user_info where user_creator <> '*IBM') | |
group by user_name | |
order by total_spool_space desc | |
limit 10; | |
stop; | |
-- Show highest levels for two system limits in the last week: | |
-- 1620:Maximum number of rows locked in a unit of work | |
-- 16201:Maximum number of row change operations in a unit of work | |
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqserviceshealth.htm | |
select user_name, job_name, last_change_timestamp as when, a.* | |
from qsys2.syslimtbl a | |
where limit_id in (1620, 16201) and last_change_timestamp > current timestamp - 1 year | |
order by current_value desc; | |
stop; | |
-- What about IFS storage? | |
select a.* | |
from qsys2.syslimits a | |
where limit_id = 18409 | |
order by current_value desc; | |
stop; | |
-- Who did it? Hmmmmm | |
select text_description, u.* from qsys2.user_info u where authorization_name = 'LDB'; | |
-- ====================================================================== | |
-- ====================================================================== | |
-- License management | |
-- ====================================================================== | |
-- ====================================================================== | |
-- | |
-- description: License Management - Expiration processing | |
-- | |
CALL systools.license_expiration_check(60); | |
-- | |
-- Review licenses that will expire within 60 days | |
-- | |
SELECT MESSAGE_TEXT, m.* | |
FROM qsys2.message_queue_info m | |
WHERE message_queue_name = 'QSYSOPR' AND | |
MESSAGE_TEXT LIKE '%EXPIRE%'; | |
-- | |
-- Review license usage violations | |
-- | |
SELECT a.* | |
FROM qsys2.license_info a | |
WHERE installed = 'YES' AND | |
PEAK_USAGE > USAGE_COUNT | |
ORDER BY peak_usage DESC; | |
stop; | |
-- ====================================================================== | |
-- ====================================================================== | |
-- Anything related to "since the last IPL" | |
-- ====================================================================== | |
-- ====================================================================== | |
-- category: DB2 for i Services | |
-- description: Index Advice - Analyzing advice since last IPL | |
-- | |
-- Examine the index advice where MTIs have been used | |
-- since the last IPL | |
-- | |
WITH last_ipl(ipl_time) | |
AS (SELECT job_entered_system_time | |
FROM TABLE(qsys2.job_info(job_status_filter => '*ACTIVE', | |
job_user_filter => 'QSYS')) x | |
WHERE job_name = '000/QSYS/SCPF') | |
SELECT mti_used, mti_created, i.* | |
FROM last_ipl, qsys2.sysixadv i | |
WHERE last_mti_used > ipl_time OR | |
last_mti_used_for_stats > ipl_time | |
order by mti_used desc; | |
stop; | |
-- ====================================================================== | |
-- ====================================================================== | |
-- History Log | |
-- ====================================================================== | |
-- ====================================================================== | |
-- | |
-- description: Study job longevity (in minutes) | |
-- | |
WITH JOB_START(start_time, from_user, sbs, from_job) AS ( | |
SELECT message_timestamp as time, | |
from_user, | |
substr(message_tokens, 59, 10) as subsystem, | |
from_job | |
FROM TABLE(qsys2.history_log_info(START_TIME => CURRENT DATE - 7 days, | |
END_TIME => CURRENT TIMESTAMP)) x | |
WHERE message_id = 'CPF1124' | |
ORDER BY ORDINAL_POSITION DESC | |
) SELECT TIMESTAMPDIFF(4, CAST(b.message_timestamp - a.start_time AS CHAR(22))) | |
AS execution_minutes, DAYNAME(b.message_timestamp) AS JOB_END_DAY, | |
a.from_user, a.from_job, a.sbs | |
FROM JOB_START A INNER JOIN | |
TABLE(qsys2.history_log_info(START_TIME => CURRENT DATE - 7 days, | |
END_TIME => CURRENT TIMESTAMP)) b | |
ON b.from_job = a.from_job | |
WHERE b.message_id = 'CPF1164' | |
ORDER BY execution_minutes desc limit 20; | |
-- ====================================================================== | |
-- ====================================================================== | |
-- Security | |
-- ====================================================================== | |
-- ====================================================================== | |
-- | |
-- description: Wait, I thought nobody was using QSECOFR | |
-- | |
-- All of this is enabled by: | |
-- CHGUSRAUD USRPRF(QSECOFR) AUDLVL(*JOBDTA) | |
-- | |
SELECT Entry_timestamp, job_number concat '/' concat | |
rtrim(job_user) concat '/' concat | |
rtrim(job_name) as job_name, | |
REMOTE_PORT, REMOTE_ADDRESS, | |
case substr(cast(entry_data as varchar(1) for bit data), 1, 1) | |
when 'A' then 'ENDJOBABN command' | |
when 'B' then 'Submit' | |
when 'C' then 'Change' | |
when 'E' then 'End' | |
when 'H' then 'Hold' | |
when 'I' then 'Disconnect' | |
when 'J' then 'The current job is attempting to interrupt another job' | |
when 'K' then 'The current job is about to be interrupted' | |
when 'L' then 'The interruption of the current job has completed' | |
when 'M' then 'Change profile or group profile' | |
when 'N' then 'ENDJOB command' | |
when 'P' then 'Attach prestart or batch immediate job' | |
when 'Q' then 'Change query attributes' | |
when 'R' then 'Release' | |
when 'S' then 'Start' | |
when 'T' then 'Change profile or group profile using a profile token' | |
when 'U' then 'CHGUSRTRC' | |
when 'V' then 'Virtual device changed by QWSACCDS API' | |
else 'unknown' | |
end as ENTRY_TYPE, | |
case substr(cast(entry_data as varchar(2) for bit data), 2, 1) | |
when 'B' then 'Batch' | |
when 'I' then 'Interactive' | |
else 'other' | |
end as Job_Type, | |
case substr(cast(entry_data as varchar(3) for bit data), 3, 1) | |
when 'J' then 'Prestart' | |
else 'other' | |
end as Job_SubType, | |
cast(entry_data as varchar(3112) for bit data) as ESD, | |
x.* | |
FROM TABLE(qsys2.display_journal( | |
JOURNAL_LIBRARY => 'QSYS', | |
JOURNAL_NAME => 'QAUDJRN', | |
STARTING_RECEIVER_NAME => '*CURAVLCHN', | |
JOURNAL_CODES => 'T', | |
journal_entry_types => 'JS' | |
)) AS x | |
where "CURRENT_USER" = 'QSECOFR' | |
ORDER BY 1; | |
stop; | |
create table user_profile_audit for system name upaudit as ( | |
SELECT entry_timestamp, "CURRENT_USER" as who_made_the_change, | |
job_number concat '/' concat rtrim(job_user) concat '/' concat rtrim(job_name) as job_name, | |
rtrim(left(object, 10)) as target_profile, | |
cast(cast(substring(entry_data,639-610+1,3) as VARCHAR(3) for bit data) as CHAR(3)) AS OPERATION, | |
cast(cast(substring(entry_data,645-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS ALLOBJ, | |
cast(cast(substring(entry_data,653-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_ALLOBJ, | |
cast(cast(substring(entry_data,646-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS JOBCTL, | |
cast(cast(substring(entry_data,654-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_JOBCTL, | |
cast(cast(substring(entry_data,647-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SAVSYS, | |
cast(cast(substring(entry_data,655-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SAVSYS, | |
cast(cast(substring(entry_data,648-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SECADM, | |
cast(cast(substring(entry_data,656-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SECADM, | |
cast(cast(substring(entry_data,649-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SPLCTL, | |
cast(cast(substring(entry_data,657-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SPLCTL, | |
cast(cast(substring(entry_data,650-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SERVICE, | |
cast(cast(substring(entry_data,658-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SERVICE, | |
cast(cast(substring(entry_data,651-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS AUDIT, | |
cast(cast(substring(entry_data,659-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_AUDIT, | |
cast(cast(substring(entry_data,652-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS IOSYSCFG, | |
cast(cast(substring(entry_data,660-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_IOSYSCFG | |
FROM TABLE(qsys2.display_journal(JOURNAL_LIBRARY => 'QSYS', | |
JOURNAL_NAME => 'QAUDJRN', | |
STARTING_RECEIVER_NAME => '*CURAVLCHN', | |
JOURNAL_ENTRY_TYPES => 'CP', | |
STARTING_TIMESTAMP => CURRENT TIMESTAMP - 1 days | |
)) AS x order by entry_timestamp desc | |
) with data; | |
select * from user_profile_audit; | |
stop; | |
-- | |
-- schduled to run once per day | |
-- | |
insert into secureit.user_profile_audit | |
-- coding guide: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzarl/rzarlf12.htm | |
with CP_ACTIVITY (when, who_made_the_change, job_name, target_profile, operation, | |
allobj, prev_allobj, jobctl, prev_jobctl, savsys, prev_savsys, secadm, prev_secadm, | |
splctl, prev_splctl, service, prev_service, audit, prev_audit, iosyscfg, prev_iosyscfg) as ( | |
SELECT entry_timestamp, "CURRENT_USER" as who_made_the_change, | |
job_number concat '/' concat rtrim(job_user) concat '/' concat rtrim(job_name) as job_name, | |
rtrim(left(object, 10)) as target_profile, | |
cast(cast(substring(entry_data,639-610+1,3) as VARCHAR(3) for bit data) as CHAR(3)) AS OPERATION, | |
cast(cast(substring(entry_data,645-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS ALLOBJ, | |
cast(cast(substring(entry_data,653-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_ALLOBJ, | |
cast(cast(substring(entry_data,646-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS JOBCTL, | |
cast(cast(substring(entry_data,654-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_JOBCTL, | |
cast(cast(substring(entry_data,647-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SAVSYS, | |
cast(cast(substring(entry_data,655-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SAVSYS, | |
cast(cast(substring(entry_data,648-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SECADM, | |
cast(cast(substring(entry_data,656-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SECADM, | |
cast(cast(substring(entry_data,649-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SPLCTL, | |
cast(cast(substring(entry_data,657-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SPLCTL, | |
cast(cast(substring(entry_data,650-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SERVICE, | |
cast(cast(substring(entry_data,658-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SERVICE, | |
cast(cast(substring(entry_data,651-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS AUDIT, | |
cast(cast(substring(entry_data,659-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_AUDIT, | |
cast(cast(substring(entry_data,652-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS IOSYSCFG, | |
cast(cast(substring(entry_data,660-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_IOSYSCFG | |
FROM TABLE(qsys2.display_journal('QSYS', 'QAUDJRN', journal_entry_types => 'CP', | |
starting_timestamp => CURRENT TIMESTAMP - 24 HOURS | |
)) AS x order by entry_timestamp desc ) | |
select when, who_made_the_change, job_name, target_profile, operation, | |
secureit.check_special('*ALLOBJ', prev_allobj, allobj) AS ALLOBJ, | |
secureit.check_special('*JOBCTL', prev_jobctl, jobctl) AS JOBCTL, | |
secureit.check_special('*SAVSYS', prev_savsys, savsys) AS SAVSYS, | |
secureit.check_special('*SECADM', prev_secadm, secadm) AS SECADM, | |
secureit.check_special('*SPLCTL', prev_splctl, splctl) AS SPLCTL, | |
secureit.check_special('*SERVICE', prev_service, service) AS SERVICE, | |
secureit.check_special('*AUDIT', prev_audit, audit) AS AUDIT, | |
secureit.check_special('*IOSYSCFG', prev_iosyscfg, iosyscfg) AS IOSYSCFG, | |
allobj, prev_allobj, jobctl, prev_jobctl, | |
savsys, prev_savsys, secadm, prev_secadm, | |
splctl, prev_splctl, service, prev_service, | |
audit, prev_audit, iosyscfg, prev_iosyscfg from CP_ACTIVITY order by when desc; | |
stop; | |
--- ===================================================================================== | |
--- ===================================================================================== | |
--- Message Queues | |
--- ===================================================================================== | |
--- ===================================================================================== | |
-- | |
-- Examine all system operator inquiry messages that have a reply | |
-- | |
SELECT a.message_text AS "INQUIRY", b.message_text AS "REPLY", B.FROM_USER, B.*, A.* | |
FROM qsys2.message_queue_info a INNER JOIN | |
qsys2.message_queue_info b | |
ON a.message_key = b.associated_message_key | |
WHERE A.MESSAGE_QUEUE_NAME = 'QSYSOPR' AND | |
A.MESSAGE_QUEUE_LIBRARY = 'QSYS' AND | |
B.MESSAGE_QUEUE_NAME = 'QSYSOPR' AND | |
B.MESSAGE_QUEUE_LIBRARY = 'QSYS' | |
ORDER BY b.message_timestamp DESC; | |
stop; | |
-- | |
-- Examine all system operator inquiry messages that have no reply | |
-- | |
WITH REPLIED_MSGS(KEY) AS ( | |
SELECT a.message_key | |
FROM qsys2.message_queue_info a INNER JOIN | |
qsys2.message_queue_info b | |
ON a.message_key = b.associated_message_key | |
WHERE A.MESSAGE_QUEUE_NAME = 'QSYSOPR' AND | |
A.MESSAGE_QUEUE_LIBRARY = 'QSYS' AND | |
B.MESSAGE_QUEUE_NAME = 'QSYSOPR' AND | |
B.MESSAGE_QUEUE_LIBRARY = 'QSYS' | |
ORDER BY b.message_timestamp DESC | |
) | |
SELECT a.message_text AS "INQUIRY", A.* | |
FROM qsys2.message_queue_info a | |
LEFT EXCEPTION JOIN REPLIED_MSGS b | |
ON a.message_key = b.key | |
WHERE MESSAGE_QUEUE_NAME = 'QSYSOPR' AND | |
MESSAGE_QUEUE_LIBRARY = 'QSYS' AND | |
message_type = 'INQUIRY' | |
ORDER BY message_timestamp DESC; | |
stop; | |
--- ===================================================================================== | |
--- ===================================================================================== | |
--- Work Management | |
--- ===================================================================================== | |
--- ===================================================================================== | |
-- | |
-- Construct a subsystem that will constrain the amount of system resources | |
-- available to users who are known to execute ad hoc queries. | |
-- | |
CL: CRTSBSD SBSD(QGPL/KIDDIESBS) POOLS((1 *BASE)) TEXT('Dangerous users SBS'); | |
CL: CRTJOBQ QGPL/KIDDIEJOBQ TEXT('Dangerous users job queue'); | |
CL: ADDJOBQE SBSD(QGPL/KIDDIESBS) JOBQ(QGPL/KIDDIEJOBQ) MAXACT(10) SEQNBR(40); | |
CL: CRTCLS CLS(QGPL/KIDDIECLS) RUNPTY(55) TIMESLICE(10) TEXT('Dangerous class'); | |
CL: ADDPJE SBSD(QGPL/KIDDIESBS) PGM(QSYS/QRWTSRVR) JOBD(QGPL/QDFTSVR) CLS(QGPL/KIDDIECLS); | |
CL: ADDPJE SBSD(QGPL/KIDDIESBS) PGM(QSYS/QZDASOINIT) JOBD(QGPL/QDFTSVR) CLS(QGPL/KIDDIECLS); | |
CL: STRSBS SBSD(QGPL/KIDDIESBS); | |
-- | |
-- Relocate TIM's server jobs to the KIDDIESBS | |
-- | |
CALL QSYS2.SET_SERVER_SBS_ROUTING('TIMMR','*ALL','KIDDIESBS'); | |
-- | |
-- Review existing configurations for users and groups | |
-- | |
SELECT * FROM QSYS2.SERVER_SBS_ROUTING; | |
stop; | |
--- ===================================================================================== | |
--- ===================================================================================== | |
--- Temporary Storage | |
--- ===================================================================================== | |
--- ===================================================================================== | |
-- | |
-- description: Find active jobs using the most temporary storage. | |
-- | |
SELECT JOB_NAME, AUTHORIZATION_NAME, TEMPORARY_STORAGE, SQL_STATEMENT_TEXT, J.* | |
FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) J | |
WHERE JOB_TYPE <> 'SYS' ORDER BY TEMPORARY_STORAGE DESC ; | |
stop; | |
-- category: IBM i Services | |
-- description: Work Management - Active Job info - Temp storage consumers, by memory pool | |
-- | |
-- Find the top 4 consumers of temporary storage, by memory pool | |
-- | |
with top_consumers (job_name, memory_pool, authorization_name, function_type, function, | |
temporary_storage, rank) as ( | |
select job_name, memory_pool, authorization_name, function_type, function, | |
temporary_storage, rank() over ( | |
partition by memory_pool | |
order by temporary_storage desc | |
) | |
from table ( | |
qsys2.active_job_info() | |
) x | |
where job_type <> 'SYS' | |
) | |
select job_name, memory_pool, authorization_name, | |
function_type concat '-' concat function as function, temporary_storage | |
from top_consumers | |
where rank in (1, 2, 3, 4) | |
order by memory_pool desc; | |
stop; | |
-- | |
-- description: Which active jobs are the top consumers of temporary storage? | |
-- | |
select bucket_current_size, bucket_peak_size, a.* | |
from qsys2.systmpstg a where job_name is null | |
order by bucket_current_size desc; | |
stop; | |
-- | |
-- WRKSYSSTS for SQL | |
-- | |
select * from qsys2.system_status_info; | |
stop; | |
-- | |
-- Go beyond what the commands can do | |
-- | |
-- Review the ASP consumption vs limit | |
-- | |
with sysval(low_limit) as ( | |
select current_numeric_value/100.0 as QSTGLOWLMT | |
from qsys2.system_value_info | |
where system_value_name = 'QSTGLOWLMT' | |
) | |
select SYSTEM_ASP_USED, | |
DEC((10.0 - low_limit),4,2) as SYSTEM_ASP_LIMIT | |
from sysval, qsys2.SYSTEM_STATUS_INFO ; | |
stop; | |
--- ===================================================================================== | |
--- ===================================================================================== | |
--- Netstat | |
--- ===================================================================================== | |
--- ===================================================================================== | |
-- | |
-- description: Find active 5250 sessions | |
-- | |
select remote_address, remote_port, authorization_name as user_name, job_name | |
from qsys2.netstat_job_info n | |
where local_port = 23 and job_type = 'INTERACTIVE'; | |
stop; | |
-- | |
-- description: Return one row for all 5250 sessions | |
-- | |
SELECT * FROM QSYS2.NETSTAT_INFO | |
WHERE LOCAL_PORT = 23 | |
AND CONNECTION_TYPE = 'IPV4'; | |
stop; | |
--- ===================================================================================== | |
--- ===================================================================================== | |
--- Disallowing Operations | |
--- ===================================================================================== | |
--- ===================================================================================== | |
-- | |
-- Plug this logic into any exit point to recognize drops, deletes, updates, etc | |
-- | |
create variable sql_statement clob(2m); | |
set sql_statement = 'DROP TABLE TOYSTORE.EMPLOYEE'; | |
select 'Please don''t delete : ' concat schema concat '/' concat name | |
as drop_table_detector | |
from table ( | |
qsys2.parse_statement(sql_statement) | |
) where sql_statement_type = 'DROP TABLE'; | |
--- ===================================================================================== | |
--- ===================================================================================== | |
--- Before you delete a library | |
--- ===================================================================================== | |
--- ===================================================================================== | |
-- Examine the library lists for every job description. | |
-- Since the library list column returns a character string containing a list of libraries, to see the individual library names it needs to be broken apart. To do this, you can create a table function that takes the library list string and returns a list of library names. | |
create or replace function systools.get_lib_names ( | |
jobd_libl varchar(2750), | |
jobd_libl_cnt int | |
) | |
returns table ( | |
libl_position int, library_name varchar(10) | |
) | |
begin | |
declare in_pos int; | |
declare lib_cnt int; | |
set in_pos = 1; | |
set lib_cnt = 1; | |
while lib_cnt <= jobd_libl_cnt do | |
pipe ( | |
lib_cnt, | |
rtrim((substr( | |
jobd_libl, | |
in_pos, | |
10 | |
))) | |
); | |
set in_pos = in_pos + 11; | |
set lib_cnt = lib_cnt + 1; | |
end while; | |
return; | |
end; | |
stop; | |
-- Use the function to interrogate the use of libraries in jobd's libl | |
select job_description, | |
job_description_library, | |
libl_position, | |
library_name | |
from qsys2.job_description_info, | |
table ( | |
systools.get_lib_names( | |
library_list, | |
library_list_count | |
) | |
) x | |
where library_name = 'QGPL'; | |
stop; | |
-- Or a simpler approach using LIKE | |
-- Note that this approach has a weakness, QGPL2 for example would be found as a match | |
select job_description_library, | |
job_description, | |
library_list | |
from qsys2.job_description_info | |
where library_list like '%QGPL%'; | |
stop; | |
select job_name | |
from qsys2.output_queue_entries_basic | |
where output_queue_library_name = 'QUSRSYS' and output_queue_name = | |
'PRT01' and user_name = session_user and spooled_file_name = | |
'QPDSPCLS' | |
order by create_timestamp desc | |
limit 1; | |
-- | |
-- UDTF... find class information | |
-- | |
create or replace function systools.class_info( ) | |
returns table (lib varchar(10) ccsid 1208, cls varchar(10) ccsid 1208, | |
clstext varchar(10) ccsid 1208, lastuse timestamp, usecount integer, | |
rpriority integer) | |
not deterministic | |
external action | |
modifies sql data | |
not fenced | |
set option commit = *none | |
begin | |
declare v_print_line char(133); | |
declare local_sqlcode integer; | |
declare local_sqlstate char(5); | |
declare v_message_text varchar(70); | |
declare v_dspcls varchar(30); | |
-- | |
-- DSPCLS detail | |
-- | |
declare v_class char(10); | |
declare v_class_library char(10); | |
declare v_class_run_priority integer; | |
declare v_library_name varchar(10); | |
-- | |
-- OBJECT_STATISTICS detail | |
-- | |
declare find_classes_query_text varchar(50); | |
declare v_cls_text char(10); | |
declare v_job_name varchar(28); | |
declare v_cls_last_use timestamp; | |
declare v_cls_use_count integer; | |
declare c_find_classes cursor for find_classes_query; | |
declare c_find_dspcls_output cursor for select job_name | |
from qsys2.output_queue_entries_basic | |
where user_name = session_user and | |
spooled_file_name = 'QPDSPCLS' | |
order by create_timestamp desc | |
limit 1; | |
declare c_dspcls_output cursor for select c1 | |
from session.splf x | |
where rrn(x) > 4 | |
order by rrn(x); | |
declare continue handler for sqlexception | |
begin | |
get diagnostics condition 1 | |
local_sqlcode = db2_returned_sqlcode, local_sqlstate = | |
returned_sqlstate; | |
set v_message_text = 'systools.class_info() failed with: ' concat | |
local_sqlcode concat ' AND ' concat local_sqlstate; | |
signal sqlstate 'QPC01' set message_text = v_message_text; | |
end; | |
declare global temporary table splf (c1 char(133)) with replace; | |
set find_classes_query_text = | |
'select libs.objname, objs.OBJNAME , rtrim(objs.OBJTEXT) , objs.LAST_USED_TIMESTAMP , objs.DAYS_USED_COUNT from table ( | |
qsys2.object_statistics(''QSYS '', ''*LIB'') | |
) libs, lateral ( select * FROM TABLE (qsys2.OBJECT_STATISTICS(libs.objname,''CLS '')) AS a ) objs'; | |
prepare find_classes_query from find_classes_query_text; | |
open c_find_classes; | |
l1: loop fetch from c_find_classes into v_library_name, | |
v_class, | |
v_cls_text, | |
v_cls_last_use, | |
v_cls_use_count; | |
get diagnostics condition 1 | |
local_sqlcode = db2_returned_sqlcode, local_sqlstate = | |
returned_sqlstate; | |
if (local_sqlstate = '0200') then | |
close c_find_classes; | |
return; | |
end if; | |
set v_dspcls = 'DSPCLS CLS(' concat rtrim(v_library_name) concat '/' concat | |
rtrim(v_class) concat ') OUTPUT(*PRINT)'; | |
call qsys2.qcmdexc(v_dspcls); | |
open c_find_dspcls_output; | |
fetch from c_find_dspcls_output into v_job_name; | |
close c_find_dspcls_output; | |
call qsys2.qcmdexc( | |
'CPYSPLF FILE(QPDSPCLS) TOFILE(QTEMP/SPLF) SPLNBR(*LAST) JOB(' concat | |
v_job_name concat ') ' | |
); | |
open c_dspcls_output; | |
fetch from c_dspcls_output into v_print_line; | |
set v_class_run_priority = int(substr(v_print_line, 56, 10)); | |
close c_dspcls_output; | |
call qsys2.qcmdexc( | |
'DLTSPLF FILE(QPDSPCLS) SPLNBR(*LAST) JOB(' concat v_job_name concat | |
') ' | |
); | |
pipe (v_library_name, v_class, v_cls_text, v_cls_last_use, v_cls_use_count, | |
v_class_run_priority);end loop; /* L1 */ | |
close c_find_classes; | |
end; | |
stop; | |
select * | |
from table ( | |
systools.class_info() | |
); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment