The Collection Services (CS) config and CS data hold a goldmine of operational insight about the IBM i. This Gist shows how a little bit of SQL can open the door to gaining insight and value from this data.

Collection Services made easy with SQL.sql

--
-- =======================================================================
-- Subject: See Collection Services (CS) config and query CS data with SQL
-- =======================================================================
--
-- Author: Scott Forstie
-- Date  : September, 2023
-- Email : [email protected]
--
-- This Gist is a two-fer:
-- 1) See how SQL can be used to understand how Collection Services (CS) is configured
-- 2) Use SQL PL and more to construct a UDTF to make it easy to dashboard or analyze CS data
--
-- Documentation:
-- https://www.ibm.com/docs/en/i/7.5?topic=services-collection-info-view
--
-- Features Used : This Gist uses SQL PL, QSYS2.COLLECTION_SERVICES_INFO, JSON_TABLE, qsys2.syspartitionstat, SQL Aliases, SQL UDTFs, PIPE, Collection Services data
--
-- Note: While this example focuses on QAPMSQLPC CS data, the Gist is a template that can be reused with other CS buckets.
--
stop;

--
-- Review the Collection Services (CS) configuration
--
select ACTIVE_COLLECTION_LIBRARY, ACTIVE_COLLECTION_NAME, CYCLE_INTERVAL, RETENTION_DAYS,
       CATEGORY_LIST, cs.*
  from QSYS2.COLLECTION_SERVICES_INFO cs;
stop;

--
-- Shred the CS categories and interval settings
--
select a.*
  from QSYS2.COLLECTION_SERVICES_INFO, lateral (select * from JSON_TABLE(CATEGORY_LIST, 'lax $.category_list[*]' 
  columns(cs_category clob(1k) ccsid 1208 path 'lax $."category"', 
          cs_interval clob(1k) ccsid 1208 path 'lax $."interval"'))) a;
stop;

-- description: Find all members within the Collection Services (SQL PLAN CACHE PERFORMANCE DATA) file
-- The Q collection is the "standard" collection
-- The R collection contains only a subset of data at a more frequent interval - intended for use by System Monitors
--
select table_partition as membername, a.*
  from qsys2.collection_services_info c, qsys2.syspartitionstat a
  where table_schema = c.active_collection_library and
        table_name = 'QAPMSQLPC'
  order by create_timestamp desc;
stop;

--
-- description: Find the member name containing yesterday's SQL PLAN CACHE PERFORMANCE DATA 
-- This example uses the row_number OLAP feature.
--
with ct1 (member_number, member_name) as (
    select row_number() over (
             order by CREATE_TIMESTAMP desc
           ) as MBR_NUMBER, TABLE_PARTITION as MBRNAME
      from qsys2.collection_services_info c, qsys2.syspartitionstat A
      where table_schema = c.active_collection_library and
            TABLE_NAME = 'QAPMSQLPC' and TABLE_PARTITION like 'Q%'
  )
  select member_name
    from CT1
    where member_number = 2;
stop;

--
-- Study the CS data
--
create or replace alias qtemp.cs_data for QPFRDATA.QAPMSQLPC(Q252000002);
select * from qtemp.cs_data;

stop;

--
-- Finding the second most recently added member (yesterday's data)
-- This example uses LIMIT and OFFSET.
--
select table_partition as membername, a.*
  from qsys2.collection_services_info c, qsys2.syspartitionstat a
  where table_schema = c.active_collection_library and
        TABLE_NAME = 'QAPMSQLPC' and
        TABLE_PARTITION like 'Q%'
  order by create_timestamp desc
  limit 1 offset 1;
stop;

--
-- coolstuff.cs_sqe_pc_info:
--  Returns Collection Services (CS) - SQL PLAN CACHE PERFORMANCE DATA 
--  in a form that can be easily queried and understood
--
-- The following function:
--   1) Determines where Collection Services (CS) is being captured
--   2) Find all the member names for SQL PLAN CACHE PERFORMANCE DATA 
--   3) Builds SQL Aliases in QTEMP for each member
--   4) Build an SQL query to union together the data for all aliases
--   5) Prepares and opens the query
--   6) Returns the results, row by row, using PIPE
--
create or replace function coolstuff.cs_sqe_pc_info ()
  returns table (
    Interval_number decimal(5, 0), Interval_time timestamp(0), current_MTI_count bigint,
    Elapsed_interval_seconds decimal(7, 0), Active_queries bigint, Plans_in_plan_cache bigint,
    Plan_cache_size bigint, Plan_cache_size_limit bigint, Plan_cache_size_threshold bigint,
    MTIs_created bigint, MTIs_deleted bigint, Total_MTI_Size bigint,
    Mapping_errors_detected_by_SQE bigint
  )
  specific coolstuff.cs_SQEinfo
  modifies sql data
  begin
    declare CS_data_stmt_text clob(2m) default '';
    declare sqlstmt clob(2m);
    declare lc integer default 0;
    declare alias_count integer;
    declare v_partition_name varchar(10);
    declare not_found int default 0;
    declare at_end int default 0;
    declare close_time int default 0;
    declare v_failure_text varchar(2000) for sbcs data;
    declare v_Interval_number decimal(5, 0);
    declare v_Interval_time timestamp(0);
    declare v_current_MTI_count bigint;
    declare v_Elapsed_interval_seconds decimal(7, 0);
    declare v_Active_queries bigint;
    declare v_Plans_in_plan_cache bigint;
    declare v_Plan_cache_size bigint;
    declare v_Plan_cache_size_limit bigint;
    declare v_Plan_cache_size_threshold bigint;
    declare v_MTIs_created bigint;
    declare v_MTIs_deleted bigint;
    declare v_Total_MTI_Size bigint;
    declare v_Mapping_errors_detected_by_SQE bigint;
    declare CS_data cursor for CS_data_stmt;
    declare CS_members cursor for
      select table_partition
        from qsys2.collection_services_info c, qsys2.syspartitionstat a
        where table_schema = c.active_collection_library and
              TABLE_NAME = 'QAPMSQLPC' and
              TABLE_PARTITION like 'Q%'
        order by create_timestamp desc
        limit 20;
    declare exit handler for sqlexception
    begin
      declare local_sqlcode integer;
      declare local_sqlstate char(5) for sbcs data;
      declare v_message_text varchar(200) for sbcs data;
      get diagnostics condition 1
          local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate,
          v_message_text = message_text;
      call systools.lprintf('coolstuff.cs_sqe_pc_info() FAILED WITH SQLCODE=' concat local_sqlcode
          concat ' SQLSTATE=' concat local_sqlstate concat ' MESSAGE= ' concat v_message_text);
      return;
    end;
    declare continue handler for not found set at_end = 1;
    select min(count(*), 100)
      into alias_count
      from qsys2.collection_services_info c, qsys2.syspartitionstat a
      where table_schema = c.active_collection_library and
            TABLE_NAME = 'QAPMSQLPC' and
            TABLE_PARTITION like 'Q%';
    open CS_members;
    while (lc < alias_count) do
      set lc = lc + 1;
      fetch from CS_members
        into v_partition_name;
      set sqlstmt = 'create or replace alias qtemp.MBR' concat lc concat ' FOR QPFRDATA.QAPMSQLPC('
            concat v_partition_name concat ')';
      execute immediate sqlstmt;
      set CS_data_stmt_text = CS_data_stmt_text concat
        ' SELECT INTNUM, DATETIME, SQCURMTI, INTSEC, SQCURQRY, SQCURPLN, SQPCSIZE, SQPCLIMIT, SQPCTHRESH, SQMTICRT, SQMTIDLT, SQPCFLD01, SQPCFLD03 FROM QTEMP.MBR' concat lc;
      if (lc < alias_count) then
        set CS_data_stmt_text = CS_data_stmt_text concat ' UNION ALL ';
      end if; 
    end while;
    close CS_members; 
    prepare CS_data_stmt from CS_data_stmt_text;
    open CS_data;
    set at_end = 0;
    fetch from CS_data
      into v_Interval_number, v_Interval_time, v_current_MTI_count, v_Elapsed_interval_seconds,
           v_Active_queries, v_Plans_in_plan_cache, v_Plan_cache_size, v_Plan_cache_size_limit,
           v_Plan_cache_size_threshold, v_MTIs_created, v_MTIs_deleted, v_Total_MTI_Size,
           v_Mapping_errors_detected_by_SQE;
    while (at_end = 0) do
      pipe (
        v_Interval_number,
        v_Interval_time, v_current_MTI_count, v_Elapsed_interval_seconds, v_Active_queries,
        v_Plans_in_plan_cache, v_Plan_cache_size, v_Plan_cache_size_limit,
        v_Plan_cache_size_threshold, v_MTIs_created, v_MTIs_deleted, v_Total_MTI_Size,
        v_Mapping_errors_detected_by_SQE);
      fetch from CS_data
        into v_Interval_number, v_Interval_time, v_current_MTI_count, v_Elapsed_interval_seconds,
             v_Active_queries, v_Plans_in_plan_cache, v_Plan_cache_size, v_Plan_cache_size_limit,
             v_Plan_cache_size_threshold, v_MTIs_created, v_MTIs_deleted, v_Total_MTI_Size,
             v_Mapping_errors_detected_by_SQE;
    end while;
    close CS_data;
    return;
  end;
stop;

--
-- Query the Collection Services (CS) - SQL PLAN CACHE PERFORMANCE DATA  
--
select *
  from table (
      coolstuff.cs_sqe_pc_info()
    );
stop;

--
-- Query the Collection Services (CS) - SQL PLAN CACHE PERFORMANCE DATA  
--
select monthname(INTERVAL_TIME) as month, dayname(INTERVAL_TIME) as day, pc.*
  from table (
      coolstuff.cs_sqe_pc_info()
    ) pc;
stop;

--
-- Least and Most number of MTIs
--
select min(CURRENT_MTI_COUNT) as min_mti_count, max(CURRENT_MTI_COUNT) as max_mti_count
  from table (
      coolstuff.cs_sqe_pc_info()
    );
stop;

--
-- Low and High points for MTI space consumption
--
select min(TOTAL_MTI_SIZE) as min_mti_total_space, max(TOTAL_MTI_SIZE) as max_mti_total_space
  from table (
      coolstuff.cs_sqe_pc_info()
    );
stop;

--
-- Low and High points for SQL Plan Cache total size
--
select min(PLAN_CACHE_SIZE) as min_SQE_PC_total_size, max(PLAN_CACHE_SIZE) as max_SQE_PC_total_size
  from table (
      coolstuff.cs_sqe_pc_info()
    );
stop;

--
-- Low and High points for MTI space consumption (by day)
--
select date(INTERVAL_TIME) as date, min(TOTAL_MTI_SIZE) as min_mti_total_space, max(TOTAL_MTI_SIZE) as max_mti_total_space
  from table (
      coolstuff.cs_sqe_pc_info()
    ) group by date(INTERVAL_TIME)
    order by date;
stop;

--
-- Low and High points for SQL Plan Cache total size (by day)
--
select date(INTERVAL_TIME) as date, min(PLAN_CACHE_SIZE) as min_SQE_PC_total_size, max(PLAN_CACHE_SIZE) as max_SQE_PC_total_size
  from table (
      coolstuff.cs_sqe_pc_info()
    ) group by date(INTERVAL_TIME)
    order by date;
stop;

--
-- MTI creates and deletes (by day)
--
select date(INTERVAL_TIME) as date, sum(MTIS_CREATED) as MTIs_created_by_day, sum(MTIS_DELETED) as MTIs_deleted_by_day 
  from table (
      coolstuff.cs_sqe_pc_info()
    ) group by date(INTERVAL_TIME)
    order by date;
stop;