Possibly unknown to new Notes users is the native data exchange feature that allows you to transfer data between DB2/400 and Notes. If you have Notes, you have this feature. Before you consider spending thousands of dollars to buy a product to handle your DB2/400 and Notes data transfer requirements, learn about the power of what is already available to you at no additional cost.

Most AS/400 installations thinking of using Domino for AS/400 are likely thinking about data exchange between DB2/400 and Domino/Notes. After all, there’s all that legacy data waiting to be shared through Domino’s powerful groupware features and Internet/intranet delivery capabilities.

A number of great data transfer products are available for Domino/Notes. Some of these products include Lotus NotesPump (see “Lotus NotesPump: A High-speed Data Pump for Your Enterprise,” MC, April 1999), soon to be known as Lotus Enterprise Integrator (LEI); DataMirror SQL Pump; Casahl Replic-Action; and Percussion Software Notrix. These products perform more than simple data transfer but carry a price tag you may not be able to justify. The good news is that Notes already has data transfer capabilities, so you may not need to spend money on additional software.

The material here shows a sample of how to use the powerful native Notes data transfer feature LotusScript Data Object (LS:DO). LS:DO requires an ODBC driver to connect to the AS/400. The Client Access/400 ODBC driver might be the most readily available, but there are others. The sample presented here imports data into Notes from DB2/400 but could also import data from any ODBC-compliant relational database.

LotusScript Data Object (LS:DO)

Before I describe LS:DO, a little information about LotusScript might help. LotusScript is a superset of Visual Basic (VB) made available in Notes Release 4. The syntax of LotusScript is similar to VB save one significant difference: LotusScript’s use of classes. LotusScript performs much of its work through objects created through a set of Notes classes that define such Notes components as databases, documents, and Views. Almost everything in a Notes application is some type of object containing properties and

methods defined by the Notes classes, so LotusScript is, to a degree, an object-oriented (OO) language.

Learning the syntax of LotusScript is easy, but learning to work with Notes classes takes more work. With LotusScript, you’ll quickly find yourself in the world of classes and objects. In fact, one advantage of LotusScript programming to those inexperienced in OO technology is that it teaches the application of OO programming concepts while avoiding the need to create classes from scratch. You’ll be working with a set of classes Lotus has already created, providing a good introduction to OO technology.

With LS:DO, you can use all the programming logic you want in your data transfer. For example, you can submit complex selection criteria, manipulate data during transfer, and apply business rules. Also, LS:DO can return more than one column of data and allows you to execute more than one SQL statement with the same connection.

However, along with the power of LS:DO comes the required knowledge of LotusScript syntax and how to work with Notes classes. LS:DO is made up of three LotusScript classes: ODBCConnection, ODBCQuery, and ODBCResultSet. These classes have many properties and methods that can retrieve data from and update data to external relational databases. As the class names imply, they use ODBC, which, as previously mentioned, means you need an ODBC driver. If you already have experience with ODBC, you may recognize many of the properties and methods used by these ODBC classes.

Before you can use ODBC in an application, you must define an ODBC data source name (DSN) for the system from which your application is to retrieve data. You define DSNs through the ODBC Data Source Administrator. On the Windows platform, you should find the ODBC Administrator in the Control Panel application group, normally with an icon labeled 32bit ODBC.

DSN configuration is simple. Just identify the ODBC driver to be used to establish a connection to the system where the data resides. Figure 1 illustrates the portion of the DSN definition using the Windows ODBC Administrator where the Client Access ODBC driver is selected to connect to the AS/400. During configuration of the Data Source, check the Translate CCSID 65535 option found on the Translation tab. For more information about DSN configuration, use the context-sensitive Help button presented by the ODBC Administrator application.

In addition to the ODBC DSN definition, your Notes application must load a LotusScript extensions (LSX) file. This file contains Public definitions for ODBC. In the form where you intend to import data from DB2/400 or any ODBC-compliant database, insert the following statement below the Option Public statement in the Globals design element:

Uselsx “*LSXODBC”

You can find the Globals design element in the design elements list near the top left of the Design pane at the bottom half of the window when you’re editing the form (see Figure 2).

In the top portion of Figure 2, you can see the form used by the sample application. It retrieves the customer’s city and state from an AS/400 file when the user clicks the Retrieve City&State button. The import function is accomplished through LS:DO code, which is linked to the button’s click event. When the user clicks the button, the code in Figure 3 executes.

In LotusScript code, connection to the AS/400 is made through the statement status = con.ConnectTo(“MC170”). The parameter value “MC170” identifies the DSN defined for the AS/400. Notice a similar connection statement following the statement just mentioned. This second connection definition (actually a comment) illustrates how you connect to DB2/400 from a Notes database residing on your AS/400 Domino server (the sample created and used here resides on a client PC). As you can see, no DSN value is specified. Instead, the AS/400 system name and user ID and password are used. When you

use LS:DO with a database that resides on the Domino for AS/400 server, an ODBC DSN and ODBC driver aren’t actually required. You do, however, use the same ODBC connection class methods. In this case, the ODBC calls are actually intercepted by OS/400 and made directly to DB2/400.

This example illustrates how easy it is to make a connection and get data from DB2/400; it is not meant as an example of all the things you can do with LS:DO. With LS:DO, you can perform some powerful data transfers, including data conversions and even data replication.

Such a Deal

Lotus Notes has some pretty powerful built-in data exchange features for those who need to transfer data to and from DB2/400 or other relational databases. If you have an ODBC driver (e.g., the Client Access ODBC driver) and some technical skill, you might be able to implement these features without spending a dime on additional software. This is not to say you shouldn’t avail yourself of other data exchange products out there; they may give you the features you want and reduce the need for in-house software development. In any case, check out what you can do with Notes’ native data transfer features before purchasing more software. You may find exactly what you need.

Figure 1: You must define an ODBC data source name and identify the ODBC driver that is to be used to establish a connection to your AS/400.

Figure 2: Here in the Notes form design session, the Global design element is selected, and the LS:DO LotusScript Extension (LSX) is defined for it.

Figure 3: This LotusScript code updates the City and State in the form in Figure 2 from data that resides in an AS/400 file.