Skip to content

Instantly share code, notes, and snippets.

@Jonathan-49
Last active January 9, 2024 06:06
Show Gist options
  • Save Jonathan-49/af2cae3e2e0be0238a26ee683dac6de1 to your computer and use it in GitHub Desktop.
Save Jonathan-49/af2cae3e2e0be0238a26ee683dac6de1 to your computer and use it in GitHub Desktop.
IBM i Size Limits
--
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment