Updating 4.0 and 4.5 DB2 databases manually

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

Before you begin

Before applying updates, back up your databases. For more information, see Backing up IBM Connections.

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

Ensure that you have installed and configured all supporting software for version 5.0.

(DB2 only) If you 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. For more information, go to the numdb webpage in the DB2 information center.
Notes:
  • If you migrated from IBM Connections 4.5, the numdb parameter was set to 14, the maximum number of IBM 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 change the parameter, enter the following command:

    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 you run dbWizard.bat but the database wizard does not launch, check whether you have 32-bit DB2 installed. You need to have 64-bit DB2 on a 64-bit system.

About this task

This topic describes how to update IBM Connections version 4.0 or 4.5 databases to version 5.0 manually. Use this procedure if you want an alternative to using the database wizard to update your databases.

Notes:
  • This topic applies to all releases of version 4.0 and 4.5
  • Use the Java Runtime Environment (JRE) under the Wizards directory in the installation media. Update your PATH variable to point to this JRE, using the instructions for your operating system. For example, the relative path to the JRE on the Microsoft Windows operating system might be Wizards\jvm\win\jre. For the AIX® or Linux operating systems, the relative path might be Wizards/jvm/aix/jre and Wizards/jvm/linux/jre.
  • IBM Connections does not support GNU Java.
  • You need to use a database administrator ID to run the Java migration utilities described in this task.
  • After running each command, examine the output of the command for error messages. If you find errors, resolve them before continuing with the update process.
  • To improve readability, some commands and file paths in this topic are displayed on separate lines. Ignore these formatting conventions when entering the commands.

To update databases manually, complete the following steps:

Procedure

  1. Log in to the WebSphere Application Server Integrated Solutions Console on your Deployment Manager.
  2. Go to Applications > Application types > WebSphere enterprise Applications.
  3. Stop all IBM Connections applications.
  4. (DB2 on Windows 2008 64-bit.) On Windows 2008, you must 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. For example: C:\Program Files\IBM\SQLLIB\BIN.
    2. Enter the following command: db2cwadmin.bat. This command opens the DB2 command line processor while also setting your DB2 privileges.
  5. Log in as the database administrator.
  6. For each application, change to the directory where the SQL scripts are stored and then enter the commands for that application.
    Note: 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 -td@ createDb.sql >> /home/db2inst1/db_activities.log

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

    Note: If your database version of IBM Connections components are already at a given schema version, then start running scripts from that schema version level. For example, if the Activities database is schema version 56, enter the commands for schema version 56 and version 69.
  7. Change to the Activities SQL scripts directory Wizards/connections.sql/activities/db2, and then check for the database schema version as follows: 
    db2 connect to OPNACT

db2 select DBSCHEMAVER from ACTIVITIES.OA_SCHEMA
    • If the schema version is 53, then enter the following commands:
      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
    • If schema version is 56, then enter the following commands:
      db2 -td@ -vf upgrade-45-50.sql 
db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
    • If schema version is 69, then enter the following commands:
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
  8. Change to the Blogs SQL scripts directory Wizards/connections.sql/blogs/db2, and then check for the database schema version as follows: 
    db2 connect to BLOGS

db2 select value from BLOGS.roller_properties where name=
'database.schema.version'
    • If the schema version is 43, then enter the following commands:
      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
    • If schema version is 51, then enter the following commands:
      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
    • If schema version is 52, then enter the following commands:
      db2 -td@ -vf upgrade-45CR4-50.sql
db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
    • If schema version is 69, then enter the following commands:
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
  9. Change to Bookmarks SQL scripts directory Wizards/connections.sql/dogear/db2, and then check for the database schema version as follows: 
    db2 connect to DOGEAR

db2 select DBSCHEMAVER from DOGEAR.DOGEAR_SCHEMA
    • If the schema version is 14, then enter the following commands:
      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
    • If schema version is 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
    • If schema version is 22, then enter the following commands
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
  10. Change to the Communities SQL scripts directory Wizards/connections.sql/communities/db2, and then check the database schema version as follows: 
    db2 connect to SNCOMM

db2 select DBSCHEMAVER from SNCOMM.SNCOMM_SCHEMA
    • If the schema version is 76, then enter the following commands:
      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
    • If schema version is 77, then enter the following commands:
      db2 -td@ -vf upgrade-45-50.sql
db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
    • If schema version is 93, then enter the following commands:
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
  11. Change to the Calendar SQL scripts directory Wizards/connections.sql/communities/db2, and then check the database schema version as follows: 
    db2 connect to SNCOMM

db2 select DBSCHEMAVER from CALENDAR.CA_SCHEMA
    • If schema version is 10, then enter the following commands:
      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
    • If schema version is 15, then enter the following commands:
      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
    • If schema version is 29, then enter the following commands:
      db2 -td@ -vf calendar-appGrants.sql 
db2 -td@ -vf calendar-reorg.sql 
db2 -td@ -vf calendar-runstats.sql
  12. Change to the Files SQL scripts directory Wizards/connections.sql/files/db2, and then check the database schema version as follows: 
    db2 connect to FILES

db2 select SCHEMA_VERSION from "FILES"."PRODUCT"
    • If the schema version is 79, then enter the following commands:
      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
    • If schema version is 82, then enter the following commands:
      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
    • If schema version is 89, then enter the following commands:
      db2 -td@ -vf upgrade-45CR3-50.sql
db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf updateStats.sql
    • If schema version is 107, then enter the following commands:
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf updateStats.sql
    In addition, enter the following commands to run the scripts for enabling Push notification:

    db2 -td@ -vf pns-createDb.sql

    db2 -td@ -vf pns-appGrants.sql

  13. Change to the Forum SQL scripts directory Wizards/connections.sql/forum/db2, and then check the database schema version as follows: 
    db2 connect to FORUM

db2 select DBSCHEMAVER from FORUM.DF_SCHEMA
    • If the schema version is 21, then enter the following commands:
      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
    • If schema version is 27, then enter the following commands:
      db2 -td@ -vf upgrade-45-50.sql
db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
    • If schema version is 35, then enter the following commands:
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
  14. Change to the Home page SQL scripts directory Wizards/connections.sql/homepage/db2, and then check the database schema version as follows: 
    db2 connect to HOMEPAGE

db2 select DBSCHEMAVER from HOMEPAGE.HOMEPAGE_SCHEMA
    • If the schema version is 110, then enter the following commands:
      db2 -td@ -vf upgrade-40-40CR2.sql 
db2 -td@ -vf upgrade-40CR2-45.sql

      Also, you need to 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, you need to 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
    • If schema version is 132, then enter the following command:
      db2 -td@ -vf upgrade-40CR2-45.sql

      Also, you need to 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, you need to 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
    • If schema version is 210, then enter the following commands:
      db2 -td@ -vf upgrade-45-45CR3.sql
db2 -td@ -vf upgrade-45CR3-45CR4.sql
db2 -td@ -vf upgrade-45CR4-50.sql

      Next, you need to 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
    • If schema version is 211, then enter the following commands:
      db2 -td@ -vf upgrade-45CR3-45CR4.sql
db2 -td@ -vf upgrade-45CR4-50.sql

      Next, you need to 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
    • If schema version is 213, then enter the following commands:
      db2 -td@ -vf homepage/db2/upgrade-45CR4-50.sql

      Next, you need to 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
    • If schema version is 475, then enter the following commands:
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf updateStats.sql
  15. Change to the Metrics SQL scripts directory Wizards/connections.sql/metrics/db2, and then check the database schema as follows: 
    db2 connect to METRICS

db2 select SCHEMA_VERSION from "METRICS"."PRODUCT"
    • If the schema version is 29, then enter the following commands:
      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
    • If schema version is 30, then enter the following commands:
      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
    • If schema version is 34, then enter the following commands:
      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
    • If schema version is 37, then enter the following commands:
      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
    • If schema version is 38, then enter the following commands:
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf updateStats.sql
  16. Change to the Mobile SQL scripts directory Wizards/connections.sql/mobile/db2, and then check the database schema version as follows: 
    db2 connect to MOBILE

db2 select VALUE from MOBILE.ROLLER_PROPERTIES where NAME='database.schema.version'
    • If the schema version is 4.0.0.0.0, then enter the following commands:
      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
    • If schema version is 4.5.0.0.0, then enter the following commands:
      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
    • If schema version is 2 or 3, then enter the following commands:
      db2 -td@ -vf upgrade-45CR4-50.sql
db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql

      Disregard any warnings about duplicate foreign keys.

    • If schema version is 7, then enter the following commands:
      db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql
  17. Change to the Profiles SQL scripts directory Wizards/connections.sql/profiles/db2, and then check the database schema version as follows: 
    db2 connect to PEOPLEDB

db2 select DBSCHEMAVER from EMPINST.SNPROF_SCHEMA
    • If schema version is 33, then enter the following commands:
      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
    • If schema version is 36, then enter the following commands:
      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
    • If schema version is 46, then enter the following commands:
      db2 -td@ -vf  appGrants.sql
db2 -td@ -vf clearScheduler.sql
db2 -td@ -vf  reorg.sql
db2 -td@ -vf  runstats.sql
  18. Change to the Wikis SQL scripts directory Wizards/connections.sql/wikis/db2, and then check the database schema version as follows: 
    db2 connect to WIKIS

db2 select SCHEMA_VERSION from "WIKIS"."PRODUCT"
    • If schema version is 79, then enter the following commands:
      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
    • If schema version is 82, then enter the following commands:
      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
    • If schema version is 89, then enter the following commands:
      db2 -td@ -vf upgrade-45CR3-50.sql
db2 -td@ -vf appGrants.sql
db2 -td@ -vf reorg.sql
db2 -td@ -vf updateStats.sql
    • If schema version is 107, then enter the following commands:
      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. For more information, see the Enabling NO FILE SYSTEM CACHING for DB2 on System z topic.