Backing up and restoring Db2 Warehouse with IBM Spectrum Protect (Tivoli Storage Manager)

You can back up Db2 Warehouse on Cloud Pak for Data by using IBM Spectrum Protect, formerly known as Tivoli Storage Manager.

Before you begin

Ensure that the SELinux module is installed on all worker nodes where Db2 Warehouse is installed.

Procedure

Downloading the IBM Spectrum Protect client

  1. Download your architecture's respective client following the instructions here. This procedure shows the Linux x86_64 version.
  2. Copy the 8.x.x.x-TIV-TSMBAC-LinuxX86.tar file into the Db2 Warehouse catalog node, where 8.x.x.x represents the client version: 
    DB2U_POD=$(oc get po --no-headers -l name=dashmpp-head-0 | awk '{print $1}')
oc cp 8.x.x.x-TIV-TSMBAC-LinuxX86.tar $DB2U_POD:/tmp/
  3. From within the pod, extract the contents of the compressed file and navigate to the target directory: 
    tar -xvf 8.x.x.x-TIV-TSMBAC-LinuxX86.tar -C target-directory
  4. As a root user, install the GSKit packages: 
    sudo rpm -U gskcrypt64-8.x.x.x.linux.x86_64.rpm gskssl64-8.x.x.x.linux.x86_64.rpm
  5. As a root user, install the IBM Spectrum Protect API: 
    sudo rpm -ivh TIVsm-API64.x86_64.rpm
  6. As a root user, install the backup-archive client components: 
    sudo rpm -ivh TIVsm-BA.x86_64.rpm

Configuring the IBM Spectrum Protect client and server

Perform the following steps for each node in your configuration:

  1. Add the following environment variables to the db2inst1 user profile: 
    cat <<EOF > ${BLUMETAHOME}/db2inst1/sqllib/userprofile
export GSK_STRICTCHECK_CBCPADBYTES=GSK_TRUE
DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm.opt
DSMI_LOG=$DIAGPATH
export DSMI_DIR DSMI_CONFIG DSMI_LOG
EOF
  2. Create the directory structure and assign ownership of the IBM Spectrum Protect API password directory to db2inst1: 
    TSMDIR="/mnt/blumeta0/tsm"
TSM_API_DIR="${TSMDIR}/api"
TSM_BA_DIR="${TSMDIR}/ba"
mkdir -p ${TSM_API_DIR}/$(hostname -s)
mkdir -p ${TSM_BA_DIR}/$(hostname -s)
mkdir -p ${TSM_API_DIR}/$(hostname -s)/cred_store
sudo chown db2inst1:db2iadm1 ${TSM_API_DIR}/$(hostname -s)/cred_store
  3. Create the configuration files (dsm.opt and dsm.sys) for the API:

    Command to create dsm.opt:

    cat << EOF >> api-directory/node-name/dsm.opt
servername server-name
EOF
    • api-directory: The path to the directory structure that you created for the API. In this setup, it is /mnt/blumeta0/tsm/api.
    • node-name: The name of the Db2 Warehouse pod, for example c-db2wh-1609890842138524-db2u-0.
    • server-name: The name of the IBM Spectrum Protect server.

    Command to create dsm.sys:

    cat << EOF >> api-directory/node-name/dsm.sys
servername server-name
tcpserveraddress server-address
commmethod communication-method
passwordaccess generate
tcpport tcp-port
ssl yes|no
nodename node-name
passworddir api-directory/node-name/cred_store
errorlogname /mnt/blumeta0/db2/log/db2-dsmerror.log
EOF

    The api-directory is /mnt/blumeta0/tsm/api.

    • server-address is the IP address of the IBM Spectrum Protect server.
    • communication-method would typically be tcpip.
  4. If you want to back up the file system, create the configuration files (dsm.opt and dsm.sys) for the IBM Spectrum Protect backup archive (BA). This step is not needed if you only want to back up the database.

    Command to create dsm.opt:

    cat << EOF >> BA-directory/node-name/dsm.opt
servername server-name
EOF
    • BA-directory: The path to the directory structure that you created for BA. In this setup, it is /mnt/blumeta0/tsm/ba.

    Command to create dsm.sys:

    cat << EOF >> BA-directory/node-name/dsm.sys
servername server-name
tcpserveraddress server-address
commmethod communication-method
passwordaccess generate
tcpport tcp-port
ssl yes|no
nodename node-name
errorlogname /mnt/blumeta0/db2/log/db2-dsmerror.log
EOF
  5. Create symlinks to the IBM Spectrum Protect configuration files: 
    ##Create symlink to API client configuration files
sudo ln -sf tsmdir/api/$(hostname -s)/dsm.opt /opt/tivoli/tsm/client/api/bin64/dsm.opt
sudo ln -sf tsmdir/api/$(hostname -s)/dsm.sys /opt/tivoli/tsm/client/api/bin64/dsm.sys
##Create symlink to BA client configuration files
sudo ln -sf tsmdir/ba/$(hostname -s)/dsm.opt /opt/tivoli/tsm/client/ba/bin/dsm.opt
sudo ln -sf tsmdir/ba/$(hostname -s)/dsm.sys /opt/tivoli/tsm/client/ba/bin/dsm.sys
    • tsmdir: The directory where you installed the IBM Spectrum Protect client.

Registering the client with the server

  1. Register your client with the IBM Spectrum Protect server by running the following command on the server in the admin mode: 
    register node client-hostname passw0rd backdel=yes maxnummp=10 SESSIONSECurity=transitional
    • client-hostname: The hostname of the system where you installed the IBM Spectrum Protect client.

Authenticating

  1. Switch to db2inst1 user: 
    su - db2inst1
  2. Set up your password: 
    /mnt/blumeta0/home/db2inst1/sqllib/adsm/dsmapipw

    When you successfully set up the password, your client is configured with the IBM Spectrum Protect server.

Running the backup

  1. Issue one of the following commands to back up Db2 Warehouse on the IBM Spectrum Protect server: 
    Online
db2 "backup db bludb on all dbpartitionnums online use tsm open <1-6> sessions include logs without prompting"
Incremental
db2 "backup db bludb on all dbpartitionnums online incremental use tsm open <1-6> sessions include logs without prompting"
Offline
db2 "backup db bludb on all dbpartitionnums use tsm open <1-6> sessions"

Restoring

  1. Issue one of the following commands to restore Db2 Warehouse: 
    Online
- Head Node
db2_all '<<+0< db2 restore database bludb use tsm taken at timestamp logtarget overflow_logpath replace existing without prompting'
- Data Nodes
db2_all '<<-0<|| db2 restore database bludb use tsm taken at timestamp logtarget overflow_logpath replace existing without prompting'
Rollforward
db2 "rollforward db bludb to end of backup and stop overflow log path (overflow_logpath) noretrieve"
Incremental
- Head Node
db2_all '<<+0< db2 restore database bludb incremental automatic use tsm taken at timestamp logtarget overflow_logpath replace existing without prompting'
- Data Nodes
db2_all '<<-0<|| db2 restore database bludb incremental automatic use tsm taken at timestamp logtarget overflow_logpath replace existing without prompting'
Rollforward
db2 "rollforward db bludb to end of backup and stop overflow log path (overflow_logpath) noretrieve"
Offline
- Head Node
db2_all '<<+0< db2 restore database bludb use tsm taken at timestamp without rolling forward without prompting'
- Data Nodes
db2_all '<<-0<|| db2 restore database bludb use tsm taken at timestamp without rolling forward without prompting'