Backup DB2 with Veeam Backup and replication? No problem!


Userlevel 7
Badge +7

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!


10 comments

Userlevel 7
Badge +20

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

Userlevel 7
Badge +17

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

Thank you for the detailled description.

Userlevel 7
Badge +7

@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

Backup DB2 with Veeam Backup ? Problem!

What this article provides is a workaround for a lack of veeam backup library (api).

Backup to disk requires 2x disk space, requires 2x backup time (backup to disk & to veeam).

Your restore point in time is not at log archive time boundary, but on backup end time, tipically once a day. But only if backup to veeam succeded before emergency.

When using full and differential backups, you need disk space management done by yourself.

Backup with disk snapshot requires “db2 set write suspend / resume”, on tipical storage it lasts several ten seconds, which IS business disruption.

Backup to disk may be suitable for basic instalations.

... 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?

Userlevel 7
Badge +7

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 7
Badge +7

What do I do about the transaction logs?  How would you recover to a point in time with  a  log file?

 

I always prefer a native database dump and backup of the native files 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 7
Badge +7

... 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?

You would need to use native DB2 commands commands to cleanup the log files after the backup to Veeam has been performed.  Veeam can run per and post scripts as part of a backup job run.

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. 

What do I do about the transaction logs?  How would you recover to a point in time with  a  log file?

 

Comment