Migrating DB2 content side by side
Transfer DB2 data from your IBM Connections™ 5.0 or 5.5 databases to the new 5.0 or 5.5 databases as part of migrating to version 6.0.
About this task
- 50
- 50CRx
- 55
- 55CRx
To update the databases, complete the following steps:
Procedure
- Using the Connections 5.0 or 5.5
database wizard, create target 5.0 or 5.5 databases on a separate system from your source databases.
The new databases host your data for migration to the 6.0 deployment.
The procedure is described in the IBM Knowledge Center: Creating databases with the database wizard.
Note: If the 5.0 or 5.5 database wizard is not on the system that hosts the target databases, copy it from the system that hosts Connections. - On Windows 2008
and 2012, you must perform DB2® administration
tasks with full administrator privileges.
- Logged in as the instance owner, open a command line and change to the DB2 bin directory. For example: C:\Program Files\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 target databases up to the latest Cumulative Refresh (CR) level for the code stream as described in Updating 5.0 and 5.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 6.0 package: Notes:Repeat the following procedures for each application that you are migrating:
- 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:
- db2jcc4.jar
- db2jcc_license_cu.jar
- 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 6.0 GA build and not the 5.0 or 5.5 build, wherexx might be:
- 50
- 50CRx
- 55
- 55CRx
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 and Directory DB2 commands Activities: /connections.sql/activities/db2 If you are at: - 5.0 or a 5.0 CR run db2 -tvf predbxfer50.sql
- 5.5 or a 5.5 CR run db2 -tvf predbxfer55.sql
Blogs:
/connections.sql/blogs/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf predbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf predbxfer55.sql
Bookmarks:
/connections.sql/dogear/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf predbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf predbxfer55.sql
Communities:
/connections.sql/communities/db2If you are at: - 5.0 or 5.0 CR1 run db2 -td@ -vf predbxfer50.sql
- 5.0 CR2 or later CR run db2 -td@ -vf predbxfer50CR2.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf predbxfer55.sql
Communities-calendar:
/connections.sql/communities/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf calendar-predbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf calendar-predbxfer55.sql
Files:
/connections.sql/files/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf predbxfer50.sql
- 5.5 or 5.5 CR1 run db2 -td@ -vf predbxfer55.sql
- 5.5 CR2 or later run db2 -td@ -vf predbxfer55CR2.sql
Forums:
/connections.sql/forum/db2If you are at: - 5.0 run db2 -tvf predbxfer50.sql
- 5.0 CR1 or later run db2 -tvf predbxfer50CR1.sql
- 5.5 or a 5.5 CR run db2 -tvf predbxfer55.sql
Home page:
/connections.sql/homepage/db2If you are at: - 5.0 run db2 -td@ -vf predbxfer50.sql
- 5.0 CR1 run db2 -td@ -vf predbxfer50CR1.sql
- 5.0 CR2 run db2 -td@ -vf predbxfer50CR2.sql
- 5.0 CR3 run db2 -td@ -vf predbxfer50CR3.sql
- 5.0 CR4 run db2 -td@ -vf predbxfer50CR4.sql
- 5.5 run db2 -td@ -vf predbxfer55.sql
- 5.5 CR1 run db2 -td@ -vf predbxfer55CR1.sql
- 5.5 CR2 run db2 -td@ -vf predbxfer55CR2.sql
Metrics:
/connections.sql/metrics/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf predbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf predbxfer55.sql
Mobile:
/connections.sql/mobile/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf predbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf predbxfer55.sql
Profiles:
/connections.sql/profiles/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf predbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf predbxfer55.sql
Wikis:
/connections.sql/wikis/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf predbxfer50.sql
- 5.5 or 5.5 CR1 run db2 -td@ -vf predbxfer55.sql
- 5.5 CR2 run db2 -td@ -vf predbxfer55CR2.sql
Push notification:
/connections.sql/pushnotification/db2If you are at 5.5 or a 5.5 CR, run db2 -td@ -vf predbxfer55.sql Note: Check that constraints were removed by checking 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:
- 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.
- 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:
- You must ensure the server is installed so that the dbt.jar file is in place before migrating the database.
- 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.
- 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"/> <!-- optional argument used to have dbtransfer skip a table --> </dbTransfer>
Note: Any <table sourceName="TABLE_TO_SKIP" exclude="true entries go directly before </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_nameNote: 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;
When progressive streaming is enabled, you can control when the JDBC driver materializes LOBs with the streamBufferSize property. If a LOB or XML object is less than or equal to the streamBufferSize value, the object is materialized. For example, if the propose value is set to 2097152 KB – this might even be to small for this migration worked fine and all data transfered without any problem. Materialization of LOB data may not only be a performance gain but also a way of handling data within LOBS.
Increasing the streamBufferSize during migration improved performance optimization by activating materialization of the CLOB objects. Refer to IBM Connections content missing after migration for additional information.
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
- Bookmarks: DOGEAR
- Files: FILES
- Forum: FORUM
- Home page: HOMEPAGE
- Metrics: METRICS
- Mobile: MOBILE
- Profiles: PEOPLEDB
- Wikis: WIKIS
- Push notification: PNSNote: For Connections 5 the push notification database name is FILES, for Connections 6 the push notification database name is PNS.
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 CALENDARNote: To migrate Communities data, run the dbt command twice; the first time for the SNCOMM schema, and the second time for the CALENDAR schema.
- Bookmarks: DOGEAR
- Files: FILES
- Forum: FORUM
- Home page: HOMEPAGE
- Metrics: METRICS
- Mobile: MOBILE
- Profiles: EMPINSTNote: 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
- Push notification: PNS
dbType is the following value:- DB2
The optional entry <table sourceName="TABLE_TO_SKIP" exclude="true"/> instructs dbtransfer to skip a named table.Note: take the following into consideration when using "TABLE_TO_SKIP"- Skip those tables that transferred fully.
- If you get an error when transferring a table, then clear the table's contents on the target, do not skip it and run dbTransfer again.
- If you need to re-run dbTransfer, the tables may execute in a different order.
- Prepare the JDBC driver of the target databases:
- Use the JDBC driver on the target database server.
- 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/db2jcc4.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
- 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.
JAVA_HOME/bin/java -cp DBT_HOME/dbt.jar:DB2_HOME/java/db2jcc4.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\db2jcc4.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. 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.
JAVA_HOME\bin\java -cp c:\DBT_HOME\dbt.jar;DB2_HOME\db2jcc4.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 5.0 or 5.5 source deployment to minimize service downtime.
Note: Data that is generated after you restart the 5.0 or 5.5 environment is not migrated. - Linux:
- 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 6.0 GA build and not the 5.0 or 5.5 build where xx might be:
- 50
- 50CRx
- 55
- 55CRx
- Enter the appropriate commands for each application, as shown in the following table:
Table 2. DB2 commands for reapplying constraints Application and Directory DB2 commands Activities:
/connections.sql/activities/db2If you are at: - 5.0 or a 5.0 CR run db2 -tvf postdbxfer50.sql
- 5.5 or a 5.5 CR run db2 -tvf postdbxfer55.sql
- Run db2 -tvf clearScheduler.sql
Blogs:
/connections.sql/blogs/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf postdbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf postdbxfer55.sql
Bookmarks:
/connections.sql/dogear/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf postdbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf postdbxfer55.sql
Communities:
/connections.sql/communities/db2If you are at: - 5.0 or 5.0 CR1 run db2 -td@ -vf postdbxfer50.sql
- 5.0 CR2 or a later CR run db2 -td@ -vf postdbxfer50CR2.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf postdbxfer55.sql
- Run db2 -td@ -vf clearScheduler.sql
Communities - calendar:
/connections.sql/communities/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf calendar-postdbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf calendar-postdbxfer55.sql
Files:
/connections.sql/files/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf postdbxfer50.sql
- 5.5 or 5.5 CR1 run db2 -td@ -vf postdbxfer55.sql
- 5.5 CR2 or later run db2 -td@ -vf postdbxfer55CR2.sql
- Run db2 -td@ -vf clearScheduler.sql
Forums:
/connections.sql/forum/db2If you are at: - 5.0 run db2 -tvf postdbxfer50.sql
- 5.0 CR1 or later run db2 -tvf postdbxfer50CR1.sql
- 5.5 or a 5.5 CR run db2 -tvf postdbxfer55.sql
- Run db2 -tvf clearScheduler.sql
Homepage:
/connections.sql/homepage/db2If you are at: - 5.0 run db2 -td@ -vf predbxfer50.sql
- 5.0 CR1 run db2 -td@ -vf postdbxfer50CR1.sql
- 5.0 CR2 run db2 -td@ -vf postdbxfer50CR2.sql
- 5.0 CR3 run db2 -td@ -vf postdbxfer50CR3.sql
- 5.0 CR4 run db2 -td@ -vf postdbxfer50CR4.sql
- 5.5 run db2 -td@ -vf postdbxfer55.sql
- 5.5 CR1 run db2 -td@ -vf postdbxfer55CR1.sql
- 5.5 CR2 run db2 -td@ -vf postdbxfer55CR2.sql
- Run db2 -td@ -vf clearScheduler.sql
Metrics:
/connections.sql/metrics/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf postdbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf postdbxfer55.sql
- Run db2 -td@ -vf clearScheduler.sql
Mobile:
/connections.sql/mobile/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf postdbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf postdbxfer55.sql
Profiles:
/connections.sql/profiles/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf postdbxfer50.sql
- 5.5 or a 5.5 CR run db2 -td@ -vf postdbxfer55.sql
- Run db2 -td@ -vf clearScheduler.sql
Wikis:
/connections.sql/wikis/db2If you are at: - 5.0 or a 5.0 CR run db2 -td@ -vf postdbxfer50.sql
- 5.5 or 5.5 CR1 run db2 -td@ -vf postdbxfer55.sql
- 5.5 CR2 run db2 -td@ -vf postdbxfer55CR2.sql
- Run db2 -td@ -vf clearScheduler.sql
Push notification:
/connections.sql/pushnotification/db2If you are at: - 5.5 or a 5.5 CR, run db2 -td@ -vf postdbxfer55.sql
- 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 6.0 level. For more information, see Migrating 5.0 and 5.5 databases to 6.0 and Updating 5.0 and 5.5 databases with the wizard.
Results
Check that all the databases are working correctly. If you find errors, resolve the problem and repeat this task.