ryan-moeller21

-- Create schema to hold function
CREATE SCHEMA COOLSTUFF;
    
CREATE OR REPLACE PROCEDURE COOLSTUFF.AGGREGATE_REMOTE_SQL_SERVICE_DATA (
                IN REMOTE_SYSTEMS VARCHAR(2000) CCSID 37 DEFAULT '', -- List of systems to query. Each system must have a corresponding RDB directory entry.
                IN SYSTEM_LIST_TABLE_SCHEMA VARCHAR(100) CCSID 37 DEFAULT '', -- The schema name of a table containing a list of systems to query.
                IN SYSTEM_LIST_TABLE_NAME VARCHAR(100) CCSID 37 DEFAULT '', -- The long (SQL) name of a table containing a list of systems to query.
                IN SQLSERVICE_SCHEMA CHAR(10) CCSID 37, -- The database schema of the desired SQL service (typically QSYS2 or SYSTOOLS).
                IN SQLSERVICE_NAME VARCHAR(100) CCSID 37, -- The long name (not system name) of the desired SQL service.
                IN SELECT_LIST VARCHAR(2000) CCSID 37 DEFAULT '*', -- Customizable SELECT list. Selecting all fields may not work if target systems have different PTF levels o

ryan-moeller21

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

ryan-moeller21

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

ryan-moeller21

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

ryan-moeller21

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),

ryan-moeller21

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

ryan-moeller21

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

ryan-moeller21

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

ryan-moeller21

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

ryan-moeller21

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