DB2 backup

Introduction to DB2 backup

DB2 backup command is used to store a copy of the current database or specified tables for the usage of this data for restoring in case if there happens any sort of data loss due to any reason. This ensures that the data’s security and availability are available throughout the period, and the performance and availability do not get hamper on the user end. In this article, we will study the scope, authorization, connection that is required, syntax, and the implementation of the backup command, along with the help of certain examples.

Scope of backup command

We can make use of the back command in DB2 to create the backup of the current partition if nothing is specified externally. In case if we have specified the option of partitioned backup, then it takes the backup of all the data on the catalog node only. In case if we want to perform the backup operation for all the partitions of our current database, we can specify the option saying that all the partitions are mentioned in the list of db2nodes.cfg file of the database server should be backed up. In the remaining cases, the partitions that are specified in the backup command get copied for backup.

Authorization required

The user who performs the backup command execution should have one of the following privileges with him/ her –

• SYSCTRL – Privilege for system control
• SYSADM – Privilege for system administration
• SYSMAINT – Privilege for system maintenance

The connection that is required

Internally, DB2 automatically creates a new connection for performing the backup operation exclusively to the database which is specified. If the connection specified in the command already exists for the same database, then that connection is terminated, and a new connection is initiated exclusively for performing a backup of the data.

Syntax

BACKUP name_of_database
USER name_of_user
ON (ALL) DBPARTITIONNUM(s) partition_number(s)
TABLESPACE name_of_tablespace
ONLINE
INCREMENTAL DELTA
USE sessions/libraries/scripts
OPTIONS

In the above syntax, the different terms used are described as below –

Name_of_database – It helps to tell which alias of the database is to back up.

User – We can specify the name of the user after specifying the USER keyword. Along with that, if there is an optional authorization assigned to the user, we can specify the password of the user after USING the keyword.

ON – This keyword is used to specify the set of the partitions of the database.

(ALL) DBPARTITIONNUM(s) partition_number(s) – We can specify DBPARTITIONNUM along with all to specify that we have to backup all the partitions if our database is partitioned. The usage of just DBPARTITIONNUM followed by the partition numbers is used to tell that only the specified partition having these partition numbers should be backed up. In case if we use DBPARTITIONNUMS, the range of partition numbers can be specified to be backed up.

TABLESPACE followed by the name of table space – It helps specify the table space list that we have to backup.

ONLINE –

We can perform the backup operation in DB2 either online or offline mode. By default, when not specified, the backup is considered to be in offline mode. In case if we want to conduct the backup online, then we have to specify this keyword. We can carry out online backup only on the database, which is configured with enabled logarchmeth1.

INCREMENTAL –

It helps in specifying that only cumulative that is data that is changed from the time of most recently conducted full backup of the database is considered for creating the backup image for this backup operation.

DELTA –

DELTA helps to specify that the incremental backup needs to be carried out for the data that is modified from the last recently carried out the backup operation of any type, full or partial or specified.

USE –

We can specify the external things which are to be used, followed by the USE keyword. Some of the most used things are as specified below –

• TSM – Tivoli Storage Manager should be used for backup.
• SNAPSHOT – If we want to carry out snapshot backup, then none of the following parameters must be used –

1. INCREMENTAL
2. BUFFER
3. PARALLELISM
4. TABLESPACE
5. COMPRESS
6. SESSIONS
7. UTIL_IMPACT_PRIORITY

• XBSA – Backup Services API that is the XBSA interface, should be used as the data storage management facility to carry out the backup.
• SCRIPT – We can specify the name of the executable script which can carry out the snapshot backup. Note that the name of the script that will be specified should contain its fully qualified filename.

OPTIONS –

We can specify c, d, or t options to specify any of the options to be considered while taking the backup. For example, C is for continue, d for device termination, and t for terminating, respectively.

Examples of DB2 backup

Let us consider the example of the offline backup to be carried out step by step –

Step 1 –

List all the databases or applications available in the system. The below command can do that –

db2 list application

The execution of the above command gives the following output –

Step 2 –

Using the handled id retrieved from the list of applications, we can force the application using the app as shown below –

db2 “force application (40)”

The output shown is somewhat shown as below –

Step 3 –

To stop the current database connection. The connection can be terminated by using the following command –

db2 terminate;

Step 4 –

We have to deactivate the database in order to stop all the operations that will be carried out on the database after this, which can lead to data modification by using the following command –

db2 deactivate database sample_database

Step 5 –

Now, we are ready to take the backup of our database named sample_database. For this, we will use the DB2 backup command and take the backup in the location C:\Users\Payal Udhani\Desktop\Articles.

db2 backup database sample_database to C:\Users\Payal Udhani\Desktop\Articles

The execution of the above command gives the following output –

In this way, we have got a new file at the specified location, which can be used to restore the data when we face loss or unavailability.

Conclusion – DB2 backup

DB2 backup command is used to perform backup operations online, or offline which creates the backup file. This backup file can be used in scenarios where we face data loss due to unavoidable circumstances so that data available at the user end doesn’t get affected.

Recommended Articles

This is a guide to DB2 backup. Here we discuss the implementation of the backup command along with the help of certain examples. You may also have a look at the following articles to learn more –

1. DB2 row_number
2. DB2 trim
3. DB2 timestamp
4. DB2 Join