Introduction

When I started working with Notes Release 3 in 1994, I fell in love with it. In just a few hours, I could create databases such as a document repository, a discussion group, or a workflow application. I quickly learned more techniques and got involved in creating complex Notes applications.

In 1996 I had an opportunity to play a leading role in developing the IBM "World Purchasing System," a complete procurement system IBM planned to sell to corporations and one of the most complex Notes systems built at that time. Working alongside some of the top developers from Lotus Consulting Services Group, I began to notice limitations of Notes -- as the complexity of the database increased, the performance of Notes decreased. I spent the next few years working on more systems that involved integration of Notes with relational databases like DB2 and Oracle. I had many successes and learned many valuable lessons. I am glad to have the opportunity to share some of my experiences in this article.

Back to top

Notes, its strengths and weaknesses

Database Notes has an unique integrated database that is document-based. The advantage of this design is that a Notes developer can easily create a database and implement it with little database design background. For a document repository database, a discussion group, and a simple workflow application, Notes is the top choice.

However, from a relational database standpoint, a Notes database is a "denormalized" database -- basically, a flat table. This causes problems when you are dealing with situations that require an abundance of relationships with data, or many "joins" of different tables.

Consider the example illustrated in Figure 1 in which I am designing a purchase approval database. In this example, each employee forwards a form to their manager for approval. But if the manager is not available for some reason, the form will be sent to a delegate or a delegate's delegate according to certain business rules. Here is the tricky part many of our fellow Notes developers have experienced. I need to call @dblookup from a formula or make a similar call from LotusScript to get the data from the absent report database, as well as from the delegate database if needed. This process slows down performance significantly. In a logic-intense Notes database, I have seen 10 or more @dblookups used on a single form. A trick often used in the developer community is to create a hidden field called a "computed field" that computes every time the document is saved. But the drawback is that sometimes the document does not have the most updated information. The database performance slows down as more and more @dblookups functions are used. In a relational database, on the other hand, because SQL is used, many database joins can be accomplished in one single SQL call. Therefore, a relational database performs better than Notes as more database-intensive operations are used.

Notes joins are linear. As rows of data get larger, the time Notes takes to respond increases exponentially. When I was working on an IBM e-commerce project as an independent consultant, Lotus had a problem creating the catalogs for the system. The maximum amount of catalog items was about 6,000. It took 150 hours on a server to join the price database, item database, and contract database in Notes. I changed the join process using a simple relational database, Access, and a 500,000-item catalog could be created within an hour on my PC. This example shows that the performance difference between joining tables in Notes and in a relational database can be in the thousands, or more, especially if you are working with a very large database. Another drawback of a Notes database is that it is not relational -- or in relational database terms, has "0" level of normalization. This causes trouble when Notes is used to build and maintain relational data. I will explore this later in this article.

Transactions Notes has no transaction mechanism built in. Developers need to write their own transaction routines, usually by writing some temporary Notes documents and putting everything on the final page for final submission. Domino (Notes) server setup is an excellent example. In a few pages, server name, password, administrator name and password, organization name and contact information, and more information are collected and submitted on the final page. WebSphere and many other IBM tools like MQSeries provide better transaction mechanisms, especially for mission-critical programs. This is already well known in the industry. I agree with IBM's recommendation to use WebSphere when transactions are the centerpiece of an application.

Back to top

Designing a Notes database and integrating it with a relational database

From 1996 to 1998, I worked as the lead developer in IBM Partner Info eCommerce Version 1.0 development. We were one of the first groups, but not the only group, that faced the problem of integrating Notes and a relational database, such as DB2. The challenge was to come up with a design that used the best of both worlds.

As seen in Figure 2, the Domino server had four main functions: authentication, company profile, Web server (through IBM HTTP server), and catalog server. All other data was passed to the IBM net.data backend for completion of the transaction. In this design, the Domino server's strength in the Name and Address Book was used as the foundation of authentication. Its strength in rich text fields (a unique feature) was also used to serve up the catalog, which had many graphics. Company profiles were served through a few Notes databases. Finally, we were able to design a system that maximized the strength of a Domino server as well as an IBM transaction server.

Normalizing Notes databases

When I was working at a major financial institution, a colleague of mine, Steve Desofi, and I made our first attempt to normalize a Notes database. As I mentioned earlier, because of the document-based nature of Notes, tables in Notes are not normalized. When you are working in a big corporation, the problem is not finding the data, it is the abundance of data. The same data is stored in many different databases. Most of these databases contain consistent instances of this data, but some do not. Cleaning up Notes databases is a huge task, and requires coordinated efforts of project managers and developers, even among departments. To keep data up to date on our project, hundreds of scheduled agents were written to check and update many databases. A dedicated team from the Notes administration group was responsible to keep track of the agents and databases.

Our approach was to break up complicated Notes forms into smaller, normalized forms with DocUniqueID as the key to link them together. When updates occurred, the background agent was called to update prospective normalized forms, or to create each document separately. When you read, @dblookup was called to construct data from multiple forms. The advantage of this approach is clear. The database is much easier to maintain, and many fewer scheduled agents need to be written. The downside is reduced performance and longer development cycles. However, the benefits greatly outweigh the cost. Normalization before creating a new Notes database thus became a standard procedure in this financial institution.

Back to top

Converting Notes data into a relational database application

Converting Notes to Java

We converted a few Notes databases that contained mainly relational data into relational databases based on Java™ applications. The reason was simple: Let the relational databases handle what they are best at -- relational data. There were three phases in the project. First, we took the database from Notes, studied the forms, normalized it and mapped it into an Oracle database. Second, we built a Java-based middleware library that had Notes-like APIs. Like Notes APIs, this library contained objects of fields, forms, and views that could be easily called by the application. It also provided Notes-like security features, such as document-level security and role-based security. We extended the security to field level security so that, depending on the user's role, a field could be hidden, read-only or read-write. To connect to the back-end database effectively, we built a middleware server, which we called "Engine." The function of "Engine" was to pull data out of the databases and send it to the front end and vice versa. "Engine" also provided data caching for performance. One of the major advantages of using this architecture was that we could access data in both Notes and Oracle in the back end while maintaining the same user interface. As a matter of fact, the front end was completely isolated from the back-end data source. The back end was so well encapsulated by the library that we could change the back-end database between Notes and a relational database without changing a single line of code in the front end. This enabled us to move back-end data from Notes to Oracle smoothly.

Third, we implemented the Notes functions in the front end through a Java applet. The result was that we could provide Notes-like functionality plus some new Java functions in an application that could work either with a relational back end or a Notes back end.

An example, as shown in Figure 3, was the real-time status application. It was designed to provide worldwide real-time banking system status, including hardware, software, network, even ATMs, to the CIO. We used a Java applet that could read Notes and Oracle database data through our "Engine" to display the status in real time.

Converting Notes applications to WebSphere

When we got our hands on the IBM WebSphere server, it was clear to us that WebSphere was the perfect tool for converting Notes applications. As mentioned earlier, WebSphere has built-in transaction support and the advantage of security and scalability. Most importantly, the J2EE standard was gathering steam and getting more and more support from the developer community and the software companies.

Our conversion of a Notes database into a WebSphere application required a few key components: the front end, the transport, and the back end.

Front end

To convert a Notes application into a WebSphere application, we used JavaServer Pages (JSP) components for the front end. There are a few libraries that provide built-in functions for interactive form-based applications, such as input validation, mapping a field value to a Java bean, and so on. Jakarta Struts is an excellent choice, because it is an open source solution and is widely used in the industry. Another good library to consider is DBForms. But it is still in beta at the time I am writing this article. The next version of Notes, Rnext, also available in beta at the time I am writing this article, also includes a tag library. (See Resources for links to each of these tools.) Because the industry trend toward these types of libraries is moving forward very quickly, there will be more and more interactive JSP tag libraries that can create Notes-like forms with relative ease. We chose Struts because of its popularity and because it was open source.

Transport

In J2EE, a Java bean is used to contain and transport data. We built a customer library that was similar to the library described before but customized for JSP pages and the tag library used in the front end. The library encapsulated the back-end data modules, as well as providing security features, so that the front-end JSP page could present the data in Notes-like style. I'll cover details of the library in a follow-on article.

Back end

A relational database was used to hold the data. In our example, we used DB2. Enterprise Java Beans (EJB) components were used to access the data and pass it to the Java beans.

Back to top

Conclusion

Notes is a great groupware product. As I mentioned earlier in this article, I believe Notes is the best tool available for group applications such as document repository, discussion group, and workflow. However, as applications grow from serving groups to serving departments and divisions, the data becomes harder and harder to maintain in Notes, and performance decreases.

As data becomes more relational and demands more transactions, your best solution is to move it from a Notes database to a J2EE-based application with a relational database as the back end. IBM offers a suite of good products to help accomplish this, including WebSphere, DB2, MQSeries, and IBM Connector. They are an obvious choice for IT managers converting from one IBM product to another. However, without any conversion tools available from IBM as of today, the transformation will be complex. For this reason, we developed the comprehensive middleware library in Java as described in this article. The library helped us successfully rebuild Notes applications in WebSphere. We'll continue to enhance it down the road.

In the past five years, I have developed many Notes applications as well as Java and WebSphere applications. Hopefully, my experiences described in this article provide some insight into when, what it takes, and how to convert a Notes application to a WebSphere-based application. I would be interested in hearing about your experiences in this regard. Feedback is very welcome.

In the next article, I will present a real-world conversion from a Notes application to a WebSphere application, including sample code and a detailed description of the design and implementation of our middleware library.

Acknowledgements

The author would like to thank Steven R. Desofi for the discussion and exchange of ideas for the article and Charles M. Jones from Virtuous Dialogue for reviewing the article.

Resources

• Find more articles about using Lotus and WebSphere to develop enterprise applications on the WebSphere Developer Domain Lotus Domino Zone.
  
  
• Investigate the beta program for the next version of Lotus Notes/Domino, Rnext.
  
  
• Learn more about the Struts Framework, an open source MVC (Model-View-Controller) framework with tag library that is part of the Jakarta Project sponsored by the Apache Software Foundation.
  
  
• Download DBForms, another open source RAD framework and tag library.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Notes, its strengths and weaknesses
• Designing a Notes database and integrating it with a relational database
• Converting Notes data into a relational database application
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Lotus Notes
• Webcast: Reach for high ROI: Forrester on the economic benefits of upgrading to Lotus Domino 8.5
• Buy: Lotus Notes