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;
 
 

Hello Scott, sorry, maybe this has nothing to do with this topic, but I didn't know where to write to you., I wanted to ask you a question, is there a way that having a table on LPAR "A", I can perform a query to a table on LPAR "B" with a join because I have tried in several ways and the DB2 error is that it does not allow me to use "different databases",

Example: I am executing this query on LPAR_"A" where I have a DATABASE called NEWRGZ and a Table called "RGZPFX2" and I want to get a relationship with qsys2.syspartitionstat but from LPAR_B

CREATE TABLE qtemp.TEMP_CHECK AS ( SELECT * FROM LPAR_B.qsys2.syspartitionstat AS CONTINGENCIA WHERE CAST(CONTINGENCIA.TABLE_SCHEMA AS CHAR(10)) IN (SELECT DISTINCT LIBRARY FROM LPAR_A.NEWRGZ.RGZPFX2) AND CAST(CONTINGENCIA.TABLE_NAME AS CHAR(10)) IN (SELECT DISTINCT TABLE FROM TEAM_A.NEWRGZ.RGZPFX2) ) WITH DATA;

I also tried a Join, but the result is the same,: (The statement references objects in multiple databases. Cause . . . . . : The statement references objects that reside in multiple databases. The objects can be explicitly qualified object names or alias names defined to reference another database. This SQL statement can refer to only a single database. A CREATE TABLE AS operation that selects from a remote database cannot reference a table whose FIELDPROC field is defined. You cannot define a materialized query table. If the remote database does not reside on an IBM i server, INCLUDING clauses are not allowed. Recovery . . . : Ensure that all objects used in the statement reside in the same database. Try the request again.)