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;
         
 

Scott, another great example and sample. Two things to consider changing on this one.

1. The routine runs a VERY LONG time on my system since there are no input parameters to filter down the search data. I modified my version to allow for more restrictive processing.
2. The routine does not handle long column names with spaces in the names 100%. I don't code my tables with these unless the table is used to create CSV's, where the users wants specific name columns in the CSV, and then rarely.

create or replace function coolthings.decimal_check (
  IN_TABLE_SCHEMA varchar(128) default null
  ,IN_TABLE_NAME varchar(128) default null
)
...

    declare continue handler for NOT_FOUND set AT_END = 1;
---
-- insert this line between above line and below line to allow for filtering by schema and/or table name
    set FIND_DECIMALS_STMT_TEXT = FIND_DECIMALS_STMT_TEXT
    || (case when IN_TABLE_SCHEMA is not null then 
        ' and TABLE_SCHEMA = ''' ||IN_TABLE_SCHEMA||''''
        else '' end) || (case when IN_TABLE_NAME is not null then 
        ' and TABLE_NAME = ''' ||IN_TABLE_NAME||''''
        else '' end);
---
    prepare FIND_DECIMALS_STMT from FIND_DECIMALS_STMT_TEXT;
...
    while (AT_END = 0) do
---
-- insert this if statement to wrap column names with embedded spaces
      if (locate(' ', v_COLUMN_NAME) > 0) then
        set v_COLUMN_NAME = '"'||v_COLUMN_NAME ||'"';
      end if;
---
...