SQL utility admin sample
- Dettagli
- Categoria: Faq AS400
- Pubblicato Giovedì, 17 Gennaio 2019 14:30
- Scritto da Super User
- Visite: 4873
--- =====================================================================================
--- =====================================================================================
--- Security
--- =====================================================================================
--- =====================================================================================
--
-- description: Security - DISPLAY_JOURNAL() of the audit journal
--
-- Use Display_Journal() to examine the Change Profile (CP) entries that
-- have occurred over the last 24 hours.
--
SELECT journal_code, journal_entry_type, object, object_type, X.*
FROM TABLE (
QSYS2.Display_Journal(JOURNAL_LIBRARY => 'QSYS',
JOURNAL_NAME => 'QAUDJRN',
STARTING_RECEIVER_NAME => '*CURAVLCHN',
JOURNAL_ENTRY_TYPES => 'CP',
STARTING_TIMESTAMP => CURRENT TIMESTAMP - 5 days
)
) AS x;
stop;
-- execute this one time
create schema secureit;
stop;
create table secureit.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;
stop;
select * from secureit.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;
--
-- description: Establish a Temporal table
-- Note: this is available at IBM i 7.3 and higher
--
ALTER TABLE secureit.user_profile_audit
ADD COLUMN row_birth TIMESTAMP(12) NOT NULL implicitly hidden GENERATED ALWAYS AS
ROW BEGIN
ADD COLUMN row_death TIMESTAMP(12) NOT NULL implicitly hidden GENERATED ALWAYS AS
ROW END
ADD COLUMN transaction_time TIMESTAMP(12) implicitly hidden GENERATED ALWAYS AS
TRANSACTION START id
ADD period system_time(row_birth, row_death);
CREATE TABLE secureit.user_profile_audit_hist LIKE secureit.user_profile_audit;
ALTER TABLE user_profile_audit ADD VERSIONING USE HISTORY TABLE user_profile_audit_hist;
--
-- From this point, you would use MERGE or INSERT/UPDATE/DELETE to update the table
-- This produces a time travel capability whereas the previous is a running log
--
stop;
--- =====================================================================================
--- =====================================================================================
--- Spool
--- =====================================================================================
--- =====================================================================================
-- category: IBM i Services
-- description: Spool - Output queue basic detail
--
stop;
--
-- Find the top 10 consumers of SPOOL storage.
--
SELECT USER_NAME, SUM(SIZE) AS TOTAL_SPOOL_SPACE
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
WHERE USER_NAME NOT LIKE 'Q%'
GROUP BY USER_NAME
ORDER BY TOTAL_SPOOL_SPACE DESC LIMIT 10;
stop;
--
-- Boy... Tim is hogging spool again... surprise surprise
--
SELECT job_name, spooled_file_name, file_number
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
WHERE USER_NAME = 'TIMMR';
GROUP BY USER_NAME;
ORDER BY TOTAL_SPOOL_SPACE DESC LIMIT 10;
stop;
--
-- Lets sample the data
--
with Tims_spooled_files(job, file, file_number) AS (
SELECT job_name, spooled_file_name, file_number
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
WHERE USER_NAME = 'TIMMR'
order by size desc limit 1
)
select * from Tims_spooled_files, table(SYSTOOLS.SPOOLED_FILE_DETAIL(
JOB_NAME => job,
SPOOL_FILE_NAME => file,
SPOOL_FILE_NUMBER => file_number
));
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,
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,
END_TIME => CURRENT TIMESTAMP)) b
ON b.from_job = a.from_job
WHERE b.message_id = 'CPF1164'
ORDER BY execution_minutes desc limit 20;
stop;
--
-- Syslog options
--
select cast(syslog_event as varchar(2048) CCSID 37) RFC5424_History
from table(qsys2.history_log_info(GENERATE_SYSLOG => 'RFC5424')) h;
create or replace function scottf.doit( )
returns table (ord bigint,
RFC5424_syslog_event VARCHAR(2048) CCSID 37,
RFC3164_syslog_event VARCHAR(2048) CCSID 37
)
no external action
not fenced
return
with tbla(ord, ev) as (
SELECT ORDINAL_POSITION, cast(syslog_event as varchar(2048) CCSID 37) FROM
table(qsys2.history_log_info(GENERATE_SYSLOG => 'RFC5424')) H
ORDER BY ORDINAL_POSITION ASC
),
tblb(ord, ev) as (
SELECT ORDINAL_POSITION, varchar(syslog_event) FROM
table(qsys2.history_log_info(GENERATE_SYSLOG => 'RFC3164')) H
ORDER BY ORDINAL_POSITION ASC
)
select a.ord, a.ev, b.ev from tbla a
inner join
(select x.ord, x.ev from tblb x) as b
on a.ord = b.ord
order by ord
fetch first 10 rows only;
stop;
select * from table(scottf.doit()) x;
stop;
--- =====================================================================================
--- =====================================================================================
--- System Limits
--- =====================================================================================
--- =====================================================================================
--
-- description: Review the largest database files in System Limits
--
WITH X AS (
SELECT ROW_NUMBER() OVER (
PARTITION BY SYSTEM_SCHEMA_NAME, SYSTEM_OBJECT_NAME, SYSTEM_TABLE_MEMBER
ORDER BY CURRENT_VALUE DESC NULLS LAST
) AS R, U.*
FROM QSYS2.SYSLIMITS U
WHERE LIMIT_ID = 15000 and LAST_CHANGE_TIMESTAMP > current date - 100 days
)
SELECT
LAST_CHANGE_TIMESTAMP, SYSTEM_SCHEMA_NAME, SYSTEM_OBJECT_NAME, SYSTEM_TABLE_MEMBER,
CURRENT_VALUE
FROM X
WHERE R = 1
ORDER BY CURRENT_VALUE DESC;
stop;
--
-- description: Review the largest IFS files in System Limits
--
WITH X AS (
SELECT ROW_NUMBER() OVER (
PARTITION BY IFS_PATH_NAME
ORDER BY CURRENT_VALUE DESC NULLS LAST
) AS R, U.*
FROM QSYS2.SYSLIMITS U
WHERE LIMIT_ID = 18409 and LAST_CHANGE_TIMESTAMP > current date - 100 days
)
SELECT
LAST_CHANGE_TIMESTAMP, IFS_PATH_NAME, CURRENT_VALUE
FROM X
WHERE R = 1
ORDER BY CURRENT_VALUE DESC;
stop;
--
-- description: Largest # of objects linked in an IFS directory - System Limits
--
select LIMIT_ID, IFS_PATH_NAME, COMMENTS, CURRENT_VALUE, last_change_timestamp
FROM QSYS2.SYSLIMITS
WHERE LIMIT_ID = 18402
order by CURRENT_VALUE desc;
stop;
--
-- description: Largest # of objects linked in an IFS directory - System Limits - who did it???
-- ==========
--
select USER_NAME, JOB_NAME, LIMIT_ID, IFS_PATH_NAME, COMMENTS, CURRENT_VALUE, last_change_timestamp
FROM QSYS2.SYSLIMITS
WHERE LIMIT_ID = 18402
order by CURRENT_VALUE desc;
stop;
--- =====================================================================================
--- =====================================================================================
--- Object Statistics
--- =====================================================================================
--- =====================================================================================
--
-- description: What is our journaling setup, by object type
--
SELECT JOURNAL_LIBRARY, JOURNAL_NAME, OBJTYPE, COUNT(*)
FROM TABLE (
QSYS2.OBJECT_STATISTICS('TOYSTORE', '*ALL')
) X
GROUP BY JOURNAL_LIBRARY, JOURNAL_NAME, OBJTYPE
ORDER BY 1, 2, 3, 4 DESC;
stop;
--
-- 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;
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;
--- =====================================================================================
--- =====================================================================================
--- Systools
--- =====================================================================================
--- =====================================================================================
-- 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;
--
-- For those that like STRSQL ;-(
--
With iLevel (iVersion, iRelease) AS (
select OS_VERSION, OS_RELEASE
from sysibmadm.env_sys_info
)
SELECT
VARCHAR(GRP_CRNCY, 26) AS "GRPCUR", GRP_ID, VARCHAR(GRP_TITLE, 20) AS "NAME",
GRP_LVL, GRP_IBMLVL, GRP_LSTUPD, GRP_RLS, GRP_SYSSTS
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;
--- =====================================================================================
--- =====================================================================================
--- 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(100) SEQNBR(40);
CL: CRTCLS CLS(QGPL/KIDDIECLS) RUNPTY(55) TIMESLICE(100) 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;
-- category: IBM i Services
-- description: Work Management - Active Job Info - Lock contention
--
-- description: Find the jobs that are encountering the most lock contention
--
SELECT JOB_NAME, DATABASE_LOCK_WAITS, NON_DATABASE_LOCK_WAITS,
DATABASE_LOCK_WAITS + NON_DATABASE_LOCK_WAITS as Total_Lock_Waits, J.*
FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) J
ORDER BY 4 DESC
LIMIT 20;
stop;
-- category: IBM i Services
-- description: Work Management - Active Job Info - Long running SQL statements
--
-- description: Look for long-running SQL statements for a subset of users
--
SELECT JOB_NAME, authorization_name as "User",
TIMESTAMPDIFF(2, CAST(CURRENT TIMESTAMP - SQL_STATEMENT_START_TIMESTAMP AS CHAR(22))) AS execution_seconds,
TIMESTAMPDIFF(4, CAST(CURRENT TIMESTAMP - SQL_STATEMENT_START_TIMESTAMP AS CHAR(22))) AS execution_minutes,
TIMESTAMPDIFF(8, CAST(CURRENT TIMESTAMP - SQL_STATEMENT_START_TIMESTAMP AS CHAR(22))) AS execution_hours,
SQL_STATEMENT_TEXT, J.*
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
CURRENT_USER_LIST_FILTER => 'SCOTTF,SLROMANO,JELSBURY',
DETAILED_INFO => 'ALL')) J
WHERE SQL_STATEMENT_STATUS = 'ACTIVE'
ORDER BY 2 DESC
LIMIT 30;
stop;
--
-- 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;
--
-- description: Identify Host Server jobs currently using >10 Meg of QTEMP
--
SELECT qtemp_size, job_name,
internal_job_id, subsystem, subsystem_library_name, authorization_name, job_type,
function_type, "FUNCTION", job_status, memory_pool, run_priority, thread_count,
temporary_storage, cpu_time, total_disk_io_count, elapsed_interaction_count,
elapsed_total_response_time, elapsed_total_disk_io_count,
elapsed_async_disk_io_count, elapsed_sync_disk_io_count, elapsed_cpu_percentage,
elapsed_cpu_time, elapsed_page_fault_count, job_end_reason, server_type, elapsed_time
FROM TABLE(qsys2.active_job_info(
subsystem_list_filter => 'QUSRWRK',
job_name_filter => 'QZDASOINIT',
detailed_info => 'QTEMP'))
WHERE qtemp_size > 10;
stop;
-- category: IBM i Services
-- description: Work Management - Active Job info - SQL Server Mode study
--
-- Find active QSQSRVR jobs and the owning application job
--
WITH tt (authorization_name, job_name, cpu_time, total_disk_io_count)
AS (
select authorization_name, job_name, cpu_time, total_disk_io_count from
table(qsys2.active_job_info(
SUBSYSTEM_LIST_FILTER=>'QSYSWRK',
JOB_NAME_FILTER=>'QSQSRVR')) x
)
select authorization_name, ss.message_text, job_name, cpu_time,
total_disk_io_count
from tt, table(qsys2.joblog_info(job_name)) ss where message_id = 'CPF9898' and
from_program = 'QSQSRVR'
ORDER BY CPU_TIME 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 (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;
--
-- Examine active Host Server jobs and find the top consumers
-- Note this example requires IBM i 7.2 or higher, because it uses named parameter
-- syntax... <parameter name> => <parameter value>
--
SELECT JOB_NAME, AUTHORIZATION_NAME, ELAPSED_CPU_PERCENTAGE,ELAPSED_TOTAL_DISK_IO_COUNT, ELAPSED_PAGE_FAULT_COUNT, X.*
FROM TABLE(ACTIVE_JOB_INFO(
JOB_NAME_FILTER => 'QZDASOINIT',
SUBSYSTEM_LIST_FILTER => 'QUSRWRK')) x
ORDER BY ELAPSED_CPU_PERCENTAGE DESC
FETCH FIRST 10 ROWS ONLY;
stop;
-- one time stuff
select * from qsys2.syscatalogs;
cl: ADDRDBDIRE RDB(SQ730) RMTLOCNAME(SQ730.RCH.STGLABS.IBM.COm *IP) ;
select * from sq730.sysibm.sysdummy1;
stop;
--
-- description: compare job descriptions between production and DR or HA
-- (note change xxxxxxx to be the RDB name of the target for comparison)
--
DECLARE GLOBAL TEMPORARY TABLE SESSION . remote_job_descriptions
(job_description_library, job_description, authorization_name, job_date,
accounting_code, routing_data, request_data, library_list_count, library_list,
job_switches, text_description, job_queue_library, job_queue, job_queue_priority,
hold_on_job_queue, output_queue_library, output_queue, output_queue_priority,
spooled_file_action, printer_device, print_text, job_message_queue_maximum_size,
job_message_queue_full_action, syntax_check_severity, job_end_severity,
joblog_output, inquiry_message_reply, message_logging_level,
message_logging_severity, message_logging_text, log_cl_program_commands,
device_recovery_action, time_slice_end_pool, allow_multiple_threads,
aspgrp, ddm_conversation)
AS (SELECT job_description_library, job_description, authorization_name, job_date,
accounting_code, routing_data, request_data, library_list_count, library_list,
job_switches, text_description, job_queue_library, job_queue, job_queue_priority,
hold_on_job_queue, output_queue_library, output_queue, output_queue_priority,
spooled_file_action, printer_device, print_text, job_message_queue_maximum_size,
job_message_queue_full_action, syntax_check_severity, job_end_severity,
joblog_output, inquiry_message_reply, message_logging_level,
message_logging_severity, message_logging_text, log_cl_program_commands,
device_recovery_action, time_slice_end_pool, allow_multiple_threads,
aspgrp, ddm_conversation
FROM sq730.qsys2.job_description_info jd)
WITH DATA WITH REPLACE;
--
-- Any rows returned represent a difference
--
SELECT 'Production' AS "System Name",
A.JOB_DESCRIPTION_LIBRARY, A.JOB_DESCRIPTION, A.AUTHORIZATION_NAME, A.JOB_DATE, A.ACCOUNTING_CODE, A.ROUTING_DATA, A.REQUEST_DATA, A.LIBRARY_LIST_COUNT, A.LIBRARY_LIST, A.JOB_SWITCHES, A.TEXT_DESCRIPTION, A.JOB_QUEUE_LIBRARY, A.JOB_QUEUE, A.JOB_QUEUE_PRIORITY, A.HOLD_ON_JOB_QUEUE, A.OUTPUT_QUEUE_LIBRARY, A.OUTPUT_QUEUE, A.OUTPUT_QUEUE_PRIORITY, A.SPOOLED_FILE_ACTION, A.PRINTER_DEVICE, A.PRINT_TEXT, A.JOB_MESSAGE_QUEUE_MAXIMUM_SIZE, A.JOB_MESSAGE_QUEUE_FULL_ACTION, A.SYNTAX_CHECK_SEVERITY, A.JOB_END_SEVERITY, A.JOBLOG_OUTPUT, A.INQUIRY_MESSAGE_REPLY, A.MESSAGE_LOGGING_LEVEL, A.MESSAGE_LOGGING_SEVERITY, A.MESSAGE_LOGGING_TEXT, A.LOG_CL_PROGRAM_COMMANDS, A.DEVICE_RECOVERY_ACTION, A.TIME_SLICE_END_POOL, A.ALLOW_MULTIPLE_THREADS, A.ASPGRP, A.DDM_CONVERSATION
FROM qsys2.job_description_info A LEFT EXCEPTION JOIN SESSION.remote_job_descriptions B
ON A.JOB_DESCRIPTION_LIBRARY IS NOT DISTINCT FROM b.JOB_DESCRIPTION_LIBRARY
AND A.JOB_DESCRIPTION IS NOT DISTINCT FROM b.JOB_DESCRIPTION
AND A.AUTHORIZATION_NAME IS NOT DISTINCT FROM b.AUTHORIZATION_NAME
AND A.JOB_DATE IS NOT DISTINCT FROM b.JOB_DATE
AND A.ACCOUNTING_CODE IS NOT DISTINCT FROM b.ACCOUNTING_CODE
AND A.ROUTING_DATA IS NOT DISTINCT FROM b.ROUTING_DATA
AND A.REQUEST_DATA IS NOT DISTINCT FROM b.REQUEST_DATA
AND A.LIBRARY_LIST_COUNT IS NOT DISTINCT FROM b.LIBRARY_LIST_COUNT
AND A.LIBRARY_LIST IS NOT DISTINCT FROM b.LIBRARY_LIST
AND A.JOB_SWITCHES IS NOT DISTINCT FROM b.JOB_SWITCHES
AND A.TEXT_DESCRIPTION IS NOT DISTINCT FROM b.TEXT_DESCRIPTION
AND A.JOB_QUEUE_LIBRARY IS NOT DISTINCT FROM b.JOB_QUEUE_LIBRARY
AND A.JOB_QUEUE IS NOT DISTINCT FROM b.JOB_QUEUE
AND A.JOB_QUEUE_PRIORITY IS NOT DISTINCT FROM b.JOB_QUEUE_PRIORITY
AND A.HOLD_ON_JOB_QUEUE IS NOT DISTINCT FROM b.HOLD_ON_JOB_QUEUE
AND A.OUTPUT_QUEUE_LIBRARY IS NOT DISTINCT FROM b.OUTPUT_QUEUE_LIBRARY
AND A.OUTPUT_QUEUE IS NOT DISTINCT FROM b.OUTPUT_QUEUE
AND A.OUTPUT_QUEUE_PRIORITY IS NOT DISTINCT FROM b.OUTPUT_QUEUE_PRIORITY
AND A.SPOOLED_FILE_ACTION IS NOT DISTINCT FROM b.SPOOLED_FILE_ACTION
AND A.PRINTER_DEVICE IS NOT DISTINCT FROM b.PRINTER_DEVICE
AND A.PRINT_TEXT IS NOT DISTINCT FROM b.PRINT_TEXT
AND A.JOB_MESSAGE_QUEUE_MAXIMUM_SIZE IS NOT DISTINCT FROM b.JOB_MESSAGE_QUEUE_MAXIMUM_SIZE
AND A.JOB_MESSAGE_QUEUE_FULL_ACTION IS NOT DISTINCT FROM b.JOB_MESSAGE_QUEUE_FULL_ACTION
AND A.SYNTAX_CHECK_SEVERITY IS NOT DISTINCT FROM b.SYNTAX_CHECK_SEVERITY
AND A.JOB_END_SEVERITY IS NOT DISTINCT FROM b.JOB_END_SEVERITY
AND A.JOBLOG_OUTPUT IS NOT DISTINCT FROM b.JOBLOG_OUTPUT
AND A.INQUIRY_MESSAGE_REPLY IS NOT DISTINCT FROM b.INQUIRY_MESSAGE_REPLY
AND A.MESSAGE_LOGGING_LEVEL IS NOT DISTINCT FROM b.MESSAGE_LOGGING_LEVEL
AND A.MESSAGE_LOGGING_SEVERITY IS NOT DISTINCT FROM b.MESSAGE_LOGGING_SEVERITY
AND A.MESSAGE_LOGGING_TEXT IS NOT DISTINCT FROM b.MESSAGE_LOGGING_TEXT
AND A.LOG_CL_PROGRAM_COMMANDS IS NOT DISTINCT FROM b.LOG_CL_PROGRAM_COMMANDS
AND A.DEVICE_RECOVERY_ACTION IS NOT DISTINCT FROM b.DEVICE_RECOVERY_ACTION
AND A.TIME_SLICE_END_POOL IS NOT DISTINCT FROM b.TIME_SLICE_END_POOL
AND A.ALLOW_MULTIPLE_THREADS IS NOT DISTINCT FROM b.ALLOW_MULTIPLE_THREADS
AND A.ASPGRP IS NOT DISTINCT FROM b.ASPGRP
AND A.DDM_CONVERSATION IS NOT DISTINCT FROM b.DDM_CONVERSATION
union all
SELECT 'Failover' AS "System Name",
B.JOB_DESCRIPTION_LIBRARY, B.JOB_DESCRIPTION, B.AUTHORIZATION_NAME, B.JOB_DATE, B.ACCOUNTING_CODE, B.ROUTING_DATA, B.REQUEST_DATA, B.LIBRARY_LIST_COUNT, B.LIBRARY_LIST, B.JOB_SWITCHES, B.TEXT_DESCRIPTION, B.JOB_QUEUE_LIBRARY, B.JOB_QUEUE, B.JOB_QUEUE_PRIORITY, B.HOLD_ON_JOB_QUEUE, B.OUTPUT_QUEUE_LIBRARY, B.OUTPUT_QUEUE, B.OUTPUT_QUEUE_PRIORITY, B.SPOOLED_FILE_ACTION, B.PRINTER_DEVICE, B.PRINT_TEXT, B.JOB_MESSAGE_QUEUE_MAXIMUM_SIZE, B.JOB_MESSAGE_QUEUE_FULL_ACTION, B.SYNTAX_CHECK_SEVERITY, B.JOB_END_SEVERITY, B.JOBLOG_OUTPUT, B.INQUIRY_MESSAGE_REPLY, B.MESSAGE_LOGGING_LEVEL, B.MESSAGE_LOGGING_SEVERITY, B.MESSAGE_LOGGING_TEXT, B.LOG_CL_PROGRAM_COMMANDS, B.DEVICE_RECOVERY_ACTION, B.TIME_SLICE_END_POOL, B.ALLOW_MULTIPLE_THREADS, B.ASPGRP, B.DDM_CONVERSATION
FROM qsys2.job_description_info A RIGHT EXCEPTION JOIN SESSION.remote_job_descriptions B
ON A.JOB_DESCRIPTION_LIBRARY IS NOT DISTINCT FROM b.JOB_DESCRIPTION_LIBRARY
AND A.JOB_DESCRIPTION IS NOT DISTINCT FROM b.JOB_DESCRIPTION
AND A.AUTHORIZATION_NAME IS NOT DISTINCT FROM b.AUTHORIZATION_NAME
AND A.JOB_DATE IS NOT DISTINCT FROM b.JOB_DATE
AND A.ACCOUNTING_CODE IS NOT DISTINCT FROM b.ACCOUNTING_CODE
AND A.ROUTING_DATA IS NOT DISTINCT FROM b.ROUTING_DATA
AND A.REQUEST_DATA IS NOT DISTINCT FROM b.REQUEST_DATA
AND A.LIBRARY_LIST_COUNT IS NOT DISTINCT FROM b.LIBRARY_LIST_COUNT
AND A.LIBRARY_LIST IS NOT DISTINCT FROM b.LIBRARY_LIST
AND A.JOB_SWITCHES IS NOT DISTINCT FROM b.JOB_SWITCHES
AND A.TEXT_DESCRIPTION IS NOT DISTINCT FROM b.TEXT_DESCRIPTION
AND A.JOB_QUEUE_LIBRARY IS NOT DISTINCT FROM b.JOB_QUEUE_LIBRARY
AND A.JOB_QUEUE IS NOT DISTINCT FROM b.JOB_QUEUE
AND A.JOB_QUEUE_PRIORITY IS NOT DISTINCT FROM b.JOB_QUEUE_PRIORITY
AND A.HOLD_ON_JOB_QUEUE IS NOT DISTINCT FROM b.HOLD_ON_JOB_QUEUE
AND A.OUTPUT_QUEUE_LIBRARY IS NOT DISTINCT FROM b.OUTPUT_QUEUE_LIBRARY
AND A.OUTPUT_QUEUE IS NOT DISTINCT FROM b.OUTPUT_QUEUE
AND A.OUTPUT_QUEUE_PRIORITY IS NOT DISTINCT FROM b.OUTPUT_QUEUE_PRIORITY
AND A.SPOOLED_FILE_ACTION IS NOT DISTINCT FROM b.SPOOLED_FILE_ACTION
AND A.PRINTER_DEVICE IS NOT DISTINCT FROM b.PRINTER_DEVICE
AND A.PRINT_TEXT IS NOT DISTINCT FROM b.PRINT_TEXT
AND A.JOB_MESSAGE_QUEUE_MAXIMUM_SIZE IS NOT DISTINCT FROM b.JOB_MESSAGE_QUEUE_MAXIMUM_SIZE
AND A.JOB_MESSAGE_QUEUE_FULL_ACTION IS NOT DISTINCT FROM b.JOB_MESSAGE_QUEUE_FULL_ACTION
AND A.SYNTAX_CHECK_SEVERITY IS NOT DISTINCT FROM b.SYNTAX_CHECK_SEVERITY
AND A.JOB_END_SEVERITY IS NOT DISTINCT FROM b.JOB_END_SEVERITY
AND A.JOBLOG_OUTPUT IS NOT DISTINCT FROM b.JOBLOG_OUTPUT
AND A.INQUIRY_MESSAGE_REPLY IS NOT DISTINCT FROM b.INQUIRY_MESSAGE_REPLY
AND A.MESSAGE_LOGGING_LEVEL IS NOT DISTINCT FROM b.MESSAGE_LOGGING_LEVEL
AND A.MESSAGE_LOGGING_SEVERITY IS NOT DISTINCT FROM b.MESSAGE_LOGGING_SEVERITY
AND A.MESSAGE_LOGGING_TEXT IS NOT DISTINCT FROM b.MESSAGE_LOGGING_TEXT
AND A.LOG_CL_PROGRAM_COMMANDS IS NOT DISTINCT FROM b.LOG_CL_PROGRAM_COMMANDS
AND A.DEVICE_RECOVERY_ACTION IS NOT DISTINCT FROM b.DEVICE_RECOVERY_ACTION
AND A.TIME_SLICE_END_POOL IS NOT DISTINCT FROM b.TIME_SLICE_END_POOL
AND A.ALLOW_MULTIPLE_THREADS IS NOT DISTINCT FROM b.ALLOW_MULTIPLE_THREADS
AND A.ASPGRP IS NOT DISTINCT FROM b.ASPGRP
AND A.DDM_CONVERSATION IS NOT DISTINCT FROM b.DDM_CONVERSATION
ORDER BY JOB_DESCRIPTION_LIBRARY, JOB_DESCRIPTION;
-- category: IBM i Services
-- description: Work Management - Job Queues
--
-- Review the job queues with the most pending jobs
--
SELECT * FROM qsys2.job_queue_info
ORDER BY NUMBER_OF_JOBS DESC
LIMIT 10;
-- category: IBM i Services
-- description: Work Management - Scheduled Job Info
--
-- Example: Review the job scheduled entries which are no longer in effect, either because they
-- were explicitly held or because they were scheduled to run a single time and the date has
-- passed.
--
SELECT * FROM QSYS2.SCHEDULED_JOB_INFO WHERE STATUS IN ('HELD', 'SAVED') ORDER BY SCHEDULED_BY;
-- category: IBM i Services
-- description: Work Management - System Status
--
--
-- Review the ASP consumption vs limit
--
with sysval(low_limit) as (
select current_numeric_value/10000.0 as QSTGLOWLMT
from qsys2.system_value_info
where system_value_name = 'QSTGLOWLMT'
)
select SYSTEM_ASP_USED,
DEC((100.00 - low_limit),4,2) as SYSTEM_ASP_LIMIT
from sysval, qsys2.SYSTEM_STATUS_INFO ;
-- category: IBM i Services
-- description: Work Management - System Values
--
-- Note: replace REMOTEPART with the name of the remote partition
-- (WRKRDBDIRE or QSYS2.SYSCATALOGS)
--
-- Compare System Values across two partitions
DECLARE GLOBAL TEMPORARY TABLE SESSION.Remote_System_Values
( SYSTEM_VALUE_NAME,CURRENT_NUMERIC_VALUE,CURRENT_CHARACTER_VALUE )
AS (SELECT * FROM REMOTEPART.QSYS2.SYSTEM_VALUE_INFO) WITH DATA
WITH REPLACE;
-- Use exception join to reveal any differences
SELECT 'REMOTEPART' AS "System Name", A.SYSTEM_VALUE_NAME,
A.CURRENT_NUMERIC_VALUE,A.CURRENT_CHARACTER_VALUE FROM QSYS2.SYSTEM_VALUE_INFO A
LEFT EXCEPTION JOIN SESSION.Remote_System_Values B
ON A.SYSTEM_VALUE_NAME = B.SYSTEM_VALUE_NAME AND
A.CURRENT_NUMERIC_VALUE IS NOT DISTINCT FROM B.CURRENT_NUMERIC_VALUE AND
A.CURRENT_CHARACTER_VALUE IS NOT DISTINCT FROM B.CURRENT_CHARACTER_VALUE
UNION ALL
SELECT 'LOCALPART' AS "System Name", B.SYSTEM_VALUE_NAME,
B.CURRENT_NUMERIC_VALUE,
B.CURRENT_CHARACTER_VALUE FROM QSYS2.SYSTEM_VALUE_INFO A
RIGHT EXCEPTION JOIN SESSION.Remote_System_Values B
ON A.SYSTEM_VALUE_NAME = B.SYSTEM_VALUE_NAME AND
A.CURRENT_NUMERIC_VALUE IS NOT DISTINCT FROM B.CURRENT_NUMERIC_VALUE AND
A.CURRENT_CHARACTER_VALUE IS NOT DISTINCT FROM B.CURRENT_CHARACTER_VALUE
ORDER BY SYSTEM_VALUE_NAME;
