Basic Backups with DB2

Introduction
To me the most important thing as a DBA is making sure the data in a database is safe. This means…when there is some sort of failure, you must be completely sure that your backups are working properly.
Actually more important then making the backups is making sure you can restore your backups. What good is a backup if you are not able to restore them.

In this article I will show the very basics of backups. There are numerous possibilities concerning backups (destination/what to backup/compression/encryption/…). That’s for another day, today we talk about the basics.

(Restore will be another article)

Offline backups
Offline backups are (I think) one of the oldest forms of backup. However no applications can be connected to the database. Just for demonstration purposes, let’s see what happens when we try to make an offline backup with applications connected to the database:

First have a look at the connected applications:

[db2inst1@bloemkool sql]$ db2 list applications

Auth Id  Application    Appl.      Application Id                        DB       # of
         Name           Handle                                           Name    Agents
-------- -------------- ---------- ------------------------------------- -------- -----
MARTIJN  db2bp          294        *LOCAL.db2inst1.220112221947          TINUS    1    
DB2INST1 db2bp          116        *LOCAL.db2inst1.220112201052          TINUS    1    

[db2inst1@bloemkool sql]$

As you can see there are 2 applications connected to database tinus. So my guess is that DB2 will not be able to make an offline backup.

Start an offline backup (offline is the default):

[db2inst1@bloemkool sql]$ db2 backup database tinus to /home/db2inst1/bu
SQL1035N The operation failed because the specified database cannot be
connected to in the mode requested. SQLSTATE=57019

[db2inst1@bloemkool sql]$

As expected: the offline backup did not succeed.
Let’s force all applications to disconnect/terminate.

[db2inst1@bloemkool sql]$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.


[db2inst1@bloemkool sql]$ db2 list applications
SQL1611W  No data was returned by Database System Monitor.
[db2inst1@bloemkool sql]$

Okidoki

And see whether an offline backup is possible now

[db2inst1@bloemkool sql]$ db2 backup database tinus to /home/db2inst1/bu

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

[db2inst1@bloemkool sql]$

Yes….We succeeded.

Take a look at the specified backup location:

[db2inst1@bloemkool bu]$ pwd
/home/db2inst1/bu
[db2inst1@bloemkool bu]$ ls -altr
total 2740524
-rw-------.  1 db2inst1 db2iadm1 146915328 Jan  9 18:42 TINUS.0.db2inst1.DBPART000.20220109124236.001
-rw-------.  1 db2inst1 db2iadm1 150499328 Jan  9 20:31 TINUS.0.db2inst1.DBPART000.20220109143143.001
-rw-------.  1 db2inst1 db2iadm1 228159488 Jan 10 21:12 TINUS.0.db2inst1.DBPART000.20220110151250.001
-rw-------.  1 db2inst1 db2iadm1 181006336 Jan 10 21:46 TINUS.0.db2inst1.DBPART000.20220110154601.001
-rw-------.  1 db2inst1 db2iadm1 183635968 Jan 11 21:43 TINUS.0.db2inst1.DBPART000.20220111154324.001
-rw-------.  1 db2inst1 db2iadm1 174927872 Jan 11 21:43 TINUS.0.db2inst1.DBPART000.20220111154337.001
-rw-------.  1 db2inst1 db2iadm1 183635968 Jan 12 19:08 TINUS.0.db2inst1.DBPART000.20220112130820.001
-rw-------.  1 db2inst1 db2iadm1 174927872 Jan 12 19:09 TINUS.0.db2inst1.DBPART000.20220112130904.001
drwxr-xr-x. 10 db2inst1 db2iadm1      4096 Jan 12 21:08 ..
-rw-------.  1 db2inst1 db2iadm1 927211520 Jan 12 21:10 TINUS.0.db2inst1.DBPART000.20220112210950.001
drwxr-xr-x.  2 db2inst1 db2iadm1      4096 Jan 12 23:21 .
-rw-------.  1 db2inst1 db2iadm1 455368704 Jan 12 23:21 TINUS.0.db2inst1.DBPART000.20220112232115.001
[db2inst1@bloemkool bu]$

If you look closely, you might notice that the timestamp mentioned in the output of making the backup (20220112232115) is also the timstamp mentioned in the latest backup in the directory.

OK….so this looks great….move on.

Online Backups
An online backup can be made with applications connected to the database. But….since you are making backups of an “open” database we do need archivelogging working so we can restore the database to an point in time (roll forward).

The database must be in archiving mode. We can achieve that by setting a value for the parameter logarchmeth1:

[db2inst1@bloemkool sql]$ db2 get db cfg | grep -i logarchmeth1
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/archs/
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) = 
[db2inst1@bloemkool sql]$

The archives are, in this case, going to a directory on disk (/home/db2inst1/archs)

Do it….make the online backup:
(first I show there are applications connected)

[db2inst1@bloemkool sql]$ db2 list applications

Auth Id  Application    Appl.      Application Id                     DB       # of
         Name           Handle                                        Name    Agents
-------- -------------- ---------- ---------------------------------- -------- -----
DB2INST1 db2bp          351        *LOCAL.db2inst1.220112225222       TINUS    1    
DB2INST1 db2bp          337        *LOCAL.db2inst1.220112225158       TINUS    1    

[db2inst1@bloemkool sql]$ db2 backup database tinus online to /home/db2inst1/bu

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

[db2inst1@bloemkool sql]$

Well that looks succesfull

Incremental backups
Imagine having a very large database (maybe some TB). Imagine this is a database for a webstore. Most of the DML will be inserting new data. Not to much data will be deleted (since you want to keep the history) and not to much data will be updated (once a customer ordered, the order will stay pretty much the same).
In short: the amount of data grows, but the data allready in the database will not change that much.
Is is really neccesary to make a full backup of all this data every day? We’re talking about tera bytes in this imaginary case. The backup will probably take a lot of time, and most of the backed up data is allready in an older backup set. Would it be possible just to backup the changes in the database since the last backup?

“Yes we can”…incremental backups.

As the word suggests, with an incremental backup you can just backup the increment/change since the last backup. Let’s have a try at that:

[db2inst1@bloemkool ~]$ db2 backup database tinus online incremental to /home/db2inst1/bu
SQL2426N  The database has not been configured to allow the incremental backup 
operation. Reason code = "1".
[db2inst1@bloemkool ~]$

hmmmmm….no succes.
Apperently the database is not configured to make incremental backups. Well, that is because the database doesn’t “record” (or track) all changes (or modifications). DB2 does currently not know wich data has changed compared to the last (full) backup. So let’s change that:

[db2inst1@bloemkool ~]$ db2 update db cfg using trackmod yes
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@bloemkool ~]$

Ah…that worked.But…the database has to be stopped and started to make the configuration change effective:

[db2inst1@bloemkool ~]$ db2stop force
01/13/2022 09:18:53     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

[db2inst1@bloemkool ~]$ db2start
01/13/2022 09:18:59     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@bloemkool ~]$

Have another go:

[db2inst1@bloemkool ~]$ db2 backup database tinus online incremental to /home/db2inst1/bu
SQL2426N  The database has not been configured to allow the incremental backup 
operation. Reason code = "2".
[db2inst1@bloemkool ~]$

Crap….that did not work out as planned also.
But something did change…we now have reasoncode 2. In the first try there was reason code 1.

Let’s think a bit about this: We did change the tracking of modifications in the data. But modifications to what. You need to have some sort of baseline tot track your modifications against. That baseline would be a regular online backup.
We’re now first going to make a full online backup, and then see if we can make an incremental backup:

[db2inst1@bloemkool ~]$ db2 backup database tinus online to /home/db2inst1/bu

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

[db2inst1@bloemkool ~]$ 
[db2inst1@bloemkool ~]$ db2 backup database tinus online incremental to /home/db2inst1/bu

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

[db2inst1@bloemkool ~]$

Ahhh…we now created an incremental backup. Great that will reduce backup time greatly.

There is one more thing I would like to show: incremental delta backups.

Incremental delta backups
The regular incremental backups check what has been changed (in the data) since the last full backup. A incremental delta backup only makes a backup of the modified data since the last backup (that can be full, incremental or incremental delta). I’ll try to catch that in a scheme:

The volume of backupped data will be a little bigger every day since all modifications compared to the full backup will be backupped.

Take a look at an incremental delta backup:

The volume of backupped data will be a lot smaller per day, and the backup will be much faster.

But….The restore may take (a lot) longer, since there is more work to be done. All the incremental delta backups must be applied to the database.

Just because I can…I’ll show an example of an incremental delta backup:

[db2inst1@bloemkool sql]$ db2 backup database tinus online incremental delta to /home/db2inst1/bu

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

[db2inst1@bloemkool sql]$

Most important…..ultimately you need your restores to work.
TEST TEST TEST
always test whether you can restore!!!