Migrating DB2-based content side-by-side

Transfer DB2-based data from your IBM® Connections 4.0/4.5 databases to the new 4.0/4.5 databases as part of migrating to version 5.0.

About this task

Transfer data from your IBM Connections 4.0/4.5 databases, described here as the source databases, to the new 4.0/4.5 databases, described here as the target databases. When the data transfer is complete and you validate the new databases, you can update them to version 5.0. References to 4.5 throughout this topic also include 4.0 since Connections 5.0 supports migrating any version from 4.0 or 4.5 to version 5.0 directly.
Important: Although you can continue to use your 4.0/4.5 databases until you are ready to move to IBM Connections 5.0, any data that you generate after the database update is not migrated to the new environment.
Note: Run the predbxferxx.sql and postdbxferxx.sql that are in the 5.0 GA build and not the 4.5 build. Depending on which database schema version you are migrating from, xx might be:
  • 40
  • 45
  • 45CR4

To update the databases, complete the following steps:

Procedure

  1. Using the IBM Connections 4.5 database wizard, create target 4.5 databases on a separate system from your 4.5 source databases. The new databases host your data for migration to the 5.0 deployment.

    The procedure is described in the IBM Connections 4.5 Knowledge Center topic:Creating databases with the database wizard.

    Note: If the 4.5 database wizard is not on the system that hosts the target databases, copy it from the system hosting IBM Connections 4.5.
  2. On Windows 2008 and 2012, 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.
  3. Bring the 4.5 target databases up to the latest Cumulative Refresh (CR) level for the 4.5 code stream as described in the IBM Connections 4.5 Knowledge Center topic:Updating 4.0 DB2 databases manually .
  4. Prepare the target 4.5 databases to accept data from the source 4.5 databases. Remove constraints from the target databases by executing the following SQL scripts from the IBM Connections 5.0 package:
    Notes:
    • Run these SQL scripts before transferring data to the target database.
    • Run each script from the same directory that you use to create the target database.
    • IBM Connections uses the following database libraries, which are already on the target database server:
      • db2jcc.jar
      • db2jcc_license_cu.jar
    Repeat the following procedures for each application that you are migrating:
    1. Log in as the instance owner. The default owner on AIX® and Linux is db2inst1. On Windows, the default is db2admin.
    2. For each application, change to the directory where the relevant SQL file is stored.
    3. Enter the appropriate commands for each application, as shown in the following table:
      Note: Run the predbxferxx.sql commands that are in the 5.0 GA build and not the 4.5 build, wherexx might be:
      • 40
      • 45
      • 45CR4
    Table 1. DB2 commands for removing constraints
    Application Directory DB2 commands
    Activities /connections.sql/activities/db2 db2 -tvf predbxferxx.sql
    Blogs /connections.sql/blogs/db2 db2 -td@ -vf predbxferxx.sql
    Bookmarks /connections.sql/dogear/db2

    db2 -td@ -vf predbxferxx.sql
    Communities /connections.sql/communities/db2 db2 -td@ -vf predbxferxx.sql

    db2 -td@ -vf calendar-predbxferxx.sql
    Files /connections.sql/files/db2 db2 -td@ -vf predbxferxx.sql
    Forum /connections.sql/forum/db2 db2 -tvf predbxferxx.sql
    Home page /connections.sql/homepage/db2 db2 -tvf predbxferxx.sql
    Metrics /connections.sql/metrics/db2 db2 -td@ -vf predbxferxx.sql

    If the Metrics database version is between IC4.0 and IC4.0CR2，use predbxfer40.sql.

    If the Metrics database version is between IC4.0CR3 and IC4.5CR1，use predbxfer45.sql.

    If the Metrics database version is IC4.5CR2 or later，use predbxfer45CR2.sql.
    Mobile /connections.sql/mobile/db2 db2 -td@ -vf predbxferxx.sql
    Profiles /connections.sql/profiles/db2 db2 -td@ -vf predbxferxx.sql
    Wikis /connections.sql/wikis/db2 db2 -td@ -vf predbxferxx.sql
  5. Using the IBM Connections database transfer tool, transfer data to the target databases:
    1. Create a directory that is called DBT_HOME on the target database server in the following location: IBM\connections\IBM\Connections\ConfigEngine\lib. This directory temporarily stores transferred data.
    2. Be sure to use the new version of the dbt.jar file and copy it from the connections_root\ConfigEngine\lib directory to the DBT_HOME directory on the target database server.
      Notes:
      • IBM Connections does not support GNU Java.
      • 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.
    3. Create an XML configuration file under the DBT_HOME directory and add the following content: 
      <dbTransfer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database role="source" 
driver="JDBC_driver" 
url="JDBC_url" 
userId="database_admin
schema="application_db_schema_name"
dbType="dbType"/>
<database role="target"
driver="JDBC_driver"
url="JDBC_url"
userId="database_admin" 
schema="application_db_schema_name" 
dbType="dbType"/>
</dbTransfer>

      where

      JDBC_driver is the following type:
      • com.ibm.db2.jcc.DB2Driver
      JDBC_url is one of the following types:
      • jdbc:db2://host_IP:port/application_database_name
        Note: You can try to increase the speed of the migration process by adapting the URL as follows:

        jdbc:db2://host_IP:port/application_database_name:streamBufferSize=2097152;progressiveStreaming=1;

      Note: where
      • host_IP is the IP address of the database server.
      • port is the port number of the server.
      • application_database_name is one of the following values:
        • Activities: OPNACT
        • Blogs: BLOGS
        • Communities: SNCOMM
        • Dogear: DOGEAR
        • Files: FILES
        • Forum: FORUM
        • Home page: HOMEPAGE
        • Metrics: METRICS
        • Mobile: MOBILE
        • Profiles: PEOPLEDB
        • Wikis: WIKIS

      database_admin is the user ID of the database administrator.

      application_db_schema_name is one of the following values:
      • Activities: ACTIVITIES
      • Blogs: BLOGS
      • Communities: SNCOMM and CALENDAR
        Note: To migrate Communities data, the dbt command needs to be run twice; once for the SNCOMM schema, and the second time for the CALENDAR schema.
      • Dogear: DOGEAR
      • Files: FILES
      • Forum: FORUM
      • Home page: HOMEPAGE
      • Metrics: METRICS
      • Mobile: MOBILE
      • Profiles: EMPINST
      • Wikis: WIKIS
      dbType is the following value:
      • DB2
    4. Prepare the JDBC driver of the target databases:
      • Use the JDBC driver on the target database server.
    5. To perform the data transfer, run the dbt.jar file, entering the command on one line. Do not add spaces in a series of file names:
      • Linux:

        "JAVA_HOME/bin/java"

        -cp DBT_HOME/dbt.jar:

        DB2_HOME/java/db2jcc.jar

        Where DB2_HOME is IBM\connections\IBM\SQLLIB\java\

        com.ibm.wps.config.db.transfer.CmdLineTransfer

        -logDir DBT_HOME/logs

        -xmlfile DBT_HOME/dbt_config_file_name

        -sourcepassword source_db_password

        -targetpassword target_db_password

        where
        • JAVA_HOME is the path to the Java JDK:

          opt/IBM/WebSphere/AppServer/java/jre

          Or:

          Wizards\jvm\win\jre\bin\java
        • dbt_config_file_name is the name of the XML configuration file you created for the dbt.jar file
        • logs is the directory where log files are stored. Create the logs directory before running this file
        • DB2_HOME is the path to the DB2 installation directory.
        Run the command on a single line; do not add extra spaces. For example, multiple file names are entered as filename.jar:filename.jar without a space between the file names. The command looks similar to this when run as a single command line (edited to your specific configuration):
        JAVA_HOME/bin/java -cp DBT_HOME/dbt.jar:DB2_HOME/java/db2jcc.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir DBT_HOME/logs -xmlfile DBT_HOME/dbt_config_file_name -sourcepassword password -targetpassword password
      • Windows:

        JAVA_HOME\bin\java

        -cp DBT_HOME\dbt.jar;

        DB2_HOME\java\db2jcc.jar

        com.ibm.wps.config.db.transfer.CmdLineTransfer

        -logDir DBT_HOME\logs

        -xmlfile DBT_HOME/dbt_config_file_name

        -sourcepassword source_db_password

        -targetpassword target_db_password

        where
        • JAVA_HOME is the path to the Java JDK.
        • dbt_config_file_name is the name of the XML configuration file you created for the dbt.jar file
        • logs is the directory where log files are stored. Create the logs directory before running this file
        • DB2_HOME is the path to the DB2 installation directory.
        Run the command on a single line; do not add extra spaces. For example, multiple file names are entered as filename.jar;filename.jar without a space between the file names. F The command looks similar to this when run as a single command line (edited to your specific configuration):
        JAVA_HOME\bin\java -cp c:\DBT_HOME\dbt.jar;DB2_HOME\db2jcc.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir c:\DBT_HOME\logs -xmlfile c:\DBT_HOME\dbt_config_file_name -sourcepassword password -targetpassword password

      When the transfer is complete, you can restart your 4.5 deployment to minimize service downtime.

      Note: Data that is generated after restarting the 4.5 environment is not migrated.
  6. Reapply constraints to the target databases:
    1. Log in as the instance owner.
    2. For each application, change to the directory where the relevant SQL file is stored.
    3. Note: Run the postdbxferxx.sql commands that are in the 5.0 GA build and not the 4.5 build where xx might be:
      • 40
      • 45
      • 45CR4
    4. Enter the appropriate commands for each application, as shown in the following table:
    Table 2. DB2 commands for reapplying constraints
    Application Directory DB2 commands
    Activities /connections.sql/activities/db2

    db2 -tvf postdbxferxx.sql

    db2 -td@ -vf clearScheduler.sql
    Blogs /connections.sql/blogs/db2 db2 -td@ -vf postdbxferxx.sql
    Bookmarks /connections.sql/dogear/db2 db2 -td@ -vf postdbxferxx.sql
    Communities /connections.sql/communities/db2

    db2 -td@ -vf postdbxferxx.sql

    db2 -td@ -vf calendar-postdbxferxx.sql

    Files /connections.sql/files/db2 db2 -td@ -vf postdbxferxx.sql

    db2 -td@ -vf clearScheduler.sql
    Forum /connections.sql/forum/db2

    db2 -tvf postdbxferxx.sql

    db2 -td@ -vf clearScheduler.sql
    Home page /connections.sql/homepage/db2

    db2 -tvf postdbxferxx.sql
    Metrics /connections.sql/metrics/db2

    db2 -td@ -vf postdbxferxx.sql

    db2 -td@ -vf clearScheduler.sql

    If the Metrics database version is between IC4.0 and IC4.0CR2，use postdbxfer40.sql.

    If the Metrics database version is between IC4.0CR3 and IC4.5CR1，use postdbxfer45.sql.

    If the Metrics database version is IC4.5CR2 or later，use postdbxfer45CR2.sql.
    Mobile /connections.sql/mobile/db2

    db2 -td@ -vf postdbxferxx.sql
    Profiles /connections.sql/profiles/db2 db2 -td@ -vf postdbxferxx.sql

    db2 -td@ -vf clearScheduler.sql
    Wikis /connections.sql/wikis/db2 db2 -td@ -vf postdbxferxx.sql

    db2 -td@ -vf clearScheduler.sql
  7. (Profiles only.) Run the following commands to update the database sequence for DB2 target databases:

    • Run the following commands on the 4.5 source database:

      SELECT NEXT VALUE FOR EMPINST.CHG_EMP_DRAFT_SEQ AS CHG_EMP_DRAFT_SEQ FROM SYSIBM.SYSDUMMY1;

      SELECT NEXT VALUE FOR EMPINST.EMP_DRAFT_SEQ AS EMP_DRAFT_SEQ FROM SYSIBM.SYSDUMMY1;

      SELECT NEXT VALUE FOR EMPINST.EXT_DRAFT_SEQ AS EXT_DRAFT_SEQ FROM SYSIBM.SYSDUMMY1;

      Run the following commands on the 4.5 target database:

      ALTER SEQUENCE EMPINST.CHG_EMP_DRAFT_SEQ RESTART WITH query_result;

      ALTER SEQUENCE EMPINST.EMP_DRAFT_SEQ RESTART WITH query_result;

      ALTER SEQUENCE EMPINST.EXT_DRAFT_SEQ RESTART WITH query_result;

  8. (Metrics only.) Run the following commands to update the database sequence for DB2 target databases:

    • Run the following command on the 4.5 source database:

      SELECT NEXT VALUE FOR METRICS.ID_VALUES AS ID_VALUES_SEQ FROM SYSIBM.SYSDUMMY1;

      SELECT MAX(ID) FROM METRICS.F_TRX_EVENTS;

    • Run the following commands on the 4.5 target database:

      ALTER SEQUENCE METRICS.ID_VALUES RESTART WITH query_result;

      where query_result is the result of the corresponding SELECT command that you ran on the 4.5 database.

      ALTER TABLE METRICS.F_TRX_EVENTS ALTER COLUMN ID RESTART WITH query_result;

      where query_result is the result of the corresponding SELECT command that you ran on the 4.5 database.

  9. You have now moved the database to a new instance to prepare it for migration. Perform the database upgrade, for more information, see Updating 4.0 and 4.5 DB2 databases manually .

Results

Check that all the databases are working correctly. If you find errors, resolve the problem and repeat this task.