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.

Back to top

Migrate database from MySQL to DB2

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
   
   
   
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
   
   
   

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).

(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.

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.

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.

Back to top

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.

Back to top

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.

<?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.

Modify JSP property configuration

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

<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.

        <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.

Back to top

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.

# 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.

<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.

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.

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.

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.

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.

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.

<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>

Back to top

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.

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.

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.

Back to top

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.

Back to top

Download

Information about download methods

Resources

Learn

• OpenMRS Trunk: Learn more about this project.
  
  
• OpenMRS Module Repository: Get all the module names and descriptions.
  
  
• IBM DB2 database software: Learn how it offers industry leading performance, scale, and reliability on your choice of platform from Linux®, UNIX® and Windows® to z/OS.
  
  
• "IBM Data Movement Tool" (developerWorks, Nov 2010): Presents a very simple and powerful tool to move data from various sources easily. (Click here for the Chinese version of the article.)
  
  
• Apache Ant: Learn more about this Java library and command-line tool whose mission is to drive processes described in build files as targets and extension points dependent upon each other.
  
  
• developerWorks Industries: Find the latest industry-specific technical resources for developers. Explore other healthcare industry articles.
  
  
• developerWorks technical events and webcasts: Stay current with technology in these sessions.
  
  
• developerWorks on Twitter: Join today to follow developerWorks tweets.
  
  
• developerWorks podcasts: Listen to interesting interviews and discussions for software developers.
  
  

Get products and technologies

• Installing OpenMRS: Install and configure OpenMRS from start to finish.
  
  
• Get WebSphere Application Server Java SDK 1.6 SR9 FP1 . Fix Central provides fixes and updates for your system's software, hardware, and operating system.
  
  
• Get IBM WebSphere Application Server Version 7.0.0 Fix Pack 17. Fix Central provides fixes and updates for your system's software, hardware, and operating system.
  
  
• Download OpenMRS svn source code. Version 1.7.2 was used for this article.
  
  
• Evaluate IBM software through the method that suits you best; downloads, trials, and cloud.
  
  

Discuss

• OpenMRS forum: Have discussions with other developers.
  
  
• IBM WebSphere developer forum
  
  
• IBM DB2 forum
  
  
• The developerWorks community: Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
  
  

About the author

Tian 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.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Migrate database from MySQL to DB2
• Import the project
• Modify the project for WebSphere Application Server
• Modify the project for DB2
• Deploy the EAR file
• Troubleshooting
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Industries on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers