This topic describes how to link a Db2 staging dSource.

Prerequisites

Source Database Preparation

  • Instance Owner Permissions

    Delphix uses the Db2 instance owner account on the dSource for multiple operations, including verifying the data inside the databases. In order to ingest a database on the staging server with different instance, we need permissions on the source database in order to perform a restore on the staging server. As an example, if we have an instance named "auto1051" at the source with a database name "delphix" and we want to create a dSource on the "auto1052" instance on the staging server, then you must explicitly grant DBADM and SECADM to the dSource instance "auto1052" on the source instance using the following steps:

    1. Connect to the source database as the source instance owner.
      1. connect to <DB_NAME> user <INSTANCE_OWNER>
    2. Issue database grant command
      1. grant DBADM, SECADM on database to user <DSOURCE_INSTANCE_OWNER>
    3. Repeat step 2 for every database to be included in the dSource, on the corresponding source database.

Determine if your dSource will be a non-HADR instance, a HADR single standby instance, or a HADR multiple standby instance. Non-HADR dSources can only be updated via a full dSource resync from a newer backup file

Non-HADR Database

HADR Single Standby Database

    1. All items in Non-HADR Database section above.
    2. The following database configuration settings must be set:
      1. update db cfg for <DB_NAME> using HADR_LOCAL_HOST <PRIMARY_IP> HADR_LOCAL_SVC <PRIMARY_PORT > immediate
      2. update db cfg for <DB_NAME> using HADR_REMOTE_HOST <STANDBY_IP> HADR_REMOTE_SVC <STANDBY_PORT> immediate
      3. update db cfg for <DB_NAME> using HADR_REMOTE_INST <STANDBY_INSTANCE_NAME> immediate
      4. update db cfg for <DB_NAME> using HADR_SYNCMODE SUPERASYNC immediate
    3. If database configuration parameter LOGINDEXBUILD is set to OFF, do the following:
        1. update db cfg for <DB_NAME> using LOGINDEXBUILD ON
        2. Force off all connections to the database and reactivate the database
    4. If database configuration parameter LOGARCHMETH1 is set to OFF, do the following:
      1. update db cfg for <DB_NAME> using LOGARCHMETH1 XXXX (must be a valid log archiving method)
      2. Take an offline backup
    5. If LOGARCHMETH1 points to a third-party backup server (i.e. TSM or Netbackup) define LOGARCHMETH2 to disk
      1. update db cfg for <DB_NAME> using LOGARCHMETH2 DISK:<full path to archive log directory>
        1. Log files in the directory must be available from the time of the backup until the restore has successfully completed on the dSource.
    6. Db2 start hadr on db <DB_NAME> as primary by force
    7. Take a full online backup as defined in the "Backup Source Database" section below.
    8. Record the following information, as it must be entered on the Delphix Engine while creating the dSource.
      1. HADR Primary hostname
      2. HADR Primary SVC
      3. HADR Standby SVC (auxiliary standby port)

HADR Multiple Standby Databases

This assumes a single standby database HADR setup already exists. The existing standby will be referred to as the main standby. The new delphix standby will be referred to as the auxiliary standby.

    1. The following database configuration settings must be set on the primary database:
      1. update db cfg for <DB_NAME> using HADR_SYNCMODE <SYNC MODE> immediate – set whichever sync mode you wish to use on your main standby.
      2. update db cfg for <DB_NAME> using HADR_TARGET_LIST "<MAIN_STANDBY_IP:MAIN_STANDBY_PORT|AUXILIARY_STANDBY_IP:AUXILIARY_STANDBY_PORT>" immediate
        1. You may have up to two auxiliary standbys defined separated by a '|'; one of which must be the delphix dSource.
    2. stop hadr on db <DB_NAME>

    3. start hadr on db <DB_NAME> as primary by force
    4. Take a full online backup as defined in the "Backup Source Database" section below. While this backup is running, you may continue with step 5.
    5. The following database configuration settings must be set on the existing main standby database:
      1. update db cfg for <DB_NAME> using HADR_SYNCMODE <same mode as defined in 1.a above.> – It must be the same value used for primary database.
      2. update db cfg for <DB_NAME> using HADR_TARGET_LIST "<PRIMARY_IP:PRIMARY_PORT|MAIN_STANDBY_IP:MAIN_STANDBY_PORT>"
    6. stop hadr on db <DB_NAME>
    7. start hadr on db <DB_NAME> as standby
    8. Record the following information, as it must be entered on the Delphix Engine while creating the dSource (the auxiliary standby database):
      1. HADR Primary hostname
      2. HADR Primary SVC
      3. HADR Standby SVC (auxiliary standby port)
      4. HADR_TARGET_LIST <PRIMARY_IP:PRIMARY_PORT|MAIN_STANDBY_IP:MAIN_STANDBY_PORT>

Delphix Recovery with Backup and Logs

The Delphix Recovery with Backup and Logs ingestion feature allows users to provide Delphix with the location of the source database archive logs. Full backups only have to be restored during dSource creation, or during RESYNC operations when there are exceptions, for example, lost/corrupt log files. Archived logs get applied during the snapshot operation. 

A full backup is required to create a dSource and prior to the snapshot operation, users can provide the archive logs to update the dSource during the snapshot operation, user-provided logs will be applied to the staging database. Applying the logs on a regular basis allows users to keep their dSource in sync with their source database. The staging database will always be in a roll forward pending state. Log validation is performed by the Plugin before applying the logs.  Plugin managed Pre and Post Hooks can be used for preparing and purging the full backups and archive logs. A list of logs which are available for purging is stored in the snapshot metadata of the dSource.

HADR and non-HADR configured dSources can be moved to this feature by performing a RESYNC operation.

Delphix supports Delphix Recovery based ingestion with Db2 version 10.5 and 11.1 To use Delphix Backup and Log ingestion simply check the checkbox provided during dSource creation. Delphix Backup and Log ingestion require that:

  • A full backup must be accessible on the staging server, it could be a shared location but must be mounted on the staging host and visible as a file system.
  • Archived logs must also be accessible on the staging server
    • The following command can be used to generate archive logs from the source database; 
      • Db2 archive log for database <database name>
    • Instance users must have read permissions on logs so that Delphix-managed recover can apply the logs.
    • Instance users must have read permissions on logs and additional write permissions on customer provided log location if Delphix is planning to use customer provided log location for applying the logs.
  • If database configuration parameter LOGINDEXBUILD is set to OFF, do the following:
    • update db cfg for <DB_NAME> using LOGINDEXBUILD ON
    • Force off all connections to the database and reactivate the database
  • Log purging from the provided log location is done by the customer. Delphix only provides the list of logs which are eligible for purging.

Limitations:

  • Continuous updates to the dSource are not available. The DB_SYNC operations apply new logs from the log location and keep the dSource updated with changes on the source.
  • Read access to the staging database is not available.

Backup Source Database

New Feature: Source Database with Raw DEVICE type Storage

Several users use raw device-based tablespaces for source Db2 databases. To leverage these environments with Delphix, Delphix has built a workflow using Db2s native tools that allow Delphix to discover and convert a raw device-based tablespace into an automatic storage-based tablespace during ingestion. Once the data is ingested into staging, customers will be able to provision VDBs of the automatic storage-based database.


In order to complete the linking process, the Standby dSource must have access to a full backup of the source Db2 databases on disk. This should be a compressed online Db2 backup and must be accessible to the dSource instance owner on disk. Delphix is currently not set up to accept Db2 backups taken using third-party sources such as Netbackup or TSM. All three features of data ingestion HADR, Non-HADR, Delphix Recovery with Backup and Log Ingestion backups must also include logs.

Example backup command: Db2 backup database <DB_NAME> online compress include logs

Best Practices for Taking a Backup

The following best practices can help improve backup and restore performance:

  1. Compression should be enabled

  2. Following parameters should be optimally configured:

    1. Utility Heap Size (UTIL_HEAP_SZ)

    2. No. of CPUs

    3. No. of Table Spaces

    4. Extent Size

    5. Page Size

  3. Parallelism & Buffer configuration may be used to improve backup performance. Parameters that should be configured are:

    1. Parallelism

    2. Buffer Size

    3. No. of Buffers

More information about backup best practices is available in IBM Knowledge Center

Procedure

  1. Login to the Delphix Management Application.
  2. On the Databases tab of the Environment Management screen, add a source config against discovered staging instance.
  3. Then, click Manage and select Datasets.

  4. Click the Plus (+) icon and select Add dSource, you’ll get a list of available source configs using which you can go for dsource creation.

  5. In the Add dSource wizard, select the required source configuration.  

    UI changes were done on add dSource page

    Field name changes are as follows :

    Old field name 

    New field name

    Type

    Delphix Recovery, Backups and Logs

    Customer Supplied Archive Logs

    Checkbox

    Log Path

    Archive Log Path

    Text

    Copy to Apply

    Archive Log Location is READ ONLY

    Checkbox

    HADR

    Link against source using HADR

    Checkbox

    Config Settings

    Staging Database Configuration Settings

    Text

  6. Consider the following when choosing an ingestion method.

    1. If you are working with a HADR setup and want to ingest the database on a staging using HADR configurations, you need to check the HADR checkbox and need to uncheck the Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs) checkbox.
    2. To use a non-HADR method for database ingestion then you need to uncheck the HADR and Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs) checkboxes.
    3. To use the Delphix Recovery, Backups and Logs method for database ingestion then only check the Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs) checkbox and uncheck the HADR checkbox. 


  7. The database name is mandatory and must be unique for a given instance. This is the name that the database was on the instance it was restored from. 

  8. Enter the complete Backup Path where the database backup file resides. If no value is entered, the default value used is the instance home directory. If there are multiple backup files for a database on the backup path, the most current one will be used.

  9. Log Archive Method1 field has been removed from the UI. The default value used in db cfg will be DISK:/mountpoint/dbname/arch.

  10. Optionally, users can set the database configuration parameters during the linking operation in the Staging Database Configuration Settings section.

  11. Optionally, users can provide a bash script as a Plugin defined hookDelphix has two Plugin managed Hooks:

    1. Plugin Pre-Restore Hook: The bash script entered in this field will get executed before restoration. This hook is useful in such cases where the users want to prepare the full backup before the restore operation.
    2. Plugin Post-Restore Hook: The bash script entered in this field will get executed after completion of the restore operation. This hook is useful in such cases where the user wants to purge the full backup file after the restore operation.

      1. Plugin managed pre-Hook - User can copy the required full backup file from some remote location to staging host. Below is an example of such bash script (name of below script is copyBackup.sh)
      #!/bin/bash
      
      
      # Copying backup file from a remote host remote.delphix.com
      scp auto1051@remote.delphix.com:/backuplocation/R74D105A.0.auto1051.DBPART000.20180510073639.001 /DB2backup/
      
      
      The user can provide the above script in Plugin managed pre-Hook field as "sh copyBackup.sh"
      
      
      2. Plugin managed post-Hook - User can purge the full backup file from the staging host. Below is an example of such bash script (name of below script is purgeBackup.sh)
      #!/bin/bash
      
      
      # Purging full backup file from staging host for saving the staging storage
      rm -f /DB2backup/R74D105A.0.auto1051.DBPART000.20180510073639.001
      
      
      The user can provide the above script in Plugin managed post-Hook field as "sh purgeBackup.sh"
  12. If the dSource is to use HADR please enter the following fields. For more information about HADR please view Linking a dSource from a Db2 Database: An Overview.

    a. Enter a fully qualified HADR Primary Hostname. This is a required field for HADR and must match the value set for HADR_LOCAL_HOST on the master.

    b. Enter the port or /etc/services name for the HADR Primary SVC. This is a required field for HADR and uses the value set for HADR_LOCAL_SVC on the master.

    c. Enter the port or /etc/services name for the HADR Standby SVC. This is a required field for HADR and uses the value set for HADR_REMOTE_SVC on the master.

  13. If you are using Delphix Recovery with Backup and Log Ingestion for dSource creation complete the following fields:

    1. Check the Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs) checkbox.

    2. Provide the log location of the archive logs. In this location, users can load copy files by appending schema and table name to it so that plugin can process those load copy files during log apply process.  Archive logs will be used for applying the logs during the snapshot operation. This location must be a part of the staging server and this location must be different than full backup location.

    3. To use the Delphix Recovery, Backups and Logs method for database ingestion then only check the Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs) checkbox and make sure HADR checkbox must be unchecked.

      Special consideration during dSource upgrade from older version plugin to Delphix Recovery, Backups and Logs feature enabled plugin

      Suppose the user is having a dSource with an older version of the plugin and now wants to upgrade that dSource to Delphix Recovery, Backups and Logs feature then the user needs to perform the following steps :

      1. Go to the configuration tab of dSource and then click on the custom sub-tab.

      2. Click on the edit icon.

      3. Check the Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs) and Archive Log Location is READ ONLY (formerly known as Copy to Apply) checkbox.

      4. Click on submit and then go for RESYNC operation to upgrade the dSource.

      How to upgrade an existing dSource to "Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs)" feature

      If the user has a dSource (with HADR or non-HADR configuration) and now wants to upgrade that dSource to Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs) feature then the user needs to perform the following steps:
      1. Go to the configuration tab of dSource and then click on the custom sub-tab.
      2. Click on the edit icon.
      3. Check the Customer Supplied Archive Logs (formerly known as Delphix Recovery, Backups and Logs) and Archive Log Location is READ ONLY (formerly known as Copy to Apply) checkbox and make sure HADR checkbox must be unchecked.
      4. Provide the log path where the user will keep the archive logs, this path should be on staging and must have read permission to instance user. Providing logs is not mandatory for this option; backups without logs can continue to be used for staging purposes
      5. Click on submit and then go for RESYNC operation to upgrade the dSource.

    4. The Archive Log Location is READ ONLY (formerly known as Copy to Apply) option indicates whether Delphix should copy the log files to the dSource’s filesystem prior to the log apply process. If this is not selected the staging database will read the log files directly from the user-provided log location and perform the log apply the operation. By default, this option would be checked on UI.

      Special considerations for Load copy files

      If load copy operations were performed on the source database and you want the Plugin to process the load files place the load copy files in your location and append the schema and table names (for example: <DB Name>.<Number>.<Instance>.DBPART000.<Timestamp>.001.<schema name>.<table name>) to the load copy file names

      If you do not want to process load copy files via the Plugin place the load copy files in a similar structure as it is on the source or set a Db2 registry parameter DB2LOADREC.

  14. Click Next.

  15. Enter a dSource name and select a group for your dSource. Adding a dSource to a dataset group lets you set Delphix Domain user permissions for that database and its objects, such as snapshots. 
  16. Click Next

  17. Set the Staging Environment to be the same as the dSource host. Select the Staging Environment User to be the same as the instance owner of the dSource instance.

    Changing the Environment User

    If you need to change or add an environment user for the dSource instance, see Managing Db2 Users and Instance.
  18. Then, click Next and you’ll get the Policies section. Set the desired Snapsync Policy for the dSource. For more information on policies see Advanced Data Management Settings for Db2 dSources

  19. Click Next and specify any desired pre- and post-scripts. For details on pre- and post-scripts, refer to Customizing Db2 Management with Hook Operations 

  20. Click Next. Review the dSource Configuration and Data Management information in the Summary section.

  21. Click Submit.

The Delphix Engine will initiate two jobs to create the dSource: DB_Link and DB_Sync. You can monitor these jobs by clicking Active Jobs in the top menu bar, or by selecting System > Event Viewer. When the jobs have completed successfully, the database icon will change to a dSource icon on the Environments > Host > Databases screen, and the dSource will also appear in the list of Datasets under its assigned group.

The dSource Configuration Screen

After you have created a dSource, the dSource Configuration tab allows you to view information about it and make modifications to its policies and permissions. In the Datasets panel, select the dSource you wish to examine. You can now choose the configuration tab to see information such as the Source files, Data Management configuration, and Hook Operations. For more information, see Advanced Data Management Settings for Db2 dSources

Purging of archive logs after snapshot operation

Logs can be purged from your log location after the snapshot operation. The Plugin will preserve the list of logs which are eligible for purging in the snapshot's metadata. The snapshot metadata can be accessed via the Delphix CLI or API.