Changing DB2 Database logging methods
PiyushChordia 2700021NT6 Visits (1823)
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.
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 ...
Alternatively you may also run the below command from the DB2 prompt (db2cmd found in the <db2 install path>\bin)
CONNECT TO TC2;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:
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
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;
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>"