Changing DB2 Database logging methods

DB2 databases can be configured for different logging mechanisms like circular and archival logging. These mechanisms can be changed for a database at any point after the database is created. By default while creating a database in IBM DB2, db logging method is set to "Circular" logging which is generally not used in production environments. Archival logging is the recommend logging type for DB2 databases, since with this type of logging, one can recover/restore the database to a specific point in time or to the end of logs.

How to change the logging type from circular to archival and vice-verse. From you DB2 server machine start the DB2 control center. (DB2CC).

Step 1: Start -> All Programs -> IBM DB2 -> DB2COPY1 (or which ever is default) -> General Administration Tools -> Control Center

Step 2: Now right click on the required database ( TC or TC2 ) for which the database logging mechanism needs to be changed.

Step 3: After clicking the "Configure Database Logging" option, a wizard will open up that guides you to change the logging type. Select Archive logging and click next.

As you can see the clear difference between Circular and Archive logging types. Circular logging provides automatic log file management ( DB2 application will never run out of log space ). Below table mentions the pros and cons.

Type	Online backups	Table space backups	Recover to any point in time	Automatic log file management	Performance	Maintenance
Circular Logging	No	No	No	Yes	High	Less compared to circular
Archive Logging	Yes	Yes	Yes	No	Reduced than circular	More compared to circular

Step 4: Specify the log archiving details like archived logs location , whether you want DB2 to automatically archive logs etc... and click next

Step 5: Specify the number of primary and secondary log files to maintain and size of each log file in 4K pages. So the total disk space needed in KB will be ( no. primary logs * no. secondary logs * size of each log file * 4  ). Now click next

Step 6: Fill details for the archive path location and if you want to mirror/backup the archived logs also... Click next

Step 7: Because you are changing the logging type from circular to archive logging, a full database backup will be performed , specify the backup file destination and the backup options like parallelism, compression etc ...

Once done no need to enable scheduling at next step. Just click next and then verify the changes in last step before clicking the finish button.

Alternatively you may also run the below command from the DB2 prompt (db2cmd found in the <db2 install path>\bin)

CONNECT TO TC2;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
UNQUIESCE DATABASE;
CONNECT RESET;
UPDATE DB CFG FOR TC2 USING logarchmeth1 "DISK:c:\ibm" logprimary 15 logsecond 4 logfilsiz 1024;
BACKUP DATABASE TC2 TO "C:\IBM" WITH 2 BUFFERS BUFFER 9 PARALLELISM 1 WITHOUT PROMPTING;

Note: Either run each above command after typing db2 on the db2cmd command prompt or copy the above code into a text file and save it as a .sql file. On windows run:

db2cmd /c /w db2 -v -tf "<full path to the .sql file>"

On linux login on to the machine with the required instance username,password and then run:

sh -c db2 -v -tf "<full path to the .sql file>"