Update 4.0 and 4.5 DB2 databases manually

Overview

Manually update IBM Connections 4.0 or 4.5 databases to v5.0 in an existing IBM WebSphere Application Server and DB2 database environment.

Before applying updates, back up Connections.

Make sure to configure the DB2 databases for unicode so that DB2 tools like export and import do not corrupt unicode data.

Install supporting software for v5.0.

Use the JRE under the Wizards directory. Update the PATH variable to point to this JRE. For example...

Wizards/jvm/linux/jre

IBM Connections does not support GNU Java.

To use a database administrator ID to run the Java migration utilities.

After running each command, review output closely for error messages. Resolve errors before proceeding.

DB2 only

If we use only one database instance, and if that instance includes other databases besides IBM Connections, configure the numdb parameter to match the total number of databases on the instance.

• If we migrated from IBM Connections 4.5, the numdb parameter was set to 14, the maximum number of Connections 4.5 databases. If the instance has additional databases, increase the value of the numdb parameter to match the total number of databases on the instance. To set:

  db2 UPDATE DBM CFG USING NUMDB nn

  where nn is a number of databases.

• Before removing (or dropping) a database, stop Connections first to ensure that no database connection is in use; otherwise you will not drop the user and the database removal will not occur.

• If we run dbWizard.bat but the database wizard does not launch, check whether you have 32-bit DB2 installed. We need to have 64-bit DB2 on a 64-bit system.

Update databases manually

1. Log on to the WAS console on the dmgr and go to...
   Applications | Application types | WebSphere Enterprise Applications

2. Stop all Connections applications.

3. (DB2 on Windows 2008 64-bit.) On Windows 2008, perform DB2 administration tasks with full administrator privileges.

   1. Logged in as the instance owner, open a command prompt and change to the DB2 bin directory.
      /IBM/SQLLIB/BIN

   2. Enter the following command: db2cwadmin.bat. This command opens the DB2 command line processor while also setting the DB2 privileges.

4. Log in as the database administrator.

5. For each application, change to the directory where the SQL scripts are stored and then enter the commands for that application.

   To capture the output of each command to a log file, append the following parameter to each command: >> /file_path/db_application.log

   where file_path is the full path to the log file and application is the name of the log file. For example:

   db2 -tvf createDb.sql >> /home/db2inst1/db_activities.log

   Ensure you have write permissions for the directories and log files.

6. cd WP_PROFILEhe Activities SQL scripts directory Wizards/connections.sql/activities/db2, and then check for the database schema version :
   db2 connect to OPNACT
   db2 select DBSCHEMAVER from ACTIVITIES.OA_SCHEMA

   • For version 53, run:
     db2 -td@ -vf upgrade-40-45.sql
     db2 -td@ -vf upgrade-45-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 56, run:
     db2 -td@ -vf upgrade-45-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 69, run:
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

7. cd WP_PROFILEhe Blogs SQL scripts directory Wizards/connections.sql/blogs/db2, and then check for the database schema version :
   db2 connect to BLOGS
   db2 select value from BLOGS.roller_properties where name= 'database.schema.version'

   • For version 43, run:
     db2 -td@ -vf upgrade-40-45.sql
     db2 -td@ -vf upgrade-45-45CR4.sql
     db2 -td@ -vf upgrade-45CR4-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 51, run:
     db2 -td@ -vf upgrade-45-45CR4.sql
     db2 -td@ -vf upgrade-45CR4-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 52, run:
     db2 -td@ -vf upgrade-45CR4-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 69, run:
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

8. Change to Bookmarks SQL scripts directory Wizards/connections.sql/dogear/db2, and then check for the database schema version :
   db2 connect to DOGEAR
   db2 select DBSCHEMAVER from DOGEAR.DOGEAR_SCHEMA

   • For version 14, run:
     db2 -td@ -vf upgrade-40-45.sql
     db2 -td@ -vf upgrade-45-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 15, then enter the following command:
     db2 -td@ -vf upgrade-45-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 22, run
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

9. cd WP_PROFILEhe Communities SQL scripts directory Wizards/connections.sql/communities/db2, and then check the database schema version :
   db2 connect to SNCOMM
   db2 select DBSCHEMAVER from SNCOMM.SNCOMM_SCHEMA

   • For version 76, run:
     db2 -td@ -vf upgrade-40-45.sql
     db2 -td@ -vf upgrade-45-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 77, run:
     db2 -td@ -vf upgrade-45-50.sql
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

   • For version 93, run:
     db2 -td@ -vf appGrants.sql
     db2 -td@ -vf reorg.sql
     db2 -td@ -vf runstats.sql

10. cd WP_PROFILEhe Calendar SQL scripts directory Wizards/connections.sql/calendar/db2, and then check the database schema version :
    db2 connect to SNCOMM
    db2 select DBSCHEMAVER from CALENDAR.CA_SCHEMA

    • For version 10, run:
      db2 -td@ -vf calendar-upgrade-40-45.sql
      db2 -td@ -vf calendar-upgrade-45-50.sql
      db2 -td@ -vf calendar-appGrants.sql
      db2 -td@ -vf calendar-reorg.sql
      db2 -td@ -vf calendar-runstats.sql

    • For version 15, run:
      db2 -td@ -vf calendar-upgrade-45-50.sql
      db2 -td@ -vf calendar-appGrants.sql
      db2 -td@ -vf calendar-reorg.sql
      db2 -td@ -vf calendar-runstats.sql

    • For version 29, run:
      db2 -td@ -vf calendar-appGrants.sql
      db2 -td@ -vf calendar-reorg.sql
      db2 -td@ -vf calendar-runstats.sql

11. cd WP_PROFILEhe Files SQL scripts directory Wizards/connections.sql/files/db2, and then check the database schema version :
    db2 connect to FILES
    db2 select SCHEMA_VERSION from "FILES"."PRODUCT"

    • For version 79, run:
      db2 -td@ -vf upgrade-40-45.sql
      db2 -td@ -vf upgrade-45-45CR3.sql
      db2 -td@ -vf upgrade-45CR3-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 82, run:
      db2 -td@ -vf upgrade-45-45CR3.sql
      db2 -td@ -vf upgrade-45CR3-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 89, run:
      db2 -td@ -vf upgrade-45CR3-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 107, run:
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

12. cd WP_PROFILEhe Forum SQL scripts directory Wizards/connections.sql/forum/db2, and then check the database schema version :
    db2 connect to FORUM
    db2 select DBSCHEMAVER from FORUM.DF_SCHEMA

    • For version 21, run:
      db2 -td@ -vf upgrade-40-45.sql
      db2 -td@ -vf upgrade-45-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 27, run:
      db2 -td@ -vf upgrade-45-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 35, run:
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

13. cd WP_PROFILEhe Home page SQL scripts directory Wizards/connections.sql/homepage/db2, and then check the database schema version :
    db2 connect to HOMEPAGE
    db2 select DBSCHEMAVER from HOMEPAGE.HOMEPAGE_SCHEMA

    • For version 110, run:
      db2 -td@ -vf upgrade-40-40CR2.sql
      db2 -td@ -vf upgrade-40CR2-45.sql

      Also, run a java migration from 4.0 to 4.5.

      Then, resume executing the upgrade commands.

      db2 -td@ -vf upgrade-45-45CR3.sql
      db2 -td@ -vf upgrade-45CR3-45CR4.sql
      db2 -td@ -vf upgrade-45CR4-50.sql

      Next, run a java migration from 4.5 to 5.0.

      Then, resume executing the upgrade commands

      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 132, then enter the following command:
      db2 -td@ -vf upgrade-40CR2-45.sql

      Also, run a java migration from 4.0 to 4.5.

      Then, resume executing the upgrade commands.

      db2 -td@ -vf upgrade-45-45CR3.sql
      db2 -td@ -vf upgrade-45CR3-45CR4.sql
      db2 -td@ -vf upgrade-45CR4-50.sql

      Next, run a java migration from 4.5 to 5.0.

      Then, resume executing the upgrade commands

      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 210, run:
      db2 -td@ -vf upgrade-45-45CR3.sql
      db2 -td@ -vf upgrade-45CR3-45CR4.sql
      db2 -td@ -vf upgrade-45CR4-50.sql

      Next, run a java migration from 4.5 to 5.0.

      Then, resume executing the upgrade commands

      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 211, run:
      db2 -td@ -vf upgrade-45CR3-45CR4.sql
      db2 -td@ -vf upgrade-45CR4-50.sql

      Next, run a java migration from 4.5 to 5.0.

      Then, resume executing the upgrade commands:

      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 213, run:
      db2 -td@ -vf upgrade-45CR-50.sql

      Next, run a java migration from 4.5 to 5.0.

      Then, resume executing the upgrade commands:

      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 475, run:
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

14. cd WP_PROFILEhe Metrics SQL scripts directory Wizards/connections.sql/metrics/db2, and then check the database schema :
    db2 connect to METRICS
    db2 select SCHEMA_VERSION from "METRICS"."PRODUCT"

    • For version 29, run:
      db2 -td@ -vf upgrade-40-40CR3.sql
      db2 -td@ -vf upgrade-40CR3-45.sql
      db2 -td@ -vf upgrade-45-45CR2.sql
      db2 -td@ -vf fixup-LO77552.sql
      db2 -td@ -vf upgrade-45CR4-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 30, run:
      db2 -td@ -vf upgrade-40CR3-45.sql
      db2 -td@ -vf upgrade-45-45CR2.sql
      db2 -td@ -vf fixup-LO77552.sql
      db2 -td@ -vf upgrade-45CR4-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 34, run:
      db2 -td@ -vf upgrade-45-45CR2.sql
      db2 -td@ -vf fixup-LO77552.sql
      db2 -td@ -vf upgrade-45CR4-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 37, run:
      db2 -td@ -vf fixup-LO77552.sql
      db2 -td@ -vf upgrade-45CR4-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

    • For version 38, run:
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

15. cd WP_PROFILEhe Mobile SQL scripts directory Wizards/connections.sql/mobile/db2, and then check the database schema version :
    db2 connect to MOBILE
    db2 select VALUE from MOBILE.ROLLER_PROPERTIES where NAME='database.schema.version'

    • For version 4.0.0.0.0, run:
      db2 -td@ -vf upgrade-40-45.sql
      db2 -td@ -vf upgrade-45-45CR4.sql
      db2 -td@ -vf upgrade-45CR4-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 4.5.0.0.0, run:
      db2 -td@ -vf upgrade-45-45CR4.sql
      db2 -td@ -vf upgrade-45CR4-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 2, run:
      db2 -td@ -vf upgrade-45CR4-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 7, run:
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

16. cd WP_PROFILEhe Profiles SQL scripts directory Wizards/connections.sql/profiles/db2, and then check the database schema version :
    db2 connect to PEOPLEDB
    db2 select DBSCHEMAVER from EMPINST.SNPROF_SCHEMA

    • For version 33, run:
      db2 -td@ -vf upgrade-40-45.sql
      db2 -td@ -vf upgrade-45-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf clearScheduler.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 36, run:
      db2 -td@ -vf upgrade-45-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf clearScheduler.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 46, run:
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf clearScheduler.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

17. cd WP_PROFILEhe Wikis SQL scripts directory Wizards/connections.sql/wikis/db2, and then check the database schema version :
    db2 connect to WIKIS
    db2 select SCHEMA_VERSION from "WIKIS"."PRODUCT"

    • For version 79, run:
      db2 -td@ -vf upgrade-40-45.sql
      db2 -td@ -vf upgrade-45-45CR3.sql
      db2 -td@ -vf upgrade-45CR3-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 82, run:
      db2 -td@ -vf upgrade-45-45CR3.sql
      db2 -td@ -vf upgrade-45CR3-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 89, run:
      db2 -td@ -vf upgrade-45CR3-50.sql
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf runstats.sql

    • For version 107, run:
      db2 -td@ -vf appGrants.sql
      db2 -td@ -vf reorg.sql
      db2 -td@ -vf updateStats.sql

What to do next

Check that all the databases are working correctly.

(DB2 for Linux on System z only.) To improve database performance, enable the NO FILE SYSTEM CACHING option. See Enable NO FILE SYSTEM CACHING for DB2 on System z topic.

Parent topic:
Update 4.0 and 4.5 databases manually

Related:

Enable NO FILE SYSTEM CACHING for DB2 on System z

Access the News configuration file