DB2 table size shell script

This shell script   creates an  output file with every table size on every database of a DB2 instance .

The columns are : TABSCHEMA, TABNAME, DATA_OBJECT_P_SIZE ,  INDEX_OBJECT_P_SIZE , LONG_OBJECT_P_SIZE ,  LOB_OBJECT_P_SIZE  ,  XML_OBJECT_P_SIZE 

 The script checks to see if it’s a db2 hadr PRIMARY database . If it’s a db2 hadr STANDBY database it skips over.

All sizes are in kilobytes

Use by placing in a shell script. Call it   “table_sizes_all_dbs.sh”  and execute by :

./table_sizes_all_dbs.sh

Execute permissions are  required  , and write permissions for the folder 

----------------------SCRIPT START ----------------------------------

 TIMESTAMP="`date +%Y-%m-%d-%H:%M:%S`"

User="$(id -un)"

Hostname="$(hostname -s)"

Server_instance=$Hostname"_"$User

working_dir=$PWD

logfile="TABLE_SIZES_$Server_instance$TIMESTAMP.log"

touch $working_dir/$logfile

>$working_dir/$logfile

echo "ALL SIZES IN KILOBYTES"   >> $working_dir/$logfile;

 for i in `db2 list db directory | grep 'Database name' | awk '{print $4}'`;

do

 echo "beginning rowcount of all tables for $i"   >> $working_dir/$logfile

 state=$(db2 get db cfg for $i | grep 'HADR database role' | awk '{print $5}');

if [ $state = "STANDBY"  ]

then

echo "THIS DATABASE IS THE STANDBY, THIS OPERATION SHOULD BE PERFORMED AT THE PRIMARY"

else

db2 connect to $i >> $working_dir/$logfile;

db2 "SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,SUBSTR(TABNAME,1,30) TABNAME,SUM(DATA_OBJECT_P_SIZE) DATA_OBJECT_P_SIZE,SUM(INDEX_OBJECT_P_SIZE) INDEX_OBJECT_P_SIZE,SUM(LONG_OBJECT_P_SIZE) LONG_OBJECT_P_SIZE,SUM(LOB_OBJECT_P_SIZE) LOB_OBJECT_P_SIZE,SUM(XML_OBJECT_P_SIZE) XML_OBJECT_P_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE 'SYS%' GROUP BY TABSCHEMA, TABNAME"  >> $working_dir/$logfile;

db2 terminate >> $working_dir/$logfile;

 fi

 echo "";

done

 --------------------------------SCRIPT FINISH-------------------------------------------------

Read More

DB2 - table sizes - DBA DB2

Database size in DB2 - DBA DB2

DB2 table reorg and table truncate - DBA DB2