Table unavailable: ADM5571W  or SQL1477N  

When during a rollforward the message:

ADM5571W  The “DATA” object with ID “<table-ID>” in table space “<tablespace-ID>” for table “TBSPACEID=<tablespace-ID>.TABLEID=<table-ID>” is being marked as unavailable

is issued there are two possible reasons for this:

1. You used “ALTER TABLE <tablename> ACTIVATE NOT LOGGED INITIALLY“ which turns off logging for a table until the next commit. Often people think this will give a performance boost but the performance gains are marginal.
2. You did use the LOAD utility and opted for the non-recoverable option instead of creating a backup OR the rollforward is unable to apply the special LOAD backup you made (e.g. the backup file cannot be found).

The rollforward messages are written to the DIAG log, but you probably already found that out. The rollforward can not continue because it detected the table was physically changed in a not-logged fashion. It earmarks the table as in-accessible. The table can no longer be used, only dropped. Any SQL other than DROP TABLE results in the message:

SQL1477N  For table “<table-id>” an object “<table-id>” in table space “<tablespace-id>” cannot be accessed. SQLSTATE=55019

When you look up the message it says you must drop the table (for a partitioned table: detach, then drop). Don’t do that yet! You can “semi-recover the data” meaning you can do some damage control! Here is how:

1. Save the DDL of the table using DB2LOOK (if the table is dropped DB2LOOK cannot do this!). Use db2look –d <dbname> –e –z <schema_name> –t <table_name> -o <output_file>
2. Save the data from the table using “DB2DART database-name /DDEL” option. It will prompt for table id, tablespace id, first page, and last page. Use the object IDs from the SQL1477N or ADM5571W message. For the first page enter zero and for the last page enter some impossible high number (e.g. higher than the number of pages in the tablespace). DB2DART will dump the data as it was when you did the ALTER or the LOAD (that is where rollforward stopped). Officially you can only use DB2DART with an offline database, but in this case, no modified page of this table will be in the bufferpool, so it can be done online.
3. DROP the table
4. CREATE the table again using the DDL from DB2LOOK: db2 -tvf <db2look file>
5. IMPORT or LOAD the data into the table using the CSV file created by DB2DART.
6. Figure out how much data is lost. Maybe you can re-process, or maybe you need to notify somebody about this.

Moving forward, how can we prevent this from happening again? There is a Database Configuration option to prevent this situation. It is called BLOCKNONLOGGED. As the name already suggests it blocks any non-logged actions in the database. Update the database config with: “UPDATE DB CFG FOR USING BLOCKNONLOGGED YES“. Beware, it becomes active at the next database activation. The BLOCKNONLOGGED was created for HADR. HADR is basically a permanent rollforward.

Finally, a last warning, for both circular and archival logging, IF your non-logged action crashes then DB2 is unable to help you. The table becomes a big mess and unusable. Be wise and turn on that blocknonlogged option in the db cfg.