====== Tables, Indexes, Partitions, Objects ======

===== Tables =====

**Show table size and organization (10.5) :**
<sxh bash>
db2 "select varchar(t.tabschema,15) as tabschema, varchar(t.tabname,40) as tabname
, COL_OBJECT_P_SIZE/1024 as col_size_mb
, DATA_OBJECT_P_SIZE/1024 as data_size_mb
, INDEX_OBJECT_P_SIZE/1024 as index_size_mb
, (LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 as lob_size_mb
, tableorg from syscat.tables t join sysibmadm.admintabinfo ati on t.tabname=ati.tabname and t.tabschema=ati.tabschema where t.type='T'
and t.tabschema not like ('SYS%') order by 3 desc with ur"
</sxh>

**Show table size and organization ( > 9.7 ) :**
<sxh bash>
db2 "select  substr(t.tabschema,1,18) as tabschema
        , substr(t.tabname,1,40) as tabname
        , DATA_OBJECT_P_SIZE/1024 as tab_size_mb
	, INDEX_OBJECT_P_SIZE/1024 as idx_size_mb
from    syscat.tables t
        join sysibmadm.admintabinfo ati
                on t.tabname=ati.tabname
                and t.tabschema=ati.tabschema
where   t.type='T'
        and t.tabschema not like ('SYS%')
order by 3 desc
with ur"
</sxh>

**Table size (old and slow) :**
<sxh bash>
db2 "SELECT SUBSTR(TabSchema,1,15) TabSchema, SUBSTR(TabName,1,15), TabType, 
(Data_Object_P_Size + Index_Object_P_Size + Long_Object_P_Size + Lob_Object_P_Size + Xml_Object_P_Size)/1024 as Total_P_Size_MB
FROM SysIbmAdm.AdminTabInfo ORDER BY Total_P_Size_MB"
</sxh>

**Query table space of particular tables :**
<sxh bash>
db2 "select a.TBSPACEID,substr(b.TBSP_NAME,1,40) TBSP_NAME, substr(a.tabname,1,20) TABNAME 
from syscat.tables a join SYSIBMADM.SNAPTBSP b on a.TBSPACEID = b.TBSP_ID  
where a.tabschema = 'DB2INST1' and a.tabname in ('TBS1TB1','TBS1TB2','TBS2TB1')"
</sxh>

**Show me dependent objects on TABLE :**
<sxh bash>
db2 "call dbms_utility.get_dependency('TABLE',<tabschema>,<tabname>)"
</sxh>

**Show me tables with LOB/XML columns :**
<sxh bash>
db2 "select varchar(tabname,20) table,varchar(colname,30) column,varchar(typename,20) type from syscat.columns where tabschema = 'DATAUSER' and typename like '%LOB%' or typename like '%XML' order by tabname with ur"
</sxh>

**RR :**
<sxh bash>
db2 "SELECT current timestamp, varchar(tabschema,20) as tabschema, 
       varchar(tabname,20) as tabname, 
       sum(rows_read) as total_rows_read, 
       sum(rows_inserted) as total_rows_inserted, 
       sum(rows_updated) as total_rows_updated, 
       sum(rows_deleted) as total_rows_deleted,
       sum(table_scans) as total_table_scans
FROM TABLE(MON_GET_TABLE('MAXIMO','MP2INT_BUFFER',-2)) AS t 
GROUP BY tabschema, tabname 
ORDER BY total_rows_read DESC"



db2 "SELECT current timestamp, VARCHAR(S.INDSCHEMA, 20) AS INDSCHEMA, 
    VARCHAR(S.INDNAME, 20) AS INDNAME, 
    T.DATA_PARTITION_ID, 
    T.MEMBER, 
    T.INDEX_SCANS, 
    T.INDEX_ONLY_SCANS
  FROM TABLE(MON_GET_INDEX('MAXIMO','MP2INT_BUFFER', -2)) as T, SYSCAT.INDEXES AS S 
  WHERE T.TABSCHEMA = S.TABSCHEMA AND 
    T.TABNAME = S.TABNAME AND 
    T.IID = S.IID   
  ORDER BY INDEX_SCANS DESC"
</sxh>

===== Packages =====
** Find package for stored procedure :**
<sxh bash>
db2 "select r.routineschema, r.routinename, rd.bschema as packageschema, rd.bname as packagename from syscat.routines r, syscat.routinedep rd where r.specificname=rd.specificname and r.routineschema=rd.routineschema and rd.btype='K' and r.routineschema = 'DATAUSER' and upper(r.routinename) = 'REVERSEINVENTORY' with ur"
</sxh>


** Rebind packages for procedures : **
<sxh bash>
db2 -x "select distinct 'rebind package ' || rtrim(rd.bschema) || '.' || rtrim(rd.bname) || ';' from syscat.routines r, syscat.routinedep rd where r.specificname=rd.specificname and r.routineschema=rd.routineschema and rd.btype='K' and r.routineschema = 'DATAUSER' with ur" | sed 's/  //g'
</sxh>
===== Views =====

**Extract DDL for existing view : **
<sxh bash>
db2 "select listagg(column_name,',') within group(order by ordinal_position) from sysibm.columns where table_name = 'VIEW1' group by table_name" 
</sxh>

==== DDL ====
**Generate DDL for table :**
<sxh bash>
db2look -d MY_DDBB -t DEPT -a -e -x -o FILE_OUT.txt
</sxh>

==== Invalid objects ====
Admin View : SYSCAT.INVALIDOBJECTS : For Finding Invalid DB Objects.
Admin Proc : SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS : For Fixing Invalid DB Objects.

**Show invalid objects :**
<sxh bash>
db2 "select substr(objectschema,1,20) SCHEMA, substr(objectname,1,70) OBJECT, case objecttype when 'B' then 'Trigger' when 'F' then 'Routine' when 'R' then 'User datatype' when 'V' then 'View' when 'v' then 'Global var' when 'y' then 'Row perm.' when 2 then 'Col. mask' when 3 then 'Usage list' end as Type from syscat.invalidobjects with ur"
</sxh>

**Soft invalidation (ON by default) :**
<sxh bash>
db2set DB2_DDL_SOFT_INVAL
</sxh>

**Revalidate invalid objects :**
<sxh bash>
db2 "ADMIN_REVALIDATE_DB_OBJECTS"
</sxh>

==== Table compression ====

**Create/enable row compression :**
<sxh bash>
db2 "create table <TABLE> ... COMPRESS YES"
# initialize compression
db2 "reorg table <TABLE> RESETDICTIONARY"

#keep the compression
db2 "reorg table <TABLE> KEEPDICTIONARY
</sxh>

**Inspect table for estimated row compression :**
<sxh bash>
db2 "inspect rowcompestimate table name <TABLE> schema <SCHEMA> results keep inspect_<TABLE>.out"

# format the output to readable form by :
db2inspf inspect_<TABLE>.out db2inspect_out.txt
</sxh>

**Compression estimation :**
<sxh bash>
SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('<SCHEMA>, '<TABLE>'))
</sxh>


===== Index =====
**Asynchronous index cleanup for MDC tables :**
<sxh bash>
db2set DB2_MDC_ROLLOUT=DEFER
#or 
SET CURRENT MDC ROLLOUT MODE
</sxh>

**Index usage :**
<sxh bash>
db2 "SELECT VARCHAR(S.INDSCHEMA, 20) AS INDSCHEMA, VARCHAR(S.INDNAME, 20) AS INDNAME,T.INDEX_SCANS,T.INDEX_ONLY_SCANS FROM TABLE(MON_GET_INDEX('MM_EDW','ACQ_GG', -2)) as T,SYSCAT.INDEXES AS S WHERE T.TABSCHEMA = S.TABSCHEMA AND T.TABNAME = S.TABNAME AND T.IID = S.IID ORDER BY INDEX_SCANS DESC with ur"
</sxh>

**Index keysize vs. table row size :**
<sxh bash>
db2 "select
        substr(t.TABNAME,1,12) as tabname
        , substr(i.INDNAME,1,20) as indnameA
        , AVGROWSIZE as RSIZE
        , AVGLEAFKEYSIZE as KSIZE
        , 100*(DECIMAL((FLOAT(AVGLEAFKEYSIZE)/FLOAT(AVGROWSIZE)),7,5)) as PCT_OF_ROWSIZE
from syscat.indexes i, syscat.tables t
where i.tabschema=t.tabschema and i.tabname=t.tabname
        and type='T'
        and AVGROWSIZE>0
        and t.tabname='STOCK_GG_FACT_NEW'
        and t.tabschema='MM'
order by PCT_OF_ROWSIZE desc
with ur"
</sxh>

==== Index compression ====
<WRAP center round tip 100%>
Not supported in block indexes and XML path indexes
Index specifications cannot be compressed
Compression attributes for indexes on temp. tables cannot be altered
</WRAP>

**Enable Index compression :**
<sxh bash>
db2 "create index <INDEX> ... COMPRESS YES"
db2 "alter index <INDEX> ... COMPRESS YES"

# enable compression by reorganization of the index
</sxh>

**Disable index compression :**
<sxh bash>
db2 "alter index <INDEX> ... COMPRESS NO"

# disable compression by reorganization of the index
</sxh>

**INVALID indexes :**
<sxh bash>
db2 "select varchar(tabname,25) table, varchar(indname,25) index, index_requires_rebuild from table(sysproc.admin_get_index_info('','MM','STOCK_FI_MESE_ST_FACT')) as t with ur"
</sxh>

**IX_STATS :**
<sxh bash>
db2 "select varchar(index_name,20) index,INDCARD,NLEAF,NUM_EMPTY_LEAFS,NLEVELS,NUMRIDS_DELETED,FULLKEYCARD,LEAF_PAGE_OVERHEAD,F4,F5,F6,F7,F8, REORG from session.ix_stats"
</sxh>

===== MQT =====
**Query dependencies for MQT :**
<sxh bash>
db2 "select substr(tabname,1,24) as tabname, dtype, substr(bname,1,24) as bname, btype from syscat.tabdep where tabschema = 'MELNYK' and dtype = 'S'"
</sxh>

**MQT Staging tables **:
  - Create MQT (data initially deffered refresh deffered);
  - create table stage for mqt propagate immediate in <TS>;

===== Runstats/Reorg =====
**Monitor reorg progress :**
<sxh bash>
db2 "select
       varchar(tabname, 40) as tab_name,
       reorg_phase,
       substr(reorg_type, 1, 70) as reorg_type,
       reorg_status,
       reorg_completion
     from sysibmadm.snaptab_reorg
     order by tabname with ur"
</sxh>
**Check for automatic runstats/reorgs of all DBs:**
<sxh bash>
for db in `db2 list db directory | grep -p Indirect| grep alias | awk '{print $NF}'`; do echo "------ $db ------" && db2 get db cfg for $db | egrep "AUTO_RUNSTATS|AUTO_REORG|AUTO_TBL_MAINT|AUTO_MAINT" && echo && echo; done
</sxh>

**Generate scripts for reorg of tables, indexes and runstats** :
<sxh bash>
db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG TABLE /g' | sed 's/$/;/g' > /tmp/reorg_tbl.ddl
db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG INDEXES ALL FOR TABLE /g' | sed 's/$/;/g' > /tmp/reorg_idx.ddl
db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/RUNSTATS ON TABLE /g' | sed 's/$/ WITH DISTRIBUTION AND DETAILED INDEXES ALL;/g' > /tmp/runstats_tbl.ddl

db2 -tvf /tmp/reorg_tbl.ddl
db2 -tvf /tmp/reorg_idx.ddl
db2 -tvf /tmp/runstats_tbl.ddl
</sxh>

**Statistical Views :**
  * Create view for statistics which matches the Join query
  * Cannot have : aggregations or distinct, 

<sxh bash>
create view .. enable for optimization
</sxh>

  * Enable it for runstats

**REORG for NON-SYS :**
<sxh bash>
db2 -x "select 'REORG TABLE ' ||rtrim (tabschema)|| '.' || rtrim (tabname) || ' ;' from syscat.tables where tabschema not like 'SYS%' and type = 'T'"
</sxh>

**RUNSTATS for NON-SYS :**
<sxh bash>
db2 -x "select 'RUNSTATS ON TABLE ' ||rtrim (tabschema)|| '.' || rtrim (tabname) || ' WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;' from syscat.tables where tabschema not like 'SYS%' and type = 'T'" > runstats.sql
</sxh>

**Reorgchk : **
<sxh bash>
db2 "select  substr(table_schema,1,18) as tabschema, substr(table_name,1,30) as tabname, f1, f2, f3, reorg from SESSION.TB_STATS"
</sxh>

**Offline reorg :**

1. Call reorgchk procedure to check the statistics :
<sxh bash>
db2 "call sysproc.reorgchk_tb_stats('T','ALL')"
</sxh>

2. Generate reorg script for needed tables (* in F1/F2 or F3 formulas) :
<sxh bash>
db2 -x "select 'reorg table ' || rtrim(table_schema) || '.' || rtrim(table_name) || ';' from session.tb_stats where table_schema not like 'SYS%' and reorg like '%*%' order by reorg" > reorg_needed.sql
</sxh>

3. Generate reorg script for optional tables (nothing in F1/F2 or F3 formulas) :
<sxh bash>
db2 -x "select 'reorg table ' || rtrim(table_schema) || '.' || rtrim(table_name) || ';' from session.tb_stats where table_schema not like 'SYS%' and reorg not like '%*%' order by reorg" > reorg_optional.sql
</sxh>


4. Generate runstats script for all reorganized tables
<sxh bash>
db2 -x "select 'runstats on table ' || rtrim(table_schema) || '.' || rtrim(table_name) || ' with distribution on all columns and detailed indexes all;' from session.tb_stats where table_schema not like 'SYS%' order by reorg" > runstats_all.sql
</sxh>

5. Launch the scripts
<sxh bash>
db2 -tvf reorg_needed.sql | tee -a reorg_needed.out
db2 -tvf reorg_optional.sql | tee -a reorg_optional.out
db2 -tvf runstats_all.sql | tee -a runstats_all.out
</sxh>

6. Rebind all packages (valid and invalid)
<sxh bash>
db2rbind <DBNAME> -l rebind_all.out all
</sxh>

**Monitor progress of Reorg :**
<sxh bash>
db2 "select substr(tabname, 1, 15) as tab_name, substr(tabschema, 1, 15) as tab_schema, reorg_phase,
substr(reorg_type, 1, 20) as reorg_type, reorg_status, reorg_completion, dbpartitionnum
from sysibmadm.snaptab_reorg order by dbpartitionnum"
</sxh>

or 

<sxh bash>
db2pd -d <DBNAME> -reorg
</sxh>

===== Partitions =====
**Check for table partitions :**
<sxh bash>
db2 "select substr(DATAPARTITIONNAME,1,20) as partition_name, substr(TABSCHEMA,1,8) as tabschema, substr(TABNAME,1,15) as tabname, 
(select substr(tbsp_name,1,15) from sysibmadm.snaptbsp where tbsp_id=TBSPACEID) as TABLE_TBSP, (select substr(tbsp_name,1,15) from sysibmadm.snaptbsp where tbsp_id=INDEX_TBSPACEID) as INDEX_TBSPACE, (select substr(tbsp_name,1,15) from sysibmadm.snaptbsp where tbsp_id=LONG_TBSPACEID) as LONG_TBSP, substr(lowvalue,1,10) as lowvalue, substr(highvalue,1,10) as highvalue, LOWINCLUSIVE, HIGHINCLUSIVE, card from SYSCAT.DATAPARTITIONS where tabname = 'TBWCSATS' order by DATAPARTITIONNAME"
</sxh>

**Add new partition :**
<sxh bash>
db2 "alter table DWCO.TBWCFPAG add partition "part2015" starting from ('2015-02-01') ending ('2016-01-31') in TSDATI78 index in TSIDX078"
</sxh>

**Detach partition & delete :**
<sxh bash>
db2 "alter table <TABNAME> detach partition <PARTITION> into <TARGETTABLE>"
db2 "drop table <TARGETTABLE>
</sxh>

===== Referential Integrity =====
**Query constraints on tables :**
<sxh bash>
db2 "select char(constname,30) const, char(tabschema,8) schema,char(tabname,30) table,char(REFTABSCHEMA,8) schem,char(REFTABNAME,30) reftab,char(FK_COLNAMES,30) refCOL,char(PK_COLNAMES,30) pkCOL from syscat.references where CONSTNAME = 'F_1145'"
</sxh>

**Disable query optimization :**
<sxh bash>
db2 "alter table <schema>.<CHILD TABLE> alter foreign key <FK NAME> disable query optimization"
</sxh>

**Query violationg rows from child table (DISABLE QUERY OPTIMIZATION !):**
<sxh bash>
db2 "(SELECT C.<COLUMN> FROM <CHILD SCHEMA>.<CHILD TABLE> C) EXCEPT (SELECT P.<COLUMN> FROM <PARENT SCHEMA>.<PARENT TABLE> P, <CHILD SCHEMA>.<CHILD TABLE> C WHERE P.<COLUMN> = C.<COLUMN>)"
</sxh>

**Enforce constraint :**
<sxh bash>
db2 "alter table <SCHEMA>.<TABLE> alter foreign key <CONSTRAINT> enforced"
</sxh>

**Query foreign keys :**
<sxh bash>
db2 "select 
	substr(constname,1,30) constname
	, substr(tabschema,1,18) tabschema
	, substr(tabname,1,20) tabname
	, deleterule 
from syscat.references 
where deleterule != 'C'
with ur"
</sxh>

**Generate statements to redefine RI from restrict to CASCADE :**
<sxh bash>
db2 "select  'alter table ' || tabschema || '.'
        || TABNAME
        || ' drop constraint '
        || CONSTNAME
        || ';'
        || CHR(10)
        || 'alter table ' || tabschema || '.'
        || TABNAME
        || ' add constraint '
        || CONSTNAME
        || ' foreign key ('
        || (select
                SUBSTR(XMLCAST(XMLGROUP(',' || colname AS a) AS VARCHAR(60)), 2)
                from syscat.keycoluse k
                where k.constname=r.constname
            and k.tabschema=r.tabschema
            and k.tabname=r.tabname)
        || ') references ' || tabschema || '.'
        || REFTABNAME
        || ' ('
        || (select
                SUBSTR(XMLCAST(XMLGROUP(',' || colname AS a) AS VARCHAR(60)), 2)
                from syscat.keycoluse k
                where k.constname=r.refkeyname
            and k.tabschema=r.reftabschema
            and k.tabname=r.reftabname)
        || ') on delete cascade;'
from syscat.references r
where DELETERULE != 'C'
and constname in ('FK_GS_CATENT','FK_XDESC_CATENT')
order by TABNAME
with ur"
</sxh>

**Recreate foreing keys as NOT ENFORCED :**
<sxh bash>
db2 "select  'alter table ' || tabschema || '.'
        || TABNAME
        || ' drop constraint '
        || CONSTNAME
        || ';'
        || CHR(10)
        || 'alter table ' || tabschema || '.'
        || TABNAME
        || ' add constraint '
        || CONSTNAME
        || ' foreign key ('
        || (select
                SUBSTR(XMLCAST(XMLGROUP(',' || colname AS a) AS VARCHAR(60)), 2)
                from syscat.keycoluse k
                where k.constname=r.constname
            and k.tabschema=r.tabschema
            and k.tabname=r.tabname)
        || ') references ' || tabschema || '.'
        || REFTABNAME
        || ' ('
        || (select
                SUBSTR(XMLCAST(XMLGROUP(',' || colname AS a) AS VARCHAR(60)), 2)
                from syscat.keycoluse k
                where k.constname=r.refkeyname
            and k.tabschema=r.reftabschema
            and k.tabname=r.reftabname)
        || ') on delete cascade not enforced;'
from syscat.references r
where tabschema = 'CTGINST1'
order by TABNAME
with ur"
</sxh>
===== Export/Import/Load/Ingest =====
**Check integrity pending states :**
<sxh bash>
db2 "select tabname,
substr(const_checked,1,1)
substr(const_checked,2,1)
substr(const_checked,5,1)
substr(const_checked,6,1)
substr(const_checked,7,1)
from syscat.tables
where status='C'"
</sxh>

**Export data :**
<sxh bash>
db2 "export to <EXPORT>.ixf of ixf <QUERY>"
</sxh>

**Load data from export :**
<sxh bash>
db2 "load from <EXPORT>.ixf of ixf replace into <SCHEMA>.<TABLE>"
</sxh>

**Table in Load Pending :**
<sxh bash>
db2 "load from /dev/null of del terminate into <SCHEMA>.<TABLE> copy yes to /dev/null"
</sxh>

**Generate SET INTEGRITY in correct chain order :**
<sxh bash>
db2 -x "with TABLIST(fullname,seqno) as
(select rtrim(tabschema)||'.'||tabname,
rownumber() over (order by tabschema||tabname)
from syscat.tables
where STATUS = 'C'),
SERIAL_LIST(s_fullname,s_seqno) as
(select long_varchar(rtrim(fullname)),
seqno
from tablist
where seqno = 1
UNION ALL
select long_varchar(rtrim(s_fullname||','||fullname)),
seqno
from tablist t, serial_list s
where s.s_seqno +1 = t.seqno )
select rtrim('set integrity for '|| s_fullname || ' immediate checked;')
from ( select s_fullname,
s_seqno,
rownumber() over (order by s_seqno desc) as rownum
from serial_list) as zzzzz
where rownum = 1" | egrep -v "SQL0347W|infinite" > SET_INTEGRITY_CHAIN.sql
</sxh>

**Load from cursor :**
<sxh bash>
db2 "declare REFRESH cursor database <DB> user <USER> using <PASSWORD> for select <SELECT STATEMENT>"
db2 load from TRANS of cursor messages </path/to/messages.log> replace into <SCHEMA>.<TABLE></sxh>

===== ADMIN_MOVE_TABLE =====
**Move table to different tablespace :**
<sxh bash>
db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('CDCUSER','TS_DDLAUD','TS_32K_CDC','TS_32K_CDC','TS_32K_CDC','','','','','','MOVE')"
</sxh>

**Generate the ADMIN_MOVE_TABLE commands :**
<sxh bash>
db2 "with dropp (cmd) as (select  'alter table ' || r.tabschema || '.'
        || r.TABNAME
        || ' drop constraint '
        || r.CONSTNAME
        || ';'
        || CHR(10)
        || 'alter table ' || r.tabschema || '.'
        || r.TABNAME
        || ' add constraint '
        || r.CONSTNAME
        || ' foreign key ('
        || (select
                SUBSTR(XMLCAST(XMLGROUP(',' || colname AS a) AS VARCHAR(60)), 2)
                from syscat.keycoluse k
                where k.constname=r.constname
            and k.tabschema=r.tabschema
            and k.tabname=r.tabname)
        || ') references ' || tabschema || '.'
        || r.REFTABNAME
        || ' ('
        || (select
                SUBSTR(XMLCAST(XMLGROUP(',' || colname AS a) AS VARCHAR(60)), 2)
                from syscat.keycoluse k
                where k.constname=r.refkeyname
            and k.tabschema=r.reftabschema
            and k.tabname=r.reftabname)
        || ') on delete ' 
	|| decode(deleterule, 'C', 'cascade', 'A', 'no action') as cmd
from syscat.references r
where reftabname in ('PX_PROMOTION')
order by TABNAME
with ur) select * from dropp"
</sxh>

**Check progress :**
<sxh bash>
db2 "with t1 as (select char (tabschema,30) as schema, char(tabname,60) as tabname,
       key, char(value,20) as value
       from systools.admin_move_table
       where key='STATUS')
select schema, tabname, value as status
    , case when value='COPY'
        then (select char(value,20) from systools.admin_move_table amt2 where t1.schema=amt2.tabschema and t1.tabname=amt2.tabname and key='COPY_TOTAL_ROWS')
        when value='REPLAY'
        then (select char(value,20) from systools.admin_move_table amt2 where t1.schema=amt2.tabschema and t1.tabname=amt2.tabname and key='REPLAY_TOTAL_ROWS')
    end as tot_rows
    from t1
with ur
"
</sxh>

===== LOBS =====

**List tables with LOB columns :**
<sxh bash>
db2 "select varchar(tbname,30) table, varchar(name,40) column from sysibm.syscolumns where tbcreator = 'DATAUSER' and coltype like '%LOB%' order by tbname with ur"
</sxh>

**Evaluate tables for INLINE LOB columns :**
<sxh bash>

</sxh>

**Procedure to calculate percentile of column lenght in table for INLINing :**
<sxh bash>
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE profile_lob
(tabschema VARCHAR(128),
 tabname   VARCHAR(128),
 colname   VARCHAR(128))
 BEGIN
  DECLARE txt  CLOB(128K) DEFAULT '';
  DECLARE res CURSOR WITH RETURN TO CLIENT FOR STMT;
  SET txt =
'SELECT MIN(len) AS "LENGTH(""' || colname || '"")", ' || 
'       percentile ' ||
'        FROM ' ||   
'(SELECT SMALLINT(TRUNCATE(DECFLOAT(ROW_NUMBER() OVER (ORDER BY LENGTH("' || colname || '") ' || 
'                                                                ASC NULLS FIRST)) ' ||
'                         * 200 / COUNT(*) OVER(), -1) / 2) AS percentile, ' ||
'       LENGTH("' || colname || '") AS len ' ||
'       FROM "' || tabschema || '"."' || tabname || '")' ||
'GROUP BY percentile ORDER BY percentile';
   PREPARE stmt FROM txt;
   OPEN res;
END
@
--#SET TERMINATOR ;
</sxh>