Technical Blog Post
Abstract
75 ways to demystify DB2 #70: Techtip :How to check the status of table which was altered with not logged initially on primary after takeover in HADR environment
Body
Abstract:
On primary database, if some tables were altered with not logged initially, after takeover, how to figure out which tables are currently under inaccessible state?
DB2 *does not* record the status of tables altered with not logged initially in catalog tables and query for status in syscat.tables is always NORMAL, thus the status of these tables cannot be queried by SQL.
Solution:
One solution is using "load query table" command to check the status of the table.
Below is a shell script to check all the inaccessible table status for the specific schema.
Usage:
sh query.sh <dbname> <shcemaname>
cat query.sh
++++++++++++++++++++++++++++++++++++++
if [[ ($# < 2)]]
then
echo "Usage: $0 <dbname> <schemaname>"
echo
exit
fi
DB=$1
myschema="$(echo $2 | tr '[:lower:]' '[:upper:]')"
db2 connect to $DB
db2 "select tabname from syscat.tables where tabschema='$myschema'" |wc -l > NLItemp
file="./NLItemp"
while read line
do
totalline=$line
done<$file
newline=`expr $totalline - 2`
db2 "select tabname from syscat.tables where tabschema='$myschema'" | awk '{if(NR>3&&NR<"'$newline'") print $1}' >NLItemp
while read line
do
db2 load query table $line | awk '{ if($1=="Unavailable") {printf("
'$line' : unaccessable\n")} }'
done <$file
rm ./NLItemp
++++++++++++++++++++++++++++++++++++++
Testing results:
Tables T1, T2 and T5 were altered with not logged initially on primary database, after issuing takeover on standby database, all the tables can be queried by the script.
$ sh query.sh hadrdb inst1
Database Connection Information
Database server = DB2/AIX64 9.7.4
SQL authorization ID = INST1
Local database alias = HADRDB
T1 : unaccessable
T2 : unaccessable
T5 : unaccessable
UID
ibm13286845