Verifying the database before the upgrade

Before you upgrade the database engine on Db2® Warehouse, it is important to use the db2ckupgrade command to verify that your databases are ready for upgrade.

About this task

The db2ckupgrade command verifies that a list of conditions is true to succeed at the database upgrade. Also, this command writes to the log file, which is specified with the -l parameter, a warning message for a list of conditions that affect database upgrades.

Note that in the Db2 Warehouse environment you must use the containerized version of the command. You can download it from IBM Hybrid Data Management Community. The executable file is in the zip package.

If you are upgrading Db2 Warehouse Version 3.8 or later to Version 11.5, which involves the Db2 engine upgrade, you must run the db2ckupgrade utility prior to upgrading to make sure that the database is ready for the upgrade.

You must run the command from the DB2DIR/bin directory, where DB2DIR is the location where the Db2 copy is installed. In the Db2 Warehouse environment, it is /mnt/blumeta0/home/db2inst1/sqllib/bin.

Following is the command syntax:

db2ckupgrade dbname -container [-allchecks] -l logfile

<dbname>: Specifies an alias name of a local database to be scanned.
-allChecks: Optional. Specifies that all the checks are to be run. This requires exclusive database connection.
-l filename: Specifies a log file to keep a list of errors and warnings generated for the scanned database.

Example usage:

db2ckupgrade bludb -container -l /tmp/db2ckupgrade.log

To learn more details about the db2ckupgrade command and how it works, see the following topic in the Db2 Knowledge Center db2ckupgrade - Check database for upgrade command. Note that in the containerized version that you use for Db2 Warehouse the list of parameters that you can use is limited, as described above.

Procedure

Download the package as instructed by IBM Support.

Copy the file to the /scratch partition:


scp db2ckupgrade64_exe user@system:/root
cp db2ckupgrade64_exe /mnt/blumeta0/scratch

Access the container as root and run the following Docker or Podman command:
```
docker exec -it dashDB bash 
```
```
podman exec -it dashDB bash
```

Move to the Db2 binary directory and copy the db2ckupgrade64_exe file to that directory. Make copy of the original preserving the creation date.

cd /mnt/blumeta0/home/db2inst1/sqllib/bin
# pwd
/mnt/blumeta0/home/db2inst1/sqllib/bin
# cp /mnt/blumeta0/scratch/db2ckupgrade64_exe  
# ls -lra db2cku*
-rwxr-xr-x. 1 root root 358224 Apr 22 14:16 db2ckupgrade64_exe
-r-xr-xr-x. 1 bin bin 360073 Apr 13 19:07 db2ckupgrade64_exe
-r-xr-xr-x. 1 bin bin  13096 Mar 24 20:48 db2ckupgrade  
# cp -p db2ckupgrade64_exe db2ckupgrade64_exe.org
# ls -lra db2cku*
-rwxr-xr-x. 1 root root 358224 Apr 22 14:16 db2ckupgrade64_exe
-r-xr-xr-x. 1 bin bin 360073 Apr 13 19:07 db2ckupgrade64_exe
-r-xr-xr-x. 1 bin bin  13096 Mar 24 20:48 db2ckupgrade  
-r-xr-xr-x. 1 bin bin 360073 Apr 13 19:07 db2ckupgrade64_exe.org

Modify the permissions, owner, group to match existing, make copy of original:

# chmod 555 db2ckupgrade64_exe
[root@node0101-fab - Db2wh bin]# ls -lra db2cku*
-r-xr-xr-x. 1 bin bin 360073 Apr 13 19:07 db2ckupgrade64_exe.org
-r-xr-xr-x. 1 root root 358224 Apr 22 14:16 db2ckupgrade64_exe
-r-xr-xr-x. 1 bin bin 360073 Apr 13 19:07 db2ckupgrade64_exe
-r-xr-xr-x. 1 bin bin  13096 Mar 24 20:48 db2ckupgrade
# chown bin db2ckupgrade64_exe
# chgrp bin db2ckupgrade64_exe
# ls -lra db2cku*
-r-xr-xr-x. 1 bin bin 360073 Apr 13 19:07 db2ckupgrade64_exe.org
-r-xr-xr-x. 1 bin bin 358224 Apr 22 14:16 db2ckupgrade64_exe
-r-xr-xr-x. 1 bin bin 13096 Mar 24 20:48 db2ckupgrade

Copy new binary over existing:

# cp -p db2ckupgrade64_exe
# ls -lra db2cku*
-r-xr-xr-x. 1 bin bin 360073 Apr 13 19:07 db2ckupgrade64_exe.org
-r-xr-xr-x. 1 bin bin 358224 Apr 22 14:16 db2ckupgrade64_exe
-r-xr-xr-x. 1 bin bin 13096 Mar 24 20:48 db2ckupgrade

You now have the db2ckupgrade utility ready to be used.

Note: db2ckupgrade needs exclusive database access and if it does not meet this requirement, the following warning is printed:

[db2inst1@node0101-fab - Db2wh bin]$ db2ckupgrade bludb -container -l /tmp/db2ckupgrade.log
DBT5546W  The db2ckupgrade utility completed successfully, however on some databases exclusive access database checks could not be completed.

Run the following steps to resolve the exclusive database access warning:

Switch to bluadmin user:

[db2inst1@node0101-fab - Db2wh bin]$ exit
exit
[root@node0101-fab - Db2wh /]# su bluadmin

Run db_stopsrc -all. This command will disable HA and the web console (DSM).
Once you confirm HA and DSM are disabled successfully, exit as bluadmin user and log in as db2inst1 user as shown below:
```
[bluadmin@node0101-fab - Db2wh /]$ exit
exit
[root@node0101-fab - Db2wh /]# su db2inst1
```

Run db2 list applications for db bludb:

[db2inst1@node0101-fab - Db2wh /]$ db2 list applications for db bludb
SQL1611W No data was returned by Database System Monitor.

Run the following command:

[db2inst1@node0101-fab - Db2wh /]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.

Run the following command:

[db2inst1@node0101-fab - Db2wh /]$ db2 deactivate db bludb
DB20000I  The DEACTIVATE DATABASE command completed successfully.

Run db2 list applications for db bludb:

[db2inst1@node0101-fab - Db2wh /]$ db2 list applications for db bludb
SQL1611W No data was returned by Database System Monitor.

Go to the path where db2ckupgrade utility is copied to:

[db2inst1@node0101-fab - Db2wh /]$ cd /mnt/blumeta0/home/bluadmin/sqllib/bin

Run the command with, or without the -allchecks option, as in the following examples:

[db2inst1@node0101-fab - Db2wh bin]$ db2ckupgrade bludb -container -l /tmp/db2ckupgrade.log

DBT5508I  The db2ckupgrade utility completed successfully. The database or databases can be upgraded.

[db2inst1@node0101-fab - Db2wh bin]$ cat /tmp/db2ckupgrade.log
Version of DB2CKUPGRADE being run: VERSION "11.5"
Database: "BLUDB"
DBT5508I  The db2ckupgrade utility completed successfully. The database or databases can be upgraded.

[db2inst1@node0101-fab - Db2wh bin]$ db2ckupgrade bludb -container -allchecks -l /tmp/db2ckupgrade.log
DBT5508I  The db2ckupgrade utility completed successfully. The database or databases can be upgraded.
[db2inst1@node0101-fab - Db2wh bin]$ cat /tmp/db2ckupgrade.log

Version of DB2CKUPGRADE being run: VERSION "11.5"
Database: "BLUDB"
DBT5508I  The db2ckupgrade utility completed successfully. The database or databases can be upgraded.

When done, switch user to bluadmin:

[db2inst1@node0101-fab - Db2wh bin]$ exit
exit
[root@node0101-fab - Db2wh /]# su bluadmin

As bluadmin, run the following command:

 [bluadmin@node0101-fab - Db2wh /]$ db_startsrc -all
Successfully enabled HA.
Successfully enabled API Server.
Successfully enabled DS Server.
[bluadmin@node0101-fab - Db2wh /]$ db2 connect to bludb
  Database Connection Information
Database server        = DB2/LINUXPPC64LE 11.1.9.0
SQL authorization ID   = BLUADMIN
Local database alias   = BLUDB

Results

Your database is now ready to be upgraded. Return to section Upgrading IBM Db2 Warehouse to version 11.5 (main procedure).

Troubleshooting:

If the following Failed to disable DSM connections error message is printed:

[db2inst1@node0101-fab - Db2wh bin]$ su - bluadmin -c "sudo -E /bin/db_stopsrc -all"
Password: 
HA is already Disabled.
Failed to disable DSM connections.
Traceback (most recent call last):
  File "/usr/bin/db_stopsrc", line 10, in module
    sys.exit(main())
  File "/usr/lib/python2.7/site-packages/bnr/cli/db_stopsrc.py", line 231, in main
    disable.main(sys.argv[1:])
  File "/usr/lib/python2.7/site-packages/bnr/cli/db_stopsrc.py", line 210, in main
    self.process_arguments(parsed_args)
  File "/usr/lib/python2.7/site-packages/bnr/cli/db_stopsrc.py", line 202, in process_arguments
    self.disable_DSM_connections()
  File "/usr/lib/python2.7/site-packages/bnr/cli/db_stopsrc.py", line 128, in disable_DSM_connections
    raise err
bnr.modules.cmd_runner_error.CmdRunnerError:

it usually indicates that the web console was not enabled at the time the connections were disabled. This error can be ignored and you should continue with the steps to resolve the exclusive database access, and then proceed to run db2ckupgrade.