.::: Backup & Restore database IBM-DB2 DB2 :::.

 1. Introduction
Backup and recovery methods are designed to keep our information safe. In Command Line Interface (CLI) or Graphical User Interface (GUI) using backup and recovery utilities you can take backup or restore the data of databases in DB2 UDB.

Logging
Log files consist of error logs, which are used to recover from application errors. The logs keep the record of changes in the database. There are two types of logging as described below:

Circular logging
It is a method where the old transaction logs are overwritten when there is a need to allocate a new transaction log file, thus erasing the sequences of log files and reusing them. You are permitted to take only full back-up in offline mode. i.e., the database must be offline to take the full backup.

Archive logging
This mode supports for Online Backup and database recovery using log files called roll forward recovery. The mode of backup can be changed from circular to archive by setting logretain or userexit to ON. For archive logging, backup setting database require a directory that is writable for DB2 process.
 2. Backup
Using Backup command you can take copy of entire database. This backup copy includes database system files, data files, log files, control information and so on.

You can take backup while working offline as well as online.

2.1 Offline backup

[db2inst1@teguhth-grid ~]$ db2 list application
SQL1611W  No data was returned by Database System Monitor.
[db2inst1@teguhth-grid ~]$

[db2inst1@teguhth-grid ~]$ db2 terminate  
DB20000I  The TERMINATE command completed successfully.
[db2inst1@teguhth-grid ~]$

db2 deactivate database teguhth

[db2inst1@teguhth-grid ~]$ db2 deactivate database teguhth
DB20000I  The DEACTIVATE DATABASE command completed successfully.
[db2inst1@teguhth-grid ~]$


[root@teguhth-grid ~]# chmod -R 777 /data/
[root@teguhth-grid ~]#

[db2inst1@teguhth-grid ~]$ db2 backup database teguhth to /data

Backup successful. The timestamp for this backup image is : 20221009161144

[db2inst1@teguhth-grid ~]$


[db2inst1@teguhth-grid ~]$ cd /data/
[db2inst1@teguhth-grid data]$ ls -lh
total 2.0G
-rwxrwxrwx 1 root     root     1.9G Sep 23 22:04 db2v11.5.7_linuxx64_server_dec.tar
drwxrwxrwx 3 root     root      206 Nov 23  2021 server_dec
-rw------- 1 db2inst1 db2iadm1 137M Oct  9 16:11 TEGUHTH.0.db2inst1.DBPART000.20221009161144.001
[db2inst1@teguhth-grid data]$
[db2inst1@teguhth-grid data]$


2.2 Online backup
To start, you need to change the mode from Circular logging to Archive Logging.

[db2inst1@teguhth-grid data]$ db2 get db cfg for teguhth | grep LOGARCH   
 First log archive method                 (LOGARCHMETH1) = OFF
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
[db2inst1@teguhth-grid data]$


[db2inst1@teguhth-grid data]$ cd /home/db2inst1/backup/ArchiveDest/
[db2inst1@teguhth-grid ArchiveDest]$
[db2inst1@teguhth-grid ArchiveDest]$ db2 update database configuration for teguhth using LOGARCHMETH1 'DISK:/home/db2inst1/backup/ArchiveDest'
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, the database
must be shutdown and reactivated before the configuration parameter changes
become effective.
[db2inst1@teguhth-grid ArchiveDest]$

[db2inst1@teguhth-grid ArchiveDest]$ db2 backup database teguhth online to /home/db2inst1/onlinebackup/ compress include logs
SQL2413N  Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
[db2inst1@teguhth-grid ArchiveDest]$

3. restore database

[db2inst1@teguhth-grid data]$ db2 restore database teguhth from /data taken at 20221009161144
SQL2539W  The specified name of the backup image to restore is the same as the
name of the target database.  Restoring to an existing database that is the
same as the backup image database will cause the current database to be
overwritten by the backup version.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.
[db2inst1@teguhth-grid data]$