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 :
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 .. enable for optimization
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 ;