how to detect unavailable tables?

[Jenniferdb2]

We recently identified a table on a primary HADR database that had been
loaded with the parm "nonrecoverable " thus the table was unavailable to
the HADR standby copy. The table was identified by the diag log message
ADM5571. After we resolved the problem (resync'ing the HADR standby) we
have been investigating the best way to report on any tables that may
become "unavailabl e" to the HADR standby database. The main reason for
the concern is that we have a lot of tables in one database that have
been defined as not logged initially and we need to verify that there
HADR standby copy for these tables is available.

There does not appear to be any one command that verifies the
availability of the HADR standby tables. The only way to regenerate the
ADM5571 message is to reorg the HADR primary tables and then review the
diag log on the standby, searching for the ADM5571 msg.

Is there a way to identify unavailable tables on the HADR standby
database without having to failover or reorg the primary tables?

1) we cannot use db2look as it don't record the ALTERs with NLI and we have many tables which are altered after some time of creation.
2) we are on v9.1 aix 5.3
so newly (v9.5fp4) introduced para BLOCKNONLOGGED is not helpful.

-TAC

[vijay2082]

Probably you can create a script as below and run the desired schema.

db2 -x "select 'load query table
'||ltrim(rtrim( tabschema))||'. '||ltrim(rtrim( tabname))|| ' ;' from
syscat.tables where TYPE='T' and tabschema='DB2A DMIN' with ur" >
chk_tbl.sql

db2 -tvf chk_tbl.sql -z out_tbl.txt


Look for output like :

load query table DB2ADMIN.MYTABL E1
Tablestate:
Unavailable


Cheers, Vijay