StarQuest Technical Documents

Updating to Db2 11.5.5

Last Update: 17 June 2021
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 IBM Db2 for Linux, UNIX & Windows (aka Db2 LUW) database as the control and staging database for each Staging Agent.

StarQuest originally recommended the use of Db2 Express 10.5, followed by Db2 Workgroup 11.1 or Db2 Standard 11.5.0 or 11.5.4. StarQuest now recommends using Db2 11.5.5fp1.

Existing users with Db2 10.5 should be aware that IBM ended support for 10.5 in April 2020. IBM will end support fo Db2 11.1 in April 2022.

SQDR Plus 5.21 and later ships with Db2 template images in 11.5.5 format, so all new installations of SQDR Plus must be using Db2 11.5.5 or later. If you are currently using SQDR Plus with an earlier version of Db2 (10.5, 11.1, or 11.5.0), you can continue to use existing agents, or create new agents using existing staging databases, but will not be able to create new staging databases. In any case, we recommend updating to Db2 11.5.5fp1 at your earliest convenience.

If you are using SQDR Plus 5.20 or earlier, you can create new agent databases while using Db2 11.5.5, but creation of those new databases will require extra time for conversion from 10.5 format.

This document describes the process for upgrading to Db2 11.5.5fp1.

It also includes information about updating to the latest fixpack (e.g. to 11.5.5fp1 from 11.5.5).

In most cases, you can update in place directly to 11.5.5fp1, without first uninstalling the existing version of Db2. If uninstalling and reinstalling is recommended, refer to Reinstalling Db2.

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 tasks: stop services and perform a precautionary backup
• Download & expand installer image
• Run db2ckupgrade from 11.x media
• Run the 11.x installer
• Post-install tasks
• Licensing
• Issues
• Deprecation of db2jcc.jar (Db2 11.5; SQDR Plus 5.08 & earlier)
• Linux Considerations
• Upgrading to the Latest Fixpack

Preparation

1. 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.x 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.
2. Examine the disk usage of the drive where the Db2 logs reside; this is the drive listed as "Local database directory" when you run db2 list db directory. Using the recommended increase of LOGFILSIZ from 4096 to 8192, the amount of disk space used by Db2 logs will double. Confirm that the drive is large enough to handle the increase, or increase LOGFILSIZ selectively - i.e. make this change only for databases that will benefit from the change.
3. 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 on Windows will prompt whether or not you want to remove .\db2admin.
4. 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, set the SQDRSVC service to Disabled.

On the Tier 2 system (running SQDR Plus):

3. If you will be creating a precautionary backup, 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.
4. Identify the Db2 databases in use by SQDR Plus (e.g. SQDRP0, etc) by selecting the top level (system) in SQDR Control Center; the Control DB will be listed in the right panel.. You can also run the command db2 list database directory to list all databases; note the location of the database directory (e.g. L::).
5. 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.
6. Stop and disable the SQDR Plus Derby services from the Services control panel (Windows) or /etc/init.d and using chkconfig (Linux); this will also stop the other SQDR Plus services. We do not want these services to start automatically after a reboot until we have finished the upgrade.
7. Use the db2 list applications command to verify that there are no active connections to Db2.
8. Optional: Back up the Db2 database to a temporary location (the compress argument is optional).

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 and Db2 Management Console or Data Server Manager.
2. Also download and expand db2update.zip.
3. Right-click and select Properties of the downloaded images; unblock if necessary.
4. Expand the installer images.

Run db2ckupgrade

Except in the case of updating from an earlier version of 11.5.x, run the db2ckupgrade utility from the db2\Windows\utilities\ directory of the Db2 11.5.5 installer media or from a local copy to verify that the databases can be updated to 11.5.5; perform corrective actions as directed if necessary.

This can be done using db2list.bat and ckupgrade.bat scripts supplied in the db2update.zip package. These scripts do the following:

db2list creates a list of local databases using the following

setlocal enableDelayedExpansion
for /f "tokens=4" %%x in ('db2 list database directory ^| find "Database name"') do (
echo %%x
)

Edit the resulting list (removing remote databases and appending a caret symbol at the end of each line) and paste it in to the ckupgrade script. Save this list of datdabases for later.

ckupgrade does the following:

Deactivate the database

Run db2ckupgrade i.e.

C> db2ckupgrade SQDRP0 -l SQDRP0.log -u adminuser -p password

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.x.x.x Server Editions
5. The next panel (Select the Db2 copy to work with) will display your existing (default name DB2COPY1) with the available action Upgrade.
6. Select DB2COPY1 and Launch Db2 Setup wizard
7. Select Installation type: Custom
8. You will receive several warnings about the need to reboot after installation.
9. You may be prompted for the db2admin credentials. If you are using a local db2admin user and the dropdown for the domain field does not show the local system as an option, enter a blank line for the domain.
10. On Enable operating system security for Db2 objects, unselect the checkbox Enable operating system security.
11. 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 L:

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. db2stop & db2start should be issued from an elevated Db2 prompt.

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

3. Edit and run the RunDb2Update.bat script supplied in the db2update.zip package to perform the following operations:

• Upgrade the database
  • db2 UPGRADE DATABASE database-alias USER username USING password
    e.g. db2 UPGRADE DATABASE SQDRP0
• Run db2updv115 to update the system catalog for the current release.
• Unless you have already customized these parameters, we recommend increasing CATALOGCACHE_SZ from 350 to 1000 and LOGFILSIZ from 4096 to 8192 for each database, using DB2 UPDATE CFG. Note that increasing LOGFILSIZ will double disk usage on the drive where the logs reside.
• Unless you have upgraded to SQDR Plus 5.21 or later, save this script, as you may need it again if you create additional staging databases.
  

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

db2 connect to SQDRPO user SQDR
db2 disconnect SQDRP0

5. License Db2.
6. If you are using SQDR Plus 5.08 or earlier, handle the deprecation of db2jcc.jar.
7. In the Services control panel, set the SQDR Plus services startup type to Automatic and start them.
8. Set the StarQuest Data Replicator service startup type to Automatic (Delayed) and start it .
9. Use a browser to connect to SQDR Control Center and verify that the agent is running; its icon should be green.
10. On the Tier 3 system (running the SQDR service), use Data Replicator Manager to resume the I/R group.
11. Open Task Scheduler in Administrative Tasks. Right-click on the task ActivateDB2Databases, select Properties, and (if necessary) 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 in 10.5 to DB2-0 in 11.x.

Licensing Db2

Db2 11.5 requires different license keys than Db2 10.5 or 11.1. The Db2 11.5 installer includes a "DB2DEC" Community Edition license, which is limited to 4 cores and 16gb RAM. When you receive your Db2 license file from StarQuest support, install it using db2licm -a db2std_vpc.lic in an elevated command prompt, and remove the Community Edition license with the command db2licm -r db2dec. The command db2licm -l will display the current licensing status.

Issues

Port Number

Typically an update preserves the Db2 listening port (db2c_DB2 50000). However, on rare occasions, we have seen the upgrade change Db2 to use a new port (e.g. db2c_DB2_01 50001). Reconfiguring SQDR and SQDR Plus to use 50001 instead of 50000 is tricky; instead

• Stop Db2
• Edit C:\windows\system32\drivers\etc\services, delete the line for db2c_DB2 and change the value of db2c_DB2_01 to 50000
  OR
  db2 update dbm cfg using SVCENAME db2c_DB2
• Restart Db2

wmiprvse.exe could not be stopped

Symptom:

On rare occasions, the Db2 update failed with the message:

The following process(es) could not be stopped: wmiprvse.exe

Solution:

1. Open the cmd prompt with administrator privileges
2. Run the following command: net stop winmgmt
3. Run the Db2 upgrade again.
4. Reboot the system (typical after a Db2 update) or run net start winmgmt
   

Deprecation of db2jcc.jar (Db2 11.5; SQDR Plus 5.08 & earlier)

The Db2 JDBC 3.0 driver db2jcc.jar was removed from the Db2 installation in 11.5. This file is used by the Capture Agent in SQDR Plus 5.08 or earlier. We recommend updating to SQDR Plus 5.10 or later, but if that is not possible, use one of the following workarounds:

• Make a copy of C:\Program Files\IBM\SQLLIB\java\db2jcc4.jar and rename it to db2jcc.jar (suggested)
• Copy db2jcc.jar from an earlier Db2 installation to C:\Program Files\IBM\SQLLIB\java.
• Edit C:\Program Files\StarQuest\sqdrplus\capagent\wrapper\conf\wrapper.conf and change the reference of db2jcc.jar to db2jcc4.jar.

Linux & AIX Considerations

This process has been tested on Windows. It may differ on Linux & AIX; 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 & AIX, the location may change.

Workaround: create a symbolic link after the update with

# ln -s /opt/ibm/db2V11.5 /opt/ibm/db2V10.5 (Linux)

Upgrading to the Latest Fixpack

Before updating to a new fixpack, we recommend checking with StarQuest support to verify that SQDR Plus has been tested with the new fixpack level.

Windows:

When updating to the latest fix pack (but not changing major version):

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. Run the Db2 installer and update the existing copy of Db2.
4. On Enable operating system security for DB2 objects, unselect the checkbox Enable operating system security.
5. Reboot the system if prompted by the installer.
6. Verify the new fixpack level with the db2level command.
7. Use db2updv115 to update the system catalog in each database. e.g.

db2updv115 -d SQDRP0
etc

7. Resume SQDR Plus and SQDR services.

For Linux & AIX, see Installing the Latest Fix Pack on Linux or AIX.