
-- Calculate the page size with the highest space utilisation for application tablespaces.  

-- Note that space utilisation is only one aspect of picking the 'best' pagesize - 
-- as a rough rule of thumb, transactional systems tend to favour smaller page sizes,
-- whilst decision support systems prefer larger page sizes.

-- Factor in the number of supported rows for each allowable page size. 
-- Rank by table cardinality within tablespace, since page size is set at tablespace level.

-- Ignore catalog tables, as their page size is fixed unless you redirect restore; 
-- also ignore small tables with less than 1,000 rows, unless that's the norm on your database!

-- DB2 Information Center, v8.1.11, http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp,
-- search on CREATE TABLE statement:

-- Limits for Number of Columns and Row Size in Each Table Space Page Size 
-- Page Size  Row Size Limit  Column Count Limit   
--        4K ......... 4,005 ............... 500  
--        8K ......... 8,101 ............. 1,012  
--       16K......... 16,293 ............. 1,012  
--       32K ........ 32,677 ............. 1,012  
-- Refer to Table 6. Byte Counts of Columns by Data Type for other values used here.

-- Usage Notes:
-- 1. You will get more reliable results if catalog statistics are up to date.
--    For example, calculation of row length for tables with VARCHAR columns uses the
--    average row length, not the maximum length possible for the column. 
-- 2. Run this query with autocommit set to off. 
--    If autocommit is on, ON COMMIT DELETE ROWS will delete after EVERY sql statement.
--    You can set autocommit off by
--        db2 update command options using c off; export db2options=+c   
--     or db2 -t +c -vf <filename>
--    The data will then persist until a rerun, explicit delete, commit or end of session.
-- 3. Substitute the name of your single partition user temporary tablespace for all 
--    occurences of 'USERTEMPTS000' below.
--    If you don't have a user temporary tablespace yet(!), sample DDL is at the foot of the script.
-- 4. The script logic only provides an estimate for structured tables, structured types or compressed columns. 
--    A purely personal opinion, but the complexity, restrictions and overhead of structured tables and compressed columns mean that up to now 
--    the application alternatives have always appeared more attractive. These features appear to be rarely used, at least in the current release. 
--  - Calculations will slightly understate row lengths for structured objects; if you use those, you mUst be used to pain! 
--    so you can modify the code to add an additional 4 bytes per row of subtable, and an extra null byte for each additional subtype column,
--    even if the column is declared as not null.
--  - Calculations use the base datatype length and do not adjust for COMPRESS SYSTEM DEFAULTS columns in tables defined with the VALUE COMPRESSION clause; 
--    in some circumstances the storage length will be overstated, in others understated. To calculate accurately, one would have to factor in both the 
--    frequency and distribution of system defaults, even assuming there are full and current catalog statistics for each given column.
--    
-- 5. Last run 28 Feb 2006 on DB2 V8.2.2 (FixPack 9); 
--    3000 row result set returned in < 8 seconds running on an AIX 5.2.6 P-680 12-way.

-- Maintenance notes:
-- 1. Include the boolean datatype if later releases make this available beyond internal use.
-- 2. You may wish to rank application tables by NLEAF rather than cardinality.  

-- March 2006 Alex, Sustainable Software Ltd. http://www.sustainablesoftware.net 

-- Define a DGTT table to hold a list of candidate tables for the report:
DECLARE GLOBAL TEMPORARY TABLE temp
AS (
    select t.tabschema                         AS schema    ,
           t.tabname                           AS table     ,
           t.card                              AS row_count ,
           t.tbspace                           AS tbspace   ,
           ts.pagesize                         AS pagesize  ,
           rank() over (order by t.card desc)  AS rank      ,
           sum(c.length)                       AS row_length,
           count(*)                            AS nr_columns 
    from   syscat.tables      t,
           syscat.columns     c,
           syscat.tablespaces ts
    group by t.tabschema,t.tabname,t.card,t.tbspace,ts.pagesize
   ) 
DEFINITION ONLY 
WITH REPLACE 
ON COMMIT DELETE ROWS 
NOT LOGGED
IN USERTEMPTS000
;

-- Populate the set of candidate tables for the report. 
-- - exclude system tables (where tabschema like 'SYS%');
-- - include materialised tables only (type in ('S','T'));
-- Row length calculations:
-- - decimal data is held in hexadecimal form with each digit ocupying a half byte, plus an implicit half byte for the sign;
--   storage is therefore a whole half the number of digits in the length (precision and scale) plus one, as byte count must be an integer. 
-- - DB2 adds a 4 byte overhead to store the length of smaller variable length datatypes (VARCHAR and VARGRAPHIC); 
-- - GRAPHIC and VARGRAPHIC data uses a double byte character set, so double the stated length;
-- - the declared length of a DATALINK column is fixed at 200 bytes plus an overhead of 54 bytes;
-- - only factor in the descriptor lengths for LOB, LONG VARCHAR or LONG VARGRAPHIC columns; these are shown in Table 6 of the CREATE TABLE statement
--   in the current version of the Information Center.  
--   (DB2 stores their data in separate files, with .LB/.LBA and .LF extensions respectively);
-- Modify the CASE statement if you have user defined types defined over these base data types.

insert   into session.temp          
(schema,table,row_count,tbspace,pagesize,rank,row_length,nr_columns)
select   t.tabschema                        ,
         t.tabname                          ,
         t.card                             ,
         t.tbspace                          ,
         ts.pagesize                        ,
         rank() over (order by t.card desc) ,
         sum(case
             when typename in ('BIGINT','CHARACTER','DATE','DOUBLE','INTEGER','REAL','SMALLINT','TIME','TIMESTAMP')
                  then length
             when typename = 'DECIMAL'
                  then ( length / 2 ) + 1
             when typename = 'DATALINK' 
                  then 254 
             when typename = 'VARCHAR'
                  then avgcollen + 4
             when typename in ('LOB','BLOB','CLOB','DBCLOB')
                  then case 
                       when length <= 1024
                            then  72
                       when length <= 8192
                            then  96
                       when length <= 65536
                            then 120
                       when length <= 524000
                            then 144
                       when length <= 4190000
                            then 168
                       when length <= 134000000
                            then 200
                       when length <= 536000000
                            then 224
                       when length <= 1070000000
                            then 256
                       when length <= 1470000000
                            then 280
                       when length <= 2147483647
                            then 316
                       else
--                     <else there's been a new release since v8.2 FP11>
                            316    
                       end 
             when typename in ('LONG VARCHAR','LONG VARGRAPHIC')
                  then 24
             when typename = 'VARGRAPHIC'
                  then (length * 2) + 4 
             when typename = 'GRAPHIC'
                  then length * 2
             else length
             end)                           ,
         count(*)                         
from     syscat.tables  t
                INNER JOIN
         syscat.columns c
                ON c.tabschema = t.tabschema
                AND c.tabname  = t.tabname
                INNER JOIN
         syscat.tablespaces ts
                ON t.tbspaceid = ts.tbspaceid
where    t.tabschema not like 'SYS%'
and      t.type in ('S','T')
and      t.card >= 1000
group by t.tabschema,
         t.tabname  ,
         t.card     ,
         t.tbspace  ,
         ts.pagesize
;

-- Add NULLS overhead:
-- DB2 imposes a storage tax of one byte per nullable column.
update session.temp t
set    row_length = row_length + 
       (select count(*)
        from   syscat.columns c
        where  c.tabschema = t.schema
        and    c.tabname   = t.table
        and    c.nulls = 'Y')
;  
      
-- Declare a report results table.
-- You may wish to edit the declared lengths for columns 'schema', 'table' and 
-- tbspace; they're shortened here for report display purposes only.
DECLARE GLOBAL TEMPORARY TABLE temp2
   ( schema       char(10) not null,
     table        char(30) not null,
     row_count    integer  not null,
     tbspace      char(18) not null,
     pagesize     char(3)  not null,
     rank         smallint not null,
     row_length   integer  not null,
     rows_per_4k  smallint not null,
     rows_per_8k  smallint not null,
     rows_per_16k smallint not null,
     rows_per_32k smallint not null,
     util_per_4k  dec(6,3) not null,
     util_per_8k  dec(6,3) not null,
     util_per_16k dec(6,3) not null,
     util_per_32k dec(6,3) not null
   ) 
WITH REPLACE 
ON COMMIT DELETE ROWS 
NOT LOGGED
IN USERTEMPTS000
;

-- Build the report table, calculating the maximum number of rows for each page size;
-- use the floor function since rows cannot span pages.
insert into session.temp2
       (schema,table,row_count,tbspace,
        pagesize,
        rank,row_length,
        rows_per_4k,rows_per_8k,rows_per_16k,rows_per_32k,
        util_per_4k,util_per_8k,util_per_16k,util_per_32k)
select substr(schema,1,10), substr(table,1,30),row_count,substr(tbspace,1,18),
       case pagesize
            when  4096 then ' 4K'
            when  8192 then ' 8K'
            when 16384 then '16K'
            when 32768 then '32K'
            else '???'
       end,
       rank,row_length,
--     calculate maximum rows per supported page size, based on current row length
       case
          when 4005/row_length > 500
          then 500
          else floor(4005/row_length)
       end,
       case
          when 8101/row_length > 1012
          then 1012
          else floor(8101/row_length)
       end,
       case
          when 16293/row_length > 1012
          then 1012
          else floor(16293/row_length)
       end,
       case
          when 32677/row_length > 1012
          then 1012
          else floor(32677/row_length)
       end,
--     % utilisation for each supported page size calculated in next query
       0,0,0,0
from session.temp
; 

-- calculate % utilisation for each page size:
update session.temp2
set util_per_4k = dec((multiply_alt(rows_per_4k,row_length * 100)  / 4005),6,3) ,
    util_per_8k = dec((multiply_alt(rows_per_8k,row_length * 100)  / 8101),6,3) ,
    util_per_16k= dec((multiply_alt(rows_per_16k,row_length * 100) / 16293),6,3),
    util_per_32k= dec((multiply_alt(rows_per_32k,row_length)  / 32677 * 100),6,3)
;
-- and report by most populated table within each tablespace;
-- Note it would be very simple to write a UDF to select the largest of 4 numbers.
select schema,table,tbspace,row_count,row_length,
       util_per_4k,
       util_per_8k,
       util_per_16k,
       util_per_32k,
       case when util_per_32k > util_per_16k and util_per_32k > util_per_8k and util_per_32k > util_per_4k
                 then '32k'
            when util_per_16k > util_per_32k and util_per_16k > util_per_8k and util_per_16k > util_per_4k
                 then '16k'
            when util_per_8k  > util_per_32k and util_per_8k  > util_per_16k and util_per_8k > util_per_4k
                 then '8k'
            when util_per_4k  > util_per_32k and util_per_4k  > util_per_16k and util_per_4k > util_per_8k
                 then '4k'
            else 'You choose!'
       end as BEST_FIT,
       pagesize as CURRENT_PAGESIZE
from   session.temp2
order  by tbspace, rank
;

COMMIT WORK
;
--    Sample DDL for a user temporary tablespace. 
--    This example uses IBM recommendations for ESS 'Shark'SAN 16k pagesizes. Modify the path names, and the partition number 
--    in the dbpartitionnum clause and the path name. This example further assumes a partitioned database with the catalog 
--    partition on node 0 and the single partition tablespace on node 1:
-- create USER TEMPORARY tablespace USERTEMPTS000 
--        in your_singlepartition_databasepartitiongroup 
--        pagesize 16 K managed by system
-- using ('/mydb/filesystem1/NODE0001/usertempts000',
--        '/mydb/filesystem2/NODE0001/usertempts000',
--        '/mydb/filesystem3/NODE0001/usertempts000',
--        '/mydb/filesystem4/NODE0001/usertempts000') on dbpartitionnum(1)
-- EXTENTSIZE   192
-- PREFETCHSIZE 768
-- BUFFERPOOL   my16kbp
-- OVERHEAD     12.5
-- TRANSFERRATE 0.7
