IBM Support

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

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  

 

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13286845