Deploying highly available IBM Db2 11.1 on Compute Engine with automatic client reroute and a network tiebreaker

In this tutorial, you deploy a highly available IBM Db2 cluster in Google Cloud. You use High Availability Disaster Recovery (HADR) for transaction replication and Tivoli System Automation for Multiplatforms (TSAMP) for automated failover.

This tutorial is useful if you are a system admin, developer, engineer, or database admin and you want to deploy a highly available Db2 cluster on Compute Engine.

The tutorial assumes that you are familiar with the following:

  • Red Hat Enterprise Linux 7
  • IBM Db2 11.1
  • Compute Engine
  • Cloud Storage

Architecture

The following architectural diagram illustrates your environment.

Architectural diagram of the three deployed instances

  • There are two Db2 instances. db2-a is the primary instance and db2-b is the standby instance. These instances are in two different zones.
  • There is a client instance, db2-client, in a different zone.
  • Transactions from the primary instance are replicated to the standby instance by using HADR SYNC replication.
  • The cluster uses TSAMP for failover automation.
  • You use Google Cloud's metadata server as a TSAMP network quorum.
  • The cluster uses ACR to remain highly available after a failover.

Key terms

IBM Db2
An enterprise grade RDBMS (relational database management system), with replication and failover capabilities.
HADR (High Availability Disaster Recovery)
A capability for Db2 that uses database logged activity to replicate data from the primary database to the standby. It also allows to manually fail over from the primary database to the standby database.
TSAMP (Tivoli System Automation for Multiplatforms)
A cluster management software that facilitates automatic switching of users, apps, and data from one database system to another in a cluster. Db2 enterprise edition comes with TSAMP entitlement for HADR included.
ACR (Automatic Client Reroute)
A Db2 feature that seamlessly redirects client apps from a failed server to an alternate server so that the apps can continue working with minimal interruption.

Objectives

  • Install Db2 on the two Compute Engine instances.
  • Configure HADR and ACR on both instances.
  • Configure TSAMP on both instances.
  • Install Db2 Client Runtime Libraries on the client instance.
  • Verify that ACR and TSAMP work properly.

Costs

This tutorial uses the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

Before you begin

  1. Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

  2. In the Cloud Console, on the project selector page, select or create a Cloud project.

    Go to the project selector page

  3. Zorg dat facturering is ingeschakeld voor uw project.

    Meer informatie over het inschakelen van facturering

  4. Enable the Compute Engine API.

    Enable the API

When you finish this tutorial, you can avoid continued billing by deleting the resources you created. For more information, see Cleaning up.

Downloading the Db2 11.1 data server trial installation file

  1. Download IBM DB2 database.

  2. Log in with your IBM account ID or sign up for a new account.

  3. Click the Download using http tab.

    Download using http tab

  4. Download DB2 11.1 data server trial for Linux® on AMD64 and Intel® EM64T systems (x64)

    Download B2 11.1 data server trial for Linux® on AMD64 and Intel® EM64T systems (x64)

Downloading the Db2 Client Runtime installation file

  1. Go to the IBM Data Server Runtime Client Download Page. Use the same credentials from the previous section to log in.

  2. Click IBM Data Server Runtime Client (Linux AMD64 and Intel EM64T) version 11.1, and then click Continue.

    Download IBM Data Server Runtime Client (Linux AMD64 and Intel EM64T) version 11.1

  3. On the Download using http tab, click Download now.

Preparing your environment

  1. In the Google Cloud Console, click Activate Cloud Shell.

    GO TO Cloud Shell

  2. Create a service account.

    gcloud iam service-accounts create db2-instance --display-name "db2-instance"
    
  3. Add the storage.objectEditor role to the db2-instance service account.

    gcloud projects add-iam-policy-binding ${DEVSHELL_PROJECT_ID} \
        --member=serviceAccount:db2-instance@${DEVSHELL_PROJECT_ID}.iam.gserviceaccount.com \
        --role=roles/storage.objectAdmin
    

    This role is required to view and download files from the Cloud Storage bucket.

  4. Create a Db2 instance called db2-a.

    gcloud compute instances create db2-a \
        --image-family=rhel-7 \
        --image-project=rhel-cloud \
        --tags=db2 \
        --machine-type=n1-standard-2 \
        --zone=us-central1-b --boot-disk-size=50GB \
        --service-account=db2-instance@`gcloud config get-value project`.iam.gserviceaccount.com \
        --scopes=https://www.googleapis.com/auth/cloud-platform
    
  5. Create another Db2 instance called db2-b.

    gcloud compute instances create db2-b \
        --image-family=rhel-7 \
        --image-project=rhel-cloud \
        --tags=db2 \
        --machine-type=n1-standard-2 \
        --zone=us-central1-c \
        --boot-disk-size=50GB   \
        --service-account=db2-instance@`gcloud config get-value  project`.iam.gserviceaccount.com \
        --scopes=https://www.googleapis.com/auth/cloud-platform
    
  6. Create the client instance called db2-client.

    gcloud compute instances create db2-client \
        --image-family=rhel-7 \
        --image-project=rhel-cloud \
        --tags=db2-client \
        --zone=us-central1-f \
        --boot-disk-size=10GB \
        --service-account=db2-instance@`gcloud config get-value project`.iam.gserviceaccount.com \
        --scopes=https://www.googleapis.com/auth/cloud-platform
    
  7. Enable Db2 communication between the instances.

    gcloud compute firewall-rules create db2-internal  \
        --allow="tcp:55000,tcp:60000"  \
        --source-tags db2 \
        --target-tags db2
    
  8. Enable communication between the client and the Db2 instances.

    gcloud compute firewall-rules create db2-client  \
        --allow="tcp:55000" \
        --source-tags db2-client \
        --target-tags db2
    
  9. Enable TSAMP communication between the two Db2 instances.

    gcloud compute firewall-rules create db2-tsamp  \
        --allow="udp:12347-12348,udp:657,tcp:657,icmp" \
        --source-tags db2 \
        --target-tags db2
    

Setting up a Cloud Storage bucket

You create a Cloud Storage bucket to hold the Db2 installation and backup files that are used throughout this tutorial.

  1. Use gsutil to create a Cloud Storage bucket, where [GCS_BUCKET_NAME] represents the name of the bucket. For the purpose of this tutorial, use the us-central1 region.

    gsutil mb -c regional -l us-central1 gs://[GCS_BUCKET_NAME]
    
  2. In the Cloud Console, upload your Db2 installation file to your new Cloud Storage bucket.

  3. In the Cloud Console, upload your Db2 Client Runtime installation file to your new Cloud Storage bucket.

Setting up Db2 on the db2-a instance

To set up the db2-a instance, you have to create users and user groups before installing DB2 on the primary instance.

Prepare the db2-a instance

You create the required users and groups and install the prerequisite software packages on the primary instance.

  1. In the Cloud Console, go to the VM instances page.

    GO TO THE VM INSTANCES PAGE

  2. In the db2-a instance row, click SSH.

  3. Add the required user groups. For more information about users and groups, see Creating group and user IDs for Db2 installation.

    sudo groupadd -g 1999 db2iadm1
    sudo groupadd -g 1998 db2fsdm1
    sudo groupadd -g 1997 dasadm1
    
  4. Add the required users.

    sudo useradd -u 2004 -g db2iadm1 -m -d /home/db2inst1 db2inst1
    sudo useradd -u 2003 -g db2fsdm1 -m -d /home/db2fenc1 db2fenc1
    sudo useradd -u 2002 -g dasadm1 -m -d /home/dasusr1 dasusr1
    
  5. Set the password for the Db2 instance user. For the purpose of this tutorial, use PasswordDb23@.

    sudo bash -c 'echo "db2inst1:PasswordDb23@" | chpasswd'
    
  6. Update the swappiness parameter. For more information, see Kernel parameter requirements.

    sudo bash -c 'echo "vm.swappiness=5" >> /etc/sysctl.conf'
    
  7. Install the prerequisites.

    sudo yum -y install  numactl libaio ksh perl-Sys-Syslog perl
    
  8. Create the installation directory.

    sudo mkdir -p /db2/install
    
  9. Copy your db2 installation file.

    sudo gsutil cp gs://[GCS_BUCKET_NAME]/v11.1_linuxx64_server_t.tar.gz /db2/install/
    

    Where:

    [GCS_BUCKET_NAME] represents the Cloud Storage bucket you created at the beginning of this tutorial.

Install Db2 on the db2-a instance

  1. Create a Db2 installation parameters file.

    sudo bash -c 'cat <<EOF  > /db2/install/hadr_config.rsp
    PROD                          = DB2_SERVER_EDITION
    FILE                          = /db2/bin
    LIC_AGREEMENT                 = ACCEPT
    INSTANCE                      = DB2_INST
    DB2_INST.NAME                 = db2inst1
    DB2_INST.GROUP_NAME           = db2iadm1
    DB2_INST.AUTOSTART            = NO
    DB2_INST.START_DURING_INSTALL = NO
    DB2_INST.PORT_NUMBER          = 55000
    DB2_INST.FENCED_USERNAME      = db2fenc1
    DB2_INST.FENCED_GROUP_NAME    = db2fsdm1
    INSTALL_TYPE                  = CUSTOM
    COMP                          = TSAMP
    EOF'
    
  2. Unzip the installation file.

    sudo bash -c "cd /db2/install && tar -xvf v11.1_linuxx64_server_t.tar.gz"
    
  3. Create the Db2 working directories.

    sudo mkdir /db2/data
    sudo mkdir /db2/backup
    sudo mkdir /db2/logs
    
  4. Assign ownership to the working directories.

    sudo chown db2inst1:db2iadm1 /db2/data
    sudo chown db2inst1:db2iadm1 /db2/logs
    sudo chown db2inst1:db2iadm1 /db2/backup
    
  5. Launch the Db2 installer.

    sudo bash -c "/db2/install/server_t/db2setup -f sysreq -r /db2/install/hadr_config.rsp"
    
  6. Start the database instance.

    sudo su - db2inst1 -c "db2start"
    

Setting up Db2 on the db2-b instance

You now create the required users and groups and install the prerequisite software packages on the standby instance.

Prepare the db2-b instance

  1. In the Cloud Console, use ssh to connect to the db2-b instance.

  2. Add the required user groups:

    sudo groupadd -g 1999 db2iadm1
    sudo groupadd -g 1998 db2fsdm1
    sudo groupadd -g 1997 dasadm1
    
  3. Add the users.

    sudo useradd -u 2004 -g db2iadm1 -m -d /home/db2inst1 db2inst1
    sudo useradd -u 2003 -g db2fsdm1 -m -d /home/db2fenc1 db2fenc1
    sudo useradd -u 2002 -g dasadm1 -m -d /home/dasusr1 dasusr1
    
  4. Set the password for the Db2 instance user. For the purpose of this tutorial, use PasswordDb23@.

    sudo bash -c 'echo "db2inst1:PasswordDb23@" | chpasswd'
    
  5. Update the swappiness parameter.

    sudo bash -c 'echo "vm.swappiness=5" >> /etc/sysctl.conf'
    
  6. Instal the prerequisites.

    sudo yum -y install numactl libaio ksh perl-Sys-Syslog perl
    
  7. Create the installation directories.

    sudo mkdir -p /db2/install
    
  8. Copy your Db2 installation file.

     sudo gsutil cp gs://[GCS_BUCKET_NAME]/v11.1_linuxx64_server_t.tar.gz /db2/install/
    

    Where:

    [GCS_BUCKET_NAME] represents the bucket you created at the beginning of this tutorial.

Install Db2 on the db2-b instance

  1. Create a Db2 installation parameters file.

    sudo bash -c 'cat <<EOF  > /db2/install/hadr_config.rsp
    PROD                          = DB2_SERVER_EDITION
    FILE                          = /db2/bin
    LIC_AGREEMENT                 = ACCEPT
    INSTANCE                      = DB2_INST
    DB2_INST.NAME                 = db2inst1
    DB2_INST.GROUP_NAME           = db2iadm1
    DB2_INST.AUTOSTART            = NO
    DB2_INST.START_DURING_INSTALL = NO
    DB2_INST.PORT_NUMBER          = 55000
    DB2_INST.FENCED_USERNAME      = db2fenc1
    DB2_INST.FENCED_GROUP_NAME    = db2fsdm1
    INSTALL_TYPE                  = CUSTOM
    COMP                          = TSAMP
    EOF'
    
  2. Unzip the installation file.

    sudo bash -c "cd /db2/install && tar -xvf v11.1_linuxx64_server_t.tar.gz"
    
  3. Create the Db2 working directories.

    sudo mkdir /db2/data
    sudo mkdir /db2/backup
    sudo mkdir /db2/logs
    
  4. Assign ownership to the working directories.

    sudo chown db2inst1:db2iadm1 /db2/data
    sudo chown db2inst1:db2iadm1 /db2/logs
    sudo chown db2inst1:db2iadm1 /db2/backup
    
  5. Launch the Db2 installer.

    sudo bash -c "/db2/install/server_t/db2setup -f sysreq -r /db2/install/hadr_config.rsp"
    
  6. Start the database instance.

    sudo su - db2inst1 -c "db2start"
    

Copying the database from the db2-a instance to the db2-b instance

You create a database on the db2-a instance, configure it for transaction logging, and then back it up and restore it on the db2-b instance.

  1. In the Cloud Console, use ssh to connect to the db2-a instance.

  2. Create the database.

    sudo bash -c 'su - db2inst1  \
        -c "db2 CREATE DATABASE TESTDB  \
        AUTOMATIC STORAGE YES ON /db2/data DBPATH ON /db2/logs"'
    
  3. Enable log archiving.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using LOGARCHMETH1 LOGRETAIN"'
    
  4. Back up the database.

    sudo bash -c 'su - db2inst1 \
        -c "db2 backup database TESTDB TO /db2/backup/"'
    
  5. Copy the backup to your Cloud Storage bucket.

    sudo gsutil cp /db2/backup/* gs://[GCS_BUCKET_NAME]/backup/
    
  6. In the Cloud Console, use ssh to connect to the db2-b instance.

  7. Download the backup file.

    sudo gsutil cp gs://[GCS_BUCKET_NAME]/backup/* /db2/backup/
    
  8. Restore the database from the backup file.

    sudo bash -c 'su - db2inst1 \
        -c "db2 restore database TESTDB FROM /db2/backup"'
    

Configuring HADR on the db2-a instance

You now configure all of the HADR parameters on the primary instance. This is required to start the replication process later.

  1. In the Cloud Console, use ssh to connect to the db2-a instance.

  2. Specify the HADR primary, db2-a, and standby, db2-b, servers.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_local_host db2-a"'
    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_remote_host db2-b"'
    
  3. Specify the HADR primary and standby service names.

    sudo bash -c 'su - db2inst1 \
        -c  "db2 update db cfg for TESTDB using hadr_local_svc 60000"'
    sudo bash -c 'su - db2inst1 \
        -c  "db2 update db cfg for TESTDB using hadr_remote_svc 60000"'
    
  4. Specify the Db2 instance name for the standby server.

    sudo bash -c 'su - db2inst1 -c "db2 update db cfg for TESTDB using hadr_remote_inst db2inst1"'
    
  5. Specify the time (in seconds) that the HADR process waits before considering a communication attempt to have failed. For more information abouthadr_timeout, see Setting the hadr_timeout and hadr_peer_window database configuration parameters.

    sudo bash -c 'su - db2inst1 -c "db2 update db cfg for TESTDB using hadr_timeout 120"'
    
  6. Set the HADR synchronization mode to synchronous replication.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_syncmode SYNC"'
    
  7. Enable replication of index operations.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using LOGINDEXBUILD ON"'
    
  8. Specify the time (in seconds) that the primary instance waits for the standby to come back online after it loses connection:

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_peer_window 300"'
    
  9. Set up automatic client reroute.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update alternate server for database TESTDB using hostname db2-b port 55000 "'
        sudo bash -c 'su - db2inst1 -c "db2 terminate"'
    
  10. Prepare the server for TSAMP clustering.

    sudo preprpnode db2-a db2-b
    
  11. Verify your configuration values.

    sudo bash -c 'su - db2inst1 -c "db2 get db cfg for TESTDB | grep HADR"'
    

    The output is:

    HADR database role                                      = STANDARD
    HADR local host name                  (HADR_LOCAL_HOST) = db2-a
    HADR local service name                (HADR_LOCAL_SVC) = 60000
    HADR remote host name                (HADR_REMOTE_HOST) = db2-b
    HADR remote service name              (HADR_REMOTE_SVC) = 60000
    HADR instance name of remote server  (HADR_REMOTE_INST) = db2inst1
    HADR timeout value                       (HADR_TIMEOUT) = 120
    HADR target list                     (HADR_TARGET_LIST) =
    HADR log write synchronization mode     (HADR_SYNCMODE) = SYNC
    HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
    HADR log replay delay (seconds)     (HADR_REPLAY_DELAY) = 0
    HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 300
    HADR SSL certificate label             (HADR_SSL_LABEL) =
    

Configuring HADR on the db2-b instance

You now configure all of the HADR parameters on the standby instance. This is required to start the replication process later.

  1. In the Cloud Console, use ssh to connect to the db2-b instance.

  2. Specify the HADR primary, db2-a, and standby, db2-b, servers.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_local_host db2-b"'
    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_remote_host db2-a"'
    
  3. Specify the HADR primary and standby service names.

    sudo bash -c 'su - db2inst1 \
        -c  "db2 update db cfg for TESTDB using hadr_local_svc 60000"'
    sudo bash -c 'su - db2inst1 \
        -c  "db2 update db cfg for TESTDB using hadr_remote_svc 60000"'
    
  4. Specify the Db2 instance name for the primary server.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_remote_inst db2inst1"'
    
  5. Specify the time (in seconds) that the HADR process waits before considering a communication attempt to have failed.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_timeout 120"'
    
  6. Specify the HADR synchronization mode.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_syncmode SYNC"'
    
  7. Enable replication of index operations.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using LOGINDEXBUILD ON"'
    
  8. Specify the time (in seconds) that the primary instance waits for the standby to come back online after it loses connection.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update db cfg for TESTDB using hadr_peer_window 300"'
    
  9. Set up automatic client reroute.

    sudo bash -c 'su - db2inst1 \
        -c "db2 update alternate server for database TESTDB using hostname db2-a port 55000 "'
    sudo bash -c 'su - db2inst1 -c "db2 terminate "'
    
  10. Prepare the server for TSAMP clustering.

    sudo preprpnode db2-a db2-b
    
  11. Verify your configuration values.

    sudo bash -c 'su - db2inst1 -c "db2 get db cfg for TESTDB | grep HADR"'
    

    The output is:

    HADR database role                                      = STANDARD
    HADR local host name                  (HADR_LOCAL_HOST) = db2-b
    HADR local service name                (HADR_LOCAL_SVC) = 60000
    HADR remote host name                (HADR_REMOTE_HOST) = db2-a
    HADR remote service name              (HADR_REMOTE_SVC) = 60000
    HADR instance name of remote server  (HADR_REMOTE_INST) = db2inst1
    HADR timeout value                       (HADR_TIMEOUT) = 120
    HADR target list                     (HADR_TARGET_LIST) =
    HADR log write synchronization mode     (HADR_SYNCMODE) = SYNC
    HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
    HADR log replay delay (seconds)     (HADR_REPLAY_DELAY) = 0
    HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 300
    HADR SSL certificate label             (HADR_SSL_LABEL) =
    

Starting HADR

After configuring the two instances for their HADR roles, you now start the replication.

  1. In the Cloud Console, use ssh to connect to the db2-b instance.

  2. Deactivate the database.

    sudo bash -c 'su - db2inst1 -c "db2  deactivate db TESTDB"'
    
  3. Start HADR as the standby instance.

    sudo bash -c 'su - db2inst1 -c "db2  start hadr on db TESTDB as standby"'
    
  4. Verify the role is Standby.

    sudo bash -c 'su - db2inst1 -c "db2 get snapshot for db on TESTDB | grep Role"'
    

    The output is:

    Role                   = Standby
    
  5. In the Cloud Console, use ssh to connect to the db2-a instance.

  6. Activate the database.

    sudo bash -c 'su - db2inst1 -c "db2  activate db TESTDB"'
    
  7. Start HADR as primary.

    sudo bash -c 'su - db2inst1 -c "db2  start hadr ON db TESTDB as primary"'
    
  8. Verify that the role is Primary.

    sudo bash -c 'su - db2inst1 -c "db2 get snapshot for db on TESTDB  | grep Role"'
    

    The output is:

    Role                   = Primary
    
  9. Create a data population script.

    sudo bash -c  "cat <<EOF > /home/db2inst1/populate.sql
    CONNECT TO TESTDB;
    CREATE TABLE source_table(id integer NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    timestamp timestamp NOT NULL WITH DEFAULT CURRENT_TIMESTAMP,
    event_data integer DEFAULT NULL,
    PRIMARY KEY (id));
    --#SET TERMINATOR ??
    CREATE PROCEDURE simulate_data()
    BEGIN
     DECLARE I INTEGER DEFAULT 0;
     WHILE I < 5000 DO
      INSERT INTO source_table (event_data) VALUES (ROUND(RAND()*15000,2));
      SET I = I + 1;
     END WHILE;
    END??
    --#SET TERMINATOR ;
    CALL simulate_data();
    SELECT * FROM source_table LIMIT 10;
    TERMINATE;
    EOF"
    
  10. Create and populate the table.

    sudo bash -c 'su - db2inst1 -c "db2 -tvf /home/db2inst1/populate.sql"'
    
  11. In the Cloud Console, use ssh to connect to the db2-b instance.

  12. Initiate a takeover. db2-b becomes the primary instance so you can determine if the test table and data have been replicated.

    sudo bash -c 'su - db2inst1 -c "db2 takeover hadr on db TESTDB"'
    
  13. Verify that the role of the db2-b instance is Primary.

    sudo bash -c 'su - db2inst1 -c "db2 get snapshot for db on TESTDB | grep Role"'
    

    The output is:

    Role                   = Primary
    
  14. Verify the replicated data on the standby instance.

    sudo bash -c 'su - db2inst1 \
        -c "db2 -t connect to testdb && db2 -t select \*  \
        from source_table limit 10 &&  \
        db2 -t select count\(\*\) as count from source_table;"'
    

    The output shows the first 10 lines of the table and indicates there are 5000 rows in total:

    ID          TIMESTAMP                  EVENT_DATA
    ----------- -------------------------- -----------
    1 2018-11-03-09.29.58.159482         208
    2 2018-11-03-09.29.58.162545       11326
    3 2018-11-03-09.29.58.162584         396
    4 2018-11-03-09.29.58.162595       10303
    5 2018-11-03-09.29.58.162605       11565
    6 2018-11-03-09.29.58.162614        8513
    7 2018-11-03-09.29.58.162626        4391
    8 2018-11-03-09.29.58.162638        5890
    9 2018-11-03-09.29.58.162650        8658
    10 2018-11-03-09.29.58.162693       14872
    10 record(s) selected.
    COUNT
    -----------
           5000
    1 record(s) selected.
    

Setting up TSAMP on the db2-a instance

The db2-a instance is currently the standby instance. Set up TSAMP to facilitate automatic switching of users, applications, and data from one database system to another in a cluster.

  1. In the Cloud Console, use ssh to connect to the db2-a instance.

  2. Run the TSAMP configuration process.

    sudo bash -c 'su - db2inst1 -c "db2haicu"'
    
  3. When asked, Create a domain and continue? , enter 1 for yes.

  4. When prompted to Create a unique name for the new domain, enter db2-ha.

  5. When asked, How many cluster nodes will the domain 'db2-ha' contain? , enter 2.

  6. When prompted to Enter the host name of a machine to add to the domain:, enter db2-a.

  7. When prompted again to Enter the host name of a machine to add to the domain:, enter db2-b.

  8. When asked, Create the domain now?, enter 1 for yes.

  9. When asked, Configure a quorum device for the domain called 'db2-ha', enter 1 for yes.

  10. When prompted to Enter the number corresponding to the quorum device type to be used, enter 1.

  11. When prompted to Specify the network address of the quorum device, enter 169.254.169.254. This is the address of the Google Cloud metadata server.

  12. When asked, Create networks for these network interface cards?, enter 1.

  13. When prompted to Enter the name of the network for the network interface card: 'eth0' on cluster node, enter 1 for public network.

  14. When asked, Are you sure you want to add the network interface card 'eth0', enter 1.

  15. When prompted to Enter the name of the network for the network interface card: 'eth0, enter 3 for private network.

  16. When asked, Are you sure you want to add the network interface card 'eth0', enter 1.

  17. When prompted with, The following are valid settings for the high availability configuration parameter:, enter 1 for TSA.

  18. When asked, Do you want to validate and automate HADR failover for the HADR database 'TESTDB'?, enter 1.

Setting up TSAMP on the db2-b instance

The db2-b instance is currently the primary instance. Set up TSAMP on this instance as well.

  1. In the Cloud Console, use ssh to connect to the db2-b instance.

  2. Run the TSAMP configuration process.

    sudo bash -c 'su - db2inst1 -c "db2haicu"'
    
  3. When prompted if The following are valid settings for the high availability configuration parameter, enter 1 for TSA.

  4. When asked, Do you want to validate and automate HADR failover for the HADR database 'TESTDB'? [1], enter 1.

  5. When asked, Do you want to configure a virtual IP address for the HADR database 'TESTDB'?, enter 2 for no.

  6. When asked, Do you want to configure mount point monitoring for the HADR database 'TESTDB'?, enter 2.

  7. Verify your TSAMP status.

    sudo bash -c 'su - db2inst1 -c "db2pd -ha | grep Online"'
    

    The output is:

    db2-b                         Online
    db2-a                         Online
    Resource Group OpState         = Online
    Resource Group Nominal OpState = Online
    Resource State                 = Online
    Resource Group OpState         = Online
    Resource Group Nominal OpState = Online
    Resource State                 = Online
    db2_Quorum_Network_169_254_169_254:11_36_11  Online
    

Testing your deployment

There are two key parts to testing the deployment, ACR and automatic failover. You start by setting up the db2-client instance. You verify that client rerouting works when a standby instance is promoted to primary, and then you make sure that the standby instance is promoted to primary when the primary stops functioning.

Prepare the client instance

  1. In the Cloud Console, use ssh to connect to the db2-client instance.

  2. Add the required user groups.

    sudo groupadd -g 1999 db2iadm1
    
  3. Add the users.

    sudo useradd -u 2004 -g db2iadm1 -m -d /home/db2inst1 db2inst1
    
  4. Install the required packages.

    sudo yum -y install libaio
    
  5. Create the installation directory.

    sudo mkdir -p /db2/install
    
  6. Copy the runtime client.

    sudo gsutil cp \
        gs://[GCS_BUCKET_NAME]/ibm_data_server_runtime_client_linuxx64_v11.1.tar.gz \
        /db2/install/
    

    Where:

    [GCS_BUCKET_NAME] represents the bucket you created at the beginning of this tutorial.

  7. Unzip the file.

    sudo bash -c \
        "cd /db2/install && tar \
        -xvf ibm_data_server_runtime_client_linuxx64_v11.1.tar.gz"
    
  8. Create the installation response file.

    sudo bash -c 'cat <<EOF  > /db2/install/db2client.rsp
    PROD                          = RUNTIME_CLIENT
    FILE                          = /db2/bin
    LIC_AGREEMENT                 = ACCEPT
    INSTANCE                      = DB2_INST
    DB2_INST.NAME                 = db2inst1
    DB2_INST.TYPE                 = CLIENT
    DB2_INST.GROUP_NAME           = db2iadm1
    DB2_INST.AUTOSTART            = NO
    DB2_INST.START_DURING_INSTALL = NO
    INSTALL_TYPE                  = TYPICAL
    EOF'
    
  9. Install the db2-client runtime.

    sudo bash -c \
        "/db2/install/rtcl//db2setup -f sysreq -r \
        /db2/install/db2client.rsp"
    
  10. Catalog the current primary instance, db2-b.

    sudo bash -c 'su - db2inst1 \
        -c "db2 catalog tcpip node HAPNODE remote db2-b server 55000"'
    
  11. Catalog the database.

    sudo bash -c 'su - db2inst1 -c "db2 catalog  db TESTDB  at node HAPNODE"'
    
  12. Terminate the running process to refresh the client cache.

    sudo bash -c 'su - db2inst1 -c "db2 terminate"'
    

Verify ACR from the client instance

To verify that automatic client rerouting works, you switch the primary from the db2-b instance back to the db2-a instance and make sure that the client reconnects to the new primary instance.

  1. Start the Db2 command-line processor.

    sudo bash -c 'su - db2inst1 -c "db2"'
    
  2. Connect to the database.

    connect to TESTDB user db2inst1 using PasswordDb23@
    
  3. Verify that the other Db2 instance, db2-a, is registered as the alternative instance. The db2-a instance is currently the standby instance.

    list db directory
    

    The output is:

    System Database Directory Number of entries in the directory = 1
    Database 1 entry: Database alias     = TESTDB
    Database name                        = TESTDB
    Node name                            = HAPNODE
    Database release level               = 14.00
    Comment                              =
    Directory entry type                 = Remote
    Catalog database partition number    = -1
    Alternate server hostname            = db2-a
    Alternate server port number         = 55000
    
  4. In the Cloud Console, use ssh to connect to the db2-a instance.

  5. Initiate takeover.

    sudo bash -c 'su - db2inst1 -c "db2 takeover hadr on db testdb"'
    
  6. Return to the db2-client instance and run the following query:

    select * from source_table limit 10
    

    This query outputs a connection error:

    SQL30108N  A connection failed in an automatic client reroute environment. The transaction was rolled back.
    

    This is because the client couldn't connect to the db2-b instance . During that process the client understands it has to communicate with the new primary instance, db2-a.

  7. Run the preceding query again.

    The output displays 10 lines similar to:

    ID          TIMESTAMP                  EVENT_DATA
    ----------- -------------------------- -----------
    1 2018-11-06-11.07.36.883056         118
    2 2018-11-06-11.07.36.886453       13781
    3 2018-11-06-11.07.36.886517        9897
    4 2018-11-06-11.07.36.886529       13102
    5 2018-11-06-11.07.36.886539       11327
    6 2018-11-06-11.07.36.886549       11242
    7 2018-11-06-11.07.36.886558        8141
    8 2018-11-06-11.07.36.886568         404
    9 2018-11-06-11.07.36.886577        6170
    10 2018-11-06-11.07.36.886588         436
    
  8. Check the connection status.

    get connection state
    

    The output is:

    Database Connection State
    Connection state       = Connectable and Connected
    Connection mode        = SHARE
    Local database alias   = TESTDB
    Database name          = TESTDB
    Hostname               = db2-a
    Service name           = 55000
    

Verify TSAMP automated failover

To verify that TSAMP automated failover works, you stop the primary instance, db2-a, and make sure that the standby instance, db2-b, is promoted to be the primary instance. Then you restart the db2-a instance and make sure it is now the standby instance.

  1. In Cloud Shell, stop the primary instance, db2-a.

    gcloud compute instances stop db2-a --zone=us-central1-b
    
  2. In the Cloud Console, use ssh to connect to the db2-b instance. The takeover process might take a few minutes.

  3. Check the quorum logs.

    sudo grep QUORUM /var/log/messages
    

    If the output is:

    The operational quorum state of the active peer domain has changed to PENDING_QUORUM.
    

    This means that the standby instance noticed the primary is down and therefore, lost quorum.

    Now, if the output is:

    The operational quorum state of the active peer domain has changed to HAS_QUORUM.
    

    This means that the db2-b instance took the quorum.

  4. Check the cluster logs for the takeover operation.

    sudo bash -c "su - db2inst1 \
        -c 'db2diag  -g db=TESTDB,MESSAGE:=takeover  -l INFO '"
    

    The output is:

    MESSAGE : Standby has completed takeover (now primary).
    
  5. Verify that the role of the db2-b instance changed to Primary.

    sudo bash -c 'su - db2inst1 -c "db2 get snapshot for db on TESTDB | grep Role"'
    

    The output is:

    Role                   = Primary
    
  6. In Cloud Shell, start the db2-a instance.

    gcloud compute instances start db2-a --zone=us-central1-b
    
  7. In the Cloud Console, use ssh to connect to the db2-a instance.

  8. Verify that the role of the db2-a instance changed to Standby.

    sudo bash -c 'su - db2inst1 -c "db2 get snapshot for db on TESTDB | grep Role"'
    

    The output is:

    Role                   = Standby
    

Cleaning up

To avoid incurring charges to your Google Cloud Platform account for the resources used in this tutorial:

Delete the project

  1. In the Cloud Console, go to the Manage resources page.

    Go to the Manage resources page

  2. In the project list, select the project that you want to delete and then click Delete .
  3. In the dialog, type the project ID and then click Shut down to delete the project.

What's next