Backup DB2 with Veeam Backup and replication? No problem!

  • 22 August 2022
  • 6 comments
  • 734 views

Userlevel 5
Badge +1
  • Comes here often
  • 26 comments

Protecting DB2 servers with Veeam Backup and Replication is pretty straightforward.

 

The IBM DB2 can be protected with the following Veeam jobs:

  • Classic VM backup jobs
  • Veeam  Agent for Linux (VAL) jobs
  • Enhanced NAS backup jobs

To ensure that the DB2 DBs are in a consistent state, IBM DB2 commands will need to be called to either create a native DB2 database backup or to put database(s) in a quiesced state so that a clean application backup of the database can be made.

 

DB2 has several ways to perform backups from the CLI that make backups of the database reasonably simple.

  • Native flat file backup can be performed against an offline or online DB. The options include using full, incremental, and differential backups
  • A simple pause and resume of the DB2 writer that can be used to store (and VMware) snapshots that Veeam Backup and Replication can leverage.
  • The ability to leverage storage array snapshots as restore points through a component called IBM DB2 ACS. I will not discuss this process as Veeam Backup and Replication can’t leverage it.

Ways to create DB2 native backups

When using the native DB2 backup process with Veeam Backup and Replication, have the DB2 backup file sent to a file location that Veeam Backup and Replication can protect with the classic VM backup, Agent-based backup, or the Enhanced NAS backup job.

 

The preferred way to create a native backup is to stop the DB, back it up, and restart the database. This ensures that the DB is in an application consistent state without requiring an IO quiescence. This also allows for the fastest and safest DB restores.  Not many organizations have the luxury of being able to stop all DB operations while a backup occurs, so an online backup process will need to be used.

 

The online backup option allows users to connect to the DB (not quiesced) while the backup is created.  It requires archive logs to be enabled for the DBs to be backed up. Since this operation does not require any IO quiescing, the recovery of the DB(s) will require that the archive logs are restored with the database and replayed to the point in time for recovery.

 

A simple DB2 online backup command looks like this:

 

db2 BACKUP DATABASE <db name> ON ALL DBPARTITIONNUMS TO <backup file location path>

 

More information regarding the BACKUP DATABASE command with further examples can be found at https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001933.html

 

Since there is not any quiescing of the database with a native backup, there will not need to be a pre-freeze/post-thaw script called from Veeam.

 

Leveraging snapshots for DB2 backups

Backup up a DB2 DB from a snapshot (VM, or storage snapshot) is really easy.

 

The steps are:

1.     Pause the DB2 writer

2.     Create a snapshot (through VMware or an array snapshot)

3.     Resume DB2 writer

4.     Backup from a snapshot using a classic VM backup job, a Veeam Agent for Linux job, or Enhanced NAS backup job backing up from an array snapshot.

 

Since there is a freeze and thaw step, a freeze and thaw script will need to be provided to the Veeam backup job.

 

A sample of the commands required to put the DB2 writer in pause before a snapshot can be created:

 

$ db2 Connect <DB name>

$ db2 set writer suspend for database

<create snapshot>

$ db2 set writer resume for database

 

An example script for pre-freeze/post thaw can be found at https://community.hitachivantara.com/s/article/db2-freeze-thaw-quiesce-and-unquiesce-scripts-for-hitachi-virtual-infrastructure-integrator

 

The restore process would be to restore the needed files from the backup of the run db2inidb tool and bring the restore DB up as a mirror, a hot standby DB, or a snapshot and recovered.

 

Calling scripts from Veeam Backup and replication jobs

Classic VM backups

To use pre-freeze/post-thaw scripts in Veeam backup jobs, navigate to the guest processing and go to the scripts topic and add the path to where the scripts to be used are kept on the Veeam Backup and Replication console. The scripts need to be stored on the Veeam Backup and Replication console server to be executed.

 

More information can be found in the Veeam User Guide - https://helpcenter.veeam.com/docs/backup/vsphere/replica_vss_scripts_vm.html?ver=110

 

Veeam Agent for Linux

Pre-freeze/post-thaw scripts can be called for the guest processing section under the Snapshot scripts setting. These scripts will be call before and after the Veeam volume snap module is called.

 

More information can be found at

https://helpcenter.veeam.com/docs/backup/vsphere/replica_vss_scripts_vm.html?ver=110

Enhanced NAS backups

 

If DB2 data is stored on a NAS server, that data can be backup using the Veeam Enhanced NAS backup job. If the NAS filer supports it, snapshot backups of the DB2 data can be performed from a snapshot of the NFS volume without impacting the DB2 DB.

 

The quiescence and the snapshot creation can be called with two scripts that do the following:

1.     Pre-freeze: Pause the DB2 writer and create the storage snapshot

2.     Post-Thaw: Resume the DB2 writer and synch a copy to second location if supported and/or needed

 

The scripts can be added to the Enhanced NAS backup job under the advanced section of the job. More information can be found at https://helpcenter.veeam.com/docs/backup/vsphere/file_share_backup_job_advanced_scripts.html?ver=110

 

A Veeam Enhanced NAS backup job can also be created using an existing NFS array snapshot.  With this process, the scripts needed for quiescing and calling the storage snapshot are called from the user's environment outside of Veeam Backup and Replication. The Enhanced NAS backup job would then be set up to use an existing snapshot that is stored either on the primary array or a secondary array.

 

Example scripts for creating NetApp snapshots can be found on the Veeam Hub site.

 

This can be done with any other NFS systems that support calling snapshots from scripts.

 

DB2 is still a pretty common common database program.  Although there is no VBr integration for DB2 the backup of the database is simple enough with Veeam.  

Feel free to drop me a question, or leave a comment below!


6 comments

Userlevel 7
Badge +8

Never used or even looked at DB2 but nice to know Veeam has you covered for backups. 😎

Userlevel 7
Badge +8

Fine, very straight-forward process to backup DB2. 😎👍🏼

Thank you for the detailled description.

Userlevel 7
Badge +4

thx @vmJoe for info.

The aspect that I do not like is the DB2 writer stop, this results in application disruption throughout the backup.
Many applications and customers cannot afford to stop the db.
In my opinion a dump of the Db to a dedicated disk and backed up by a classic job is the most performant and suitable solution. imho.
An Application aware strategy would be needed... without interroping database operations.

 

Userlevel 5
Badge +1

@Link State I always prefer a native database dump and backup for these situations.

However, the majority of DB2 customers that I have talked  with do not like the DB2 native backup with log truncation for a number of reasons.
 

The main reason is that there is no quiescent if the DB and the transactions logs need to be gathered during the backup and restores need log replays.  This makes for a longer backup and longer restores. In most instances it is hard to go back and change their DB2 behavior to work like this.

The customers who have used this are usually running the DB2 on arrays that create fast array snapshots - so it’s not much of a disruption to do a momentary DB pause then a fast array snapshot then the db resume write. They see it as a lesser of 2 evils. 

Userlevel 1
Badge

Very nice article Joe. I especially like using the native DB2 backup commands. They can be used in a regularly scheduled backup method, or be called from Pre and post type processing. Using the native backup is familiar to the DBAs and does not require relocation and outside management of archive logs. This will also help to allow the backup to be used as the DBA is used to doing

... and regarding online backup with dump to disk. How to copy DB2 logs and then delete files to clean and free up file system space?

Comment