Getting Started with SQL Functions and Procedures

GettingStartedWithFunctions.sql

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

CREATE SCHEMA GETSTARTED;
SET SCHEMA GETSTARTED;

/*************************************************************************************************/
/* First, let's use the JOURNAL_RECEIVER_INFO service to see what data is available.             */
/*************************************************************************************************/

SELECT *
    FROM QSYS2.JOURNAL_RECEIVER_INFO
    WHERE DETACH_TIMESTAMP < CURRENT DATE - 3 DAYS AND JOURNAL_RECEIVER_LIBRARY = 'GETSTARTED';

stop;
/*************************************************************************************************/
/* Now that we know what data is available, let's make a basic User-Defined Function (UDF) that  */
/* calculates the size of all journal receivers in a given library.                              */
/*************************************************************************************************/

-- Create function that just returns the results of a simple SQL query
CREATE OR REPLACE FUNCTION GETSTARTED.CALC_JOURNAL_RECEIVER_SIZE (
            LIBRARY VARCHAR(10)
    )
    RETURNS INTEGER
    READS SQL DATA
    LANGUAGE SQL
    BEGIN
        RETURN (SELECT SUM(SIZE) AS TOTAL_SIZE_BYTES
                    FROM QSYS2.JOURNAL_RECEIVER_INFO
                    WHERE JOURNAL_RECEIVER_LIBRARY = LIBRARY);
    END;

-- Let's run our function!
VALUES GETSTARTED.CALC_JOURNAL_RECEIVER_SIZE(:libname);


-- Let's change the query to return 0 instead of NULL if no receivers are found!
-- Also, let's specify a DEFAULT on the LIBRARY parameter to the user's library.
CREATE OR REPLACE FUNCTION GETSTARTED.CALC_JOURNAL_RECEIVER_SIZE (
            LIBRARY VARCHAR(10) DEFAULT CURRENT_USER
    )
    RETURNS INTEGER
    READS SQL DATA
    LANGUAGE SQL
    BEGIN
        DECLARE RECEIVER_SIZE INT DEFAULT 0;
    
        SELECT SUM(SIZE) INTO RECEIVER_SIZE
                    FROM QSYS2.JOURNAL_RECEIVER_INFO
                    WHERE JOURNAL_RECEIVER_LIBRARY = LIBRARY;
    
        IF RECEIVER_SIZE IS NULL THEN
            RETURN 0;
        ELSE
            RETURN RECEIVER_SIZE;
        END IF;
    END;
    
-- Let's run our function with the default...
VALUES GETSTARTED.CALC_JOURNAL_RECEIVER_SIZE();

-- And now with the parameter specified with its name...
VALUES GETSTARTED.CALC_JOURNAL_RECEIVER_SIZE(LIBRARY => :libname);

-- We can combine our function with JOURNAL_RECEIVER_INFO to produce a report that shows the journal receiver size for all libraries!
SELECT DISTINCT JOURNAL_RECEIVER_LIBRARY,
                GETSTARTED.CALC_JOURNAL_RECEIVER_SIZE(LIBRARY => JOURNAL_RECEIVER_LIBRARY)
    FROM QSYS2.JOURNAL_RECEIVER_INFO
    LIMIT 5;

stop;
/*************************************************************************************************/
/* Let's take what we've learned from making UDFs, and let's apply it to creating a User-Defined */
/* Table Function (UDTF). This can return a table instead of a single value, which can create    */
/* some very powerful tools.                                                                     */
/*************************************************************************************************/

-- Note the RETURNS TABLE part of the function declaration...
CREATE OR REPLACE FUNCTION GETSTARTED.CALC_JOURNAL_RECEIVER_SIZE (
            LIBRARY VARCHAR(10) DEFAULT CURRENT_USER
    )
    RETURNS TABLE (
        LIBRARY_NAME VARCHAR(10),
        NUMBER_JOURNAL_ENTRIES INTEGER,
        SIZE INTEGER,
        OLDEST_RECEIVER TIMESTAMP
    )
    READS SQL DATA
    LANGUAGE SQL
    BEGIN
        RETURN SELECT JOURNAL_RECEIVER_LIBRARY, SUM(NUMBER_OF_JOURNAL_ENTRIES), SUM(SIZE), MIN(ATTACH_TIMESTAMP)
                    FROM QSYS2.JOURNAL_RECEIVER_INFO
                    WHERE JOURNAL_RECEIVER_LIBRARY = LIBRARY
                    GROUP BY JOURNAL_RECEIVER_LIBRARY
                    LIMIT 5;
    END;

-- Because we've now made a table function, we can't use the function in the SELECT list. Instead, a UDTF acts
-- like a source of data -- we SELECT from the function itself. To do this, we wrap the function call in a 
-- TABLE() statement.
SELECT * FROM TABLE(GETSTARTED.CALC_JOURNAL_RECEIVER_SIZE('COOLSTUFF'));

stop;
/*************************************************************************************************/
/* Now that we have a basic UDTF working, let's enhance the UDTF to take action based on a       */
/* parameter that's passed into the function.                                                    */
/*************************************************************************************************/

CREATE OR REPLACE FUNCTION GETSTARTED.DELETE_OLD_RECEIVERS (
            DAYS_OLD INT,
            RECEIVER_LIBRARY VARCHAR(10),
            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
    )
    MODIFIES SQL DATA
    LANGUAGE SQL
    BEGIN
        FOR JRN_CURSOR CURSOR FOR SELECT X.*
                                FROM QSYS2.JOURNAL_RECEIVER_INFO X
                                WHERE DETACH_TIMESTAMP < CURRENT TIMESTAMP - DAYS_OLD DAYS 
                                    AND JOURNAL_RECEIVER_LIBRARY = RECEIVER_LIBRARY
                                ORDER BY DETACH_TIMESTAMP ASC, JOURNAL_RECEIVER_NAME ASC  
        DO
          IF PREVIEW = 'NO' THEN
             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
-- DAYS_OLD           => Journals with a detach timestamp older than DAYS_OLD days ago will be deleted
SELECT * FROM TABLE (GETSTARTED.DELETE_OLD_RECEIVERS(DAYS_OLD => 1,
                                                   RECEIVER_LIBRARY => 'GETSTARTED',
                                                   PREVIEW => 'YES'));
    

-- How do we actually delete the receivers? With the DLTJRNRCV CL command, of course. 
CREATE OR REPLACE FUNCTION GETSTARTED.DELETE_OLD_RECEIVERS (
            DAYS_OLD INT,
            RECEIVER_LIBRARY VARCHAR(10),
            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
    )
    MODIFIES SQL DATA
    LANGUAGE SQL
    BEGIN
        FOR JRN_CURSOR CURSOR FOR SELECT X.*
                                FROM QSYS2.JOURNAL_RECEIVER_INFO X
                                WHERE DETACH_TIMESTAMP < CURRENT TIMESTAMP - DAYS_OLD DAYS 
                                    AND JOURNAL_RECEIVER_LIBRARY = RECEIVER_LIBRARY
                                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;
    
    
    
-- We should probably handle errors if DLTJRNRCV doesn't work. Let's add an error handler. 
CREATE OR REPLACE FUNCTION GETSTARTED.DELETE_OLD_RECEIVERS (
            DAYS_OLD INT,
            RECEIVER_LIBRARY VARCHAR(10),
            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
    )
    MODIFIES SQL DATA
    LANGUAGE SQL
    BEGIN
        DECLARE ERROR_HIT INTEGER DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_HIT = 1; -- Declare CONTINUE HANDLER when an exception is caught
    
        FOR JRN_CURSOR CURSOR FOR SELECT X.*
                                FROM QSYS2.JOURNAL_RECEIVER_INFO X
                                WHERE DETACH_TIMESTAMP < CURRENT TIMESTAMP - DAYS_OLD DAYS 
                                    AND JOURNAL_RECEIVER_LIBRARY = RECEIVER_LIBRARY
                                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 '12345)'); -- Introduce an error

             IF ERROR_HIT = 0 THEN
                PIPE ('YES', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
             ELSE -- If we hit an error, return 'ERR' for the action taken!
                PIPE ('ERR', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
                SET ERROR_HIT = 0;
             END IF;
          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; 
    
SELECT * FROM TABLE (GETSTARTED.DELETE_OLD_RECEIVERS(DAYS_OLD => 1,
                                                     RECEIVER_LIBRARY => 'COOLSTUFF',
                                                     PREVIEW => 'NO'));
    
    
    
    
-- Final step: Let's add some more useful default parameters, and let's spice up the query to handle those default parameters.
CREATE OR REPLACE FUNCTION GETSTARTED.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 GETSTARTED.DLTOLDJRCV
    MODIFIES SQL DATA
    LANGUAGE SQL
    BEGIN
        DECLARE ERROR_HIT INTEGER DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_HIT = 1; -- Declare CONTINUE HANDLER when an exception is caught
    
        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 ')');

             IF ERROR_HIT = 0 THEN
                PIPE ('YES', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
             ELSE -- If we hit an error, return 'ERR' for the action taken!
                PIPE ('ERR', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
                SET ERROR_HIT = 0;
             END IF;
          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 (GETSTARTED.DELETE_OLD_RECEIVERS(DAYS_OLD => 10,
                                                   RECEIVER_LIBRARY => 'COOLSTUFF',
                                                   RECEIVER_NAME => 'QSQ%', -- Find all journal names starting with QSQ
                                                   DELETE_UNSAVED => 'NO',
                                                   PREVIEW => 'YES'));