Preparing & Executing Automated DB2 Backups when Running on RedHat OpenShift

Abstract: We are continuing our series of Blog Entries to cover some MAS deployment scenarios either left out or not appropriately covered on the IBM Ansible scripts. We are going to also discuss considerations and tips and hints around these deployments. This time we are configuring automated DB2 backups against a pre-existing Storage Class and discussing limitations and considerations as well.

Due to the length of the procedure and considerations being discussed, we are splitting this blog post into two parts. This is Part 1 of 2.

References:

The related IBM documentation for the Ansible Role to backup and restore the Suite Configurations (not the same as the DB2 database) can be found at the URL https://github.com/ibm-mas/ansible-devops/tree/master/ibm/mas_devops/roles/suite_backup_restore.

The related IBM documentation for the Ansible Role to backup and restore Applications Configurations (such as Manage) (not the same as the Manage Database) can be found at the URL https://github.com/ibm-mas/ansible-devops/tree/master/ibm/mas_devops/roles/suite_app_backup_restore.

DB2 estimates for Storage Calculations can be found at https://www.ibm.com/docs/en/db2-for-zos/12?topic=data-general-approach-estimating-storage and https://studylib.net/doc/7013177/storage-system-volume-sizing-requirements. Capacity considerations can be found at https://www.ibm.com/docs/en/db2/11.5?topic=views-get-dbsize-info-database-size-capacity.

Content:

We wanted to periodically back up a DB2 database running inside OpenShift using the DB2 Operator that can be installed as part of the IBM Ansible install or using manual or our own Interloc scripting (not publicly available).

up files on the main DB2 Pod filesystem should not be a proper/good solution from a backup perspective.

As a result, there should be a DB2 Operator installed which manages a Db2u Cluster as seen in the below screenshot:

While there may be more than one DB2 instance (depending on deployment options and how many MAS Manage or Monitor/Predict, etc. instances are associated/connected to each DB2 instance), the general case being that we want to periodically and automatically backup one instance identified by its Project/Namespace in OpenShift. DB2 Warehouse instances as required by Monitor/IoT/Predict, etc. are considered as well.

The main consideration being that we need a Backup Storage Class that can be used to store the backups, typically we recommend a Storage Class that is outside of the OpenShift Cluster in terms of actual storage, such as a Cloud Provisioned Storage (such as NFS based) or a separate Storage Server (NAS, SAN, etc.) for on-premises. Also, for Cloud Storage, the recommendation is to have the actual Storage outside of the same Data Center and if possible, even in a different Region that hosts the OpenShift Cluster. Naming such Storage Class as the Backup Storage Class for the purposes of this Blog.

We are going to discuss important Backup Storage Class considerations and architectural design on a future blog post. But just wanted to mention that in a previous Blog Entry with URL: MAS Deployment Series: Configuring S3 Object Storage as an OpenShift Storage Class, we discussed the creation of a Storage Class suited for backups that can be configured in IBM Cloud as a “Global” S3/COS and therefore is not tied to specific geography constraints.

Also, the Storage Class should support Dynamic Provisioning (i.e. automatically creating Persistent Volumes or PVs from Persistent Volume Claims or PVCs).

We are also going to automatically execute the Backups using an OpenShift Cron Job that periodically executes the backup using the “db2” command line utility which is being passed a command to back up the database on a specific Target Path (see below).

The main dependencies and moving parts of the backup process are:

1. A Service Account and Cluster Role + Cluster Role Binding for authorizing the backups privileges required on the target MongoDB CE Project/Namespace.
2. The PVC contains the actual backup files. If the Storage Class supports dynamic provisioning as requested, then the PV will be dynamically created and depending on the strategy for the Storage Class a subfolder or other element will be created for the PV.
3. To have the backup path defined on the DB2 Pods, on the main “db2ucluster” Custom Resource for the Cluster; we are defining the standard “spec/storage” YAML node with name=”backup” as the target for the backup file. If not defined, we will define it. This will cause the DB2U Cluster to mount and restart the DB2 Pods with a PVC on the chosen Storage Class pointing to the associated Mount Point (“/mnt/backup” by default).
4. We are also putting the DB2 Archive Logs into a PVC using the same Backup Storage Class discussed above. And therefore, on the main “db2ucluster” Custom Resource for the Cluster; we are defining the standard “spec/storage” YAML node with name=”archivelogs” as the target for the backup file. If not defined, we will define it. This will cause the DB2U Cluster to mount and restart the DB2 Pods with a PVC on the chosen Storage Class pointing to the associated Mount Point (“/mnt/logs/archive” by default).
5. We are going to estimate the amount of disk space required for all the backups considering the desired Retention Policy.
6. We are going to configure some DB2 Parameters to associate to the Mount Points as per above and to make periodic backups possible and define retention policies.
7. The Backup Pod where the “oc” commands are being executed which is part of the OpenShift Cron Job
8. All that sequence of commands is executed by the Backup Pod via Shell Chaining using the “&&” “logical AND” sequential operator where for a command to be executed, the previous one must have returned a zero-value return code (and have been therefore executed successfully).

Input: Database Size (uncompressed backup): represented as N

Output: System / Metadata: 10 + N / 5

Output: User / Data: N * 2

Output: Active / Transaction Logs: N / 5

Output: Temporary: N / 3

Output: Backup: N * 20

Output: Archive Logs: N / 2

Those are used typically when configuring the different Storage parameters when creating the DB2 database instance. Some of them could be modified after the database has been created and deployed. In our case, for backup purposes we are only interested on the “Backup” size and the “Archive Logs” size. Which are calculated as N*20 and N/2 respectively. The formulas consider a daily backup schedule and retention policy of 1 month (including Archive Logs). If these assumptions change, the formulas should be modified accordingly.

We are also assuming a minimum size for the Backups destination of 300GB and for the Archive Logs of 150GB which should be reasonable. The minimum is used in case the calculation above yields number less than the minimum.

We also use other parameters that we reconfigure the database with, those are:

The DB2 parameter NUM_DB_BACKUPS: Specifies the number of backups to retain for a database before marking the oldest backup as deleted. We use “30” as the default value. Which with daily frequency means 30 days of backups.

The DB2 parameter REC_HIS_RETENTN: Specifies the number of days that historical information on backups is retained. We use “30” as the default value. Which with daily frequency means 30 days of backups. This parameter is combined with the above NUM_DB_BACKUPS and the most stringent is kept (so if more than one backup is taken daily, even for non-scheduled backups, more than “30” backups may be present on storage as all backups inside the specified time window will be retained).

The DB2 parameter AUTO_DEL_REC_OBJ: Specifies whether database log files, backup images, and load copy images should be deleted when their associated recovery history file entry is pruned. The default value we use is “ON” which works in conjunction with the previous two parameters.

The DB2 parameter LOGARCHMETH1: Specifies the Disk folder where the Archive Logs should be stored. The value is dependent on the Mount Point for the DB2 Archive Logs PVC as per above. Our default value is “DISK:/mnt/logs/archive”.

Now that we have discussed the considerations and numbers we use to configure the automatic backups, we will close this post and explain the step by step procedure of creating the PVCs/Mount Points, configuring DB2 instances and programming the backups themselves in our Part 2 which should come out soon.

Thanks for reading!