Db/2 – LUW – Managing Transaction Log Backup

Background

Wanted to document some of the steps to take when wanting to implement Transaction Log backup in DB/2 LUW.

 

Outline

  1. Configuration
    • Review & Apply Settings
      • LOGRETAIN
        • Deprecated
      •  LOGARCHMETH1
        • Set folder to place LOG backups
  2. Stop Database
  3. Restart Database
  4. Take full database backup
  5. Review
    • Review File System Folder
      • Active Logs
      • Archive Logs

 

Configuration

 

Outline

Here are the entries that need to be reviewed, set, and tweaked for Db/2 Log Backup.

Items

  1. Path to log files
    • Path where active log files are kept
    • Current :- M:\DB2\NODE0000\SQL00002\LOGSTREAM0000\
  2. LogRetain
    • Text :- Log retain for recovery status
    •  Setting :- NO
    • References
      • Stackoverflow
        • Need to properly set USEREXIT and LOGARCHMETH1
          Link
  3. Primary
    • LOGARCHMETH1 ( Change )
      • Text :- First log archive method (LOGARCHMETH1)
      • Current :- DISK:M:\DB2\NODE0000\Backup\Log\WideWrld\
    • LOGPRIMARY
      • Text :- Number of primary log files (LOGPRIMARY)
      • Current :- 13
    • LOGARCHCOMPR1
      • Text :- Archive compression for logarchmeth1 (LOGARCHCOMPR1)
      • Current :- OFF
  4. Secondary
    • LOGARCHMETH2 ( Change )
      • Text :- First log archive method (LOGARCHMETH2)
      • Current :- DISK:M:\DB2\NODE0000\Backup\Log\WideWrld\
    • LOGSECOND
      • Text :- Number of primary log files (LOGPRIMARY)
      • Current :- 13
    • LOGARCHCOMPR2
      • Text :- Archive compression for logarchmeth2 (LOGARCHCOMPR2)
      • Current :- OFF

 

Settings – Set

Code


setlocal

set "_folder=M:\DB2\NODE0000\Backup\Log\WideWrld"

set "_database=WideWrld"

set "_logRetain=Recovery"

set "_logfileSize=1024"

if not exist %_folder%  mkdir %_folder%

db2 connect to %_database%

db2 update db cfg for %_database% using LOGARCHMETH1 "DISK:%_folder%"

db2 update db cfg for %_database% using LOGFILSIZ %_logfileSize%

db2 CONNECT RESET

Images

 

Settings – Get

Outline

Using “db2 get database configuration” highlight pertinent entries.

Textual


setlocal

set _database=WideWrld

db2 connect to %_database%

db2 get database configuration | findstr /I /C:LOGFILSIZ /C:LOGPRIMARY /C:LOGSECOND /C:LOGARCHMETH /C:"Log retain for recovery status"  /C:"Path to log files" 

db2 CONNECT RESET

endlocal

Images

 

 

Database Backup

Outline

Syntax

Code


db2 backup database {database} ONLINE to {folder} INCLUDE LOGS

Sample

Take full database backup

Code

@echo on

set "_database=WideWrld"

set "_folder=M:\DB2\NODE0000\Backup\Full\WideWrld"

if not exist %_folder%  mkdir %_folder%

rem terminate current connection
db2 terminate

rem deactivate database
db2 deactivate database %_database%

rem backup database
db2 backup database %_database% ONLINE to %_folder% INCLUDE LOGS

Review

Review File System

Active Logs

File Explorer

Image

 

Archive Logs

File Explorer

Image

 

Transaction Log Utilization

CLP

DB2 GET SNAPSHOT FOR DATABASE

Code
Syntax

DB2 GET SNAPSHOT FOR DATABASE ON {database}

Sample

DB2 GET SNAPSHOT FOR DATABASE ON WideWrld   | findstr /I /C:"log space used" /C:"active"

 

Output – Image

Output – Textual
Log space used by the database (Bytes) = 2114776
Maximum secondary log space used (Bytes) = 3577958
Maximum total log space used (Bytes) = 57837670
File number of first active log = 2060
File number of last active log = 2072
File number of current active log = 2072

Explanation
  1. Log space used by the database (Bytes)
  2. Maximum secondary log space used (Bytes)
  3. Maximum total log space used (Bytes)
  4. File number of first active log
  5. File number of last active log
  6. File number of current active log

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s