With
every DB2 UDB database there are two different databases logging mode,

Circular database mode & Archival
database mode.When the database is created by default it is in circular mode
& as per the business Requirement you can change the mode from circular to
archival logging mode.Also with the database creation there are 3 primary and 2
secondary log files gets created.

Circular logging mode

Circular
logging is a default logging mode when it is used, records stored into the

Log
buffer are written to primary log files in a circular fashion. Redo log entries
are written to the current “active” log file and when that log file becomes
full , it is marked as “unavailable”.DB2 marks next log file in the sequence as
a active log file and continue writing log entries into it. And when that log
file becomes full , then process

gets
repeated. When the transactions are terminated the corresponding log records
are released because they are no longer needed. When all the records stored in
an individual log file are released, that log file is treated as being
“reusable” and it becomes “active log file “ for the next transactions thus its
contents are overwritten by new log entries.

gets back
to the primary log file which is marked as “unavailable” then DB2 databasemanager will allocate secondary log file. Now
the log entries are being written into the secondary log file.As soon as this secondary
log file becomes full,

the DB2
Database Manager will check the primary log file again and if its status

is still
“unavailable”, another secondary log file is allocated and redo entries are
being written into it. This process continues until all the secondary log files
get full,

which is
indicated by the parameter “logsecond” . Once all the secondary log files get
full and there is no primary log file available for writing redo entries then
following error message will generated :

SQL0964C
The transaction log
for the database is full.

Here you
have to increase number of secondary log files .

Ødb2
update db cfg for db_name using logsecond ‘value’

In circular
logging mode as the contents of log file are overwritten you can recover the
database up to the last full database backup performed. and you cannot perform
the point in time recovery with circular logging mode.

Archival logging mode

Like circular logging mode, when
archival logging is used the redo log entries from log buffer are written into
the primary log files. However unlike circular logging these log files are
never reused.when
all records stored in an individual log file are released, that file is marked
as being “archived” rather than as being “reusable” and it can be used again if
it is needed for the roll forward recovery. When the first primary log file
becomes full , next primary log file is allocated so that the desired number of
primary log files “logprimary”is always
available for use. All the log entries related to a single transaction must fit
within the active log space available.In case of log running transaction it
requires more log space other than the primary log file size thus the secondary
log file may be allocated and used.

In
this mode once the log file gets full another primary log file is allocated and the transaction information
gets logged into that. During that period of time the first primary log file is
being archived to another destination configured by the parameter “Logarchmeth1”
and can be reusable for the further log information storage. Same process is
repeated until the disk space is available for the archived destination.

In case of
archival logging mode you can recover your database up to a current state or up
to a particular state by using the point in time recovery options which are
described in later section of this article. Here the archived log files are be
used for the point in time recovery.

b)How to change the database logging
mode:

As
described earlier wherever the DB2 UDB database is created by default it is in
Circular logging mode , if you want to change the mode from circular to
archival mode

Perform
following steps:

Step
1:change the following parameters

i)logretain=recovery
or userexit=on

Ødb2 terminate

Ødb2 force application all

Ødb2 update db cfg for db_name using
Logretain recovery

or

Ødb2 update db cfg for db_name using
Userexit=on

ii)blk_log_dsk_ful=yes

Ødb2 update db cfg for db_name using
blk_log_dsk_ful yes

Setting blk_log_dsk_ful to yes causes applications to hang when DB2 encounters a log
disk full error, thus allowing you to resolve the error and allowing the
transaction to complete. You can resolve a disk full situation by moving old
log files to another file system or by enlarging the file system, so that
hanging applications can complete.

SQL1116N A connection to or activation of
database "db_name" cannot be made because of backup pending:sqlstate=57019

This is because we have changed the database
logging mode from circular to archival so we must perform full database backup
here, as the backup of circular logging mode cannot be applicable to archival
logging mode and vice versa.

Ødb2 backup database
db_name to d:\db_name\backup

Ødb2 connect to db_name

Step
2: Set the archival destination

To
set the archival destination create a folder “archive” on a disk where you have
sufficient disk space so that the log files will get archived to this location
once it is filled. Keep your archive destination other than log files
destination.