richardschoen

/***********************************************************************************/
/* 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,

richardschoen

/**************************************************************************************/
/* 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,

richardschoen

/*************************************************************/
/* 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;

richardschoen

/************************************************************************************************************/
/* 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

richardschoen

/******************************************************************************/
/* 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,

richardschoen

/*************************************************************************************************/
/* 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 SYSTOOLS.DELETE_OLD_RECEIVERS (

richardschoen

CREATE OR REPLACE FUNCTION SYSTOOLS.FIND_OPEN_COMMIT_CYCLES(
            JOURNAL_NAME VARCHAR(10) DEFAULT '*ALL',
            JOURNAL_LIBRARY VARCHAR(10) DEFAULT '*ALL',
            IASP_NUMBER INT DEFAULT 0
        ) 
    RETURNS TABLE (
        JOURNAL_RECEIVER_LIBRARY VARCHAR(10),
        JOURNAL_RECEIVER_NAME VARCHAR(10),
        JOURNAL_LIBRARY VARCHAR(10),
        JOURNAL_NAME VARCHAR(10),

richardschoen

/*************************************************************************************************/
/* The goal of this SQL script is to demonstrate various features of SQL PL. This will include   */
/* loops, error handling, parameters, cursor management, returning of result sets, and more.     */
/* The topic of this script will revolve around the management (deletion) of journal receivers.  */
/*                                                                                               */
/* Created for Common POWERUp 2024                                                               */
/* Session: Getting Started with SQL Functions and Procedures                                    */
/* Author: Ryan Moeller (rmoeller@ibm.com)                                                       */
/* Date: May 2024                                                                                */
/*************************************************************************************************/           

richardschoen

/*************************************************************/
/* 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;

richardschoen

--
-- Find database tables that contain deleted rows, return the largest potential storage savings first.
--
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,
               CASE