Safety first with BLOCKNONLOGGED
Upgrades of software often comes with upgrading the database. But what if your only allowed downtime during a certain time span? Can you complete all your upgrades within this time? When you have to move or load a huge amount of data, not logging will speedup the job. Great, that’s what we want. But how about recover-ability? For short: you can’t assure it! The changes have not been logged, remember? Your fail-over (HADR) cluster isn’t aware that things have changed, so with your fail-over cluster you have issues right after the transaction is commited. And if your have a single database and it crashes, crash recovery starts. And if this crash recovery failes (I haven’t seen it happen ever, but there are lot’s of APARs about it) it will not be able to recover.
This is where the database configuration parameter
BLOCKNONLOGGED comes in. This DB CFG parameter was introduced in DB2 v9.5 fix pack 4 and V9.7 GA and can be set online. The default setting is NO. There are some potential drawbacks associated with this default configuration, however, particularly in high availability disaster recovery (HADR) database environments. DB2 HADR database environments use database logs to replicate data from the primary database to the standby database. Non-logged operations are allowed on the primary database, but are not replicated to the standby database. When someone uses no-logged transactions it makes the entire tablespace on the HADR secondary unusable and no one will know until you need to fail over. If you want non-logged operations to be reflected in the standby database, you must take extra steps to cause this to happen.
If you set the
BLOCKNONLOGGED parameter to YES, non-logged transactions can not be executed, instead an error message is returned. This goes for all not-logged transactions, tables that have the NOT LOGGED or NOT LOGGED INITIALLY attributes activated, a LOAD command with the specified options NONRECOVERABLE or COPY NO. Same goes for the ADMIN_MOVE_TABLE procedure for which the options are a bit opaque. If you do not specify any options for COPY_USE_LOAD, then the NONRECOVERABLE option of the db2Load API is used to copy the data from the source table to the target table.
In regard to Safety first you can set the
BLOCKNONLOGGED to YES. That way you will always get an error if a non-logged transaction is being issued. The error you’ll get for a NONRECOVERABLE ADMIN_MOVE_TABLE for instance will be:
SQL2032N invalid "COPY FLAG" parameter SQLSTATE=22531
BLOCKNONLOGGED you’ll be triggered to undertake the appropriate actions in getting your fail-over (HADR) cluster in the right state or with a single database can think about taking a backup to be sure that if a crash recovery fails or needs the backup to recover all data is present.