This topic describes how to link a DB2 staging dSource.

Prerequisites

• Be sure that the source and staging instances meet the host requirements and the databases meet the container requirements described in Requirements for DB2 Hosts and Databases.

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, an HADR single standby instance, or an HADR multiple standby instance. Non-HADR dSources can only be updated via a full dSource resync from a newer backup file

Non-HADR Database

• 1. See "Instance Owner Permissions" section above. 

  2. Ensure that the source database has the necessary user permissions for the provisioned VDBs as described in Database Permissions for Provisioned DB2 VDBs

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. 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 - Backup and Logs

The Delphix Recovery - Backup and Log 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 rollforward 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

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

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 fordsource creation.

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

   Consider the following when choosing an ingestion method.

   1. If you are working with an 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 Delphix Recovery, Backups and Logs checkboxes.
   2. To use a non-HADR method for database ingestion then you need to uncheck the HADR and Delphix Recovery, Backups and Logs checkboxes.
   3. To use the Delphix Recovery, Backups and Logs method for database ingestion then only check the Delphix Recovery, Backups and Logs checkbox and uncheck the HADR checkbox.

   
   

   
   

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

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

8. Enter the Log Archive Method1 you wish to use for the database. If no value is entered, the default value used is DISK:/mountpoint/dbname/arch.

9. Optionally, users can set the database configuration parameters during the linking operation in the Config Settings section.

10. Optionally, users can provide a bash script as a Toolkit defined hook. Delphix has two Toolkit managed Hooks:

    1. Toolkit 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. Toolkit 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"

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

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

    1. Check the 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 the plugin can process those load copy files during the log application 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 a full backup location.
       
       

       
       

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

       If a user has a dSource with an older version of the plugin and now wants to upgrade that dSource to the 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 "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 Delphix Recovery, Backups and Logs and 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.

    3. The Copy to Apply ( default) 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.

       Special considerations for Load copy files

       If load copy operations were performed on the source database and you want the Toolkit 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 Toolkit place the load copy files in a similar structure as it is on the source or set a DB2 registry parameter DB2LOADREC.

13. Click Next.

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

15. Click Next. 

16. 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 Owners.

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

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

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

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