The request was this... I want to see how close some internal identification columns are to maxing out their maximum value. For example a counter that’s defined as DECIMAL(7,0) has a high value of 9,995,000 would indicate that we need to intercede ASAP.

Decimal column checker upper.sql

--
-- Subject: Decimal column checker upper
-- Author: Scott Forstie
-- Date  : March, 2023
-- Features Used : This Gist uses qsys2.syscolumns2, qsys2.object_statistics, RPAD, COALESCE, and SQL PL
--
-- Notes:
-- ===============================================
-- 1) This example can help find those columns which rely upon ever increasing values
-- 2) The examples could be revised to study other numeric columns, or ever descending values
--
 
stop;
--
-- Find database file columns that are based upon a decimal data type, 
-- with no scale, and generate the maximum value for that column
--
with decimal_columns(sn, tn, cn, c_max) as (
select table_schema, table_name, column_name, rpad('', LENGTH, 9)
  from qsys2.syscolumns2, lateral 
  (select objattribute from table(qsys2.object_statistics(table_schema, '*FILE', table_name))) ttt
  where data_type = 'DECIMAL' and
        numeric_scale = 0 and
        ttt.objattribute = 'PF' and 
        table_schema not like 'Q%'
)
  select * from  decimal_columns;
stop;

--
-- Find database file columns that are based upon a decimal data type, 
-- with scale, and generate the maximum value for that column
--
with decimal_columns(sn, tn, cn, c_max) as (
select table_schema, table_name, column_name, rpad('', LENGTH - numeric_scale, 9) concat
             case
               when numeric_scale > 0 then '.' concat rpad('', numeric_scale, 9)
               else ''
             end
  from qsys2.syscolumns2, lateral 
  (select objattribute from table(qsys2.object_statistics(table_schema, '*FILE', table_name))) ttt
  where data_type = 'DECIMAL' and 
        ttt.objattribute = 'PF' and 
        table_schema not like 'Q%'
)
  select * from  decimal_columns;
stop;

--
-- Decimal column fitness check
--
create or replace function coolstuff.decimal_check ()
  returns table (
    table_schema varchar(128) for sbcs data, 
    table_name varchar(128) for sbcs data,
    column_name varchar(128) for sbcs data, 
    maximum_value_used varchar(1000) for sbcs data,
    maximum_value_possible varchar(1000) for sbcs data,
    percentage_used decimal(4,2)
  )
  not deterministic
  external action
  modifies sql data
  not fenced
  set option COMMIT = *NONE
  begin
    declare local_sqlcode integer;
    declare local_sqlstate char(5);
    declare not_found condition for '02000';
    declare at_end integer default 0;
    declare at_end2 integer default 0;
    declare v_error integer default 0;
    declare v_message_text varchar(70);
    declare v_table_schema varchar(128) for sbcs data;
    declare v_table_name varchar(128) for sbcs data;
    declare v_column_name varchar(128) for sbcs data;
    declare v_maximum_value_used varchar(1000) for sbcs data;
    declare v_maximum_value_possible varchar(1000) for sbcs data;
    declare v_percentage_used decimal(4,2);
    declare find_decimals_stmt_text varchar(5000) for sbcs data default
    'select table_schema, table_name, column_name, rpad('''', LENGTH - numeric_scale, 9) concat
             case
               when numeric_scale > 0 then ''.'' concat rpad('''', numeric_scale, 9)
               else ''''
             end
  from qsys2.syscolumns2, lateral 
  (select objattribute from table(qsys2.object_statistics(table_schema, ''*FILE'', table_name))) ttt
  where data_type = ''DECIMAL'' and 
        ttt.objattribute = ''PF'' and 
        table_schema not like ''Q%'' ';
    
    declare compute_current_max_text varchar(1000) for sbcs data;
    declare c_find_decimals cursor for find_decimals_stmt;
    declare continue handler for SQLEXCEPTION
    begin
      get diagnostics condition 1
          local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
      set v_message_text = 'coolstuff.decimal_check() failed with: ' concat local_sqlcode
            concat '  AND ' concat local_sqlstate;
      signal sqlstate 'QPC01'
        set message_text = v_message_text;
    end;
    declare continue handler for not_found set at_end = 1;
    prepare find_decimals_stmt from find_decimals_stmt_text;
    open c_find_decimals;
    set at_end = 0;
    fetch from c_find_decimals
      into v_table_schema, v_table_name, v_column_name, v_maximum_value_possible;
    while (at_end = 0) do
      call systools.lprintf('Processing: ' concat v_table_schema concat '.' concat v_table_name concat '(' concat v_column_name concat ')');
      begin
        declare c_compute_current_max cursor for compute_current_max_stmt;
        declare continue handler for sqlexception set at_end2 = 1;
        declare continue handler for not_found set at_end2 = 1;
        set at_end2 = 0;
        set compute_current_max_text = 'select max(' concat v_column_name concat '), coalesce(max(' concat v_column_name concat ')  / ' concat v_maximum_value_possible concat ', 0.00)  from ' concat v_table_schema concat '.' concat v_table_name;
        prepare compute_current_max_stmt from compute_current_max_text;
        open c_compute_current_max;
        fetch from c_compute_current_max into v_maximum_value_used, v_percentage_used;
        close c_compute_current_max;
      end;
      -- project a row      
      pipe (v_table_schema, v_table_name, v_column_name, v_maximum_value_used, v_maximum_value_possible, v_percentage_used);
      -- Move onto the next column
      set at_end = 0;
      fetch from c_find_decimals
        into v_table_schema, v_table_name, v_column_name, v_maximum_value_possible;
    end while;
    close c_find_decimals;
    return;
  end;
stop;

--
-- Review the decimal-based columns to find those columns that are closest to consume the maximum value for that column
--
select *
  from table (
      coolstuff.decimal_check()
    )
  order by percentage_used desc;