At a recent webinar, I was asked whether a flexible view could be constructed such that it consumed a subset of members within a multiple member physical file. This is a working example of how to leverage a Db2 for i Global Variable as the external control for such view. The member processing is encapsulated within a User Defined Table Function …

flexible view over a mmpf.sql

-- ====================================================================
-- description: Flexible view that uses a multiple member physical file
-- author:      Scott Forstie
-- date:        March 10, 2020
--
-- Create a global variable that will be used to create the select statement
--
create or replace variable coolstuff.member_query_statement_text clob(1M) for sbcs data;

--
-- Create a global variable that will control the # of members to query
--
create or replace variable coolstuff.member_count integer default 10;

--
-- This UDTF finds the N most recent members in the qpfrdata/qapmsqlpc multiple member physical file.
-- This file contains Collection Services timesliced statistics of the SQL Plan Cache Performance Data.
-- Documentation: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzahx/rzahxqapmsqlpc.htm
-- 
create or replace function coolstuff.mti_usage (
      cs_member_count integer default 10
  )
  returns table (
    cs_capture_date date, minimum_mtis bigint, maximum_mtis bigint, mtis_created bigint,
    mtis_delete bigint
  )
  not deterministic
  external action
  modifies sql data
  not fenced
  set option commit = *none
  begin
    declare v_print_line char(133);
    declare local_sqlcode integer;
    declare local_sqlstate char(5);
    declare v_message_text varchar(70);
    --
    -- Discover the members, and create an SQL alias for every member 
    --  within the target multi-member physical file
    --
    begin
      declare query_sqlstmt clob(1g) default '';
      declare sqlstmt clob(1g);
      declare lc integer default 0;
      declare alias_count integer;
      declare v_partition_name varchar(10);
      declare collection_services_sqe_pc_members cursor for
        select table_partition
          from qsys2.syspartitionstat
          where table_schema = 'QPFRDATA'
                and table_name = 'QAPMSQLPC'
          order by create_timestamp desc
          limit cs_member_count;
      select min(count(*), cs_member_count)
        into alias_count
        from qsys2.syspartitionstat
        where table_schema = 'QPFRDATA'
              and table_name = 'QAPMSQLPC';
      open collection_services_sqe_pc_members;
      set query_sqlstmt = 'select date(datetime) as cs_Capture_date, 
       min(sqcurmti) as minimum_mtis,  max(sqcurmti) as maximum_mtis, 
       sum(sqmticrt) as mtis_created,  sum(sqmtidlt) as mtis_delete
     FROM (';
      while (lc < alias_count) do
        set lc = lc + 1;
        fetch from collection_services_sqe_pc_members into v_partition_name;
        set sqlstmt = 'create or replace alias QTEMP.SQEPC' concat lc concat
              ' FOR QPFRDATA.QAPMSQLPC(' concat v_partition_name concat ')';
        execute immediate sqlstmt;
        set query_sqlstmt = query_sqlstmt concat 'select * FROM QTEMP.SQEPC' concat lc;
        if (lc < alias_count) then
          set query_sqlstmt = query_sqlstmt concat ' UNION ALL ';
        end if;
      end while;
      set query_sqlstmt = query_sqlstmt concat ' ) group by date(datetime)';
      set coolstuff.member_query_statement_text = query_sqlstmt;
      close collection_services_sqe_pc_members;
    end;
    begin
      declare v_cs_capture_date date;
      declare v_minimum_mtis bigint;
      declare v_maximum_mtis bigint;
      declare v_mtis_created bigint;
      declare v_mtis_delete bigint;
      declare c_return_sqe_pc_summary cursor for return_sqe_pc_summary_query;
      declare continue handler for sqlexception
      begin
        get diagnostics condition 1
            local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
        set v_message_text = 'coolstuff.MTI_Usage() failed with: ' concat local_sqlcode
              concat '  AND ' concat local_sqlstate;
        signal sqlstate 'QPC01'
          set message_text = v_message_text;
      end;
      prepare return_sqe_pc_summary_query from coolstuff.member_query_statement_text;
      open c_return_sqe_pc_summary;
      l1: loop
        fetch from c_return_sqe_pc_summary into v_cs_capture_date, v_minimum_mtis,
                                                v_maximum_mtis, v_mtis_created,
                                                v_mtis_delete;
        get diagnostics condition 1
            local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
        if (local_sqlstate = '02000') then
          close c_return_sqe_pc_summary;
          return;
        end if;
        pipe (
          v_cs_capture_date,
          v_minimum_mtis, v_maximum_mtis, v_mtis_created, v_mtis_delete);
      end loop; /* L1 */
      close c_return_sqe_pc_summary;
    end;
    return;
  end;     
stop;

--
-- The view is flexible, at the member level.
-- The UDTF's input parameter is a Global Variable.
-- If you change the contents of the Global Variable, 
-- the query will find the requested # of members (newest to oldest)
--
create or replace view coolstuff.mti_usage as
    select *
      from table (
          coolstuff.mti_usage(coolstuff.member_count)
        );
stop;

---
-- Usage
---
select cs_capture_date, minimum_mtis, maximum_mtis, mtis_created, mtis_delete
  from coolstuff.mti_usage;

-- or 
set coolstuff.member_count = 15;
select cs_capture_date, minimum_mtis, maximum_mtis, mtis_created, mtis_delete
  from coolstuff.mti_usage;

-- or    
set coolstuff.member_count = default;
select cs_capture_date, minimum_mtis, maximum_mtis, mtis_created, mtis_delete
  from coolstuff.mti_usage;