Checking for unavailable tables on the HADR standby

It is possible for a table to become inoperative on the Standby database, while the table remains fully available on the Primary database. This can happen when non-logged operations are performed on the Primary database, as described in the Knowledge Center BLOCKNONLOGGED config parameter.


When the Standby database is configured with Reads-On-Standby, unavailable or inoperative tables can be determined by querying the AVAILABLE column of the ADMIN_GET_TAB_INFO table function:

] [Standby]$ db2 "select TABSCHEMA, TABNAME, TABTYPE, AVAILABLE from TABLE(ADMIN_GET_TAB_INFO(null, null)) where AVAILABLE='N' " TABSCHEMA TABNAME TABTYPE AVAILABLE --------- -------- ------- -------- MYSCHEMA1 MYTABLE3 T N [] If the Standby database is not configured with Reads-On-Standby, unavailable or inoperative tables can be determined using the [db2dart and db2pd tools.
You need to use both commands because each provides a potentially incomplete picture of the table state: db2dart reads only changes that are on disk, and db2pd can report only the information about tables that are loaded into memory.


1. On the primary, query the SYSCAT.TABLES catalog view to obtain a list of table space and object IDs that you are interested in:
SELECT TBSPACEID, TABLEID, TABSCHEMA, TABNAME from SYSCAT.TABLES WHERE TABSCHEMA='<schemaName>' and TABNAME=’<tableName>’

2. Verify the table states of the tables on disk. On the standby, issue the db2dart command with the /T action, specifying the table space and object IDs you obtained in step 1:
db2dart <dbName> /T /TSI <tablespaceID> /OI <objectID>

If any tables are in the drop-pending state, a warning is returned, as shown in the following example output:
Table inspection start: AMYTANG.T2

Data inspection phase start. Data obj: 10 In pool: 3
Warning: Data object in drop-pending (unavailable) state,
errors reported for this object may be due to this state.
Data inspection phase end.

…

Table inspection end.

If you are using the Version 9.7 fix pack 5 or newer versions of the DB2 product, there are three additional refinements you can make to your db2dart command to speed up the process:

· Specify the quick option to skip unnecessary checks (for the purposes of the current procedure) and make run db2dart faster:
db2dart <dbName> /T /TSI <tablespaceID> /OI <objectID> /QCK 15

· Specify multiple object IDs to avoid issuing a db2dart command for each table:
db2dart <dbName> /T /TSI <tablespaceID> /OI <objectID1>, <objectID2>, <objectIDx> /QCK 15

· If iterating all tables (as mentioned in the previous bullet) is not ideal, you can also run db2dart against the table space that contains the interested tables with the quick option:
db2dart <dbName> /TS /TSI <tablespaceID> /QCK 15

Note: Because the db2dart command is run with a database connection, the report might contain false positives, such as orphaned extents, number of records mismatched, etc. The db2dart command reports these false positives because the pages are read from disk but changes on the HADR standby are still in the buffer pool. For the purposes of this procedure, disregard any warning messages that do not pertain to the "drop-pending (unavailable) state."

3a. Verify the table states of the tables in memory. On the standby, issue the db2pd command with the –dmsdbcb option:
db2pd –db <dbName> -dmsdbcb

The –dmsdbcb option (an intentionally undocumented option) generates a binary file that needs to be submitted to IBM service, who will format it for you. If you are using Version 9.7 fix pack 6 or newer versions of the DB2 product and you have a DB2 service password, you can format the results yourself using the following command:

db2pd –db <dbName> -dmsdbcb –service <db2_service_passwd>

3b. Search the formatted output that you obtained in step 3a for the object description for the table space and object ID of interest to see if its state indicates that the table is unavailable. If the object state, which is indicated by the odhObjectState command, has the x0800 bit turned on, then the table is not available, as shown in the following example:

SQLD_TCB: db2fdump pointer:0x2aaad6758080, Size:0x748(1864)
Table(2:5)=TBSPACEID=2.TABLEID=5

SQLD_COLUMN: db2fdump pointer:(nil), Size:0x8(8)
x0110 col_info NULL
x0118 reorgCB NULL
x0120 olrCB NULL
x0128 reorgMonData NULL
x0130 dataDesc
SQLB_OBJECT_DESC: db2fdump pointer:0x2aaad67581b0, Size:0x58(88)
x0000 odHead
SQLB_OBJECT_DESC_HEAD: db2fdump pointer:0x2aaad67581b0, Size:0x38(56)
x0000 odhObjID {TBSPACEID=<2>; OBJECTID=<5>; OBJECTTYPE=<DAT>} Parent object = {TBSPACEID=<2>; OBJECTID=<5>}
x000C odhExtentAnchor x000000A0
x0010 odhLifeLSN 0000000002B281AC
x0018 odhInitEmpPages 0
x001C odhPoolPage0 x00000000
x0020 odhPoolflags x00003122
x0024 odhObjectState x00000827
x0028 odhLastSMP x00000000


If you find a warning message (in step 2) or if the object state has the 0x0800 bit turned on (in step 3), then the indicated table or tables are in a drop-pending state on the HADR standby and will not be available if the standby takes over as the new HADR primary.