Migrating DB2 content side-by-side

Transfer DB2 data from your IBM® Connections 4.5 or 5.0 databases to the new 4.5 or 5.0 databases as part of migrating to version 5.5.

About this task

Transfer data from your IBM Connections 4.5 or 5.0 databases, described here as the source databases, to the new 4.5 or 5.0 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.5.

Important: You can continue to use your 4.5 or 5.0 databases until you are ready to move to IBM Connections 5.5. 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.5 GA build and not the 4.5 or 5.0 build. Depending on which database schema version you are migrating from, xx might be:

45
45CR4
50
50CR3

To update the databases, complete the following steps:

Procedure

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

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

Note: If the 4.5 or 5.0 database wizard is not on the system that hosts the target databases, copy it from the system that hosts IBM Connections.
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.
Bring the target databases up to the latest Cumulative Refresh (CR) level for the code stream as described in the IBM Connections 5.0 Knowledge Center topic: Updating 4.5 DB2 databases manually .

Prepare the target databases to accept data from the source databases. Remove constraints from the target databases by running the following SQL scripts from the IBM Connections 5.5 package:

Notes:

Run these SQL scripts before you transfer data to 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:

Log in as the instance owner. The default owner on AIX® and Linux™ is db2inst1. On Windows, the default is db2admin.
For each application, change to the directory where the relevant SQL file is stored.
Enter the appropriate commands for each application, as shown in the following table:
Note: Run the predbxferxx.sql commands that are in the 5.5 GA build and not the 4.5 or 5.0 build, wherexx might be:
- 45
- 45CR4
- 50
- 50CR3
Run the predbxfer file that corresponds to your database CR level only. For example, if the target database is at 5.0 CR3, run only the predbxfer50CR3.sql file. Do not run other CR level files (such as 50, cr1, and cr2 files). Also, if it is present do not run predbxfer55.sql.
If you cannot find the required predbxx.sql in the location outline, you can take the predbxx.sql files from the Upgrade-Wizard output

Table 1. DB2 commands for removing constraints
Application	Directory	DB2 commands
Activities	/connections.sql/activities/db2	Run `db2 -tvf predbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -tvf predbxfer45.sql` if you are at 4.5 or a 4.5 CR.
Blogs	/connections.sql/blogs/db2	Run `db2 -td@ -vf predbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf predbxfer45.sql` if you are at 4.5 or a 4.5 CR.
Bookmarks	/connections.sql/dogear/db2	Run `db2 -td@ -vf predbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf predbxfer45.sql` if you are at 4.5 or a 4.5 CR.
Communities	/connections.sql/communities/db2	Run `db2 -td@ -vf predbxfer50CR2.sql` if you are at 5.0 or a 5.0. Run `db2 -td@ -vf predbxfer45.sql` if you are at 4.5 or a 4.5 CR.
Communities - calendar	/connections.sql/communities/db2	Run `db2 -td@ -vf calendar-predbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf calendar-predbxfer45.sql` if you are on 4.5 or a 4.5 CR.
Files	/connections.sql/files/db2	Run `db2 -td@ -vf predbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf predbxfer45CR3.sql` if you are on 4.5 or a 4.5 CR.
Forum	/connections.sql/forum/db2	Run `db2 -tvf predbxfer50CR1.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -tvf predbxfer45.sql` if you are on 4.5 or a 4.5 CR.
Home page	/connections.sql/homepage/db2	Run `db2 -td@ -vf predbxfer50cr4.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf predbxfer45CR4.sql` if you are at 4.5 or a 4.5 CR.
Metrics	/connections.sql/metrics/db2	Run `db2 -td@ -vf predbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf predbxfer45CR2.sql` if you are on 4.5 or a 4.5 CR.
Mobile	/connections.sql/mobile/db2	Run `db2 -td@ -vf predbxfer50.sql` if you are at 5.0 or a 5.0 CR Run `db2 -td@ -vf predbxfer45CR4.sql` if you are on 4.5 or a 4.5 CR.
Profiles	/connections.sql/profiles/db2	Run `db2 -td@ -vf predbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf predbxfer45.sql` if you are on 4.5 or a 4.5 CR.
Wikis	/connections.sql/wikis/db2	Run `db2 -td@ -vf predbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf predbxfer45CR3.sql` if you are on 4.5 or a 4.5 CR.

Note: To tell if constraints were removed check the output from the predbxfer scripts. If the scripts run successfully, the constraints are dropped.

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\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 Directory path conventionsConnections\xkit\migration 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 by 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"/>
<table sourceName="TABLE_TO_SKIP" exclude="true"/> 
</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; the first time 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
    Note: If you use the TDI-related table EMP_UPDATE_TIMESTAMP, it does not need to be transferred. You must add the instruction: <table sourceName="EMP_UPDATE_TIMESTAMP" exclude="true"/>.
  - Wikis: WIKIS
  dbType is the following value:
  - DB2
  The optional entry <table sourceName="TABLE_TO_SKIP" exclude="true"/> instructs dbtransfer to skip a table. This instruction is of limited use as most table content is expected to transfer.
4. Prepare the JDBC driver of the target databases:
  - Use the JDBC driver on the target database server.
5. To transfer the data, 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 you run 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 the following when run as a single command (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 you run 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 the following when run as a single command (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 or 5.0 source deployment to minimize service downtime.
  
  Note: Data that is generated after you restart the 4.5 or 5.0 environment is not migrated.

Reapply constraints to the target databases:

Log in as the instance owner.
For each application, change to the directory where the relevant SQL file is stored.
Note: Run the postdbxferxx.sql commands that are in the 5.0 GA build and not the 4.5 build where xx might be:
- 45
- 45CR4
- 50
- 50CR3
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	Run `db2 -tvf postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -tvf postdbxfer45.sql` if you are on 4.5 or a 4.5 CR. Run `db2 -td@ -vf clearScheduler.sql`
Blogs	/connections.sql/blogs/db2	Run `db2 -td@ -vf postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf postdbxfer45.sql` if you are on 4.5 or a 4.5 CR.
Bookmarks	/connections.sql/dogear/db2	Run `db2 -td@ -vf postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf postdbxfer45.sql` if you are on 4.5 or a 4.5 CR.
Communities	/connections.sql/communities/db2	Run `db2 -td@ -vf postdbxfer50CR2.sql` if you are at 5.0 or a 5.0. Run `db2 -td@ -vf postdbxfer45.sql` if you are at 4.5 or a 4.5 CR.
Communities - Calendar	/connections.sql/communities/db2	Run `db2 -td@ -vf calendar-postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf calendar-postdbxfer45.sql` if you are on 4.5 or a 4.5 CR.
Files	/connections.sql/files/db2	Run `db2 -td@ -vf postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf postdbxfer45CR3.sql` if you are on 4.5 or a 4.5 CR. Run `db2 -td@ -vf clearScheduler.sql`
Forum	/connections.sql/forum/db2	Run `db2 -tvf postdbxfer50CR1.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -tvf postdbxfer45.sql` if you are on 4.5 or a 4.5 CR. Run `db2 -td@ -vf clearScheduler.sql`
Home page	/connections.sql/homepage/db2	Run `db2 -td@ -vf postdbxfer50CR4.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf postdbxfer45CR4.sql` if you are at 4.5 or a 4.5 CR. Run `db2 -td@ -vf clearScheduler.sql`
Metrics	/connections.sql/metrics/db2	Run `db2 -td@ -vf postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf postdbxfer45CR2.sql` if you are on 4.5 or a 4.5 CR. Run `db2 -td@ -vf clearScheduler.sql`
Mobile	/connections.sql/mobile/db2	Run `db2 -td@ -vf postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf postdbxfer45CR4.sql` if you are on 4.5 or a 4.5 CR.
Profiles	/connections.sql/profiles/db2	Run `db2 -td@ -vf postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf postdbxfer45.sql` if you are on 4.5 or a 4.5 CR. Run `db2 -td@ -vf clearScheduler.sql`
Wikis	/connections.sql/wikis/db2	Run `db2 -td@ -vf postdbxfer50.sql` if you are at 5.0 or a 5.0 CR. Run `db2 -td@ -vf postdbxfer45CR3.sql` if you are on 4.5 or a 4.5 CR. Run `db2 -td@ -vf clearScheduler.sql`

(Profiles only.) Run the following commands to update the database sequence for DB2 target databases:
- Run the following commands on the 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;
  
  Note: The results of these queries are the query_result that you use on the target databases in the next step.
  Run the following commands on the 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;
  
  Note: Where query_result is the corresponding result from the previous select statements.
(Metrics only.) Run the following commands to update the database sequence for DB2 target databases:
- Run the following command on the source database:
  SELECT NEXT VALUE FOR METRICS.ID_VALUES AS ID_VALUES_SEQ FROM SYSIBM.SYSDUMMY1;
  SELECT MAX(ID) FROM METRICS.F_TRX_EVENTS;
  
  Note: The results of these queries are the query_result that you use on the target databases in the next step.
- Run the following commands on the target database:
  ALTER SEQUENCE METRICS.ID_VALUES RESTART WITH query_result;
  ALTER TABLE METRICS.F_TRX_EVENTS ALTER COLUMN ID RESTART WITH query_result;
  
  Note: Where query_result is the corresponding result from the previous select statements.
Manually, or using the DB Wizard, update the new databases to bring them to the IBM Connections version 5.5 level. For more information, see Updating 4.5 and 5.0 databases to 5.5 and Updating 4.5 and 5.0 databases with the wizard.

Results

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