You can temporarily turn off logging for a table with the statement “ALTER TABLE <tablename> ACTIVATE NOT LOGGED INITIALLY“. Do not do this if you are using archival logging; here is why:
The ALTER statement will turn off logging for the table until the next commit. So you are creating one not logged unit of work. Although the logging resumes after this unit work, the log for that table is useless in a rollforward recovery. If during the rollforward the ALTER is detected on the log a message is written to the DIAG log:
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.
From that point on the log applied to the table is no longer done. There is a good reason why DB2 stops doing log apply. Maybe the not logged unit of work produced many new pages. New log records point to pages that don’t exist during the rollforward. During the rollforward, no messages are produced on the console output to indicate the “problem” and you are in for a surprise when you try to access the table. Any SQL other than DROP TABLE results in:
SQL1477N For table “<table-id>” an object “<table-id>” in table space “<tablespace-id>” cannot be accessed. SQLSTATE=55019
According to the manuals, you must drop the table (or for a partitioned table, detach and then drop). Don’t do that yet! You can “recover the data”. Maybe “recover the data” is not the best word… You can do damage control! Here is how:
- Step 1: Save the DDL of the table using DB2LOOK (if the table is dropped DB2LOOK cannot do this)
- Step 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 at the moment you did the ACTIVATE NOT LOGGED INITIALLY (that is where rollforward stopped).
- Step 3: DROP the table
- Step 4: CREATE the table using the DDL from DB2LOOK
- Step 5: IMPORT or LOAD the data created by DB2DART into the table
- Step 6: Figure out what is lost. Maybe you can re-process. Maybe you need to notify somebody about this.
How can we prevent this from happening again? There is a Database Configuration option to prevent this 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“. It becomes active at the next database activation. The BLOCKNONLOGGED was created for HADR where it must be used.
As I wrote at the start, this only applies to archival logging. If you are on circular logging then the ACTIVATE NOT LOGGED INITIALLY can be a good thing to prevent “out of log” conditions. On circular logging you never can recover once you commit, the log is gone. The log is purely there for rollback and crash recovery.
Finally, a last warning, for both circular and archival logging, IF your non-logged unit of work crashes then DB2 is unable to help you. The table can be a big mess, some updates are done, some might not be done. So… not logged initially should be forbidden! ;-)
