Configure IBM InfoSphere Information Integrator to access a Lotus Notes database

Learn how to use IBM® WebSphere® Information Integrator for integrating and updating data in IBM Lotus Notes® databases and how to set up access to the data sources from a DB2® federated database using the DB2 Control Center. This article provides a hands-on exercise for retrieving and merging data from a Lotus Notes database to various other data sources and makes use of database federation.

Share:

Vinod Lamba (vinlamba@in.ibm.com), Staff Software Engineer, IBM

Vinod LambaVinod Lamba is a Staff Software Engineer for IBM System and Technology Group. His expertise include design and development in object database management systems and J2EE middleware. He is a certified Java developer and IBM Certified WebSphere Application Server Administrator. He is currently working on an IBM Rational ClearQuest and ClearCase deployment project.



Jorge Rasillo (jarasill@us.ibm.com), Software Engineer, IBM

Jorge RasilloJorge Rasillo is a Software Engineer for IBM System and Technology Group. He graduated recently from the University of Texas at El Paso with an M.S. in Computer Science. His field of interest is software engineering with a main focus in the development process. He is a member of the Society of Hispanic Professional Engineers.



17 November 2005

Introduction

In today's business information environment, managing heterogeneous and geographically distributed data sources is perhaps the most significant challenge in many enterprises. InfoSphere Information Integrator, a strategic information integration framework, provides a platform to meet the diverse range of data integration requirements for business intelligence and business integration.

With InfoSphere Information Integrator, you can maintain information in your current data stores and access that diverse information as though it resided in a single data store. Imagine the power you have when, from a single application interface, you can access data from almost any data store, including relational databases, Domino (Lotus Notes) databases, XML, Microsoft® Excel spreadsheets, and flat files.

The main purpose of this article is to give you step-by-step instructions for setting up and configuring a InfoSphere Information Integrator federated database system to retrieve data from a Lotus Notes database using an ODBC wrapper and merging the retrieved data with data from an XML document. This article also provides a quick overview to different kinds of JDBC drivers available for DB2 and shows you how to access the merged data from Domino/Notes Notes and XML in a Java®-based application.


Prerequisites

If you don't already have them installed, start out by installing the latest versions of the following software, whose download info can be found in Resources:

The product Web sites provide detailed product description, press reviews, downloadable fixes and enhancements, and a collection of offerings and solutions from IBM business partners.


ODBC wrapper for Lotus Domino/Notes databases

InfoSphere Information Integrator provides an ODBC wrapper that can be used to access data from Lotus Domino/Notes database. This ODBC wrapper requires a DB2 Federated Server, which is created when you install InfoSphere Information Integrator. Additionally, the DB2 federated server must be able to open and read Domino/Notes database in order to retrieve the data.

Figure 1. ODBC (Open Database Connectivity) wrapper using Lotus NotesSQL
ODBC wrapper using Lotus NotesSQL

Features/requirements for ODBC wrapper

Table 1. Features/Requirements for ODBC wrapper
Feature/requirement
Software requirement Lotus NotesSQL
Hardware RequirementModerate
Supported DML operations (data manipulation language)SELECT, INSERT, UPDATE, DELETE
Supported transparent DDL Operations (data definition language)CREATE, ALTER & DROP TABLE
Supported other DDL operations (data definition language)CREATE, ALTER & DROP VIEW
DBCS (duble-byte character set) supportSupported
Domino/Notes attachmentsNot supported
Domino/Notes picturesNot Supported
Domino/Notes table elementsSupported
Pushdown (Allowing predicates and aggregate functions to be pushed down to the data source for processing)Supported
Ease of useSimple

Setting Up access to Lotus Domino/Notes database using ODBC wrapper

  1. Creating and populating the Lotus Notes database
    Create a temporary Lotus Notes database with three fields (ID, FirstName, and LastName), and populate with some values.
  2. Configure the ODBC data source using Lotus NotesSQL
    To access a Lotus Notes data source as an ODBC data source, you need to set it up to use the NotesSQL driver.
    1. Click on Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC).
    2. Select the System DSN tab and select Add. The wizard will ask you what kind of driver you want to use, select Lotus NotesSQL Driver (.nsf) and click Finish.
    Figure 2. Create ODBC Data source: System DSN
    Create ODBC Data source: System DSN
    1. Once you click on Finish, the wizard will take you to the screen where you configure your Lotus Domino Database as an ODBC Data Source.
    2. Enter the data source name and a description for your data source. Select the Notes database from your local machine. Select Local from the Domino Server drop down list. Click on Browse, and enter or navigate to the path where the Notes database is stored, and click OK.
    Figure 3. Create ODBC Data source: Lotus Notes setup
    Create ODBC Data source: Lotus Notes setup
    1. Now we will associate the Lotus ID with the ODBC Data Source. Click on Options. Select the User name you used to create your Notes database, enter the password associated with it, and click OK.
    Figure 4. Create ODBC Data source: NotesSQL options
    Create ODBC Data source: NotesSQL options
  1. Create the ODBC wrapper
    A wrapper is the mechanism that the federated system uses to communicate with and retrieve data from a data source. The wrapper module routines allow the server to connect to a data source and retrieve data from it iteratively. The CREATE WRAPPER statement is used to register a wrapper for each data source.
    1. Open the DB2 Control Center.
    2. Right-click on the Federated Databases Objects folder and select Create Wrapper.
    Figure 5. Create ODBC wrapper for Lotus Domino datasource, Step 1
    Create ODBC wrapper for Lotus Domino datasource, Step 1
    1. On the Wrapper tab of the Create Wrapper wizard, specify the wrapper information.
      1. In the Data source field, select ODBC.
      Figure 6. Create ODBC wrapper for Lotus Domino datasource, Step 2
      Create ODBC wrapper for Lotus Domino datasource, Step 2
      1. In the wrapper name field, type DOMINO_WRAPPER.
      Figure 7. Create ODBC wrapper for Lotus Domino datasource, Step 3
      Create ODBC wrapper for Lotus Domino datasource, Step 3
    1. Click OK to create the wrapper.
  2. Create the server definition
    A server definition registers the information about the data source server with the DB2 federated server. When you create a server definition, you provide the federated database with the server information, such as the type of server, the version, the wrapper that it is associated with, databases, nodes, and server options.
    1. Right-click the Server folder under the wrapper that you just created, and click Create.
    Figure 8. Create server definition for Lotus Domino datasource, Step 1
    Create server definition for Lotus Domino datasource, Step 1
    1. On the Create Server Definition wizard, click on Discover, and check the checkbox corresponding to your Lotus Notes database name. In our example, the database name is LOTUSDOMINODB. Make sure you have unchecked all other checkboxes, if any are checked.

      Alternatively, you can click Add and provide the required Lotus database name in the Server Definition & Settings tabs.

    Figure 9. Create server definition for Lotus Domino datasource, Step 2
    Create server definition for Lotus Domino datasource, Step 2
    1. Click OK to create the server definition.
  3. Create the user mappings
    User mappings map one or more local user IDs and passwords to a remote data source user ID and password.
    1. Right-click the User Mappings folder under your server you just created, and click Create.
    Figure 10. Create user mapping for Lotus Domino datasource, Step 1
    Create user mapping for Lotus Domino datasource, Step 1
    1. Select the users from the Available local user IDs list and move them to the Selected user IDs list that you want to map to your data source ID.
    Figure 11. Create user mapping for Lotus Domino datasource, Step 2
    Create user mapping for Lotus Domino datasource, Step 2
    1. On the Settings tab, provide Remote user ID & Remote password field; in other words, the user ID and password for the federated database.
    Figure 12. Create user mapping for Lotus Domino datasource, Step 3
    Create user mapping for Lotus Domino datasource, Step 3
    1. Click Ok to create the user mappings.
  4. Create the nicknames
    A nickname is like an alias for a table or view. When you create a nickname, information about the remote object is stored in the local DB2 system tables. From then onward, DB2 acts as if the remote object is a local DB2 table.
    1. Right-click the Nicknames folder under your server definition you just created, and click Create.
    Figure 13. Create nickname for Lotus Domino datasource, Step 1
    Create nickname for Lotus Domino datasource, Step 1
    1. Click on Discover, fill in your Lotus Notes ID and password for the federated database, and click OK.
    Figure 14. Create nickname for Lotus Domino datasource, Step 2
    Create nickname for Lotus Domino datasource, Step 2
    1. Click Ok to create the nicknames.
    2. All set. You are ready to access your Lotus Notes database from InfoSphere Information Integrator. Right click on the nickname you have just created and select Query.
    Figure 15. Access data using ODBC wrapper: Create query
    Access data using ODBC wrapper: Create query
    1. Click on the Execute icon.
    Figure 16. Access data using ODBC wrapper: Execute query
    Access data using ODBC wrapper: Create query

Merging the data from Lotus Domino/Lotus database and XML

This section assumes that you have already created the XML wrapper for the XML data source with the following data:

Figure 17. Query results from XML wrapper
Query results from XML wrapper
  1. Click on the Commands tab and enter an SQL query that will select data from both data sources.
    Figure 18. SQL statement for merged data
    SQL statement for merged data
  2. Click on the Results tab to see the data merged from both of the data sources.
    Figure 19. Merged data from XML and Lotus Notes wrappers
    Merged data from XML and Lotus Notes wrappers

Accessing merged data in Java applications

This section focuses on how to access the merged data in Java applications using various JDBC drivers available with DB2. Here is the outline for this section, but if you are already familiar with JDBC technology, you can easily skip to the last sub-section, "Accessing merged data using Java":

What is JDBC?

Java database connectivity (JDBC) is an application programming interface (API) that Java applications use to access local or remote relational databases. But the JDBC 3.0 API goes beyond that, also making it possible to interact with other kinds of data sources, such as files that are outside of a database, accessing virtually any kind of tabular data. The JDBC API consists of a set of classes and interfaces that provide an industry-standard API for developers to write database applications using all Java code.

JDBC driver architecture and DB2 JDBC technology

Table 2. JDBC Driver architecture and DB2 JDBC technology
TypeDescription DB2 JDBC technology
Type 1JDBC-ODBC bridge: Drivers that implement the JDBC API as a mapping to another data access API, such as Open Database Connectivity (ODBC). Drivers of this type are generally dependent on a native library, which limits their portability.IBM does not provide this driver, and it is not a recommended environment because normally it provides a slower performance than the other driver types, although you can use the one provided by Sun Microsystems.
Type 2Partly Java Partly Native | App/Universal Driver: Drivers that are written partly in the Java programming language and partly in native code. The drivers use a native client library specific to the data source to which they connect. Because of the native code, their portability is limited. DB2 UDB provides two types of drivers in this category:
  • Legacy/CLI (Call Level Interface) JDBC Driver (App Driver)
    • db2java.zip
    • COM.ibm.db2.jdbc.app.DB2Driver
  • Universal JDBC Driver (Universal Driver)
    • db2jcc.jar
    • com.ibm.db2.jcc.DB2Driver
JDBC functions supported:
  • Connection pooling
  • Distributed transactions
  • Java user-defined functions and stored procedures
  • Most of the methods that are described in the JDBC 1.2 specification, and some of the methods that are described in the JDBC 2.0 specification
If you are connecting to a remote DB2 server, you should install the DB2 Application Development Client or the DB2 Runtime Client. (See Resources.) Note that the Legacy/CLI type 2 driver will not be enhanced by IBM in future versions.
Type 3Pure Java DBMS independent protocol | Applet/Net driver: Drivers that use a pure Java client and communicate with a server using a database-independent protocol, which is then translated to a DBMS specific protocol. The server then communicates the client's requests to the data source. Still supported, but it will not be enhanced by IBM in the future. Use type 4 driver instead, which provides better performance.
  • db2java.zip
  • COM.ibm.db2.jdbc.net.DB2Driver
Built on top of DB2 CLI (Call Level Interface). If you are connecting to a remote DB2 server, no DB2 UDB client needs to be installed at the client machine.
Type 4Pure Java DBMS dependent protocol | Universal JDBC driver: Drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source. The DB2 Universal JDBC driver is a single driver that includes JDBC type 2 (Partly Java Partly Native | App/Universal Driver) and JDBC type 4 (Pure Java DBMS dependent protocol | Universal JDBC driver) behavior, as well as SQLJ support -- it is independent of driver-type connectivity or target platform. It's an architecture-neutral JDBC driver for remote/distributed and local DB2 UDB access.
  • db2jcc.jar
  • com.ibm.db2.jcc.DB2Driver
JDBC functions supported:
  • Connection pooling
  • Distributed transaction management
  • Global transactions
  • Java user-defined functions and stored procedures
  • Most of the methods that are described in the JDBC 1.2 and JDBC 2.0 specifications, and some of the methods that are described in the JDBC 3.0 specifications.

Accessing merged data Using JDBC

Let's come back to our original purpose of this article. This section uses code snippets to demonstrate how to connect to the federated databases and how to access the data that we had merged previously from IBM Lotus Domino/Notes and XML data sources using various JDBC drivers.

Follow the steps/considerations in Table 3 to use the code snippet to access the merged data for different JDBC drivers. Before running any of the examples, make sure db2java.zip, db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar, and common.jar are added in the CLASSPATH environment variable.

Listing 1. Common class for all types of JDBC drivers
//----------------------------------------------
// Common class for all types of JDBC drivers
//----------------------------------------------

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExample {
	public static void main( String[] args ) {
		// Check number of args
		if (args.length < 4) {
			System.out.println("USAGE: java JDBCExample " +
				"<url> <user> <password> " +
				"<driver>");
			System.exit(-1);
		}

		String url = args[0];
		String user = args[1];
		String password = args[2];
		String driver = args[3];

		try {
			// Make database connection
			Class.forName(driver).newInstance();
			Connection con =
				DriverManager.getConnection(url, user, password);
			System.out.println(
				"\n::Successfully Connected::" + "\n\t" +
				"URL:\t\t" + url + "\n\t" +
				"JDBC Driver:\t" + driver
			);

			// Create a statement & execute
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(
				"SELECT * FROM SYSTEM.PERSONALINFO, SYSTEM.ORDER_NN"
				+ " WHERE SYSTEM.PERSONALINFO.ID = SYSTEM.ORDER_NN.ID"
			);

			System.out.println("\n::Query Output::");
			while ( rs.next() ) {
				String id = rs.getString("ID");
				String firstName = rs.getString("FIRSTNAME");
				String lastName = rs.getString("LASTNAME");
				String phoneNumber = rs.getString("PHONE");
				String address = rs.getString("ADDRESS");
				System.out.println("\t" + id + "|" + firstName + "|"
					+ lastName + "|" + phoneNumber + "|" + address);
			}

			stmt.close();
			con.close();
		}
		catch (Exception e) {
			System.out.println("ERROR:\n" + e.toString() );
			e.printStackTrace();
		}
	}
}

Conclusion

InfoSphere Information Integrator is designed to ease some of the complications of managing heterogeneous and geographically distributed data sources. This article has described how to configure InfoSphere Information Integrator to communicate with/access a Lotus Domino/Notes data source using an ODBC wrapper and also described InfoSphere Information Integrator's power to merge Lotus Domino/Notes data with an XML data source. Last but not least, it described how to access the merged data in Java applications using various JDBC drivers.

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, Lotus
ArticleID=98898
ArticleTitle=Configure IBM InfoSphere Information Integrator to access a Lotus Notes database
publish-date=11172005