ShowTable of Contents
Introduction
The IBM® Connections 4.0 release supports upgrading from the Connections 3.0.1 previous major release as is expected. There are three parts to the upgrade process:
- database migration and upgrade
- content data migration
- application configuration migration
However, this article focuses only on database migration. The associated tools to support database migration are the v3.0.1 database transfer scripts, the database transfer tool, the 3.0.1-to-v4.0 database upgrade scripts and the migrate .jar, and the unified database wizard (both GUI and silent) that support database create, delete, and upgrade.
In the
Connections product document wiki, database upgrade is divided into two types, in-place and side-by-side. With an in-place upgrade, you perform the 3.0.1-to-4.0 database upgrade on the original 3.0.1 database instance. The side-by-side upgrade has two steps:
(1) Make a 3.0.1 database copy of the original database, and then transfer all the necessary application configuration and data of the original to the copy.
(2) Perform a 3.0.1-to-4.0 database upgrade on the copy.
So, we can see that the side-by-side upgrade has one more database transfer process than the in-place upgrade. An advantage of the side-by-side upgrade is that, by not removing the original 3.0.1 database, it can still be run until the new 4.0 system setup is complete.
The database transfer scripts and tools in the 4.0 install package help you transfer data between the original and copied 3.0.1 database, and database upgrade scripts and migrate .jar are provided to do in-place upgrade. Looking at the real product ISO package, we find that most of them are placed in the Wizard directory, which is "DBWizard" (see figure 1).
The executable of DBWizard is databaseWizard.bat/sh. The database transfer and upgrade SQL scripts are placed under the "connections.sql" or "connections.s390.sql" (zLinux® specific) directory
Figure 1. DBWizard directory
The first level of subfolders is organized by applications, and the next level is orgainized by database type: db2, oracle, and sqlserver (see figure 2). "predbxfer301.sql" and "postdbxfer301.sql are the database transfer SQL scripts for the 3.0.1-to-4.0 upgrade, and "upgrade-301-40.sql" and "appGrants.sql" are the typical database upgrade SQL scripts.
Figure 2. Subfolders
There are some extra scripts for special use of some applications, for instance, "calendar-createDb.sql" and "calendar-appGrants.sql" for Communities. You can refer to the detailed list on the 4.0 product documentation topic, "
Updating 3.0.1 databases manually."
There is also a requisite .jar file for database transfer, dbt.jar, which is placed under an installed Connections product directory, such as C:\Program Files(x86)\IBM\Connections\ConfigEngine\lib\dbt.jar on Microsoft® Windows® 2008.
In this article, we introduce how to verify database upgrade results, include some details for preparing your own database transfer script, share some common issues and troubleshooting tips, and provide advice to avoid database upgrade problems in advance.
The emphasis of this article are functional issues and common usages, and the environment topology is a single database instance that is configured with default settings. For the database upgrade cases in more complex environments such as a database cluster, refer to other troubleshooting resources from systems anb/or performance teams.
Verify results
The install package provides only database transfer scripts and dbt.jar, so you must consolidate the batch script yourself. The database transfer process includes three parts: pre-database-transfer SQL scripts, database transfer using dbt.jar, and post-database- transfer SQL scripts.
Dbt.jar is the main part, and pre- and post- SQL scripts handle constraints like foreign keys to ensure the transfer process goes smoothly. For a detailed command list, refer to the product documentation topic, "
Migrating 3.0.1 data side-by-side."
The dbt.jar requires an XML configuration file for each application, containing both source and target 3.0.1 database connection information, as shown in listing 1.
Listing 1. XML config file sample
<dbTransfer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database role="source" driver="com.ibm.db2.jcc.DB2Driver" url="jdbc:db2://9.119.44.149:50000/OPNACT" userId="LCUSER" schema="ACTIVITIES" dbType="db2"/>
<database role="target" driver="com.ibm.db2.jcc.DB2Driver" url="jdbc:db2://9.186.12.145:50000/OPNACT" userId="LCUSER" schema="ACTIVITIES" dbType="db2"/>
</dbTransfer>
If we assume the above XML configuration file is named "dbt_release_A.xml", then the db transfer command is as follows:
java -cp %dbxfer_HOME%\lib\dbt.jar;%dbxfer_HOME%\jdbc\db2jcc.jar;%dbxfer_HOME%\jdbc\db2jcc_license_cu.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir %dbxfer_HOME%\logs -xmlfile %dbxfer_HOME%\DB2\dbt_release_A.xml -sourcepassword %SRC_PASSWORD% -targetpassword %DEST_PASSWORD%
where "%dbxfer_HOME%" is the home directory of the user-made db transfer tool, including dbt.jar under "%dbxfer_HOME%\lib", jdbc drivers under "%dbxfer_HOME%\jdbc", XML configuration file under %dbxfer_HOME%\DB2, as well as the logs under "%dbxfer_HOME%\logs". The main class is "com.ibm.wps.config.db.transfer.CmdLineTransfer". After running the tool, be sure to verify the result.
Database transfer result
In the above command, though we specify the log parameter, it is for dbt.jar command log only. For the pre- and post-database transfer SQL scripts, we still must redirect all the results to a log file. On Windows, the command is
dbxfer.bat > transfer.log 2>&1
In the batch execute script, you can add some printing messages to make the log file more readable. The SQL script results are standard results according to the specific database system type. For instance, IBM DB2® error messages usually start with "SQLSTATE=". Below is a database transfer tool log:
[07/03/12 18:58:54.374 PDT] Attempting to make connection using: jdbc:sqlserver://9.186.12.147:1433;databaseName=OPNACT :: OAUSER :: PASSWORD_REMOVED
[07/03/12 18:58:55.878 PDT] Connection successfully made
[07/03/12 18:58:55.879 PDT] Attempting to make connection using: jdbc:sqlserver://9.119.44.153:1433;databaseName=OPNACT :: OAUSER :: PASSWORD_REMOVED
[07/03/12 18:58:55.917 PDT] Connection successfully made
[07/03/12 18:58:55.922 PDT] Transfer started
[07/03/12 18:59:00.916 PDT] Transferring table --{ ACTIVITIES.OA_ACLENTRY}-- to table --{ACTIVITIES.OA_ACLENTRY }--
[07/03/12 18:59:03.543 PDT] Transferring table --{ ACTIVITIES.OA_MEMBERPROFILE}-- to table --{ACTIVITIES.OA_MEMBERPROFILE }--
...
[07/03/12 18:59:26.437 PDT] Transferring table --{ ACTIVITIES.OA_NODEMEMBER}-- to table --{ACTIVITIES.OA_NODEMEMBER }--
[07/03/12 18:59:26.743 PDT] Transferring table --{ ACTIVITIES.OA_TREE}-- to table --{ACTIVITIES.OA_TREE }--
[07/03/12 18:59:29.129 PDT] Transferring table --{ ACTIVITIES.OA_EVENTLOG}-- to table --{ACTIVITIES.OA_EVENTLOG }--
[07/03/12 18:59:33.820 PDT] Transfer finished
[INFO] Activities finished ...
The above log is a successful transfer log of the Activities application and includes initial database connecting information and data transfer records, table by table. The log is rather clean, without errors and exceptions, but once something goes wrong, an obvious JavaTM Exception will appear in the log, and the transfer process of this application will be stopped.
Database upgrade result
For in-place database upgrades, you have the option to use DBWizard or to do it manually. If you choose to upgrade manually, you also control the log files as with database transfer. Though you have more flexibilty with the manual method, DBWizard has a useful parameter validation and handles some special cross-application functions. If you choose to upgrade manually, read the product documentation carefully for these special steps. For Connections 4.0, these types of requirements have been increased.
For DBWizard, you can see the results in the result window, including the success or failure of each application and all the log files. Each SQL script has a separate log file, and any application that has a Java migrate process also has a log file.
On Windows, DBWizard provides the Log link button, but on Linux, you must access the log files yourself. The paths are like:
- Windows 2003: C:\Documents and Settings\Administrator\lcwizard\log\dbwizard
- Windows 2008: C:\Users\Administrator\lcWizard\log\dbwizard
- Linux / AIX: /home/db2inst1/lcwizard/log/dbwizard
The log file list is as shown in figure 3, which lists parts of the applications. In particular, *_activities_migrate.log is the log file of the activities migrate .jar, meaning that you cannot find the corresponding SQL script.
Figure 3. Log file list
Besides log files, you can also verify the upgrade result by checking data integrity, verifying the database table directly or verifying the application data on Web pages.
Common issues and troubleshooting
In this section, we provide examples of how to troubleshoot and resolve errors with db upgrade. The error messages are gathered from actual product development and test processes. As above, we divide the content into two parts: db transfer and db upgrade.
Database transfer
Now let's point out some tips to help you avoid unexpected issues:
- Above we noted where to get the pre/postdbxfer301.sql from the 4.0 install package; however, more clarification is needed. Specifically, in the 4.0 install package, there are also pre/postdbxfer40.sql, but do not use them; they are used for db transfer in a future product release.
- Also, you will find that not all the applications have pre/postdbxfer301.sql, because either some applications don't have dedicated databases or they are new 4.0 applications and don't have the requirement to transfer.
- You may find there are pre/postdbxfer301.sql in the 3.0.1 install package but, again, do not use them; those scripts are not up to date for a 3.0.1-to-4.0 db upgrade.
Next, let's discuss a few specific common issues.
db transfer tool fails to run
The db transfer tool (dbt.jar) requires many parameters, such as source and target database URLs, ports, db types, as well as the JDBC driver .jar, and JRE. Any error in these parameters can lead to a failure. You can find threads in these parameters when the error message is returned, for example:
Exception in thread "main" java.lang.NoClassDefFoundError: sqlserver\lib\dbt.jar;C:\Docume~1\Administrator\Desktop\dbxfer
Caused by: java.lang.ClassNotFoundException: sqlserver\lib\dbt.jar;C:\Docume~1\Administrator\Desktop\dbxfer
at java.net.URLClassLoader.findClass(URLClassLoader.java:432)
at java.lang.ClassLoader.loadClass(ClassLoader.java:643)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:311)
at java.lang.ClassLoader.loadClass(ClassLoader.java:609)
This error message comes from the dbt.jar Activities command, and the same error message appears in other application logs. It should be one of certain global parameter in the db transfer command or XML configuration file.
We can determine that the home directory is C:\Users\Administrator\Desktop\dbxfer4 sqlserver, and the above message shows "sqlserver\lib\dbt.jar", rather than "dbxfer4 sqlserver\lib\dbt.jar." So this is a space character issue, which is a common Java issue.
db transfer error logs
The following is a typical data transfer error:
[11/12/12 23:02:36.094 PST] Transferring table --{ ACTIVITIES.OA_SCHEMA}-- to table --{ACTIVITIES.OA_SCHEMA }--
[11/12/12 23:02:36.161 PST] Transferring table --{ ACTIVITIES.OA_SCHEDULERTASK}-- to table --{ACTIVITIES.OA_SCHEDULERTASK }--
[11/12/12 23:02:36.916 PST] Transferring table --{ ACTIVITIES.OA_EVENTLOG}-- to table --{ACTIVITIES.OA_EVENTLOG }--
[11/12/12 23:02:42.640 PST] Transferring table --{ ACTIVITIES.OA_SCHEDULERLMPR}-- to table --{ACTIVITIES.OA_SCHEDULERLMPR }--
[11/12/12 23:02:42.664 PST] Transferring table --{ ACTIVITIES.OA_SCHEDULERLMGR}-- to table --{ACTIVITIES.OA_SCHEDULERLMGR }--
[11/12/12 23:02:42.792 PST] error.executing.transfer
err.dbtransfer.exception.labelclass java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'ACTIVITIESLMGR_PK'. Cannot insert duplicate key in object 'ACTIVITIES.OA_SCHEDULERLMGR'.
java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'ACTIVITIESLMGR_PK'. Cannot insert duplicate key in object 'ACTIVITIES.OA_SCHEDULERLMGR'.
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1132)
at com.ibm.wps.config.db.transfer.DefaultTableHandler.execute(DefaultTableHandler.java:145)
at com.ibm.wps.config.db.transfer.TransferEngine.execute(TransferEngine.java:70)
at com.ibm.wps.config.db.transfer.CmdLineTransfer.execute(CmdLineTransfer.java:96)
at com.ibm.wps.config.db.transfer.CmdLineTransfer.main(CmdLineTransfer.java:43)
[INFO] Activities finished …
According to above log, the Java exception is caused by a duplicate primary key. The db transfer process is interrupted, and the other Activities tables will not be transferred; however, the other applications aren't impacted.
When the error occurs you cannot resume the db transfer process easily. Since the table already has some transferred data, you should restore the original 3.0.1 database copy from backup, and then rerun the db transfer process.
db transfer process hangs
The duration of the db transfer process depends on the data set size of your database. It may take some hours, and you should prepare your own batch script as mentioned above. The quality of the batch script is critical to the db upgrade. Not only can the batch scripts cause problems, but also sometimes the SQL scripts can bring unexpected issues.
In figure 4 the cursor has been hanging for a long time, and we need to check the log file.
Figure 4. Transfer process hanging
We found the last lines of log file as follows:
SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 12 21:03:50 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
1 row deleted.
Commit complete.
1 row deleted.
Commit complete.
1 row deleted.
Commit complete.
0 rows deleted.
Commit complete.
SQL>
Note the command promptat the end. According to the other print log messages, we know that Dogear postdbxfer301.sql has been executed, and this block is processing Profiles postdbxfer301.sql and clearScheduler.sql. If we check clearSheduler.sql, we find the following content:
DELETE FROM EMPINST.PROFILES_SCHEDULER_TASK;
COMMIT;
DELETE FROM EMPINST.PROFILES_SCHEDULER_TREG;
COMMIT;
DELETE FROM EMPINST.PROFILES_SCHEDULER_LMGR;
COMMIT;
DELETE FROM EMPINST.PROFILES_SCHEDULER_LMPR;
COMMIT;
The root cause of the error is that the QUIT statement is missing, and the invoke process of this SQL script cannot return to the main batch script. If we input QUIT and press the ENTER key, the db transfer process continues.
Database upgrade
Those familiar with a v2.5-to-3.0.1 db upgrade will find there are many new changes in the the 3.0.1-to-4.0 db upgrade process. For example, Calendar db creation is added to the Communities upgrade steps, "post-java-migration-301-40.sql" as well as new migrate commands are added to Homepage, and the Metrics db creation is also added as a step, etc.
Configuring the new features incorrectly can cause upgrade issues, and in the section we provide two examples of new features and two examples of common issues.
Many news story records are lost after db upgrade
On the Connections application pages, you can access many event messages on the Status Update or "I'm following" page. After a db upgrade, you may find that some of them are lost from the Web pages when, in fact, in the backend, they are stored in the Homepage database as news story records.
In the 3.0.1 to 4.0 db upgrade, a new storyLifetimeInDays parameter has been added to the Homepage migrate command, which you can see on the DBWizard prompt window as shown in figure 5.
Figure 5. Prompt for storyLifetimeInDays parameter
The value of storyLifetimeInDays should be the same as the value in news-config.xml that is stored in the WAS config folder. The attribute configuration is as follows:
<databaseCleanup>
...
<storyLifetimeInDays>30</storyLifetimeInDays>
</databaseCleanup>
The attribute is used to specify the interval at which news stories are deleted from the News repository. For example, if we assign the attribute 30, the news stories that are more than 30 days will be removed.
The db upgrade also reserves the news stories according to the value in the Homepage migrate command. Assigning an incorrect value can cause the news stories to be lost in the db upgrade process. Thus we recommend setting the value to the same as in the news-config.xml, so all your reserved news stories can be migrated.
OutOfMemoryError during Homepage db upgrade
We saw the following error log from a Homepage db upgrade that used larger data sets:
Homepage news_migrate failed :
INFO:
JVMDUMP006I Processing dump event "systhrow", detail "java/lang/OutOfMemoryError" - please wait.
JVMDUMP032I JVM requested Heap dump using '/opt2/LCI4.0/Wizards/heapdump.20120702.100931.15258.0001.phd' in response to an event
JVMDUMP010I Heap dump written to /opt2/LCI4.0/Wizards/heapdump.20120702.100931.15258.0001.phd
JVMDUMP032I JVM requested Java dump using '/opt2/LCI4.0/Wizards/javacore.20120702.100931.15258.0002.txt' in response to an event
JVMDUMP010I Java dump written to /opt2/LCI4.0/Wizards/javacore.20120702.100931.15258.0002.txt
JVMDUMP032I JVM requested Snap dump using '/opt2/LCI4.0/Wizards/Snap.20120702.100931.15258.0003.trc' in response to an event
JVMDUMP010I Snap dump written to /opt2/LCI4.0_20120624-/Wizards/Snap.20120702.100931.15258.0003.trc
JVMDUMP013I Processed dump event "systhrow", detail "java/lang/OutOfMemoryError".
Exception in thread "main" java.lang.OutOfMemoryError
at com.ibm.lconn.news.migration.next40.stories.EntriesMigration.buildEntry(EntriesMigration.java:989)
at com.ibm.lconn.news.migration.next40.stories.EntriesMigration.readEntriesOrderByItemCorrelationId(EntriesMigration.java:400)
at com.ibm.lconn.news.migration.next40.stories.EntriesMigration.setPrevLast(EntriesMigration.java:297)
at com.ibm.lconn.news.migration.next40.stories.EntriesMigration.migrate(EntriesMigration.java:98)
at com.ibm.lconn.news.migration.next40.NewsMigrationFrom301To40.migrate(NewsMigrationFrom301To40.java:236)
at com.ibm.lconn.news.migration.next40.NewsMigrationFrom301To40.main(NewsMigrationFrom301To40.java:121)
Exit value: 1
Quit.
This is the log of running the Homepage migrate .jar. Before this, upgrade-301-40.sql took about 3 hours. The test environment is DB2 9.7 on Red Hat Enterprise Linux 6.2, but the default JVM heap size doesn't work in this case. The recommended maximum JVM heap size is 3072MB according to the product documentation; however, in a 3.0.1-to-4.0 db upgrade, Homepage copies a lot of user data from the Profiles database.
The Profile database is in the 3.0.1 status, so you must upgrade Profiles from 3.0.1 to 4.0 after running the Homepage migrate jar command. This is also a checkpoint for those who execute the db upgrade manually.
Application log-in fails after db upgrade
This issue may occur in more often in the test process than the upgrade in a production environment. The error shown in figure 6 may be seen.
Figure 6. Example log-in failure error
Actually, many factors can cause the failure, such as XML configuration errors in the WAS config folder, or content data inconsistence. It's also possible that the database status is not normal. In this case, we verified the WAS system.out log and found the account was locked but the db upgrade finished without any errors.
The error occurred because the user mentioned here is the application db user, "filesuser", and not the db admin user used in the db upgrade. It was locked because the password expired or some other reason, and this eventually caused the application log-in error.
So, before performing a db upgrade, check the status of applications and databases to ensure they are working properly. In that way, you can avoid errors that are not caused by the db upgrade itself.
db upgrade fails for wrong admin user
You are required to create a Connections db using the "db2admin user" on Windows, but if you install with an "administrator" user, and then input the "db2admin" user in the window in figure 7 as per the product documentation, your db upgrade may fail.
Figure 7. Database communication properties window
The failure log is as follows:
DB: Attmept connection with url jdbc:db2://localhost:50000/OPNACT
Connection Metadata (productName: DB2/NT64; productVersion: SQL09074; majorVersion: 9; minorVersion: 7)
Starting Activities Migration: v3.0.1 --> v4.0
Error preparing statement: SELECT * FROM ACTIVITIES.OA_MEMBERPROFILE MP JOIN ACTIVITIES.OA_ACLENTRY AE ON (AE.MEMBERID = MP.MEMBERID) AND MP.MEMBERID > ? AND MP.MEMBERTYPE = 2 ORDER BY MP.MEMBERID FETCH FIRST 250 ROWS ONLY OPTIMIZE FOR 250 ROWS
DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=DB2ADMIN;SELECT;ACTIVITIES.OA_ACLENTRY, DRIVER=3.62.56
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=DB2ADMIN;SELECT;ACTIVITIES.OA_ACLENTRY, DRIVER=3.62.56
at com.ibm.db2.jcc.am.fd.a(fd.java:676)
...
at com.ibm.openactivities.migrate.activities.MigrationPreparedStatements.<init>(MigrationPreparedStatements.java:59)
at com.ibm.openactivities.migrate.activities.MemberPrincipalPerformanceMigration.doMigration(MemberPrincipalPerformanceMigration.java:46)
at com.ibm.openactivities.migrate.util.MigrationWorker.migrate(MigrationWorker.java:47)
at com.ibm.openactivities.migrate.ActivitiesMigrationDriver.main(ActivitiesMigrationDriver.java:78)
Exit value: 1
Quit.
The process fails because the "db2admin" user cannot be used to run your migrate jar. We can check DB2 control center to see the reason:
If you access your application database Authorities, such as OPNACT (Activities database), you see there is only the administrator user, not db2admin, and it is granted special permissions (see figure 8)
Figure 8. Database Authorites window
If you installed the original 3.0.1 db using db2admin, here you will see db2admin. Even though using the administrator can resolve this failure, we still recommend following the product documentation to create the database using db2admin, even on a Windows GUI system.
Conclusion
In addition to the above troubleshooting steps for the 3.0.1-to-4.0 db upgrade, you should also perform these two actions to make the process more smooth and successful:
- Back up the databases. You can use DatabaseMS native tool to back up and restore Connections databases. Run the backup at the important time points, such as before db upgrade, as well as before and after using the Homepage migration command, so as to avoid any negative impact if the db upgrade fails. Also, before backup, you should check the health status for the DatabaseMS, ensuring there are no critical problems.
- Run the db upgrade in a test environment FIRST. Set up a test environment to practice the db upgrade. You can copy all the data from the production environment and then, after running, you will be more aware of potential issues and can avoid them in the actual db upgrade process. Also, you'll have a more accurate time estimate for the process.
Tell us what you think
Please visit this link to take a one-question survey about this article:
Resources
developerWorks IBM Connections product page:
https://www.ibm.com/developerworks/lotus/products/connections/
IBM Connections documentation:
http://www.ibm.com/developerworks/lotus/documentation/connections/
IBM Connections Forum:
http://www-10.lotus.com/ldd/lcforum.nsfAbout the authors
Xiang Jun Fan is a Software Engineer at IBM's China Software Development Lab in Shanghai. He's been a member of the Lotus Connections Functional Verification Test (FVT) team since March 2009 and is responsible for Connections migration from the 3.0 release. Before that, he worked on software development for the telecom industry for two years. You can reach Fan at
xjfanxj@cn.ibm.com.
Tian E Yang is a Staff Software Engineer based at IBM's China Software Development Lab (CDL), where her current job is the installation/migration FVT Leader for IBM Connections. She has five years of experience in Web 2.0 social software testing and product installation/migration testing, and also has rich experiences on automation testing. You can reach Tina at
yangte@cn.ibm.com.
Jing Yao is a Staff Software Engineer working with the IBM Connections Install and Lotus Quickr Install team in Shanghai, China. She has four years of experience in install technology and has extensive knowledge of WebSphere Application Server deployment and configuration. You can reach Jing at
yaojing@cn.ibm.com.
Shao Li Huang joined IBM in 2010 as a Software Quality Assurance Engineer for IBM Connections. She has more than two years of experience in Connections deployment and software integration. You can reach Shaoli at
slhuang@cn.ibm.com.