2013-01-08

Use db2pd to get table usage statistics

For example: Use this to get the top 5 tables in terms of rows inserted. 

$ db2pd -db ed1 -tcbstats | awk '/TCB Table Stats/ { found=1 } found==1 { print }' | awk '/^0x/ { print $11,$12,$13,$2 }' | sort -rn | head -5
66952378 0 66952378 SMENSAPT
50779560 11070 49599240 SYSCOLDIST
16985688 0 16985688 SMENSAPNEW
14499524 0 0 HIST_SNAPDBMEM
7983536 0 8017280 CCSELKEY

Where field: 2 = Table name 11 = Inserts 12 = Updates 13 = Deletes

2013-01-18

Use VARCHAR_FORMAT in DB2

Want to format output of date or timestamp column in DB2, use theVARCHAR_FORMAT function.
For example, this SQL will list the SAP tables and date statistics were last collected: 

select VARCHAR_FORMAT(STATS_TIME, 'YYYYMMDD') as stats_day,count(*) from syscat.tables 
where tabschema='SAPR3' 
and type = 'T'
group by VARCHAR_FORMAT(STATS_TIME, 'YYYYMMDD')
order by 1 asc