StarQuest Technical Documents

Updating from IBM Db2 10.5 to Db2 11.1

Last Update: 1 February 2019
Product: SQDR Plus
Version: 4.2 & later
Article ID: SQV00PL035

Abstract

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

Until recently, StarQuest recommended the use of Db2 Express 10.5, though recent new installations have been based on Db2 Workgroup 11.1. IBM has announced an end of support date for 10.5 of April 2020, so we recommend updating to 11.1 before that date.

This document describes the upgrade process for upgrading from Db2 10.5 to 11.1.

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

Solution

The procedure consists of these major tasks:

Preparation

  1. 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. If SQDR is using Db2 (database SQDRC) for its control database, stop the service to manual start.

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. Use the process described in the technical document Detecting and Cleaning up Unused Db2 Databases to avoid checking and upgrading databases that are no longer needed.
  4. Before stopping the SQDR and SQDR Plus services, use Task Manager or other tools to record your typical RAM usage. In some cases, Db2 11.1 may have slightly higher memory requirements than 10.5; if you have a large number of agents and your memory usage is high, be prepared to add more memory to the system.
  5. Stop the SQDR Plus jetty & launch agent (capagent) services from the Services control panel (Windows) or /etc/init.d (Linux).
  6. 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.
  7. Use the db2 list applications command to verify that there are no active connections to DB2.
  8. Back up the db2 database to a temporary location (the compress argument is optional). Creating the backup is only precautionary.

db2 backup database SQDRP0 to D:\temp compress

Download & expand the Db2 installer image

  1. Download the Db2 installer image 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, Data Studio Web Console and Data Server Manager.
  2. Expand the installer image.

Run db2ckupgrade

Run the db2ckupgrade utility from the Db2 11.1 installer media to verify that the databases can be updated to 11.1; perform corrective actions as directed if necessary:

C> ...db2\Windows\utilities\db2ckupgrade SQDRP0 -l db2ckupgrade.log -u adminuser -p password

You may need to deactivate some of the databases before db2ckupgrade can succeed:

C> db2 deactivate DB SQDRC

Install

  1. Run setup.exe to start the DB2 Setup Launchpad
  2. Select Install a Product from the left panel.
  3. You will see several possible products to install, and Install New and Work with Existing buttons for each product.
  4. Choose Work with Existing for DB2 Version 11.1.x.x Server Editions
  5. The next panel (Select the DB2 copy to work with) will display your existing 10.5 copy (default name DB2COPY1) with the available action Upgrade.
  6. Select DB2COPY1 and Launch DB2 Setup wizard
  7. You will receive several warnings about the need to reboot after installation.
  8. You will see the dialog that allows you to enable Extended Security; we recommend unchecking Extended Security.
  9. On completion of the upgrade, reboot.

Post-install Tasks

  1. Precautionary: Use db2 list db directory to verify that the databases are still cataloged; if necessary, catalog them again e.g.:.

    db2 catalog db SQDRP0 on D:

  2. Use db2 get dbm cfg to verify or modify the following DBM parameters. Note that we now recommend a JAVA_HEAP_SZ of 204800 rather than 20480.

db2 UPDATE DBM CFG USING JAVA_HEAP_SZ 204800
db2 UPDATE DBM CFG USING DIAGSIZE 2
db2stop
db2start

  1. Upgrade the databases:

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

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

db2 connect to SQDRPO user SQDR
db2 disconnect SQDRP0

  1. Repeat steps 2 and 3 for additional databases.
  2. In the Services control panel, set the SQDR Plus services startup type to Automatic and start them.
  3. Set the StarQuest Data Replicator service startup type to Automatic (Delayed) and start it .
  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.
  6. Open Task Scheduler in Administrative Tasks. Right-click on the task ActivateDB2Databases, select Properties, and edit the trigger to run on an event of Source DB2-0 rather than DB2. This is because the default name of the DB2 service has changed from DB2 to DB2-0.

Licensing DB2 Workgroup

Db2 11.1 requires different license keys than Db2 10.5. After installation, DB2 11.1 will function in evaluation mode for 90 days as Advanced Enterprise Server Edition. When you receive your DB2 license file from StarQuest support, install it using the db2licm command. The command db2licm -l will display the current licensing status.

db2licm -a db2wse_u.lic

Linux Considerations

This process has been tested on Windows. It may differ on Linux; in particular, pay attention to 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 may change.

Workaround: create a symbolic link after the update with

# ln -s /opt/ibm/db2V11.1 /opt/ibm/db2V10.5.


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.