Showing posts with label db2. Show all posts
Showing posts with label db2. Show all posts
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.

TypeOnline backupsTable space backupsRecover to any point in timeAutomatic log file managementPerformanceMaintenance
Circular LoggingNoNoNoYesHighLess compared to circular
Archive LoggingYesYesYesNoReduced than circularMore 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>"

Changing DB2 Database logging methods

Oct 29, 2012
All databases have logs associated with them which keep a record of changes to the database. Lets consider IBM DB2 database for understanding the database logging techniques.

 A database that uses archival logging can be backed up online. To reach a specified point in time, you can perform a rollforward recovery. A database that uses archival logging is therefore also called recoverable.There is another type of logging , Circular logging which keeps all restart data in a ring of log files. It starts logging in first file in the ring, then moves on to the next, and so on, until all the files are full. Circular logging overwrites and reuses the first log file after the data it contains has been written to the database. This continues as long as the product is in use, and has the advantage that you never run out of log files.


In circular logging only full backups of the database are allowed while the database is offline. The database must be offline (inaccessible to users) when a full backup is taken. So consider the case of a real time database which needs to be backed up and uses circular logging. For example a telecommunication company will want to log all details of a user's call details.A call detail record contains details of a telecommunication transaction, such as:

1. Phone number of the calling party
2. Phone number of the called party
3. Call start time and date
4. Call duration
5. Identification of the telephone exchange or equipment writing the record
6. A unique sequence number identifying the record
7. Additional digits on the called number used to route or charge the call
8. Call type (voice, SMS, etc.)
9. Any fault condition encountered
Now suppose a database backup is to be done for which it needs to stopped or taken to an offline mode. However users will continue to make or receive calls. We as mobile users are not aware when our service provider will take a database backup. Hence if circular logging is used , backups lose any incoming data while the backup operation is in progress.



Archive logging is exactly opposite to circular logging. Online, Incremental and Delta backups are supported only if the database is configured for archive logging. All activities against the database are logged during an online backup. After an online backup is complete, the database manager forces the currently active log to be closed, and as a result, it will be archived. As a result of this, online backup has a complete set of archived logs available for recovery. In simple terms:

Archive Logging = Serial Logging ( no overwrite of log files as in circular logging )


When an online backup image is restored, the logs must be rolled forward at least to the point in time at which the backup operation completed. Circular logging can recover data only to a specific point in time at which full backup was taken, it is also known as version recovery. Archive logging can recover data to any point in time hence also known as full recovery or rollforward recovery

The advantage of choosing archive logging is that recovery tools can use both archived logs and active logs to restore a database either to the end of the logs, or to a specific point in time. The advantage of using circular logging is that you never run out of log files or storage space issues.
Below is an easy to understand comparison between the two logging methods.

TypeOnline backupsTable space backupsRecover to any point in timeAutomatic log file managementPerformanceMaintenance
Circular LoggingNoNoNoYesHighLess compared to circular
Archive LoggingYesYesYesNoReduced than circularMore compared to circular

Database logging mechanisms

Oct 24, 2012
Detecting installations of a particular software on Windows is a simple job , but things get worst in case of Linux and UNIX , especially if the software was not installed using native binaries like rpm or deb packages. Windows has the registry (start -> run -> regedit.exe) to list all the required details like install path , product version , locale or product type. But on Linux or UNIX it may not be that easy. Faced a similar problem about detecting DB2 installations on Non-Windows OS.

DB2 documentation mentions that you can detect DB2 installation details using the "db2ls" utility located as "/usr/local/bin/db2ls". But the problem here is it will list all products of the DB2 family like : DB2 Server , DB2 Client , DB2 Connect etc. Running db2ls command provides details like : Installation path ,  Level , Fix pack , Special Install Number , Installation date , Installer UID. But what if I need to detect these details without running any command , just like we do in windows by reading the existing registry keys.
After searching the DB2 info-center I realized that its not possible to detect DB2 installations without running a command like db2ls ...

It sounds impossible but thats the truth ... however the fact is even db2ls command will internally refer to some global file which may contain all the details ... so which is this file ??? "global.reg" is the answer. However this file is encoded and cannot be read directly. In order to decode this file one needs to run the db2greg tool ... We are back to the same problem where we need to run a command to find DB2 installation details ... So I tried to read the file using cat command , obviously it wasn't readable as clear text since it had come Unicode characters ... Below is the screenshot of the file content :

NOTE : location of global.reg is fixed as /var/db2/global.reg always



Interesting problem to solve ... Let try to read it as a Unicode encoded file using special editors like notepad++ or edit+  or ultra-edit...


At first sight it looks worst than the previous screenshot. But if you observe carefully , there is pattern hidden in the second screenshot.  Path to the root install directory for DB2 Server has a pattern of %OO%OO%12<ROOT_INSTALL_PATH>%OO%OO , in our case the value is /opt/IBM/DB2/V9.7 , on Linux operating systems like RHEL and SUSE the pattern is %OO%12%OO<ROOT_INSTALL_PATH>%OO%OO

Path to the database has a pattern of %OO%OO%16<PATH_TO_DATABASE>%OO%OO%O4 which in our case is "/home/db2inst1/sqllib" or "/home/db2inst2/sqllib" , on Linux operating systems like RHEL and SUSE the pattern is %OO%16%OO<PATH_TO_DATABASE>%OO%OO%O4

Once we get the root install directory location , its easy to find the type of product installed by looking for the file <ROOT_INSTALL_PATH>/cfg/.*.lvl file where the name of .lvl file will contain ese (enterprise server edition), client , admcl (administration client) , adcl (application development client) , rtcl (run-time client) etc.

Note :  <ROOT_INSTALL_PATH>/cfg will always have only one .lvl file.

The scenario here is "writing a relevance for a fixlet to install DB2 server" using BigFix. Lets get our hands dirty with all the above information and use the below relevance:
if name of operating system as lowercase starts with "aix" then (exists true whose ( exists files whose ((it contains "client" and it ends with ".lvl") of (name of it as lowercase)) of (( folders ("/" & it as string & "/cfg") ) of ( preceding texts of firsts "%OO%OO" of ( following texts of firsts "/" of ( lines of file "/var/db2/global.reg") whose (it contains "DB2SYSTEM" ) ) )))) else if name of operating system as lowercase starts with "linux" then (exists true whose ( exists files whose ((it contains "client" and it ends with ".lvl") of (name of it as lowercase)) of (( folders ("/" & it as string & "/cfg") ) of ( preceding texts of firsts "%OO%OO" of ( following texts of firsts "/" of ( lines of file "/var/db2/global.reg") whose (it contains "DB2SYSTEM" ) ) ))) ) else false
Note : String patterns shown above contain numeric zero "0" and not alphabetic "O"

Detecting DB2 installations without running DB2 specific commands

Aug 18, 2012
+

Copyright 2016 Cubic Race Published By cubicrace.com