Stelo Technical Documents

Reinstalling or Updating Db2

Last Update: 2 April 2024
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.

In most cases, upgrading to a newer version of Db2 can be done in place by simply running the installer and choosing to upgrade an existing instance. See Updating to IBM Db2 11.5.5 for details, including post-upgrade tasks.

However, there are several scenarios where removing Db2 and reinstalling it as a fresh install is recommended. For instance:

• Upgrading from DB2 Express-C 10.x or from a special build of Db2.
• Moving Program Files\IBM\SQLLIB or ProgramData\IBM to a different drive. This may be in preparation to a migration to a Windows Failover Cluster environment.
• Repairing a situation where Db2 was installed from or to a drive that was not enabled for 8.3 Naming.
• Migrating to a new server.
• Setting up a clean environment to resolve unexplained issues.

This document describes the upgrade process for the following scenarios:

• Considerations
• Overview of the Process
• Preparation tasks
• Uninstall old version and install new
• Post-install tasks
• Licensing Db2
• Instantiate the stored procedure JAR file
• Updating to the Latest Fix Pack
• Removing Db2 (Linux or AIX)
• Installing the Latest Fixpack on Linux or AIX

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 to a major version eg /opt/ibm/db2/V10.5 -> /opt/ibm/db2/V11.5. Workaround: create a symbolic link after the update with ln -s /opt/ibm/db2V11.5 /opt/ibm/db2V10.5.
• 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.
• This technical document is intended to be a general guide to removing & reinstalling Db2 at all versions, but it may include some information that is specific to a particular version (currently 11.5.5).

Overview of the Process

The procedure consists of these major tasks:

• Preparation tasks: stop services and perform a precautionary backup
• Uninstall old version and install new
• Post-install tasks: configure Db2, recatalog and update databases, and resume services

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. 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 Db2 Management Console or Data Server Manager.
4. Also download and expand db2cat_fix.zip.
5. Right-click and select Properties of the downloaded images; unblock if necessary.
6. Expand the installer image.
7. In a split tier scenario, 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. In a combined tier 2 scenario, stop the SQDRSVC and set it 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. Stop the SQDR Plus Derby service from the Services control panel (Windows) or /etc/init.d (Linux) and set it to Disabled so it doesn't attempt to start after a reboot. This will also stop the other (dependent) SQDR Plus services.
6. Use the db2 list applications command to verify that there are no active connections to Db2.
7. Optional: 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

Run db2ckupgrade

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

This can be done using db2list.bat and ckupgrade.bat scripts supplied in the db2cat_fix.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

Uninstall and Install

1. Remove the existing version of Db2 using the Programs and Features control panel (Windows). See below for Linux uninstall instructions.
2. Reboot the system.
3. Install the new version of Db2.
4. On the initial installer dialog, select the option to install a new copy of Db2.
5. Choose Custom Install.
6. You will be prompted for the db2admin credentials.
7. On the panel related to creating an Instance, select the button to display Instance details and confirm that the Port is 50000. We have occasionally encountered the situation where the removal of the old version did not remove an entry from etc\services, and the new install ended up with a different port number; this is to be avoided.
8. On Set up notifications, set up a notification SMTP server if desired.
9. On Enable operating system security for DB2 objects, unselect the checkbox Enable operating system security.

Post-install Tasks

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

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

2. Catalog the databases e.g.

   db2 catalog db SQDRP0 on L:

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

3. Edit and run the RunDb2CatFixt.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. Change the location of the Db2 logfiles if desired:

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

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

db2 connect to SQDRPO user SQDR

6. If you did not preserve the original C:\ProgramData\IBM directory, instantiate the stored procedure JAR file.
7. Start the SQDR Plus jetty & launch agent (capagent) services from the Services control panel (Windows) or /etc/init.d (Linux),
8. Configure the services to autostart with the Services control panel (Windows) or chkconfig (Linux).
9. Use a browser to connect to SQDR Control Center and verify that the agent is running; its icon should be green.
10. In a split tier environment, on the Tier 3 system (running the SQDR service), use Data Replicator Manager to resume the I/R group. In a combined tier environment, set the Service to Automatic (Delayed) and start it.
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.x 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.

 

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):

• -- 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

Updating Db2 to the Latest Fix Pack

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. For Windows, run the Db2 installer and update the existing copy of Db2. For Linux, see Installing the Latest Fix Pack on Linux or AIX.
4. Reboot the system if prompted by the installer.
5. Resume SQDR Plus and SQDR services.

Removing Db2 (Linux or AIX)

# 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 reinstall Db2. Note that dasusr1 may not exist on new installations of Db2.

Installing the Latest Fixpack on Linux or AIX

Preparation:

1. Stop SQDR Plus and SQDR services
   

# /etc/init.d/sqdr-capagent stop
# /etc/init.d/sqdr-jetty stop
# /etc/init.d/sqdr-derby stop

2. Stop the SQDR service. See Start the Service in the Quick Start Guide to using SQDR on Linux; using systemd is recommended.
3. As the instance owner, use the db2 list applications command to verify that there are no active connections to Db2,
4. As the instance owner, stop Db2 with db2stop
5. Stop the DAS instance if it exists (note that it may not exist):

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

or
# su - dasusr1
$ db2admin stop

4. 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/V11.5 (Linux)
or /opt/IBM/db2/V11.5 (AIX)

You can ignore prerequisite failures such as those referencing 32-bit library files or ksh (usd by TSAMP).

Enable Fault Monitor to automatically restart Db2

1. Run db2iauto as user db2inst1:

$ db2iauto -on db2inst1

2. Enable the Fault Monitor Coordinator - as root execute:

Linux:

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

(in RHEL7 & later, this creates and enables the db2fm systemd service)

AIX:

# cd /opt/IBM/db2/V11.5/bin/
# ./db2fmcu -u -p /opt/IBM/db2/V11.5/bin/db2fmcd

3. Start up the fault monitor daemon:

# ./db2fm -i db2inst1 -U

4. Start up the fault monitor service (the Db2 instance should not be running):

# ./db2fm -i db2inst1 -u

4. Turn on the fault monitor for the instance:

# ./db2fm -i db2inst1 -f on

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

Post Fixpack Tasks

At this point, Db2 should be running.

5. Verify the new fixpack level with the db2level command.
6. Use db2updv115 to update the system catalog in each database. As db2inst1:

$ db2updv115 -d SQDRP0
etc

7. Start the SQDR Plus services:

# /etc/init.d/sqdr-derby start
# /etc/init.d/sqdr-jetty start
# /etc/init.d/sqdr-capagent start

8. Start the SQDR service.