Migrate DB2-based content side-by-side
Transfer DB2-based data from the Connections 4.0/4.5 databases to the new 4.0/4.5 databases as part of migrating to v5.0.
Transfer data from the 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, we can update them to v5.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 v5.0 directly.
We can continue to use the 4.0 or 4.5 databases until we are ready to move to Connections 5.0.
Important: Although we can continue to use the 4.0/4.5 databases until we are ready to move to Connections 5.0, any data that you generate after the database update is not migrated to the new environment.
Run the predbxferxx.sql and postdbxferxx.sql in the 5.0 GA build and not the 4.5 build. Depending on which database schema version we are migrating from, xx might be:
- 4.0
- 4.5
- 4.5CR4
To update the databases:
- Use the Connections 4.5 database wizard, create target 4.5 databases on a separate system from the 4.5 source databases. The new databases host the data for migration to the 5.0 deployment.
If the 4.5 database wizard is not on the system hosting the target databases, copy it from the system hosting IBM Connections 4.5.
- On Windows 2008, perform DB2 administration tasks with full administrator privileges.
- Logged in as the instance owner, open a command prompt and change to the DB2 bin directory. For example: C:\IBM\SQLLIB\BIN.
- Enter the following command: db2cwadmin.bat. This command opens the DB2 command line processor while also setting your DB2 privileges.
- Bring the 4.5 target databases up to the level of the Cumulative Refresh (CR) updates that have been made to the 4.5 source server as described in Update 4.0 and 4.5 DB2 databases manually.
- 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:
- 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 we 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:
Run the predbxferxx.sql commands in the 5.0 GA build and not the 4.5 build, wherexx might be:
- 4.0
- 4.5
- 4.5CR4
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 predbxfer45.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 -tvf 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 -tvf predbxferxx.sql Wikis /connections.sql/wikis/db2 db2 -td@ -vf predbxferxx.sql
- Use the Connections database transfer tool, transfer data to the target databases:
- Create a directory called DBT_HOME on the target database server.
This directory temporarily stores transferred data.
- Use the new version of dbt.jar, and copy it from the connections_root\ConfigEngine\lib directory to the DBT_HOME directory on the target database server.
- IBM Connections does not support GNU Java™.
- Use the JRE under the Wizards directory.
- Update the PATH variable to point to this JRE. For example...
Wizards/jvm/linux/jre
- 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:
- DB2: jdbc:db2://host_IP:port/application_database_name
We can try to increase the speed of the migration process by adapting the URL :
DB2: jdbc:db2://host_IP:port/application_database_name:streamBufferSize=2097152;progressiveStreaming=1;
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
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
- Prepare the JDBC driver of the target databases:
- Use the JDBC driver on the target database server.
- To perform the data transfer, run the dbt.jar file:
Remove the lines for the database systems that are not in the deployment.
- Linux:
"JAVA_HOME/bin/java"
-cp DBT_HOME /dbt.jar:
DB2_HOME/java/db2jcc.jar:
DB2_HOME/java/db2jcc_license_cu.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 install directory.
- Windows:
"JAVA_HOME/bin/java"
-cp DBT_HOME /dbt.jar;
DB2_HOME/java/db2jcc.jar;
DB2_HOME/java/db2jcc_license_cu.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 install directory.
When the transfer is complete, we can restart the 4.5 deployment to minimize service downtime.
Data generated after restarting the 4.5 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.
Run the postdbxferxx.sql commands in the 5.0 GA build and not the 4.5 build where xx might be:
- 4.0
- 4.5
- 4.5CR4
- Enter the appropriate commands for each application, as shown in the following table:
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 clearScheduler.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
db2 -tvf clearScheduler.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
db2 -td@ -vf clearScheduler.sql
Profiles /connections.sql/profiles/db2 db2 -tvf postdbxferxx.sql db2 -tvf clearScheduler.sql
Wikis /connections.sql/wikis/db2 db2 -td@ -vf postdbxferxx.sql db2 -td@ -vf clearScheduler.sql
- (Profiles only.) Update the database sequence for DB2 target databases:
Runs 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;
Runs on the 4.5 target database:
ALTER SEQUENCE EMPINST.CHG_EMP_DRAFT_SEQ RESTART WITH query_result;
ALTER SEQUENCE EMPINST.EMP_DRAFT_SEQ RESTART WITHquery_result;
ALTER SEQUENCE EMPINST.EXT_DRAFT_SEQ RESTART WITHquery_result;
- (Metrics only.) Update the database sequence for DB2 target databases:
Run 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;
Runs 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.
Parent topic:
Migrate data side-by-side