This example takes a previous example and extends it. The idea here is that you want to proactively manage user consumption of storage. For the top storage consumers, return a report that lists their largest objects (either in QSYS or IFS) and provide some contextual detail. This solution includes simple controls to allow the caller to specify t…

TopN user storage report.sql

-- #SQLcandoit #IBMiServices #Db2fori

-- ======================================================================
--
-- Establish a UDTF that finds the largest N objects for a specific user
--
-- ======================================================================
create or replace function systools.N_largest_objects_owned (
      user_name varchar(10) for sbcs data, limit_size bigint default 10
  )
  returns table (
    object varchar(10000) for sbcs data, 
    size varchar(50) for sbcs data, 
    created timestamp,
    changed timestamp, 
    used timestamp
  )
  specific systools.N_largest_objects_owned
  not deterministic
  no external action
  not fenced
  modifies sql data
  called on null input
  system_time sensitive no
  set option alwblk = *allread, alwcpydta = *optimize, commit = *none, decresult = (31,
             31, 00), dftrdbcol = qsys2, dlyprp = *no, dyndftcol = *no, dynusrprf = *user,
             monitor = *system, srtseq = *hex, usrprf = *user
  return
    with qsysobjs (lib, obj, type) as (
           select object_library, object_name, object_type
             from table (
                 qsys2.object_ownership(user_name)
               ) a
             where path_name is null
         ),
         qsysobjs_with_size (object, size, created, changed, used) as (
           select lib concat '/' concat obj concat ' ' concat type as object, objsize,
                  objcreated, change_timestamp, last_used_timestamp
             from qsysobjs q, lateral (
                    select objsize, objcreated, change_timestamp, last_used_timestamp
                      from table (
                          qsys2.object_statistics(lib, type, obj)
                        )
                  ) z
         ),
         ifsobjs (path, type) as (
           select path_name, object_type
             from table (
                 qsys2.object_ownership(user_name)
               ) a
             where path_name is not null and object_type in ('*DOC', '*STMF', '*DSTMF', '*USRSPC')
         ),
         ifsobjs_with_size (object, size, created, changed, used) as (
           select path concat ' ' concat type, data_size, create_timestamp,
                  change_timestamp, last_used_timestamp
             from ifsobjs i, lateral (
                    select data_size, create_timestamp,
                           data_change_timestamp as change_timestamp, last_used_timestamp
                      from table (
                          qsys2.ifs_object_statistics(
                            start_path_name => path, 
                            subtree_directories => 'NO')
                        )
                  ) z
         ),
         alltogether (object, size, created, changed, used) as (
           select object, varchar_format(size, '999G999G999G999G999G999G999G999G999G999G999G999') size,
                  created, changed, used
             from qsysobjs_with_size
           union all
           select object, varchar_format(size, '999G999G999G999G999G999G999G999G999G999G999G999') size,
                  created, changed, used
             from ifsobjs_with_size
         ) select object, size, created, changed, used
      from alltogether
    order by size desc
    limit limit_size;
 
-- What are the 12 largest objects owned by Tim
-- select object, size, created, changed, used
--  from table (
--      systools.n_largest_objects_owned('TIMMR', 12)
--   );

CREATE OR REPLACE FUNCTION systools.topN (
         how_many_users bigint default 10,
         how_many_objects bigint default 10 
      )
   RETURNS TABLE (
    user_name varchar(10) for sbcs data,
    object varchar(10000) for sbcs data,
    size varchar(50) for sbcs data, 
    created timestamp,
    changed timestamp, 
    used timestamp
   )
   NOT DETERMINISTIC
   EXTERNAL ACTION
   MODIFIES SQL DATA
   NOT FENCED
   SET OPTION COMMIT = *NONE
BEGIN
   DECLARE local_sqlcode INTEGER;
   DECLARE local_sqlstate CHAR(5) for sbcs data;
   DECLARE v_message_text VARCHAR(70) for sbcs data;
   DECLARE v_user_name varchar(10) for sbcs data;
   DECLARE v_object varchar(10000) for sbcs data;
   DECLARE v_size varchar(50) for sbcs data; 
   DECLARE v_created timestamp;
   DECLARE v_changed timestamp; 
   DECLARE v_used timestamp;
   DECLARE object_count bigint;
   DECLARE user_count bigint;
   declare not_found condition for '02000';
   declare at_end integer default 0;
   
   DECLARE topN_objects_cursor_stmttext varchar(10000) for sbcs data default 
    'select object, size, created, changed, used 
       from table(systools.N_largest_objects_owned(?, ?))';
   DECLARE topN_objects_cursor CURSOR FOR topN_objects_cursor_statement;
   
   DECLARE topN_users_cursor CURSOR FOR 
    select a.authorization_name 
      from qsys2.user_storage a
           inner join qsys2.user_info b
             on b.user_name = a.authorization_name
      where a.user_name in (select user_name
            from qsys2.user_info
            where user_creator <> '*IBM')
      group by a.authorization_name, b.text_description, b.accounting_code,
               b.maximum_allowed_storage
      order by sum(a.storage_used) desc
      limit how_many_users;
      
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
   BEGIN
      GET DIAGNOSTICS CONDITION 1
            local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
      SET v_message_text = 'systools.topN() failed with: ' CONCAT local_sqlcode
               CONCAT '  AND ' CONCAT local_sqlstate;
      SIGNAL SQLSTATE 'QPC01' SET MESSAGE_TEXT = v_message_text;
   END;

   PREPARE topN_objects_cursor_statement from topN_objects_cursor_stmttext;
   -- Open a cursor that returns the N user profile names with the largest storage consumption
   OPEN topN_users_cursor;
   set user_count = 1;
   l1: LOOP
      IF user_count > how_many_users THEN
       LEAVE l1;
      END IF;
      set user_count = user_count + 1;
      FETCH FROM topN_users_cursor INTO v_user_name;
      
      -- Open a cursor which returns the N largest objects for the user
      OPEN topN_objects_cursor using v_user_name, how_many_objects;
      
      SET object_count = 0;
      SET at_end = 0;
      w1: while (object_count < how_many_objects) do
        begin      
          declare continue handler for sqlexception set at_end = 1;
          declare continue handler for not_found set at_end = 1;
  
          SET object_count = object_count + 1;
          FETCH FROM topN_objects_cursor into v_object, v_size, v_created, v_changed, v_used;
          if (at_end = 1) then
            leave w1;
          end if;
          PIPE (
            v_user_name,v_object, v_size, 
            v_created, v_changed, v_used
          );
        end;
      end while;
      CLOSE topN_objects_cursor;
   END LOOP; /* L1 */
   CLOSE topN_users_cursor;
   return;
END;
stop;

--- Show the 5 largest objects for the 7 users who are consuming the most storage
select user_name, object, size, created, changed, used
  from table (
      systools.topn(how_many_users => 7, how_many_objects => 5)
    );

stop;
-- Alternative... run the report in batch

cl: crtlib coolstuff;

cl: SBMJOB CMD(RUNSQL SQL('
create or replace table coolstuff.topNreport as (
select user_name, object, size, created, changed, used
  from table (
      systools.topn(how_many_users => 7, how_many_objects => 5)
    )
 ) with data on replace delete rows') commit(*NONE) )  INLASPGRP(*CURRENT) JOB(TOPNREPORT) JOBMSGQFL(*PRTWRAP) LOG(4 00 *SECLVL);
stop;

select count(*) isitrunning
  from table (
      qsys2.active_job_info(subsystem_list_filter => 'QBATCH')
    )
  where job_name like '%TOPNREPORT%';

select user_name, object, size, created, changed, used
  from coolstuff.topnreport ;

Hello Scott,

Our system Auxiliary storage is increasing and reaching its limit. Currently we are at 89% consumption. We looked into top consumers but we are not able to figure it out what job/thread is causing the increase. It is slowly increasing but nothing is standing out.

Do you have any article here that will guide me how to look for the culprit who is slowly chewing up AUX space.