IBM i Size Limits

SizeLimits.SQL

--
-- Subject: Create a table function that returns size information, similiar
--                to the 'Size Limits' analysis in the Health Center found in IBM i Access Client Solutions.
--                However, not all size subjects are returned.
--                It returns size information per the IASP (Independent ASP)  or SYSBAS (system ASP)
--                The IASP must be in the users job namespace.
-- Author:  Jonathan Heinz
-- Date:     6th September 2022
-- OS: IBM i 7.3 / 7.4
-- Parameters: There are two parameters, p_ASP - mandatory, auxiliary storage pool (ASP) device name.
--             Must be the name of an IASP (Independent ASP) or the system ASP can be checked by using *SYSBAS, SYSBAS or SYSTEM.
--             If IASP is not in the 'AVAILABLE' state then an error is returned.
--             If the IASP is not found then an error is returned.
--             The second parameter is p_rows, top 10 largest values are returned by default per Sizing_ID (Size Type).
-- Returned columns:
--   ORDERRANK INTEGER,
--   SIZING_ID INTEGER,
--   SIZE_NAME VARCHAR(128),
--   "SCHEMA" VARCHAR(128),
--   "TABLE" VARCHAR(128),
--    ASP_TYPE VARCHAR(9),
--    RDB_NAME VARCHAR(18),
--    BYTES BIGINT,
--    DATA_SIZE VARCHAR(128),
--    PERCENTAGEOFLIMIT DECIMAL(5, 2),
--    PERCENTAGEOFTOTALCAPACITY DECIMAL(5, 2) -- Note, only populated for size id 15003 Maximum size of the data in a table partition.

-- Example uses:
-- Return size information for tables/indexes in schemas in the 'IASPEXP' IASP, return top 10 rows.
--select * from table(health.sizelimits('IASPEXP'))

-- Select a particular size id and format for use in interactive SQL session:
-- select orderrank,                                    
--       Sizing_id ,                                   
--       Size_Name ,                                   
--       Schema,                                       
--       Table ,  
--       ASP_TYPE,
--       RDB_NAME,
--       Value as bytes,                               
--       Data_size ,                                   
--       dec(Percent_of_Limit, 5, 2) as percentageoflimit,
--       dec(Percent_of_Total_Capacity, 5, 2) as percentageofTotalCapacity
--  from table(health.sizelimits('IASPEXP'))           
--  where sizing_id='15003'        

-- Return size information for tables/indexes in schemas in the SYSBAS, return top 12 rows.
--select * from table(health.sizelimits('SYSBAS', 12))

================================================================================================================================

Create schema health;
stop;

CREATE OR REPLACE FUNCTION health.sizelimits (
            p_ASP VARCHAR(10),
            p_rows INT DEFAULT 10
        )
    RETURNS TABLE (
        orderrank INT,
        Sizing_id INT,
        Size_Name VARCHAR(128),
        ASP_TYPE VARCHAR(9),
        RDB_NAME VARCHAR(18),
        Schema VARCHAR(128),
        Table VARCHAR(128),
        Table_Partition VARCHAR(128),
        Value BIGINT,
        Data_size VARCHAR(128),
        Percent_of_Limit FLOAT(29),
        Percent_of_Total_Capacity DECIMAL(63, 2)
    )
    LANGUAGE SQL
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    SET OPTION DBGVIEW = *SOURCE
    BEGIN
        DECLARE v_sizing_id INT;
        DECLARE v_ASP_STATE VARCHAR(10);
        DECLARE v_ASP_NUMBER INTEGER;
        DECLARE v_ASP_TYPE VARCHAR(9);
        DECLARE v_RDB_NAME VARCHAR(18);
        DECLARE v_TOTAL_CAPACITY BIGINT;
        DECLARE EXIT HANDLER FOR sqlexception RESIGNAL;
        DECLARE GLOBAL TEMPORARY TABLE Session.output (
                    Sizing_Id INT,
                    Size_name VARCHAR(128),
                    Schema VARCHAR(128),
                    Table VARCHAR(128),
                    Table_Partition VARCHAR(128),
                    Value BIGINT,
                    Data_Size VARCHAR(128) DEFAULT NULL,
                    Percent_of_Limit FLOAT(29),
                    Percent_of_Total_Capacity DECIMAL(63, 2) DEFAULT NULL,
                    ASP_TYPE VARCHAR(9),
                    RDB_NAME VARCHAR(18),
                    ASP_NUMBER INTEGER
                )
            WITH replace;
        SET v_ASP_STATE = ' ';
        set P_ASP = UPPER(P_ASP);
        IF P_ASP NOT IN ('*SYSBAS', 'SYSBAS', 'SYSTEM') THEN
            SELECT ASP_NUMBER, ASP_STATE, ASP_TYPE, RDB_NAME, TOTAL_CAPACITY
                INTO v_ASP_NUMBER, v_ASP_STATE, v_ASP_TYPE, v_RDB_NAME, v_TOTAL_CAPACITY
                FROM Qsys2.ASP_INFO
                WHERE DEVICE_DESCRIPTION_NAME = P_ASP;
            IF v_ASP_STATE = ' ' THEN
                SIGNAL SQLSTATE '75001'
                    SET MESSAGE_TEXT = 'IASP Name not found';
            END IF;
            IF v_ASP_STATE <> 'AVAILABLE ' THEN
                SIGNAL SQLSTATE '75002'
                    SET MESSAGE_TEXT = 'IASP not in AVAILABLE state';
            END IF;
        ELSE
            SELECT  ASP_NUMBER, TOTAL_CAPACITY, ASP_TYPE, RDB_NAME
                INTO  v_ASP_NUMBER, v_TOTAL_CAPACITY, v_ASP_TYPE, v_RDB_NAME
                FROM Qsys2.ASP_INFO
                WHERE ASP_NUMBER = 1;
        END IF;

        -- Maximum number of all rows in a partition
        SET v_sizing_id = 15000;
        INSERT INTO session.output
            WITH f1 AS (
                    SELECT v_sizing_id,
                           (SELECT comments
                                   FROM QSYS2.SQL_SIZING
                                   WHERE sizing_id = v_sizing_id),
                           table_schema,
                           table_name,
                           table_partition,
                           number_rows,
                           ' ',
                           Varchar_format((number_rows / (SELECT supported_value
                                           FROM QSYS2.SQL_SIZING
                                           WHERE sizing_id = v_sizing_id)) * 100,
                               '999D99'), 0, 
                          v_ASP_TYPE, v_RDB_NAME,
                          (SELECT iasp_number
                                   FROM TABLE (
                                           QSYS2.LIBRARY_INFO(table_schema)
                                       ))
                        FROM QSYS2.SYSPARTITIONSTAT
                )
                SELECT *
                    FROM f1
                    WHERE v_ASP_NUMBER = iASP_NUMBER
                    ORDER BY number_rows DESC
                    FETCH FIRST p_rows ROWS ONLY;
        --Maximum number of deleted rows in a partition
        SET v_sizing_id = 15002;
        INSERT INTO session.output
            WITH f1 AS (
                    SELECT v_sizing_id,
                           (SELECT comments
                                   FROM QSYS2.SQL_SIZING
                                   WHERE sizing_id = v_sizing_id),
                           table_schema,
                           table_name,
                           table_partition,
                           number_deleted_rows,
                           ' ',
                           Varchar_format((number_deleted_rows / (SELECT supported_value
                                           FROM QSYS2.SQL_SIZING
                                           WHERE sizing_id = v_sizing_id)) * 100,
                               '999D99'), 0, 
                            v_ASP_TYPE, v_RDB_NAME,
                            (SELECT iasp_number
                                   FROM TABLE (
                                           QSYS2.LIBRARY_INFO(table_schema)
                                       ))
                        FROM QSYS2.SYSPARTITIONSTAT
                )
                SELECT *
                    FROM f1
                    WHERE v_ASP_NUMBER = iASP_NUMBER
                    ORDER BY number_deleted_rows DESC
                    FETCH FIRST p_rows ROWS ONLY;

--          --Maximum size of the data in a table partition
        SET V_SIZING_ID = 15003;
        INSERT INTO SESSION/OUTPUT
            WITH f1 AS (
                    SELECT V_SIZING_ID,
                           (SELECT comments
                                   FROM QSYS2.SQL_SIZING
                                   WHERE sizing_id = v_SIZING_ID),
                           table_schema,
                           table_name,
                           table_partition,
                           data_size,
                           CASE
                               WHEN
                                   data_size > POWER(1024, 4)
                                   THEN varchar_format(data_size / POWER(1024, 4), '9999D99') CONCAT ' ' CONCAT 'TB'
                               WHEN
                                   data_size > POWER(1024, 3)
                                   THEN varchar_format(data_size / POWER(1024, 3), '9999D99') CONCAT ' ' CONCAT 'GB'
                               ELSE varchar_format(data_size / POWER(1024, 2), '9999D99') CONCAT ' ' CONCAT 'MB'
                           END,
                           Varchar_format((data_size / (SELECT supported_value
                                           FROM QSYS2.SQL_SIZING
                                           WHERE sizing_id = V_SIZING_ID)) * 100,
                               '999D99'), ((data_size/POWER(1024, 2)) /V_Total_Capacity) * 100 ,
                           v_ASP_TYPE, v_RDB_NAME,
                           (SELECT iasp_number
                                   FROM TABLE (
                                           QSYS2.LIBRARY_INFO(table_schema)
                                       ))
                        FROM QSYS2.SYSPARTITIONSTAT
                )
                SELECT *
                    FROM f1
                    WHERE v_ASP_NUMBER = iASP_NUMBER
                    ORDER BY data_size DESC
                    FETCH FIRST p_rows ROWS ONLY;
--         -- Maximum number of overflow rows in a partition
        SET v_sizing_id = 15004;
        INSERT INTO session.output
            WITH f1 AS (
                    SELECT v_sizing_id,
                           (SELECT comments
                                   FROM QSYS2.SQL_SIZING
                                   WHERE sizing_id = v_sizing_id),
                           table_schema,
                           table_name,
                           table_partition,
                           overflow,
                           ' ',
                           Varchar_format((overflow / (SELECT supported_value
                                           FROM QSYS2.SQL_SIZING
                                           WHERE sizing_id = v_sizing_id)) * 100,
                               '999D99'), 0, 
                           v_ASP_TYPE, v_RDB_NAME,
                           (SELECT iasp_number
                                   FROM TABLE (
                                           QSYS2.LIBRARY_INFO(table_schema)
                                       ))
                        FROM QSYS2.SYSPARTITIONSTAT
                )
                SELECT *
                    FROM f1
                    WHERE v_ASP_NUMBER = iASP_NUMBER
                    ORDER BY overflow DESC
                    FETCH FIRST p_rows ROWS ONLY;
--
--                     -- Maximum size of a *MAX4GB index
                    SET v_sizing_id = 15400;
                    INSERT INTO session.output
                    WITH f1 AS (
                    SELECT v_sizing_id,
                           (SELECT comments
                                   FROM QSYS2.SQL_SIZING
                                   WHERE sizing_id = v_sizing_id),
                           index_schema,
                           index_name,
                           index_partition,
                           index_size,
                           ' ',
                           Varchar_format((index_size / (SELECT supported_value
                                           FROM QSYS2.SQL_SIZING
                                           WHERE sizing_id = v_sizing_id)) * 100,
                               '999D99'), 0, 
                            v_ASP_TYPE, v_RDB_NAME,
                            (SELECT iasp_number
                                   FROM TABLE (
                                           QSYS2.LIBRARY_INFO(table_schema)
                                       ))
                        FROM QSYS2.SYSPARTITIONINDEXES where ACCPTH_TYPE='4 GB'
                )
                SELECT *
                    FROM f1
                    WHERE v_ASP_NUMBER = iASP_NUMBER
                    ORDER BY index_size DESC
                    FETCH FIRST p_rows ROWS ONLY;
--
--                      --Maximum size of a *MAX1TB index
                     SET v_sizing_id = 15401;
                    INSERT INTO session.output
                    WITH f1 AS (
                    SELECT v_sizing_id,
                           (SELECT comments
                                   FROM QSYS2.SQL_SIZING
                                   WHERE sizing_id = v_sizing_id),
                           index_schema,
                           index_name,
                           index_partition,
                           index_size,
                           ' ',
                           Varchar_format((index_size / (SELECT supported_value
                                           FROM QSYS2.SQL_SIZING
                                           WHERE sizing_id = v_sizing_id)) * 100,
                               '999D99'), 0,
                            v_ASP_TYPE, v_RDB_NAME,
                            (SELECT iasp_number
                                   FROM TABLE (
                                           QSYS2.LIBRARY_INFO(table_schema)
                                       ))
                        FROM QSYS2.SYSPARTITIONINDEXES where ACCPTH_TYPE='1 TB'
                )
                SELECT *
                    FROM f1
                    WHERE v_ASP_NUMBER = iASP_NUMBER
                    ORDER BY index_size DESC
                    FETCH FIRST p_rows ROWS ONLY;
        RETURN SELECT RANK() OVER (
                          PARTITION BY o.sizing_id
                          ORDER BY o.value DESC
                      ) AS orderrank,
                      o.sizing_id,
                      o.size_name,
                      o.ASP_TYPE,
                      o.RDB_NAME,
                      o.schema,
                      o.table,
                      o.table_partition,
                      o.value,
                      o.data_size,
                      o.Percent_of_Limit,
                      case when o.sizing_id<>15003 then NULL else o.Percent_of_Total_Capacity end
                FROM session.output O
                WHERE value <> 0
                ORDER BY o.sizing_id,
                         o.value DESC;
    END;
    
    stop;