Upgrading Db2 HADR environment from v11.5.8.0 to v12.1.1.0 - fsmegale/Db2-luw GitHub Wiki
This is a test case to upgrade from v11.5.8.0 CSB (Cumulative Special Build) 32821 to v12.1.1.0 on TSA-managed HADR environment.
If any doubt, you can check the "References" in the end of this document.
This documentation doesn't envolve Automating the environment (Pacemaker) after the upgrade.
The upgrade requires the automation to be deleted before upgrading. The automation need to be recreated after the upgrade with Pacemaker (on Linux environment. For AIX, TSA is supported instead). Check the "References".
Both databases are in the same host:
-
zLinux Red Hat 9.4
-
Db2 version (pre upgrade): v11.5.8.0 CSB 3281
-
Db2 version (post upgrade): v12.1.1.0
-
hostname: malafifi1
-
Instances:
db2inst2 (primary)
db2inst3 (single standby)
- Database: SAMPLE
Ps.: The fact that the databases are in the same host doesn't change the method to upgrade described in this documentation.
There are some methods in the official documentation for upgrading.
For this Test Cat it was chosen: "Upgrading Db2 servers in HADR environments without standby reinitialization"
-
All the System Requirements and Pre Upgrades steps MUST be checked. Those information are all described in the oficial documentation from the "References" section.
-
Ensure that applications do not connect so that no new log data is generated and the log replay position on the standby database eventually matches the log shipping position on the primary database.
-
Check what are all your MQTs, because those will need to be refreshed after the upgrade:
"
Refresh the data in existing materialized query tables by using the REFRESH TABLE statement.
Materialized query tables (MQT) on unicode databases using language aware collation, where the MQT definition involves a LIKE predicate or substring function involved in a basic predicate, need to be refreshed.
"
Post-upgrade tasks for Db2 servers
https://www.ibm.com/docs/en/db2/12.1?topic=servers-post-upgrade-tasks
Query:
db2 "SELECT TABSCHEMA, TABNAME, DEFINER, REMARKS
FROM SYSCAT.TABLES
WHERE TYPE = 'S'
ORDER BY TABSCHEMA, TABNAME"
Db2look to check those condititions:
db2look -d <db_name> -e -t <schema.tab> -o <output_file>
"Cat" the output file to check if there is any "like" or "sub" string in the predicate.
- Remember you will need to remove the TSA Automation before upgrading.
Check states:
- check if it is "ON" on both Primary and Standby
db2 get db cfg for sample |grep -i logindexbuild
- on standby check if hadr_replay_delay is s 0 (zero)
db2 get db cfg for <db_name> |grep -i hadr_replay_delay
- check if HADR is in PEER and CONNECTED states:
db2pd -d <db_name> -hadr
- check if db2prereqcheck has no errors for NON PureScale installation:
db2prereqcheck -v 12.1.1.0
- check if Pacemaker pre requirements has no errors:
db2prereqPCMK
- install Db2 v12.1 from the installer package you downloaded. The command below is for this single host (for a topology with more hosts run it on All the hosts):
/tmp/v12.1/universal/db2/linux390/install/db2_install -b /opt/ibm/db2/V12.1.1.0 -p SERVER -y
- on the Primary
db2 deactivate db <db_name> //otherwise db2ckupgrade will fail with DBT5549N
db2ckupgrade <db_name> -allChecks -l <output_file> -u <instance_owner> -p <instance_owner_password>
db2haicu -delete
lssam
lsrpdomain
- on the Standby
db2haicu -delete
lssam
lsrpdomain
- on Primary
db2 deactivate db <db_name> //if it's now already deactivated
db2stop
db2iupgrade -u <fenced_ID> <instance_name> //with root from the DB2 v12.1 that was installed
- on the Standby
db2 deactivate db <db_name>
db2stop
db2iupgrade -u <fenced_ID> <instance_name> //with root from the DB2 v12.1 that was installed
db2start
db2 upgrade database <db_name>
// if needed, monitor the upgrade with:
db2pd -d <db_name> -hadr
db2licm -a <license_file>
- on the Primary
db2start
db2 upgrade database <db_name>
//if needed, monitor the upgrade with:
- db2pd -d <db_name> -hadr
- db2 activate db <db_name>
db2licm -a <license_file>
db2rbind <db_name> -l <log_file>
db2 refresh table <table_name> ... // If needed: "MQT definition involves a LIKE predicate or substring function involved in a basic predicate, need to be refreshed." - view "Post-upgrade tasks" in the References
db2exmig -d <db_name> -e <explain_schema>
db2 runstats ... //"this is strictly optional. If the current performance and access plans meet your needs, you can skip this step."
- checking if LOGINDEXBUILD is ON on both databases (PRIMARY and STANDBY):
[db2inst2@malafifi1 ~]$ db2 get db cfg for sample |grep -i logindexbuild
Log pages during index build (LOGINDEXBUILD) = ON
[db2inst3@malafifi1 ~]$ db2 get db cfg for sample |grep -i logindexbuild
Log pages during index build (LOGINDEXBUILD) = ON
- checking if HADR_REPLAY_DELAY is 0 on the STANDBY database:
[db2inst3@malafifi1 ~]$ db2 get db cfg for sample |grep -i hadr_replay_delay
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
- db2pd -hadr output (PEER and CONNECTED states):
db2[db2inst2@malafifi1 ~]$ db2pd -d sample -hadr
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 15:44:37 -- Date 2025-02-13-06.22.23.862623
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = NEARSYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = 9.30.25.79
PRIMARY_INSTANCE = db2inst2
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 9.30.25.79
STANDBY_INSTANCE = db2inst3
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 02/12/2025 14:37:49.410877 (1739399869)
HEARTBEAT_INTERVAL(seconds) = 1
HEARTBEAT_MISSED = 0
HEARTBEAT_EXPECTED = 56674
HADR_TIMEOUT(seconds) = 3
TIME_SINCE_LAST_RECV(seconds) = 0
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000921
LOG_HADR_WAIT_ACCUMULATED(seconds) = 1.785
LOG_HADR_WAIT_COUNT = 2292
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 2626560
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 131072
PRIMARY_LOG_FILE,PAGE,POS = S0000002.LOG, 991, 61103804
STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 991, 61103804
HADR_LOG_GAP(bytes) = 0
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 991, 61103804
STANDBY_RECV_REPLAY_GAP(bytes) = 0
PRIMARY_LOG_TIME = 02/13/2025 05:53:55.000000 (1739454835)
STANDBY_LOG_TIME = 02/13/2025 05:53:55.000000 (1739454835)
STANDBY_REPLAY_LOG_TIME = 02/13/2025 05:53:55.000000 (1739454835)
STANDBY_RECV_BUF_SIZE(pages) = 512
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 13000
STANDBY_SPOOL_PERCENT = 0
STANDBY_ERROR_TIME = NULL
PEER_WINDOW(seconds) = 120
PEER_WINDOW_END = 02/13/2025 06:24:23.000000 (1739456663)
READS_ON_STANDBY_ENABLED = N
HADR_LAST_TAKEOVER_TIME = NULL
-
db2prereqcheck. Checking if pre requirements match for Db2 itself:
From the installation package:
[root@malafifi1 universal]# /tmp/v12.1/universal/db2prereqcheck -v 12.1.1.0
==========================================================================
Thu Feb 13 07:17:25 2025
Checking prerequisites for DB2 installation. Version "12.1.1.0". Operating system "Linux"
Validating "kernel level " ...
Required minimum operating system kernel level: "5.14.0".
Actual operating system kernel level: "5.14.0".
Requirement matched.
Validating "Linux distribution " ...
Required minimum operating system distribution: "RHEL"; Version: "9"; Service pack: "2".
Actual operating system distribution Version: "9"; Service pack: "4".
Requirement matched.
Validating "ksh symbolic link" ...
Requirement matched.
Validating "Bin user" ...
Requirement matched.
Validating "libxcrypt-compat" ...
Package (or file) found: "libxcrypt-compat"
Requirement matched.
Validating "C++ Library version " ...
Required minimum C++ library: "libstdc++.so.6"
Standard C++ library is located in the following directory: "/usr/lib64/libstdc++.so.6.0.29".
Actual C++ library: "CXXABI_1.3.1"
Requirement matched.
Requirement matched.
Validating "libaio.so version " ...
DBT3553I The db2prereqcheck utility successfully loaded the libaio.so.1 file.
Requirement matched.
Validating "libnuma.so version " ...
DBT3610I The db2prereqcheck utility successfully loaded the libnuma.so.1 file.
Requirement matched.
Validating "perl-Net-Ping" ...
Package (or file) found: "perl-Net-Ping"
Requirement matched.
DBT3533I The db2prereqcheck utility has confirmed that all installation prerequisites were met.
-
In case of automation needed:
-
Pacemaker
If you will automate with Pacemaker after the upgrade, run the db2prereqPCMK:
-
[root@malafifi1 pcmk]# /tmp/v12.1/universal/db2/linux390/pcmk/db2prereqPCMK
Prerequisite checking for the Pacemaker installation failed.
For more information, check /tmp/db2prereqPCMK.log.1009212
On this case, it is missing a package for Pacemaker:
[root@malafifi1 pcmk]# cat /tmp/db2prereqPCMK.log.1009212
The db2prereqPCMK utility found that python3-dnf-plugin-versionlock package is not installed on the system.
If any package is missing for the prereq, you MUST install it.
In this case I won't automate with Pacemaker. The db2prereqPCMK was run just for demonstration.
So I will skip that package installation. Once again, If any package is missing for the prereq, you MUST install it.
- TSA
The TSA for Linux was discontinued.
It's available for AIX, only.
For AIX, there is a prereqSAM. Run it and install any missing package.
Discontinued functionality in Db2 12.1
https://www.ibm.com/docs/en/db2/12.1?topic=121-discontinued-functionality
- Install Db2 v12.1:
[root@malafifi1 install]# /tmp/v12.1/universal/db2/linux390/install/db2_install -b /opt/ibm/db2/V12.1.1.0 -p SERVER -y
***********************************************************
Do you want to install the DB2 pureScale Feature? [yes/no]
no
DB2 installation is being initialized.
(...)
- on the Primary
7.1) db2ckupgrade
This is also automatically run by the db2iupgrade. If db2iupgrade fails, db2ckupgrade does not run.
[db2inst2@malafifi1 ~]$ db2ckupgrade sample -l /tmp/db2ckupgrade.out
DBT5546W The db2ckupgrade utility completed successfully, however on some databases exclusive access database checks could not be completed.
[db2inst2@malafifi1 ~]$ cat /tmp/db2ckupgrade.out
Version of DB2CKUPGRADE being run: VERSION "11.5"
Database: "SAMPLE"
DBT5550W The db2ckupgrade utility failed to complete checks requiring exclusive database access.
DBT5546W The db2ckupgrade utility completed successfully, however on some databases exclusive access database checks could not be completed.
Because of DBT5546W from the db2ckupgrade output, it was:
[db2inst2@malafifi1 ~]$ db2ckupgrade sample -allChecks -l /tmp/db2ckupgrade1.out -u db2inst2 -p db2inst2
DBT5529N The db2ckupgrade utility did not complete successfully. The database cannot be upgraded. The output log file is named "/tmp/db2ckupgrade1.out".
[db2inst2@malafifi1 ~]$ cat /tmp/db2ckupgrade1.out
Version of DB2CKUPGRADE being run: VERSION "11.5"
Database: "SAMPLE"
DBT5549N No upgrade verification tests were performed because the db2ckupgrade utility failed to get an exclusive database connection.
DBT5529N The db2ckupgrade utility did not complete successfully. The database cannot be upgraded. The output log file is named "/tmp/db2ckupgrade1.out".
Because of DBT5549N from the db2ckupgrade output, it was as the following and finally the output allows the upgrade:
[db2inst2@malafifi1 ~]$ db2 deactivate db sample
DB20000I The DEACTIVATE DATABASE command completed successfully.
[db2inst2@malafifi1 ~]$ db2ckupgrade sample -allChecks -l /tmp/db2ckupgrade2.out -u db2inst2 -p db2inst2
DBT5508I The db2ckupgrade utility completed successfully. The database or databases can be upgraded.
- Remove TSA Automation
Commands here are just a example.
You can see details of a Test Case here:
Removing TSA Automation from HADR
https://github.com/fsmegale/Db2-luw/wiki/Removing-TSA-Automation-from-HADR
The commands are:
- on Primary:
db2haicu -delete
- on standby:
db2haicu -delete
- on both hosts:
Check is the TSA automation was removed:
lssam
lsrpdomain
- on Primary
9.1) Deactivate the database (if it's now already deactivated)
[db2inst2@malafifi1 ~]$ db2 deactivate db sample
DB20000I The DEACTIVATE DATABASE command completed successfully.
9.2) Stop Instance
[db2inst2@malafifi1 ~]$ db2stop
02/14/2025 04:57:30 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
9.3) Upgrade the instance
With root from the DB2 v12.1 that was installed:
[root@malafifi1 instance]# /opt/ibm/db2/V12.1.1.0/instance/db2iupgrade -u db2fenc2 db2inst2
DBI1446I The db2iupgrade command is running.
DB2 installation is being initialized.
Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)
Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end
Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end
Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end
Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end
The execution completed successfully.
For more information see the DB2 installation log at
"/tmp/db2iupgrade.log.1160378".
DBI1070I Program db2iupgrade completed successfully.
- on the Standby
10.1) Deactivate the database
[db2inst3@malafifi1 ~]$ db2 deactivate db sample
DB20000I The DEACTIVATE DATABASE command completed successfully.
10.2) Stop the instance
[db2inst3@malafifi1 ~]$ db2stop
02/14/2025 05:17:21 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
10.3) Upgrade the instance as root from the DB2 v12.1 that was installed
[root@malafifi1 ~]# /opt/ibm/db2/V12.1.1.0/instance/db2iupgrade -u db2fenc3 db2inst3
DBI1446I The db2iupgrade command is running.
DB2 installation is being initialized.
Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)
Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end
Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end
Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end
Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end
The execution completed successfully.
For more information see the DB2 installation log at
"/tmp/db2iupgrade.log.1186807".
DBI1070I Program db2iupgrade completed successfully.
10.4) Start de instance
[db2inst3@malafifi1 ~]$ db2start
SQL8007W There are "89" day(s) left in the evaluation period for the product
"DB2 Advanced Enterprise Server Edition". For evaluation license terms and
conditions, refer to the License Agreement document located in the license
directory in the installation path of this product. If you have licensed this
product, ensure the license key is properly registered. You can register the
license by using the db2licm command line utility. The license key can be
obtained from your licensed product CD.
02/14/2025 05:34:31 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
10.5) Upgrade the database
As instance owner:
[db2inst3@malafifi1 ~]$ db2 upgrade database sample
SQL1103W The UPGRADE DATABASE command was completed successfully.
if needed, monitor the upgrade with:
db2pd -d <db_name> -hadr
10.6) Apply the license
[db2inst3@malafifi1 v12.1]$ db2licm -a db2####.lic
LIC1402I License added successfully.
- on the Primary
11.1) Start de instance
[db2inst2@malafifi1 ~]$ db2start
SQL8007W There are "89" day(s) left in the evaluation period for the product
"DB2 Advanced Enterprise Server Edition". For evaluation license terms and
conditions, refer to the License Agreement document located in the license
directory in the installation path of this product. If you have licensed this
product, ensure the license key is properly registered. You can register the
license by using the db2licm command line utility. The license key can be
obtained from your licensed product CD.
02/14/2025 05:41:27 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
11.2) Upgrade the database
As instance owner:
[db2inst2@malafifi1 ~]$ db2 upgrade database sample
DB20000I The UPGRADE DATABASE command completed successfully.
monitor the upgrade checking:
-
db2diag.log
-
db2pd -d <db_name> -hadr
11.3) Activate the database
[db2inst2@malafifi1 ~]$ db2 activate db sample
DB20000I The ACTIVATE DATABASE command completed successfully.
11.4) Apply the license
[db2inst2@malafifi1 v12.1]$ db2licm -a db2#####.lic
LIC1402I License added successfully.
11.5) Rebind packages
[db2inst2@malafifi1 v12.1]$ db2rbind sample -l /tmp/primary_rebind.out
Rebind done successfully for database 'SAMPLE'.
[db2inst2@malafifi1 v12.1]$ cat /tmp/primary_rebind.out
Starting time .... Fri Feb 14 06:34:31 2025
Succeeded to rebind = 0
Failed to rebind = 0 (or more)
Ending time .... Fri Feb 14 06:34:31 2025
11.6) Refresh MQTs if needed
For the MQTs: "MQT definition involves a LIKE predicate or substring function involved in a basic predicate, need to be refreshed." - view "Post-upgrade tasks" in the References
Took a db2look of the MQT to check if it has any Substring or LIKE in predicate:
[db2inst2@malafifi1 tmp]$ db2look -d sample -e -t ADEFUSR -o ADEFUSR.db2look.out
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST2
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Output is sent to file: ADEFUSR.db2look.out
Checked for those conditions:
[db2inst2@malafifi1 tmp]$ cat ADEFUSR.db2look.out |grep -i like
[db2inst2@malafifi1 tmp]$ cat ADEFUSR.db2look.out |grep -i sub
Those conditions were not matched. So, refresh is not required.
Refreshed the MQT just as an example:
[db2inst2@malafifi1 v12.1]$ db2 refresh table ADEFUSR
DB20000I The SQL command completed successfully.
11.7) Upgrade Explain tables
Just a example once I didn't create the explain tables before the upgrade:
db2exmig -d dbname -e explain_schema
11.8) Runstat
From the documentation:
"this is strictly optional. If the current performance and access plans meet your needs, you can skip this step."
db2 runstats ...
======================================
Db2 11.5 system requirements
https://www.ibm.com/docs/en/db2/11.5?topic=database-system-requirements
Db2 12.1 system requirements
https://www.ibm.com/docs/en/db2/12.1?topic=database-system-requirements
Upgrade Db2 High Availability Disaster Recovery (HADR) environments
https://www.ibm.com/docs/en/db2/12.1?topic=udssc-upgrade-db2-high-availability-disaster-recovery-hadr-environments
Upgrading Db2 servers in HADR single standby environments without standby reinitialization
https://www.ibm.com/docs/en/db2/12.1?topic=udsihewsr-upgrading-db2-servers-in-hadr-single-standby-environments-without-standby-reinitialization
Upgrading Db2 servers in a TSA automated HADR environment from a previous Db2 Version on AIX
https://www.ibm.com/docs/en/db2/12.1?topic=udhadrhe-upgrading-db2-servers-in-tsa-automated-hadr-environment-fr
db2ckupgrade - Check database for upgrade command
https://www.ibm.com/docs/en/db2/12.1?topic=commands-db2ckupgrade-check-database-upgrade
db2iupgrade - Upgrade instance command
https://www.ibm.com/docs/en/db2/12.1?topic=commands-db2iupgrade-upgrade-instance
UPGRADE DATABASE command
https://www.ibm.com/docs/en/db2/12.1?topic=commands-upgrade-database
Post-upgrade tasks for Db2 servers
https://www.ibm.com/docs/en/db2/12.1?topic=servers-post-upgrade-tasks
Rebinding packages in upgraded databases
https://www.ibm.com/docs/en/db2/12.1?topic=tasks-rebinding-packages-in-upgraded-databases
REFRESH TABLE statement
https://www.ibm.com/docs/en/db2/12.1?topic=statements-refresh-table
Verifying upgrade of Db2 servers
https://www.ibm.com/docs/en/db2/12.1?topic=tasks-verifying-upgrade-db2-servers
Discontinued functionality in Db2 12.1
https://www.ibm.com/docs/en/db2/12.1?topic=121-discontinued-functionality
Upgrading explain tables
https://www.ibm.com/docs/en/db2/12.1?topic=tasks-upgrading-explain-tables
Removing TSA Automation from HADR
https://github.com/fsmegale/Db2-luw/wiki/Removing-TSA-Automation-from-HADR