StarQuest Technical Documents

Updating from IBM DB2 Express-C to DB2 Express

Last Update: 21 March 2016
Product: SQDR Plus
Version: 4.2 & later
Article ID: SQV00PL016

 

Abstract

In the four tier StarQuest Data Replication architecture, SQDR Plus runs on Tier 2 and uses a local IBM DB2 for Linux, UNIX & Windows (DB2 LUW) database as the control and staging database for each Staging Agent.

IBM provides several editions of DB2; DB2 Express-C is a no-charge community edition of DB2 server which provides the core features of the more scalable DB2 editions, but is limited in terms of support and updates. DB2 Express is fully supported by IBM and can take advantage of additional RAM and CPU resources. A license to use DB2 Express with SQDR Plus is included with your purchase of SQDR Plus.

If DB2 LUW Express-C 10.1 or 10.5 was previously installed for use by SQDR Plus, we recommend upgrading to DB2 Express 10.5 for enhanced stability and serviceability. You must remove DB2 Express-C before installing DB2 Express.

This document describes the upgrade process for the following scenarios:

For additional guidance in working with DB2 LUW as used by SQDR Plus, see the technical document Troubleshooting Tips for the SQDR Plus DB2 LUW Staging Database.

Considerations

  • Consider where you want to place the databases and logfiles - this is a good time to change the location if desired. See the topic Moving the DB2 control database used by the Staging Agent in Troubleshooting Tips for the SQDR Plus DB2 LUW Staging Database.
  • Avoid changing the location of the DB2 program files, since it is referenced in the wrapper.conf files used by SQDR Plus. On Windows, this is not an issue unless you install DB2 to a different drive. On Linux, the location will change if you update from 10.1 to 10.5 - /opt/ibm/db2/V10 -> /opt/ibm/db2/V10.5. Workaround: create a symbolic link after the update with ln -s /opt/ibm/db2V10.5 /opt/ibm/db2V10.1.
  • Verify that you know the credentials used for the DB2 services (typically .\db2admin or Local System Account on Windows, and db2inst1, dasusr1, and db2fenc1 on Linux ), or delete the service users and recreate them during the reinstall of DB2. Removing DB2 10.1 on Windows will prompt whether or not you want to remove .\db2admin.

Updating Express-C 10.1 or 10.5 to Express 10.5

The procedure consists of these major tasks:

Preparation

  1. Download the installer image for DB2 using the URL supplied by StarQuest support. The image is over 1gb in size and should be downloaded in advance. You may also want to download the installers for IBM Data Studio client and Data Studio Web Console.
  2. On the Tier 3 system (running the SQDR service), Use Data Replicator Manager to pause the I/R group and delete any connections to the staging system.

On the Tier 2 system (running SQDR Plus):

  1. Verify that you have adequate available disk space in the temporary directory where you will store a backup file. Be sure that there are no older backup images of your database in the temporary directory. In our example, we are using the D: drive for both.
  2. Identify the DB2 databases in use by SQDR Plus (e.g. SQDRP0) by examining the controlDbUrl keyword in the Agent configuration. You can also run the command db2 list database directory to list all databases; note the location of the database directory (e.g. D:).
  3. Stop the SQDR Plus jetty & launch agent (capagent) services from the Services control panel (Windows) or /etc/init.d (Linux).
  4. Configure the SQDR Plus jetty & launch agent (capagent) services for manual start, so they do not start automatically after a reboot. On Windows, this can be done from the Services control panel; on Linux, this can be done with the chkconfig command.
  5. Use the db2 list applications command to verify that there are no active connections to DB2.
  6. Back up the db2 database to a temporary location (the compress argument is optional). Unless you are changing the location of the database, creating the backup is only precautionary.

db2 backup database SQDRP0 to D:\temp compress

  1. If you are updating from DB2 10.1, run the db2ckupgrade utility from the DB2 10.5 installer media to verify that the databases can be updated to 10.5; perform corrective actions as directed if necessary:
    ...db2\Windows\utilities\db2ckupgrade SQDRP0 -l db2ckupgrade.log -u adminuser -p password

Uninstall and Install

  1. Remove DB2 Express-C using the Programs and Features control panel (Windows). See below for Linux uninstall instructions.
  2. Reboot the system. We have found this necessary on Windows to ensure that the DB2 instance gets created when you install DB2 Express.
  3. Install DB2 Express. On the initial installer dialog, select the option to install a new copy of DB2 Express.

Post-install Tasks

  1. Configure the DBM parameters as described in the Quick Start Guide - e.g.:

DB2 UPDATE DBM CFG USING DFTDBPATH D:
db2 UPDATE DBM CFG USING JAVA_HEAP_SZ 20480
db2 UPDATE DBM CFG USING DIAGSIZE 2
db2stop
db2start

  1. Catalog the databases e.g.

    db2 catalog db SQDRP0 on D:

    Note: the command db2 list db directory on D: will display all existing databases on D:, even if they haven't been cataloged yet.

  2. If you are updating from DB2 10.1, upgrade the databases:

db2 UPGRADE DATABASE database-alias USER username USING password
e.g. db2 UPGRADE DATABASE SQDRP0

  1. Change the location of the DB2 logfiles if desired:

    db2 update db cfg for SQDRP0 using newlogpath E:\DB2Logs

  2. Connect to the database to verify that it is functional:

db2 connect to SQDRPO user SQDR

  1. If you did not preserve the original C:\ProgramData\IBM directory, instantiate the stored procedure JAR file.
  2. Start the SQDR Plus jetty & launch agent (capagent) services from the Services control panel (Windows) or /etc/init.d (Linux),
  3. Configure the services to autostart with the Services control panel (Windows) or chkconfig (Linux).
  4. Use a browser to connect to SQDR Control Center and verify that the agent is running; its icon should be green.
  5. On the Tier 3 system (running the SQDR service), use Data Replicator Manager to resume the I/R group.

Updating DB2 Express 10.5 to the Latest Fix Pack

When updating from an earlier version of DB2 Express 10.5 to the latest fix pack:

  1. Stop SQDR and SQDR Plus services.
  2. Use the db2 list applications command to verify that there are no active connections to DB2.
  3. For Windows, run the DB2 installer and update the existing copy of DB2 Express. For Linux, see Installing the Latest Fix Pack on Linux.
  4. Reboot the system if prompted by the installer.
  5. Resume SQDR Plus and SQDR services.

Licensing DB2 Express

DB2 Express will function in evaluation mode for 90 days after installation. When you receive your DB2 license file from StarQuest support, install it using the db2licm command. The command db2licm -l will show the current licensing status.

db2licm -a db2exp_u.lic

Instantiate the stored procedure JAR file

If you did not preserve the original C:\ProgramData\IBM directory, instantiate the stored procedure JAR file ( C:\ProgramData\IBM\DB2\DB2COPY1\\function\jar\SQDR\CAPTUREPROCS_JAR.jar) using either of the following methods:

  • -- Create a temporary SQL Server database and an agent to it
    • Use SQL Server Management Studio to create a scratch SQL Server database.
    • Create an agent to that database
    • Delete the temporary agent - make sure SQDRPn gets dropped and agtn subdirectory deleted.
    • Use SQL Server Management Studio to drop the scratch database.
  • -- Connect to SQDRC, SQDRP0, or any other SQDR database and call sqlj.replace_jar()
    • Create the directory C:\ProgramData\IBM\DB2\DB2COPY1\function\jar
    • db2> connect to SQDRC user SQDR using password
      db2> CALL sqlj.replace_jar( 'file:C:\Program Files\StarQuest\sqdrplus\capagent\UDBProcs.jar', 'CAPTUREPROCS_JAR' )
      db2> commit
      db2> disconnect SQDRC

Removing DB2 Express-C (Linux)

# cd ~dasusr1/das
# . dasprofile
# db2admin stop
# cd $DB2DIR/instance
# ./dasdrop
# ./db2idrop db2inst1
# cd ../install
# ./db2_deinstall -a

Reuse dasusr1, db2inst1 & db2fenc1 userID's when you install DB2 Express.

Installing the Latest Fixpack on Linux

Preparation:

  1. Stop SQDR Plus and SQDR services
  2. As the instance owner, use the db2 list applications command to verify that there are no active connections to DB2, and stop DB2 with db2stop
  3. Stop the DAS instance:

# cd ~dasusr1/das
# . dasprofile
# db2admin stop

or
# su - dasusr1
$ db2admin stop

  1. Disable the fault monitor daemon (db2fmcd) process:

as root:
# . ~db2inst1/sqllib/db2profile
# db2fmcu -d

Install the Fix Pack:

# cd <installer_directory>
# ./installFixPack
Enter the full path of the base installation directory:
/opt/ibm/db2/V10.5

Enable Fault Monitor to automatically restart DB2

as db2inst1:

$ db2iauto -on db2inst1

as root:

* Enable the Fault Monitor Coordinator

# cd /opt/ibm/db2/V10.5/bin/
# ./db2fmcu -u -p /opt/ibm/db2/V10.5/bin/db2fmcd

in RHEL6, this creates or replaces the db2fmcd.conf file under the /etc/init directory
in RHEL7, this creates and enables the db2fm systemd service. (applicable starting in DB2 10.5 FP6)

* Start up the fault monitor daemon:

# /opt/ibm/db2/V10.5/bin> ./db2fm -i db2inst1 -U

* Start up the fault monitor service (the DB2 instance should not be running):

# /opt/ibm/db2/V10.5/bin> ./db2fm -i db2inst1 -u

* Turn on the fault monitor for the instance:

# /opt/ibm/db2/V10.5/bin> ./db2fm -i db2inst1 -f on

For details, see the IBM tech note How to use fault monitors to automatically restart DB2 UDB instances.



DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.