Database Engineers sometimes need to identify data models with varying length columns, where the allocate clause could be improved. When you use the ALLOCATE(n) clause, you're telling the database to establish n number of bytes for the column in the fixed portion of th record. If the column value for a row has a length > n, the database uses the…

calculating an ALLOCATE value for a column.sql

--
-- description: Compute the ALLOCATE(n) value, based upon the 80/20 rule
--              (80% of the time, the length of the column data would be less than or equal to n)
-- minvrm: V7R3M0
--
create or replace function systools.compute_allocate (
      p_schema_name varchar(128) for sbcs data, 
      p_table_name  varchar(128) for sbcs data, 
      p_column_name  varchar(128) for sbcs data, 
      allocate_percentage decimal(3,2) 
  )
  returns table (
    ideal_allocate_length bigint, 
    percentage_of_rows_that_fit_into_the_allocated_length decimal(5,4)
  )
  not deterministic
  no external action
  not fenced
  modifies sql data
  called on null input
  system_time sensitive no
  set option dynusrprf = *user, usrprf = *user
  BEGIN
   DECLARE local_sqlcode INTEGER;
   DECLARE local_sqlstate CHAR(5) for sbcs data;
   DECLARE v_message_text VARCHAR(70) for sbcs data;
   DECLARE v_percent decimal(5,4);
   DECLARE v_data_length bigint;
   declare not_found condition for '02000';
   declare at_end integer default 0;
   
   DECLARE allocate_analysis_cursor_stmttext varchar(10000) for sbcs data;
   DECLARE allocate_analysis_cursor CURSOR FOR allocate_analysis_cursor_statement;
      
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
   BEGIN
      GET DIAGNOSTICS CONDITION 1
            local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
      SET v_message_text = 'systools.compute_allocate() failed with: ' CONCAT local_sqlcode
               CONCAT '  AND ' CONCAT local_sqlstate;
      SIGNAL SQLSTATE 'QPC01' SET MESSAGE_TEXT = v_message_text;
   END;
   
   set allocate_analysis_cursor_stmttext = 
    'with column_lengths (data_length, data_length_count) as (
       select
         case when length(' concat qsys2.delimit_name(p_column_name) concat ') is null then 0
              else length(' concat qsys2.delimit_name(p_column_name) concat ')
         end, count(*)
         from ' concat qsys2.delimit_name(p_schema_name) concat '.' 
         concat qsys2.delimit_name(p_table_name) concat 
         ' group by
           case
             when length(' concat qsys2.delimit_name(p_column_name) concat ') is null then 0
             else length(' concat qsys2.delimit_name(p_column_name) concat ')
           end
     ),
     column_lengths_with_ratio (data_length, data_length_count, ratio) as (
       select data_length, data_length_count, ratio_to_report (data_length_count) over (
              ) as ratio
         from column_lengths
     ),
     column_lengths_with_ratio_and_rolling_sum (data_length, data_length_count, ratio,
         rolling_ratio_sum) as (
       select data_length, data_length_count, ratio, sum(ratio) over (
                order by data_length asc
              )
         from column_lengths_with_ratio
     )
  (select data_length, dec(rolling_ratio_sum, 5, 4) as percent
      from column_lengths_with_ratio_and_rolling_sum
      where rolling_ratio_sum < ?
    order by rolling_ratio_sum desc
    limit 1)
  union all
  (select data_length, dec(rolling_ratio_sum, 5, 4) as percent
      from column_lengths_with_ratio_and_rolling_sum
      where rolling_ratio_sum > ?
    order by rolling_ratio_sum asc
    limit 1)';
    
    PREPARE allocate_analysis_cursor_statement from allocate_analysis_cursor_stmttext;
    OPEN allocate_analysis_cursor using allocate_percentage, allocate_percentage;
    -- return the ALLOCATE value that would be closest (but below) the percent target
    FETCH FROM allocate_analysis_cursor into v_data_length, v_percent;
    PIPE( v_data_length, v_percent );
    
    -- return the ALLOCATE value that would be closest (but above) the percent target
    FETCH FROM allocate_analysis_cursor into v_data_length, v_percent;
    PIPE( v_data_length, v_percent );

    CLOSE allocate_analysis_cursor;
    RETURN;
END;
stop;

select *
  from table (
      systools.compute_allocate(
        p_schema_name       => 'QSYS2', 
        p_table_name        => 'SYSIXADV', 
        p_column_name       => 'KEY_COLUMNS_ADVISED',
        allocate_percentage => 0.80
      )
    );