Skip to content

Instantly share code, notes, and snippets.

@forstie

forstie/Kitchen Sink for the Admin.sql

Created November 11, 2020 13:33
Star
Embed
What would you like to do?
Learn more about clone URLs
Download ZIP
More SQL than you want.... or so much fine SQL that you keep coming back? Time will tell.
Raw
Kitchen Sink for the Admin.sql
--
-- ======================================================================
-- 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