Migrate OpenMRS to DB2 and WebSphere Application Server

OpenMRS® is a collaborative, open source project to develop software to support the delivery of healthcare in developing countries. Learn how to migrate OpenMRS into IBM® DB2® and IBM WebSphere® Application Server. This article shows you how to prepare the database, import the source code, and modify the project for WebSphere Application Server.

Tian Tian Gao (gaottbj@cn.ibm.com), Software Engineer, IBM

Photo of Tian GaoTian Tian Gao is a software engineer working for the IBM China Development Laboratory (CDL) Emerging Technology Institute. Currently, she is committed to standards and healthcare-related work.



15 November 2011

Also available in Russian

Introduction

The Open Medical Record System (OpenMRS), created in 2004, is a collaborative, open source project to develop software to support delivery of healthcare in developing countries. It is a web application that lets you design a customized medical records system even if you have no programming knowledge. Medical and systems analysis knowledge is required, though. OpenMRS provides the ability to add or replace bundle modules. It also has flexible customization functions. The platforms for the OpenMRS project are Apache Tomcat and MySQL.

In this article, learn how to migrate OpenMRS into IBM DB2 and IBM WebSphere Application Server. Step-by-step instructions show how to migrate from MySQL to DB2 and how to import the project into your development environment. Learn to make the necessary modifications for DB2 and WebSphere Application Server, and then deploy the EAR file.

Prerequisites

The following product versions apply to this article:

  • DB2 Version 9.7
  • WebSphere Application Server Version 7.0.0.17
  • OpenMRS Version 1.7.2.

Some of the information in this article may not apply to later versions of the these products.

To follow along with this article, it is assumed you have deployed OpenMRS on MySQL and Tomcat. For help with deployment, see Installing OpenMRS.


Migrate database from MySQL to DB2

Frequently used acronyms

  • EAR: Enterprise archive file
  • J2EE: Java™ 2 Platform Enterprise Edition
  • JAR: Java archive file
  • JDBC: Java Database Connectivity
  • JRE: Java Runtime Environment
  • JSP: JavaServer Pages
  • XML: Extensible Markup Language

The first task is to get the database ready. Before migration, go through the following steps.

  1. The current user should have DB2AMNS privileges for the DB2 database. To get the privilege, you can run the following script to add the current user to the DB2 administrators group.
    net localgroup DB2ADMNS %username% /add
    net localgroup DB2USERS %username% /add
  2. Create a DB2 database named Openmrs and ensure the current user has database permissions to that database. To do so, you can right click on the Openmrs database in the DB2 Control Center to view the user's privileges, as shown in Figure 1.
    Figure 1. User database privileges
    User database privileges
  3. Prepare the MySQL and DB2 JDBC driver JAR files. The driver JAR files are in the installation directory of your database.
    • For MySQL, the driver's name is likely mysql-connector-java-5.1.7-bin.jar
    • For DB2 there will be two JAR files: db2jcc.jar and db2jcc_license_cu.jar
  4. Make sure your DB2 JDBC driver is later than version 3.0. You can check the version by running the java com.ibm.db2.jcc.DB2Jcc -version command. Figure 2 shows a sample result.
    Figure 2. JDBC driver version
    JDBC driver version

To use OpenMRS you must have the database on MySQL. To achieve the migration target, you should have a data migration tool. The IBM Data Movement Tool can help you with data migration tasks. (See Resources for more information about this tool.)

Follow these steps to migrate the OpenMRS demonstration data from MySQL to DB2:

  1. Bring up the IBM Data Movement Tool user interface, as shown in Figure 3.
  2. Select the Source Database MySQL, and fill in the source database properties based on your setup.
  3. Select JDBC Drivers, in my environment, MySQL version 5.0, and the JDBC connector driver is mysql-connector-java-5.1.7-bin.jar.

    Click Connect to MySQL to check if the connection works. After that, select the database openmrs.

  4. Fill in the DB2 Database properties.
  5. Choose the JDBC Drivers for DB2. You can find the driver JAR file in your DB2 installation directory (for example, C:\Program Files\IBM\SQLLIB\java\).

    You will need two JAR files: db2jcc.jar and db2jcc_license_cu.jar (the license JAR file).

Figure 3. Data movement property configuration
Data movement property configuration

(View a larger version of Figure 3.)

Click Connect to DB2 to verify the connection works. If you see the instance on your workstation, you can continue your data movement. After clicking Extract DDL/Data, you'll see the tool's information messages in the View File tab, as shown in Figure 4.

Figure 4. Extract DDL and data
Extract DDL and data

Find a file named db2gen.cmd in the output directory under your tool's installation directory. At this point, you can extract the scripts to do the database deploying. Click Deploy DDL/Data. The console should look similar to Figure 5. Compare row counts to check if any data did not execute correctly.

Figure 5. Deploy data objects
Deploy data objects

You can also change the migration properties by changing the values in the IBMExtract.properties file, which is in the root directory of the IBM Data Movement Tool.


Import the project into the development environment

Some code modifications are required to migrate OpenMRS to DB2 and WebSphere Application Server. The first step is to import the source code into your development environment. The development environment must provide the ability to develop Java EE projects. Eclipse is a good choice. Download the OpenMRS source code. (Version 1.7.2 was used for this article.)

Make sure that your JRE is later than version 1.4. To verify your JRE version, run the java -version command, as shown in Figure 6.

Figure 6. Check JRE version
Check JRE version

Modify the project for WebSphere Application Server

To ensure that the code can run in WebSphere Application Server, you should build the project as an EAR file and deploy the EAR file to WebSphere Application Server.

Add the deployment file

To allow the project to be built into an EAR file, add the J2EE application.xml deployment file into this project.

In the metadata directory, add a folder named META-INF, which contains the appliction.xml that will be the deployment descriptor when you deploy this EAR file into WebSphere Application Server. It tells WebSphere Application Server the web module of the application and the context root of it. The content of the XML file is shown in Listing 1.

Listing 1. XML file
<?xml version="1.0" encoding="UTF-8"?>
<application id="Application_ID" version="1.4" xmlns="http://java.sun.com/xml/ns/j2ee"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
 http://java.sun.com/xml/ns/j2ee/application_1_4.xsd">
	<display-name>openmrsEAR</display-name>
	<module>
		<web>
			<web-uri>openmrs.war</web-uri>
			<context-root>openmrs</context-root>
		</web>
	</module>
</application>

After adding the file, your development environment should look like Figure 7.

Figure 7. Import project to eclipse
Import project to eclipse

Modify JSP property configuration

The project includes a servlet named jsp configured in web.xml, as shown in Listing 2.

Listing 2. jsp servlet
<servlet-mapping>
		<servlet-name>jsp</servlet-name>
		<url-pattern>*.withjstl</url-pattern>
</servlet-mapping>

The URL request with the suffix withjstl will be handled by that jsp servlet, but what is that jsp servlet? In Tomcat, you can find this jsp servlet configured in web.xml under tomcat-root/conf/ directory, as shown in Listing 3.

Listing 3. jsp servlet in web.xml
        <servlet-name>jsp</servlet-name>
        <servlet-class>org.apache.jasper.servlet.JspServlet</servlet-class>

Most importantly, the withjstl request will be handled by the JspServlet class defined in Tomcat's jasper core. But, if you don't have the same class in WebSphere Application Server, how can the application manage that web request? To solve the problem, you can add a configuration to the project's web.xml.

  1. Open the web.xml file and find the <jsp-config> element in the file.
  2. As in Listing 4, add a <jsp-property-group> element as a child inside the <jsp-config> element.
    Listing 4. <jsp-property-group>
    <jsp-property-group>
    <url-pattern>*.withjstl</url-pattern>
    </jsp-property-group>

The JSP property group is defined by naming the group and specifying the URL patterns. By adding this element, URL patterns will be handled as a JSP file if a resource matches the withjstl.


Modify the project for DB2

As you've seen in the src folder, the source code has two child folders: api and web. During deployment, the classes in the api folder will be compressed to a JAR file named openmrs-api-version.subversion-.jar. To migrate this project to a DB2 database, you need a JDBC connector for DB2.

Add DB2 JDBC driver

Use the following steps to put the DB2 JDBC driver into the project.

  1. Create a new subfolder named db2 under the project lib folder.
  2. Copy the DB2 JDBC drivers into the db2 folder and add them to classpath.
  3. Add the database driver path to the lib.properties by adding the lines in Listing 5 to lib.properties (which is the file that provides the Ant with a JAR lib variable configuration when building).
    Listing 5. Add to lib.properties
    #
    # DB2 Java Connector
    #
    database.dir=${lib.dir}/db2

Modify the hibernate configuration

Replace the hibernate.default.properties with those in Listing 6.

Listing 6. Hibernate properties
# Default (overridable) with runtime properties file
# Connection Properties -->
hibernate.connection.username=your db2 user name
hibernate.connection.password=your db2 password
hibernate.connection.driver_class=com.ibm.db2.jcc.DB2Driver
hibernate.connection.url=jdbc:db2://localhost:50000/OpenMRS

# Hibernate specific connection/debug properties
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.lazy=true
hibernate.dialect=org.hibernate.dialect.DB2Dialect

# Hibernate specific connection pool properties		
hibernate.c3p0.max_size=50
hibernate.c3p0.min_size=0
hibernate.c3p0.timeout=100
hibernate.c3p0.max_statements=0
hibernate.c3p0.idle_test_period=3000
hibernate.c3p0.acquire_increment=1

# Hibernate debugging options
hibernate.generate_statistics=true
hibernate.cache.use_structured_entries=true

# Shouldn't need to override these properties
hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider
hibernate.connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider
hibernate.connection.release_mode=after_transaction
hibernate.jdbc.use_get_generated_keys=false

In this system, hibernate will map the relational database tables with the entity classes according to the *. hbm.xml file, so your operation on the objects will carry out the corresponding tables in the database. The hibernate primary key generation strategy includes the following.

Assigned
Specifies the new primary key value by the application.
Increment
The new primary key will be generated by hibernate through the increment mode during operation.
Native
The primary key will generate using different strategies according to different databases.
UUID
Universally unique identifier.
Foreign
Uses a foreign key from some other tables.
Sequence
Uses the sequence mechanism for generating primary key provided by a specific database, such as Oracle. The primary key generation method in OpenMRS is identity.

You can see the configuration in the hibernate mapping configuration file. Listing 7 shows the example PatientIdentifier.hbm.xml.

Listing 7. PatientIdentifier.hbm.xml
<id name="patientIdentifierId" type="int" column="patient_identifier_id"
		unsaved-value="0">
		<generator class="identity" />
</id>

Identity will use the primary key generation mechanism provided by the database; the primary key value is completely managed by the database. Listing 8 shows the code you'll see if you look at the IdentityGenerator class in the hibernate package in org.hibernate.id.

Listing 8. IdentityGenerator class
public InsertGeneratedIdentifierDelegate getInsertGeneratedIdentifierDelegate(
	            PostInsertIdentityPersister persister,
	            Dialect dialect,
	            boolean isGetGeneratedKeysEnabled) throws HibernateException {
	        if ( isGetGeneratedKeysEnabled ) {
	            return new GetGeneratedKeysDelegate( persister, dialect );
	        }
	        else if ( dialect.supportsInsertSelectIdentity() ) {
	            return new InsertSelectDelegate( persister, dialect );
primary key generation mechanism	        }
	        else {
	            return new BasicDelegate( persister, dialect );
	        }
	    }

The hibernate.jdbc.use_get_generated_keys property enables the use of JDBC3 PreparedStatement.getGeneratedKeys() to retrieve natively generated keys after insertion. The property requires a JDBC driver later than version 3 and JRE later than version 4. If you don't add this property, you will get the exception "The database returned no natively generated identity value."

Modify the project runtime configuration

To modify the project runtime environment there are two basic tasks.

  • Modify context.xml

    The application will verify if it can connect to the database and, if the database is empty, the first time the application runs. The context.xml file is the configuration that the application uses for the verification process.

    At runtime, the application will:

    • Execute the select statement to check whether the user data is in the database.
    • Determine if initialization is necessary.

    Modify the file to include the information in Listing 9 so that the system can connect to the database. Make sure the DB2 user has the privileges to access and operate the database.

    Listing 9. context.xml
    <?xml version="1.0" encoding="UTF-8"?>
    <Context antiJARLocking="true" reloadable="false" useHttpOnly="false">
    <Resource name="jdbc/OpenMRS"
    auth="Container"
    type="javax.sql.DataSource"
    factory="org.apache.commons.dbcp.BasicDataSourceFactory"
    validationQuery="SELECT * FROM users"
    url="jdbc:db2://localhost:50000/OpenMRS"
    driverClassName="com.ibm.db2.jcc.DB2Driver"
    username="your db2 user name"
    password="your db2 password"
    maxWait="3000"
    maxIdle="100"
    maxActive="10" />
    </Context>
  • Disable the update filter

    The update filter configured in the web.xml file will check the database update by running some MySQL commands. To migrate the project to DB2, disable this update filter. You could just delete the filter, but it's a better practice to comment it out (<!-- -->), as shown in Listing 10.

    Listing 10. Disable filter
    <!--filter>
            <filter-name>UpdateFilter</filter-name>
            <filter-class>org.openmrs.web.filter.update.UpdateFilter</filter-class>
        </filter>
        <filter-mapping>
    	 <filter-name>UpdateFilter</filter-name>
    	 <url-pattern>/*</url-pattern>
    </filter-mapping-->

    Add the openmrs-runtime.properties, a runtime properties configuration, to your application. As shown in Listing 11, the OpenMRS application will connect to the database using this file's configuration at runtime.

    Listing 11. openmrs-runtime.properties
    #Auto generated by OpenMRS initialization wizard
    #Fri Apr 08 15:53:48 CST 2011
    module.allow_web_admin=true
    connection.url=jdbc:db2://localhost:50000/openmrs
    connection.username=your db2 user name
    connection.password=your db2 password
    auto_update_database=false

Modify the class method

To make sure the application can use the JDBC driver for DB2, you need to make some modifications on the database connection drivers.

Replace the verifyConnection method in the InitializationFilter class, which is in the org.openmrs.web.filter.initialization package, with the one shown in Listing 12.

Listing 12. Replace verifyConnection
private boolean verifyConnection(String connectionUsername, String connectionPassword, 
String databaseConnectionFinalUrl) {
        try {
                Class.forName("com.ibm.db2.jdbc.DB2Driver").newInstance();
	DriverManager.getConnection(databaseConnectionFinalUrl, connectionUsername, 
                    connectionPassword);
		return true;			
	   }
	      catch (Exception e) {
	errors.add("User account " + connectionUsername + " does not work. " 
                         + e.getMessage()
	+ " See the error log for more details");
                       // TODO internationalize this
                       log.warn("Error while checking the connection user account", e);
               	return false;
	       }
	}

Replace the loadDatabaseDriver method of the DatabaseUtil class, which is in the org.openmrs.util package, with the code in Listing 13.

Listing 13. Replace loadDatabaseDriver
public static void loadDatabaseDriver(String connectionUrl) throws 
   ClassNotFoundException {
		if (connectionUrl.contains("mysql"))
			Class.forName("com.mysql.jdbc.Driver");
		else if (connectionUrl.contains("hsqldb"))
			Class.forName("org.hsqldb.jdbcDriver");
		else if (connectionUrl.contains("postgresql"))
			Class.forName("org.postgresql.Driver");
		else if (connectionUrl.contains("oracle"))
			Class.forName("oracle.jdbc.driver.OracleDriver");
		else if (connectionUrl.contains("jtds"))
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
		else if (connectionUrl.contains("sqlserver"))
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
		else
			Class.forName("com.ibm.db2.jdbc.DB2Driver");
			
	}

To avoid DB2 running incompatible commands, replace the updatesRequired method of the DatabaseUpdater class, which is in the org.openmrs.util package, with the code in Listing 14.

Listing 14. Replace updatesRequired
public static boolean updatesRequired() throws Exception {
		log.debug("checking for updates");
		return false;
	}

To avoid unwanted initialization during the application's first run, replace the initializationRequired method of the InitializationFilter class, which is in the org.openmrs.web.filter.initialization package. Listing 15 shows an example.

Listing 15. Replace initializationRequired
public static boolean initializationRequired() {
		return false;
	}

At this point, the project code will work with a DB2 database. However, to migrate to WebSphere Application Server you should build the project to an EAR file. You can build an EAR file with the help of Ant. Add a target named "ear" to the ant build.xml file. In the EAR file, a WAR file and a web application deployment descriptor are necessary. Add this target as shown in Listing 16.

Listing 16. Add target
<target name="ear" depends="package-web" description="build ear">
        <ear destfile="${dist.dir}/${webapp.name}.ear"
                  appxml="${metadata.dir}/META-INF/application.xml">
	    <fileset dir="${dist.dir}" includes="*.war"/>      
	    <fileset dir="${metadata.dir}/META-INF"/>
          </ear>
   </target>

Deploy the EAR file in WebSphere Application Server

During the application's initialization, there's a bug involving the filters running out of order. The wrong order creates a runtime bug where the context is misused when it has not been set up. To solve the problem, you need a fix patch for your WebSphere Application Server:

  • 7.0.0-WS-WASSDK-FP0000017.pak
  • 7.0.0-WS-WAS-FP0000017.pak

Download the appropriate fix for your operating system.

Modify custom properties

If you deploy your EAR file at this point, you'll see "403 Forbidden" errors in your firebug Net panel, as shown in Figure 8.

Figure 8. 403 Forbidden errors
403 Forbidden errors

The error occurs when you don't have the privileges to access certain web resources. The example application returns some resources from the WEB-INF directory. In WebSphere Application Server, the resources under this directory are protected, so you don't have privileges. To solve the problem, add a property to your WebSphere Application Server custom properties.

  1. Open the Servers link in your WebSphere Application Server Integrated Solutions Console.
  2. Click the server types WebSphere application servers.
  3. Click your server name (the example uses server1), then Web container -> Custom properties.
  4. Add a new property called ExposeWEBINFonDispatch and set the Value to true, as shown in Figure 9.
Figure 9. Server custom properties, WebSphere Application Server
Server custom properties, WebSphere Application Server

The ExposeWEBINFonDispatch property must be set to true to enable a dispatched request (forward or include) to access a static file from the application WEB-INF directory.

Now the migration is complete. See the next section if you encounter problems.


Troubleshooting

In the migration process you might encounter errors similar to those outlined below. Analyzing the error log and the error phenomenon to find the root cause of the error is the best way to solve it.

ErrorSuggestion
Database connection errorCheck if the user has the required privileges, and verify whether the user can get access to the database schema. Check whether your JDBC driver matches the database version; the connection will not work with different versions.
BUILD FAILED C:\Documents\openmrs-trunk\build.xml:150: Unable to find a javac compiler;com.sun.tools.javac.Main is not on the classpathPerhaps JAVA_HOME does not point to the JDK. It is currently set to C:\Program Files\Java\jre6. Copy C:\Program Files\Java\jdk1.6.0_10\lib\tools.jar to C:\Program Files\Java\jre6\lib\ext.
Can't load moduleXXDownload the corresponding modules to the deployment directory of the WEB-INF \ bundledModules folder. Find the modules in the OpenMRS Module Repository list.
No ContextLoaderListener registered Most likely the application can't load runtime property files in the environment. You can try to copy this file to your webapps root directory.

Download

DescriptionNameSize
Sample code for this articleopenmrsear.zip37 MB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, WebSphere, Open source
ArticleID=773599
ArticleTitle=Migrate OpenMRS to DB2 and WebSphere Application Server
publish-date=11152011