Tables, Indexes, Partitions, Objects

Show table size and organization (10.5) :

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"

Show table size and organization ( > 9.7 ) :

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"

Table size (old and slow) :

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"

Query table space of particular tables :

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')"

Show me dependent objects on TABLE :

db2 "call dbms_utility.get_dependency('TABLE',<tabschema>,<tabname>)"

Show me tables with LOB/XML columns :

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"

RR :

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"

Find package for stored procedure :

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"

Rebind packages for procedures :

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'

Extract DDL for existing view :

db2 "select listagg(column_name,',') within group(order by ordinal_position) from sysibm.columns where table_name = 'VIEW1' group by table_name"

Generate DDL for table :

db2look -d MY_DDBB -t DEPT -a -e -x -o FILE_OUT.txt

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

Show invalid objects :

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"

Soft invalidation (ON by default) :

db2set DB2_DDL_SOFT_INVAL

Revalidate invalid objects :

db2 "ADMIN_REVALIDATE_DB_OBJECTS"

Create/enable row compression :

db2 "create table <TABLE> ... COMPRESS YES"
# initialize compression
db2 "reorg table <TABLE> RESETDICTIONARY"

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

Inspect table for estimated row compression :

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

Compression estimation :

SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('<SCHEMA>, '<TABLE>'))

Asynchronous index cleanup for MDC tables :

db2set DB2_MDC_ROLLOUT=DEFER
#or 
SET CURRENT MDC ROLLOUT MODE

Index usage :

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"

Index keysize vs. table row size :

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"

Not supported in block indexes and XML path indexes Index specifications cannot be compressed Compression attributes for indexes on temp. tables cannot be altered

Enable Index compression :

db2 "create index <INDEX> ... COMPRESS YES"
db2 "alter index <INDEX> ... COMPRESS YES"

# enable compression by reorganization of the index

Disable index compression :

db2 "alter index <INDEX> ... COMPRESS NO"

# disable compression by reorganization of the index

INVALID indexes :

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"

IX_STATS :

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"

Query dependencies for MQT :

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'"

MQT Staging tables :

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

Monitor reorg progress :

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"

Check for automatic runstats/reorgs of all DBs:

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

Generate scripts for reorg of tables, indexes and runstats :

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

Statistical Views :

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

create view .. enable for optimization

Enable it for runstats

REORG for NON-SYS :

db2 -x "select 'REORG TABLE ' ||rtrim (tabschema)|| '.' || rtrim (tabname) || ' ;' from syscat.tables where tabschema not like 'SYS%' and type = 'T'"

RUNSTATS for NON-SYS :

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

Reorgchk :

db2 "select  substr(table_schema,1,18) as tabschema, substr(table_name,1,30) as tabname, f1, f2, f3, reorg from SESSION.TB_STATS"

Offline reorg :

1. Call reorgchk procedure to check the statistics :

db2 "call sysproc.reorgchk_tb_stats('T','ALL')"

2. Generate reorg script for needed tables (* in F1/F2 or F3 formulas) :

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

3. Generate reorg script for optional tables (nothing in F1/F2 or F3 formulas) :

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

4. Generate runstats script for all reorganized tables

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

5. Launch the scripts

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

6. Rebind all packages (valid and invalid)

db2rbind <DBNAME> -l rebind_all.out all

Monitor progress of Reorg :

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"

or

db2pd -d <DBNAME> -reorg

Check for table partitions :

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"

Add new partition :

db2 "alter table DWCO.TBWCFPAG add partition "part2015" starting from ('2015-02-01') ending ('2016-01-31') in TSDATI78 index in TSIDX078"

Detach partition & delete :

db2 "alter table <TABNAME> detach partition <PARTITION> into <TARGETTABLE>"
db2 "drop table <TARGETTABLE>

Query constraints on tables :

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'"

Disable query optimization :

db2 "alter table <schema>.<CHILD TABLE> alter foreign key <FK NAME> disable query optimization"

Query violationg rows from child table (DISABLE QUERY OPTIMIZATION !):

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>)"

Enforce constraint :

db2 "alter table <SCHEMA>.<TABLE> alter foreign key <CONSTRAINT> enforced"

Query foreign keys :

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"

Generate statements to redefine RI from restrict to CASCADE :

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"

Recreate foreing keys as NOT ENFORCED :

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"

Check integrity pending states :

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'"

Export data :

db2 "export to <EXPORT>.ixf of ixf <QUERY>"

Load data from export :

db2 "load from <EXPORT>.ixf of ixf replace into <SCHEMA>.<TABLE>"

Table in Load Pending :

db2 "load from /dev/null of del terminate into <SCHEMA>.<TABLE> copy yes to /dev/null"

Generate SET INTEGRITY in correct chain order :

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

Load from cursor :

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>

Move table to different tablespace :

db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('CDCUSER','TS_DDLAUD','TS_32K_CDC','TS_32K_CDC','TS_32K_CDC','','','','','','MOVE')"

Generate the ADMIN_MOVE_TABLE commands :

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"

Check progress :

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
"

List tables with LOB columns :

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"

Evaluate tables for INLINE LOB columns :

Procedure to calculate percentile of column lenght in table for INLINing :

--#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 ;

Tables, Indexes, Partitions, Objects

Tables

Packages

Views

DDL

Invalid objects

Table compression

Index

Index compression

MQT

Runstats/Reorg

Partitions

Referential Integrity

Export/Import/Load/Ingest

ADMIN_MOVE_TABLE

LOBS

My wiki