这个shell脚本   creates an  output file with DB2实例的每个数据库上的每个表大小 .

列为：TABSCHEMA，TABNAME，DATA_OBJECT_P_SIZE， INDEX_OBJECT_P_SIZE，LONG_OBJECT_P_SIZE，  LOB_OBJECT_P_SIZE  ,  XML_OBJECT_P_SIZE 

 该脚本检查是否为db2 hadr PRIMARY数据库。如果是db2 hadr STANDBY数据库，它将跳过。

所有大小均以千字节为单位

通过放置在shell脚本中使用。叫它  “ 表_sizes_all_dbs.sh”  and execute by :

./table_sizes_all_dbs.sh

执行权限是  required ，并为该文件夹写权限 

----------------------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 在  `db2 list db directory | grep 'Database name' | awk '{print $4}'`;

do

 echo "beginning rowcount of all 表s 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-------------------------------------------------

阅读更多

DB2 - 桌子尺寸 -DBA DB2

数据库 尺寸 在DB2中-DBA DB2

DB2 表 重组和 表 截断-DBA DB2