Skip to content

Instantly share code, notes, and snippets.

@ryan-moeller21

ryan-moeller21/SystemAndStorageManagement.sql

Last active March 24, 2026 13:48
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ryan-moeller21/e376e7ddae173e512cef8fa9f8005bd4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ryan-moeller21/e376e7ddae173e512cef8fa9f8005bd4 to your computer and use it in GitHub Desktop.
Download ZIP
SQL for System and Storage Management
Raw
SystemAndStorageManagement.sql
/*************************************************************/
/* Use system limits to find users abusing IFS space */
/*************************************************************/
-- Look at all users who have triggered system limits in the last 24 hours...
SELECT *
FROM QSYS2.SYSLIMITS
WHERE LAST_CHANGE_TIMESTAMP > CURRENT TIMESTAMP - 1 DAY
AND USER_NAME != 'QSYS'
ORDER BY LAST_CHANGE_TIMESTAMP DESC;
-- SQL can help identify who has been creating these files!
SELECT USER_NAME,
COUNT(*) AS NUM_FILES,
MAX(ENTRY_TIMESTAMP) AS LATEST_UPDATE
FROM TABLE (
SYSTOOLS.AUDIT_JOURNAL_CO(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 14 DAY)
)
WHERE USER_NAME != 'QSYS'
GROUP BY USER_NAME
ORDER BY NUM_FILES DESC;
-- If a user profile has triggered a system limit, use audit journal entries to track what they are doing...
SELECT SYS.USER_NAME,
COUNT(*) AS NUM_FILES,
MAX(ENTRY_TIMESTAMP) AS LATEST_UPDATE
FROM QSYS2.SYSLIMTBL SYS
JOIN TABLE (
SYSTOOLS.AUDIT_JOURNAL_CO(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 14 DAY)
) AUDIT
ON SYS.USER_NAME = AUDIT.USER_NAME
WHERE SYS.USER_NAME NOT LIKE 'Q%'
AND LIMIT_CATEGORY = 5 -- Category 5 is "File system", see https://www.ibm.com/docs/en/i/7.5?topic=services-syslimtbl-table
GROUP BY SYS.USER_NAME
ORDER BY NUM_FILES DESC;
/***********************************************************************************/
/* Look at files taking up space in IFS */
/* */
/* Returns similar information to RTVDIRINF, but at the (relative) speed of light! */
/***********************************************************************************/
-- Get raw info about files from IFS_OBJECT_STATISTICS
WITH ALL_OBJS AS (
SELECT PATH_NAME,
OBJECT_TYPE,
DATA_SIZE AS FILESIZE,
OBJECT_OWNER
FROM TABLE (
QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/home', -- Set "root" directory for analysis
SUBTREE_DIRECTORIES => 'YES',
OMIT_LIST => '/QSYS.LIB /QFileSvr.400')
)
),
-- Get the total size of all data underneath root
TOTAL_DATA_SIZE AS (
SELECT CAST(SUM(FILESIZE) AS DECFLOAT) AS DATA_SIZE
FROM ALL_OBJS
WHERE OBJECT_TYPE != '*DIR'
),
-- Get path names to files, also calculate percent storage used (relative to total storage under root).
PATHS_AND_FILES AS (
SELECT SUBSTRING(PATH_NAME, 1, LOCATE_IN_STRING(PATH_NAME, '/', -1)) AS PATHNAME,
SUBSTRING(PATH_NAME, LOCATE_IN_STRING(PATH_NAME, '/', -1) + 1) AS FILENAME,
FILESIZE,
OBJECT_TYPE,
(CAST(FILESIZE AS DECFLOAT) / (SELECT DATA_SIZE FROM TOTAL_DATA_SIZE)) * 100 AS PERCENT_STORAGE
FROM ALL_OBJS
WHERE OBJECT_TYPE != '*DIR'
),
-- Sum file size by directory.
FILES_AND_DIRS AS (
SELECT PATHNAME,
COUNT(*) AS NUM_FILES,
CAST(CAST(SUM(FILESIZE) AS DECFLOAT) / 1000000 AS DEC(12, 2)) AS STORAGE_USED_MB,
CAST(SUM(PERCENT_STORAGE) AS DEC(5, 2)) AS PERCENT_STORAGE_USED_OF_RELATIVE_ROOT
FROM PATHS_AND_FILES
GROUP BY PATHNAME)
SELECT * FROM FILES_AND_DIRS
ORDER BY PERCENT_STORAGE_USED_OF_RELATIVE_ROOT DESC;
/**************************************************************************************/
/* Track temporary storage usage with QSYS2.SYSTEM_STATUS and QSYS2.SYSTMPSTG */
/**************************************************************************************/
-- Create schema for function and detail file creation.
CREATE SCHEMA TMPSTGMON;
-- Information as presented by Navigator
SELECT SYSTEM_ASP_STORAGE,
CURRENT_TEMPORARY_STORAGE,
SYSTEM_ASP_USED,
DECIMAL(DECFLOAT(CURRENT_TEMPORARY_STORAGE) / 1000, 10, 2) AS CURRENT_TEMPORARY_STORAGE_GB,
DECIMAL(DECFLOAT(MAXIMUM_TEMPORARY_STORAGE_USED) / 1000, 10, 2) AS MAX_TEMPORARY_STORAGE_USED_GB,
DECIMAL(DECFLOAT(CURRENT_TEMPORARY_STORAGE) * 100 / DECFLOAT(SYSTEM_ASP_STORAGE), 5, 2) AS CURRENT_TEMPORARY_PERCENTAGE_USED
FROM TABLE (QSYS2.SYSTEM_STATUS());
-- Take granular information from SYSTMPSTG and combine it with total temp storage information from SYSTEM_STATUS
WITH BUCKET_INFO AS (
SELECT GLOBAL_BUCKET_NAME,
BUCKET_CURRENT_SIZE / 1000000 AS CURRENT_TEMP_SIZE_MB,
BUCKET_PEAK_SIZE / 1000000 AS PEAK_TEMP_SIZE_MB
FROM QSYS2.SYSTMPSTG
),
TEMP_STORAGE_INFO AS (
SELECT
SYSTEM_ASP_STORAGE,
DECIMAL(DECFLOAT(CURRENT_TEMPORARY_STORAGE), 10, 2) AS CURRENT_TEMP_MB,
DECIMAL(DECFLOAT(MAXIMUM_TEMPORARY_STORAGE_USED), 10, 2) AS PEAK_TEMP_MB
FROM TABLE (QSYS2.SYSTEM_STATUS())
)
SELECT GLOBAL_BUCKET_NAME,
DECIMAL(CURRENT_TEMP_SIZE_MB, 10, 2),
DECIMAL(PEAK_TEMP_SIZE_MB, 10, 2),
DECIMAL(CURRENT_TEMP_SIZE_MB / (SELECT CURRENT_TEMP_MB FROM TEMP_STORAGE_INFO) * 100, 5, 2) AS PERCENT_OF_CURRENT_TEMP,
DECIMAL(PEAK_TEMP_SIZE_MB / (SELECT PEAK_TEMP_MB FROM TEMP_STORAGE_INFO) * 100, 5, 2) AS PERCENT_OF_PEAK_TEMP,
DECIMAL(CURRENT_TEMP_SIZE_MB / (SELECT SYSTEM_ASP_STORAGE FROM TEMP_STORAGE_INFO) * 100, 5, 2) AS PERCENT_OF_TOTAL_ASP
FROM BUCKET_INFO;
-- Create a function to handle automatic reporting!
CREATE OR REPLACE procedure TMPSTGMON.TEMP_STORAGE_NOTIFIER()
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE STMT VARCHAR(1000);
DECLARE FILENAME VARCHAR(100);
DECLARE DB_SEGMENT_WARNING INT DEFAULT 10000;
DECLARE SQE_WARNING INT DEFAULT 1;
DECLARE MTI_WARNING INT DEFAULT 10000;
DECLARE LOB_WARNING INT DEFAULT 1000;
DECLARE JOB_WARNING INT DEFAULT 100;
DECLARE DB_SEGMENT_USAGE INT;
DECLARE SQE_USAGE INT;
DECLARE MTI_USAGE INT;
DECLARE LOB_USAGE INT;
DECLARE ACTIVE_JOB_USAGE INT;
DECLARE HIT_LIMIT INT DEFAULT 0;
-- Get the current temporary storage usage information (in MB) from QSYS2.SYSTMPSTG, and save them for the calculations below.
SELECT BUCKET_CURRENT_SIZE / 1000000 INTO DB_SEGMENT_USAGE FROM QSYS2.SYSTMPSTG WHERE GLOBAL_BUCKET_NAME = '*DATABASE Segment Cache';
SELECT BUCKET_CURRENT_SIZE / 1000000 INTO SQE_USAGE FROM QSYS2.SYSTMPSTG WHERE GLOBAL_BUCKET_NAME = '*DATABASE SQE Heap';
SELECT BUCKET_CURRENT_SIZE / 1000000 INTO MTI_USAGE FROM QSYS2.SYSTMPSTG WHERE GLOBAL_BUCKET_NAME = '*DATABASE DSI SQE MTI';
SELECT SUM(BUCKET_CURRENT_SIZE) / 1000000 INTO LOB_USAGE FROM QSYS2.SYSTMPSTG WHERE GLOBAL_BUCKET_NAME IN ('*DATABASE DS SQE LOB', '*DATABASE DS SQQQ LOB');
SELECT COUNT(*) INTO ACTIVE_JOB_USAGE FROM QSYS2.SYSTMPSTG WHERE (BUCKET_CURRENT_SIZE / 1000000) > 100 AND JOB_NAME IS NOT NULL AND JOB_STATUS = '*ACTIVE';
CALL SYSTOOLS.LPRINTF('Checking for excessive temporary storage thresholds...');
--
-- If any temporary storage bucket exceeds the configured warning level, perform two actions:
-- 1.) Get more information about the temporary storage usage by calling an SQL service to provide
-- more detail about that specific bucket and save that data to a new file.
-- 2.) Build a string to print/email/send
-- Look for SQL Query Engine temp storage usage.
IF SQE_USAGE > SQE_WARNING THEN
SET HIT_LIMIT = 1;
SET FILENAME = 'TMPSTGMON.' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY') CONCAT 'SQE';
CALL SYSTOOLS.LPRINTF('SQE temp storage threshold exceeded! Threshold: ' CONCAT SQE_WARNING CONCAT 'MB. Used: ' CONCAT SQE_USAGE CONCAT 'MB. See ' CONCAT FILENAME CONCAT ' for details.');
-- Create details file
SET STMT = 'CREATE TABLE ' CONCAT FILENAME CONCAT ' AS (SELECT * FROM TABLE(QSYS2.ACTIVE_QUERY_INFO()) ORDER BY AVERAGE_TEMPORARY_STORAGE DESC LIMIT 50) WITH DATA';
PREPARE DETAIL_DATA_STMT FROM STMT;
EXECUTE DETAIL_DATA_STMT;
END IF;
-- Maintained Temporary Index temp storage usage
IF MTI_USAGE > MTI_WARNING THEN
SET HIT_LIMIT = 1;
SET FILENAME = 'TMPSTGMON.' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY') CONCAT 'MTI';
CALL SYSTOOLS.LPRINTF('MTI temp storage threshold exceeded! Threshold: ' CONCAT MTI_WARNING CONCAT 'MB. Used: ' CONCAT MTI_USAGE CONCAT 'MB. See ' CONCAT FILENAME CONCAT 'for details.');
-- Create details file
SET STMT = 'CREATE TABLE ' CONCAT FILENAME CONCAT ' AS (SELECT * FROM TABLE(QSYS2.MTI_INFO()) ORDER BY MTI_SIZE DESC LIMIT 30) WITH DATA';
PREPARE DETAIL_DATA_STMT FROM STMT;
EXECUTE DETAIL_DATA_STMT;
END IF;
-- Individual *ACTIVE job temp storage usage
IF ACTIVE_JOB_USAGE > 0 THEN
SET HIT_LIMIT = 1;
SET FILENAME = 'TMPSTGMON.' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY') CONCAT 'AJ';
CALL SYSTOOLS.LPRINTF('Job temp storage threshold exceeded! Per-job threshold: ' CONCAT ACTIVE_JOB_USAGE CONCAT 'MB. Number of jobs exceeding limit: ' CONCAT ACTIVE_JOB_USAGE CONCAT '. See ' CONCAT FILENAME CONCAT ' for details.');
-- Create details file
SET STMT = 'CREATE TABLE ' CONCAT FILENAME CONCAT ' AS (SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) WHERE TEMPORARY_STORAGE > ' CONCAT JOB_WARNING CONCAT ') WITH DATA';
PREPARE DETAIL_DATA_STMT FROM STMT;
EXECUTE DETAIL_DATA_STMT;
END IF;
-- If any limit was hit, get all ended jobs from the previous day
IF HIT_LIMIT = 1 THEN
SET FILENAME = 'TMPSTGMON.' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY') CONCAT 'EJ';
SET STMT = 'CREATE TABLE ' CONCAT FILENAME CONCAT ' AS (
SELECT *
FROM TABLE (
SYSTOOLS.ENDED_JOB_INFO()
) ORDER BY PEAK_TEMPORARY_STORAGE DESC) WITH DATA';
PREPARE DETAIL_DATA_STMT FROM STMT;
EXECUTE DETAIL_DATA_STMT;
END IF;
RETURN;
END;
CALL TMPSTGMON.TEMP_STORAGE_NOTIFIER();
/************************************************************************************************************/
/* Check for defective PTFs, new PTF groups, and firmware updates. Email someone a spreadsheet the results! */
/************************************************************************************************************/
CREATE OR REPLACE FUNCTION SYSTOOLS.SYSTEM_CURRENCY_STATUS (
EMAIL VARCHAR(100)
)
RETURNS INT
SPECIFIC SYSTOOLS.SYSCURSTS
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
DECLARE SUCCESSFLAG INT;
DECLARE FILEPATH VARCHAR(1000);
DECLARE FILEEXT VARCHAR(5) DEFAULT '.csv';
DECLARE ABSORBFAILURE integer;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ABSORBFAILURE = 1;
SET FILEPATH = '/dailyCurrencyReports/currency' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY');
-- Create directory to hold spreadsheets
CALL QSYS2.QCMDEXC('QSH CMD(''mkdir -p /dailyCurrencyReports'')');
-- Use query to generate spreadsheet at specified IFS directory
VALUES (
SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => FILEPATH,
SPREADSHEET_QUERY => 'WITH CURRENCY(TYPE, PRODUCT_ID, TITLE, CURRENT_VERSION, AVAILABLE_VERSION) AS (
SELECT ''PTF GROUP'',
PTF_GROUP_ID,
PTF_GROUP_TITLE,
CAST(PTF_GROUP_LEVEL_INSTALLED AS VARCHAR(20)),
CAST(PTF_GROUP_LEVEL_AVAILABLE AS VARCHAR(20))
FROM SYSTOOLS.GROUP_PTF_CURRENCY
WHERE PTF_GROUP_LEVEL_INSTALLED != PTF_GROUP_LEVEL_AVAILABLE
UNION ALL
SELECT ''PTF'',
PRODUCT_ID,
''Defective PTF'',
DEFECTIVE_PTF,
FIXING_PTF
FROM SYSTOOLS.DEFECTIVE_PTF_CURRENCY
UNION ALL
SELECT ''FIRMWARE'',
FW_PRODUCT_ID,
''Firmware'',
FW_CURRENTFIXPACK,
FW_RECOMMENDED_UPDATE
FROM SYSTOOLS.FIRMWARE_CURRENCY)
SELECT * FROM CURRENCY',
SPREADSHEET_TYPE => 'csv', COLUMN_HEADINGS => 'COLUMN')) INTO SUCCESSFLAG;
IF SUCCESSFLAG = 1 THEN
VALUES (SYSTOOLS.SEND_EMAIL(
TO_EMAIL => EMAIL,
SUBJECT => 'PTF and Firmware Currency Report for ' CONCAT CURRENT SERVER,
BODY => 'See attachment!',
ATTACHMENT => FILEPATH CONCAT FILEEXT)) INTO SUCCESSFLAG;
END IF;
RETURN SUCCESSFLAG;
END;
VALUES SYSTOOLS.SYSTEM_CURRENCY_STATUS(:email);
/******************************************************************************/
/* Find security-related changes made by potentially vulnerable user profiles */
/******************************************************************************/
-- Navigator's Users Tab
SELECT
CASE GROUP_ID_NUMBER
WHEN 0 THEN 'USER'
ELSE 'GROUP'
END AS PROFILE_TYPE,
A.*
FROM QSYS2.USER_INFO A
LIMIT 100 OFFSET 0;
-- Find users who haven't changed their password in a while...
SELECT AUTHORIZATION_NAME,
PREVIOUS_SIGNON,
PASSWORD_CHANGE_DATE,
TEXT_DESCRIPTION
FROM QSYS2.USER_INFO
WHERE GROUP_ID_NUMBER = 0
AND PREVIOUS_SIGNON IS NOT NULL
AND PASSWORD_CHANGE_DATE < CURRENT_TIMESTAMP - 3 YEAR
AND STATUS = '*ENABLED'
ORDER BY PASSWORD_CHANGE_DATE ASC;
-- Find delete operations performed by potentially-vulnerable user profiles
WITH PASSWORD_NEGLECTORS AS (
SELECT AUTHORIZATION_NAME,
PREVIOUS_SIGNON,
PASSWORD_CHANGE_DATE,
TEXT_DESCRIPTION
FROM QSYS2.USER_INFO A
WHERE GROUP_ID_NUMBER = 0
AND PREVIOUS_SIGNON IS NOT NULL
AND PASSWORD_CHANGE_DATE < CURRENT_TIMESTAMP - 3 YEAR
AND STATUS = '*ENABLED'
)
SELECT *
FROM PASSWORD_NEGLECTORS PG
JOIN TABLE (
SYSTOOLS.AUDIT_JOURNAL_DO(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 1 MONTHS, USER_NAME => PG.AUTHORIZATION_NAME) -- DO finds Deleted Object
) AUDIT_INFO
ON PG.AUTHORIZATION_NAME = AUDIT_INFO.USER_NAME
WHERE AUTHORIZATION_NAME NOT LIKE 'Q%'; -- Don't include system profiles
-- Take the results of the previous query, but now disable the profiles.
WITH PASSWORD_NEGLECTORS AS (
SELECT AUTHORIZATION_NAME,
PREVIOUS_SIGNON,
PASSWORD_CHANGE_DATE,
TEXT_DESCRIPTION
FROM QSYS2.USER_INFO A
WHERE GROUP_ID_NUMBER = 0
AND PREVIOUS_SIGNON IS NOT NULL
AND PASSWORD_CHANGE_DATE < CURRENT_TIMESTAMP - 3 YEAR
AND STATUS = '*ENABLED'
)
SELECT *
FROM PASSWORD_NEGLECTORS PG,
TABLE (
SYSTOOLS.AUDIT_JOURNAL_DO(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 1 MONTHS,
USER_NAME => PG.AUTHORIZATION_NAME) -- DO finds Deleted Object
) AUDIT_INFO,
TABLE (
SYSTOOLS.CHANGE_USER_PROFILE(P_USER_NAME => PG.AUTHORIZATION_NAME,
P_STATUS => '*DISABLED',
PREVIEW => 'YES')
)
WHERE PG.AUTHORIZATION_NAME = AUDIT_INFO.USER_NAME
AND AUTHORIZATION_NAME NOT LIKE 'Q%'; -- Don't include system profiles
/*************************************************************************************************/
/* Manage consumption of storage by journal receivers, only keep last N days of journals */
/*************************************************************************************************/
SELECT *
FROM QSYS2.JOURNAL_RECEIVER_INFO
WHERE DETACH_TIMESTAMP < CURRENT DATE - 3 DAYS;
-- NOTE: *IGNINQMSG is not specified for DLTJRNRCV. Any unsaved journals receivers will not be deleted.
CREATE OR REPLACE FUNCTION COOLSTUFF.DELETE_OLD_RECEIVERS (
DAYS_OLD INT,
RECEIVER_LIBRARY VARCHAR(1000) DEFAULT '*ALL',
RECEIVER_NAME VARCHAR(10) DEFAULT '*ALL',
DELETE_UNSAVED VARCHAR(3) DEFAULT 'NO',
PREVIEW VARCHAR(3) DEFAULT 'YES'
)
RETURNS TABLE (
ACTION_TAKEN VARCHAR(3),
JOURNAL_RECEIVER_LIBRARY VARCHAR(10),
JOURNAL_RECEIVER_NAME VARCHAR(10),
DESCRIPTIVE_TEXT VARCHAR(50),
JOURNAL_LIBRARY VARCHAR(10),
JOURNAL_NAME VARCHAR(10),
SIZE INTEGER,
ATTACH_TIMESTAMP TIMESTAMP,
DETACH_TIMESTAMP TIMESTAMP,
SAVE_TIMESTAMP TIMESTAMP
) SPECIFIC COOLSTUFF.DLTOLDJRCV
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
FOR JRN_CURSOR CURSOR FOR SELECT X.*
FROM QSYS2.JOURNAL_RECEIVER_INFO X, TABLE(SYSTOOLS.SPLIT(RECEIVER_LIBRARY, ',')) Y
WHERE DETACH_TIMESTAMP < CURRENT TIMESTAMP - DAYS_OLD DAYS
AND (JOURNAL_RECEIVER_LIBRARY LIKE REPLACE(RECEIVER_LIBRARY, '*ALL', '%') OR X.JOURNAL_RECEIVER_LIBRARY = TRIM(Y.ELEMENT)) -- Handle both *ALL and comma-delimited formats
AND (JOURNAL_RECEIVER_NAME LIKE REPLACE(RECEIVER_NAME, '*ALL', '%'))
AND (UPPER(DELETE_UNSAVED) = 'YES' OR (SAVE_TIMESTAMP IS NOT NULL AND UPPER(DELETE_UNSAVED) = 'NO'))
ORDER BY DETACH_TIMESTAMP ASC, JOURNAL_RECEIVER_NAME ASC
DO
IF PREVIEW = 'NO' THEN
CALL QSYS2.QCMDEXC('DLTJRNRCV JRNRCV(' CONCAT JOURNAL_RECEIVER_LIBRARY CONCAT '/' CONCAT JOURNAL_RECEIVER_NAME CONCAT ')');
PIPE ('YES', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
ELSE
PIPE ('NO', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
END IF;
END FOR;
RETURN;
END;
-- PREVIEW => 'YES' will NOT delete receivers (default)
-- 'NO' will delete receivers
-- RECEIVER_LIBRARY => '*ALL' will search all libraries on system (default)
-- Otherwise, a comma-delimited list of library names can be provided
-- RECEIVER_NAME => '*ALL' will search for all receiver names
-- Otherwise, this is handled as a LIKE predicate, meaning wildcarding can be used. See example below.
-- DELETE_UNSAVED => 'NO' will prevent unsaved receivers from being deleted (default)
-- 'YES' will delete all receivers, including unsaved ones (typically not recommended)
-- DAYS_OLD => Journals with a detach timestamp older than DAYS_OLD days ago will be deleted
SELECT * FROM TABLE (COOLSTUFF.DELETE_OLD_RECEIVERS(DAYS_OLD => 3,
RECEIVER_LIBRARY => 'QSYS',
RECEIVER_NAME => 'QSQ%', -- Find all journal names starting with QSQ
DELETE_UNSAVED => 'NO',
PREVIEW => 'YES'));
WITH DELETED_ROWS_DATA AS (
SELECT TABLE_SCHEMA,
TABLE_NAME,
NUMBER_DELETED_ROWS,
NUMBER_ROWS,
DECIMAL(NUMBER_DELETED_ROWS, 18, 3) * 100 / (NUMBER_DELETED_ROWS + NUMBER_ROWS) AS PERCENT_DELETED,
DECIMAL(NUMBER_DELETED_ROWS, 18, 3) * (SELECT ROW_LENGTH FROM QSYS2.SYSTABLES Y WHERE Y.TABLE_NAME = X.TABLE_NAME AND Y.TABLE_SCHEMA = X.TABLE_SCHEMA)/1000000 AS UNUSED_SPACE_MB
FROM QSYS2.SYSTABLESTAT X
WHERE NUMBER_ROWS > 0 AND
NUMBER_DELETED_ROWS > 0 AND
TABLE_SCHEMA NOT LIKE 'Q%')
SELECT 'RGZPFM FILE(' CONCAT TABLE_SCHEMA CONCAT '/' CONCAT TABLE_NAME CONCAT ')', DELROWS.* FROM DELETED_ROWS_DATA DELROWS
WHERE UNUSED_SP
SELECT *
FROM QSYS2.JOURNAL_RECEIVER_INFO
WHERE DETACH_TIMESTAMP < CURRENT DATE - 3 DAYS;
-- NOTE: *IGNINQMSG is not specified for DLTJRNRCV. Any unsaved journals receivers will not be deleted.
CREATE OR REPLACE FUNCTION COOLSTUFF.DELETE_OLD_RECEIVERS (
DAYS_OLD INT,
RECEIVER_LIBRARY VARCHAR(1000) DEFAULT '*ALL',
RECEIVER_NAME VARCHAR(10) DEFAULT '*ALL',
DELETE_UNSAVED VARCHAR(3) DEFAULT 'NO',
PREVIEW VARCHAR(3) DEFAULT 'YES'
)
RETURNS TABLE (
ACTION_TAKEN VARCHAR(3),
JOURNAL_RECEIVER_LIBRARY VARCHAR(10),
JOURNAL_RECEIVER_NAME VARCHAR(10),
DESCRIPTIVE_TEXT VARCHAR(50),
JOURNAL_LIBRARY VARCHAR(10),
JOURNAL_NAME VARCHAR(10),
SIZE INTEGER,
ATTACH_TIMESTAMP TIMESTAMP,
DETACH_TIMESTAMP TIMESTAMP,
SAVE_TIMESTAMP TIMESTAMP
) SPECIFIC COOLSTUFF.DLTOLDJRCV
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
FOR JRN_CURSOR CURSOR FOR SELECT X.*
FROM QSYS2.JOURNAL_RECEIVER_INFO X, TABLE(SYSTOOLS.SPLIT(RECEIVER_LIBRARY, ',')) Y
WHERE DETACH_TIMESTAMP < CURRENT TIMESTAMP - DAYS_OLD DAYS
AND (JOURNAL_RECEIVER_LIBRARY LIKE REPLACE(RECEIVER_LIBRARY, '*ALL', '%') OR X.JOURNAL_RECEIVER_LIBRARY = TRIM(Y.ELEMENT)) -- Handle both *ALL and comma-delimited formats
AND (JOURNAL_RECEIVER_NAME LIKE REPLACE(RECEIVER_NAME, '*ALL', '%'))
AND (UPPER(DELETE_UNSAVED) = 'YES' OR (SAVE_TIMESTAMP IS NOT NULL AND UPPER(DELETE_UNSAVED) = 'NO'))
ORDER BY DETACH_TIMESTAMP ASC, JOURNAL_RECEIVER_NAME ASC
DO
IF PREVIEW = 'NO' THEN
CALL QSYS2.QCMDEXC('DLTJRNRCV JRNRCV(' CONCAT JOURNAL_RECEIVER_LIBRARY CONCAT '/' CONCAT JOURNAL_RECEIVER_NAME CONCAT ')');
PIPE ('YES', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
ELSE
PIPE ('NO', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
END IF;
END FOR;
RETURN;
END;
-- PREVIEW => 'YES' will NOT delete receivers (default)
-- 'NO' will delete receivers
-- RECEIVER_LIBRARY => '*ALL' will search all libraries on system (default)
-- Otherwise, a comma-delimited list of library names can be provided
-- RECEIVER_NAME => '*ALL' will search for all receiver names
-- Otherwise, this is handled as a LIKE predicate, meaning wildcarding can be used. See example below.
-- DELETE_UNSAVED => 'NO' will prevent unsaved receivers from being deleted (default)
-- 'YES' will delete all receivers, including unsaved ones (typically not recommended)
-- DAYS_OLD => Journals with a detach timestamp older than DAYS_OLD days ago will be deleted
SELECT * FROM TABLE (COOLSTUFF.DELETE_OLD_RECEIVERS(DAYS_OLD => 3,
RECEIVER_LIBRARY => 'QSYS',
RECEIVER_NAME => 'QSQ%', -- Find all journal names starting with QSQ
DELETE_UNSAVED => 'NO',
PREVIEW => 'YES'));
/************************************************************************************/
/* Clean up storage that is being consumed by tables with many deleted rows */
/************************************************************************************/
WITH DELETED_ROWS_DATA AS (
SELECT TABLE_SCHEMA,
TABLE_NAME,
NUMBER_DELETED_ROWS,
NUMBER_ROWS,
DECIMAL(NUMBER_DELETED_ROWS, 18, 3) * 100 / (NUMBER_DELETED_ROWS + NUMBER_ROWS) AS PERCENT_DELETED,
DECIMAL(NUMBER_DELETED_ROWS, 18, 3) * (SELECT ROW_LENGTH FROM QSYS2.SYSTABLES Y WHERE Y.TABLE_NAME = X.TABLE_NAME AND Y.TABLE_SCHEMA = X.TABLE_SCHEMA)/1000000 AS UNUSED_SPACE_MB
FROM QSYS2.SYSTABLESTAT X
WHERE NUMBER_ROWS > 0 AND
NUMBER_DELETED_ROWS > 0 AND
TABLE_SCHEMA NOT LIKE 'Q%')
SELECT 'RGZPFM FILE(' CONCAT TABLE_SCHEMA CONCAT '/' CONCAT TABLE_NAME CONCAT ')', DELROWS.* FROM DELETED_ROWS_DATA DELROWS
WHERE UNUSED_SPACE_MB > 1000
ORDER BY UNUSED_SPACE_MB DESC;
CREATE OR REPLACE FUNCTION COOLSTUFF.DELETED_ROW_CLEANUP (MINIMUM_EMPTY_SPACE_MB INT, PREVIEW VARCHAR(3))
RETURNS TABLE (
ACTION_TAKEN CHAR(3),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
NUMBER_DELETED_ROWS BIGINT,
NUMBER_ROWS BIGINT,
PERCENT_DELETED DECIMAL(31, 16),
UNUSED_SPACE_MB DECIMAL(31, 5)
)
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
FOR TAB_CURSOR CURSOR FOR WITH DELETED_ROWS_DATA AS (
SELECT TABLE_SCHEMA,
TABLE_NAME,
NUMBER_DELETED_ROWS,
NUMBER_ROWS,
DECIMAL(NUMBER_DELETED_ROWS, 30, 3) / (NUMBER_DELETED_ROWS + NUMBER_ROWS) AS PERCENT_DELETED,
DECIMAL(NUMBER_DELETED_ROWS, 30, 3) * (SELECT ROW_LENGTH FROM QSYS2.SYSTABLES Y WHERE Y.TABLE_NAME = X.TABLE_NAME AND Y.TABLE_SCHEMA = X.TABLE_SCHEMA)/1000000 AS UNUSED_SPACE_MB
FROM QSYS2.SYSTABLESTAT X
WHERE NUMBER_ROWS > 0 AND
NUMBER_DELETED_ROWS > 0 AND
TABLE_SCHEMA NOT LIKE 'Q%')
SELECT *
FROM DELETED_ROWS_DATA DELROWS
WHERE UNUSED_SPACE_MB > 1000
ORDER BY UNUSED_SPACE_MB DESC
DO
IF PREVIEW = 'NO' THEN
-- 'RGZPFM FILE(' CONCAT TABLE_SCHEMA CONCAT '/' CONCAT TABLE_NAME CONCAT ')', DELROWS.
--CALL QSYS2.QCMDEXC('DLTJRNRCV JRNRCV(' CONCAT JOURNAL_RECEIVER_LIBRARY CONCAT '/' CONCAT JOURNAL_RECEIVER_NAME CONCAT ')');
PIPE ('YES', TABLE_SCHEMA, TABLE_NAME, NUMBER_DELETED_ROWS, NUMBER_ROWS, PERCENT_DELETED, UNUSED_SPACE_MB);
ELSE
PIPE ('NO', TABLE_SCHEMA, TABLE_NAME, NUMBER_DELETED_ROWS, NUMBER_ROWS, PERCENT_DELETED, UNUSED_SPACE_MB);
END IF;
END FOR;
RETURN;
RETURN;
END;
SELECT * FROM TABLE (COOLSTUFF.DELETED_ROW_CLEANUP(MINIMUM_EMPTY_SPACE_MB => 1000,
PREVIEW => 'YES'));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment