DB2/400 data can be accessed from the Lotus Notes/Domino environment in many different ways. These options include using @DB commands, writing an agent in LotusScript Data Object (LS:DO) or Java, coding a servlet, using one of the visual mapping tools such as Domino Enterprise Connection Services (DECS) or Lotus Enterprise Integrator (LEI), or writing a Lotus Extension (LSX) for Lotus Domino Connector (LC). In this article, I will focus on writing an LS:DO agent to access DB2/400 data.

Agents are batch processes that run within the context of a Domino server. Agents can run either on the Notes client or the Domino server. The key here is that the agent must run in the context of Domino. An agent can be invoked in several ways. An agent can be triggered manually or via a URL, scheduled, or invoked through an event such as clicking a button, exiting a field, or opening a document.

An agent can be written in either the LotusScript or Java programming language. The choice depends primarily on the programming skills you have access to. I will show you how to code an LS:DO agent in LotusScript to access data in DB2/400. Watch for a future article that illustrates coding an agent in Java.

Overview of LS:DO

LS:DO is similar to Open DataBase Connectivity (ODBC). If you are familiar with ODBC, coding to LS:DO will be easy for you. LS:DO is one of the LotusScript Extensions. It provides a set of object-oriented classes to allow access to back-end databases and applications. LS:DO provides full read and write access to external ODBC data sources using the control and flexibility of a structured programming language.

LS:DO Architecture

Three classes comprise the LS:DO architecture: ODBCConnection, ODBCQuery, and ODBCResultSet. I’ll take a closer look at each of these classes.

The ODBCConnection class allows you to establish a connection. In this case, you will be connecting to a back-end system. It also allows you to access some database catalog information, such as data source lists, table lists, and procedure lists. A number of properties can be set for this class, and a variety of valid methods can be used with it. In

the code sample in this article, the ConnectTo() method will be used with the ODBCConnection class.

The ODBCQuery class holds the ODBCConnection object—in which a connection is established—and an SQL statement. The three properties associated with this class are Connection, QueryExecuteTimeOut, and SQL. In this example, the connection property is set to associate the query with the ODBCConnection. Next the SQL statement being sent to the AS/400 is defined by setting the SQL property.

The ODBCResultSet class is used to work with the results returned from the SQL statement. This is done by setting the Query property on the ODBCResultSet to associate the SQL statement defined in the ODBCQuery object with the result set. Several methods can also be invoked on the ODBCResultSet class. The Execute() method executes the SQL query. Once the query has been executed, a variety of methods can be called to navigate through the returned result set. These methods include result set row navigation and location, accessing column values, result set row modification operations, column attribute operations, and SQL parameter operations. In the example, the GetValue() method, which is part of the accessing column value operations, is used.

LS:DO in an AS/400 Environment

LS:DO does not need an ODBC driver to access DB2/400 tables as long as the code runs on the server rather than on a client. No ODBC driver is needed to access DB2/400 data from Domino for AS/400.

If the agent is called from a Notes client environment, an ODBC driver needs to be installed on that client to interface with the AS/400. However, if the agent is called from a Web browser, no ODBC connection is needed. In this case, the ODBC layer is bypassed, and the agent interfaces directly with the Call Level Interface (CLI). This provides both a performance boost and ease of setup. Refer to DB2 for OS/400 SQL Call Level Interface for details on CLI. CLI does not have an ODBC.ini file. Instead, the user registers the data source on the AS/400 using the Work with Relational Database Directory Entry (WRKRDBDIRE) command. If a directory entry does not exist with a Remote Location value of *LOCAL for your AS/400, use the Add Relational Database Directory Entry (ADDRDBDIRE) command to add one. Think of the relational database directory entry as a label for your AS/400. This label is passed in to the ODBCConnection class to identify the data source that represents your AS/400.

LS:DO Example

The code sample provided here is for an LS:DO agent that is invoked when a user provides a valid customer number on a Lotus Notes form and then presses the Retrieve Customer Data button. Clicking on this button triggers the LS:DO agent. The agent uses the customer number provided on the form and constructs an SQL statement to retrieve the corresponding customer data from DB2/400. The values returned from the SQL select statement are then put into the corresponding fields on the Domino form.

Take a look at the Domino application in Figure 1. The application consists of one form with seven text fields: customer number, last name, initials, address, city, state, and zip code.

The application user inputs the customer number and then clicks on the Retrieve Customer Data button to retrieve the information. When the Retrieve Customer Data button is clicked, the LS:DO agent is invoked. The agent connects to the DB2/400 data source and retrieves the last name, initials, address, city, state, and zip code fields for the corresponding customer number that has been provided. The data will be retrieved from the AS/400 file QCUSTCDT in library QIWS.

To trigger execution of the agent, some code needs to be added to the Retrieve Customer Data action. Do this by adding the following @Command to the Click event associated with the action (Figure 2).

'This @Command will call the agent, Read Customer LSDO.
@Command([ToolsRunMacro];"Read Customer LSDO")

It’s time to delve into the code that comprises this agent. The code body of the agent has six steps:

1. Load the LSX

2. Declare new classes

3. Establish a connection

4. Define the SQL statement

5. Retrieve the result set

6. Work with the result set

Portions of the agent code are used here to illustrate the essential steps in retrieving data with LS:DO. The complete code can be found on the Midrange Computing Web site at www.midrangecomputing. com/mc.)

Step 1: Load the LSX

This first step makes the ODBC classes available to the programming environment by loading the classes. This is accomplished through the Uselsx “*lsxodbc” statement. This statement must be placed in the Options event of the Global object.

Option Public
Uselsx “*lsxodbc”

Once the classes are loaded, continue with the bulk of the code. This code is placed under the Initialize event of the Global object.

Step 2: Declare New Classes

Now that the ODBC classes are loaded, create objects from the three classes that comprise the LS:DO architecture. This can be accomplished with Dim variablename As New classname statements, which are indicated in blue. Create a new ODBCConnection object, con, a new ODBCQuery object, qry, and a new ODBCResultSet object, res.

Sub Initialize
‘ Declare a new instance of each class
‘ using the New method

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim res As New ODBCResultSet

Once new objects are created from these classes, additional objects are created that allow you to work with the current front-end document, i.e., the document the user is working with via the form.

‘Setup to work with the current document

Dim ws As New Notesuiworkspace

Dim uidoc As notesuidocument

Set uidoc=ws.currentdocument

Step 3: Establish a Connection

Now that the initial setup has been done, you are ready to connect to the back-end data store. Do this by calling the ConnectTo() method on the ODBCConnection object (con) as illustrated in Figure 3 (page 82). The ConnectTo() method requires three parameters:

• The name of the AS/400 data source. This can be found by issuing the command WRKRDBDIRE and noting the Relational Database name specified with a Remote Location value of *LOCAL. In this case, the Relational Database name is SYSTEMA.

• A valid AS/400 user ID. In this example, I’m using DOMINOUSER.

• A valid AS/400 password for the user ID you have supplied. I’m using DOMINO1 in this example.

Note that the Relational Database directory entry, the AS/400 user ID, and password are all uppercase and are contained in quotes (“ ”). These parameters are case- sensitive and need to be specified in uppercase.

Check that a valid connection has been made with some code similar to that found in Figure 4.

Step 4: Define the SQL Statement

Once you have a successful connection to the AS/400 data store, you are ready to prepare the SQL statement that you will use to retrieve the correct fields based on the customer number provided by the application user. Build an SQL statement based on the customer number provided by the user (uidoc.fieldgettext(“CustomerNumber”)).

Two steps define the SQL statement. First, the Connection property must be set on the ODBCQuery object to associate the query with the ODBCConnection.

‘ Code to build
‘ SQL query statement

Set Qry.Connection = Con

Now you can set the SQL property on the ODBCQuery object to define which SQL statement will be sent to the AS/400 (Qry.SQL = “select...”). Any valid SQL statement can be specified here: a select, update, insert, or delete. The agent is reading data from the AS/400 DB2/400 file, so a select is specified (see Figure 5). Check that the SQL statement is valid with some code similar to that found in Figure 6.

Step 5: Get Result Set

Now you are ready to retrieve the result set that is returned from the query statement. This step involves associating the result set with the query, which is done by setting the Query property on the ODBCResultSet object (Set res.Query = qry). Next, execute the query by calling the Execute method on the result set (res.Execute).

‘Code to execute SQL query
‘ and get results set

Set res.Query = qry

res.Execute

Check for any errors with the Query execution with code similar to that found in Figure 7.

Step 6: Work with the Result Set

Now you are ready to work with the virtual table that is returned as the result set. Several methods can be used to access column values in the result set that is returned from the SQL statement. These methods include GetValue(column, [variable]), IsValueAltered(column), IsValueNull(column), and SetValue(column, value) are used to access specific column values to check column properties. Use the GetValue() method to retrieve the values that have been returned (res.GetValue(“lstnam”)). The call to the GetValue method is passed as a parameter to the Fieldsettext() method that is called on the notesuidocument object (uidoc) that you created in step 2 (Figure 8).

By calling the Fieldsettext() method on the uidoc object, you can place the returned results to the correct fields on the form. Fieldsettext() takes two parameters. The first parameter is the field name on the Notes form, and the second parameter is the value returned from the DB2/400 table based on the field name specified such as lstnam or init.

You have now returned the customer data from DB2/400 and displayed it to the user, so put the cursor back in the Customer Number field, close the result set, and disconnect from the data source (Figure 9).

How to Trace and Debug LS:DO

Chances are your agent will not be 100 percent error free on first execution, so you need to know where to look for error messages. Look at the log associated with the agent by clicking on the agent and selecting Agent/Log from the pull-down menu in Domino Designer. A more helpful place to look though is in the Agent Manager job log on the AS/400. Look for the name AMGR/QNOTES/nnnnnn where nnnnnn is the job number of the agent you ran.

You can also debug your agent by selecting File/Tools/Debug LotusScript from the pull-down menu in the Domino Designer client or the Notes client. This turns on the LotusScript debugger. Now trigger your agent again, and the debugger lets you step through your code.

More Resources on LS:DO

Coding an agent in LS:DO is fairly straightforward. The three classes, ODBCConnection, ODBCQuery, and ODBCResultSet are detailed very well in the Redbook Enterprise Integration with Domino.Connect. All of the properties that can be set for each of the classes along with the numerous methods that can be invoked on each class are outlined in Chapter 4: LotusScript Data Objects and ODBC. For information specific to implementing LS:DO on the AS/400, see Chapter 5: LotusScript: Data Object in Lotus Domino for AS/400: Integration with Enterprise Applications.

I hope you have found this article to be helpful in your venture to access DB2/400 database files from the Domino environment. I’ll leave you with some helpful Web sites for you to visit. Iris Associates’ www.notes.net contains links to documentation on LS:DO. You can view the help database online or download it to your Notes client. Expand the Designer link at www.notes.net/notesua.nsf/Task?OpenView to access the help files. Don’t forget about the Domino Enterprise Integration Web site, www.lotus.com/dominoei. This Web site is invaluable for information about connecting into back-end systems from the Domino environment. For AS/400-specific information, refer to either the PartnerWorld for Developers AS/400 Domino site at www.as400.ibm.com/ developer/domino, or check out the Lotus Domino for AS/400 Web site at www.as400.ibm.com/domino. Happy surfing!

REFERENCES AND RELATED MATERIALS

• DB2 for OS/400 SQL Call Level Interface, SC41-4806

Figure 1: The Domino application populates this form with DB2/400 data.

Figure 2: Call the LS:DO agent through an action button.

Call con.ConnectTo(“SYSTEMA”,”DOMINOUSER”, “DOMINO1”)

Figure 3: Connect to the AS/400.

'Check to see if connection was successful, if not print out error message by calling the
'GetExtendedMessage method on our connection object

If con.geterror<>DBstsSuccess Then

Messagebox con.GetExtendedErrorMessage,, "Could not make

connection to AS/400 system"

Exit Sub

Else

End If

Figure 4: Inform the user of a failed connection.

'Get Customer Number from Cusnum field

Qry.SQL = "select * from QIWS.QCUSTCDT where Cusnum="+

Uidoc.fieldgettext("CustomerNumber")

Figure 5: Build the SQL statement.

'Check query to see if SQL statement built successfully,
if not print out an error message and
'Disconnect by calling the Disconnect method on the ODBCConnection object (con)

If qry.geterror<>DBstsSuccess Then

Messagebox qry.GetExtendedErrorMessage,,"SQL Error"

con.Disconnect

Exit Sub

Else

End If

Figure 6: Inform the user of an invalid SQL statement.

' Check for errors and if there are any, we print out a valid error message by calling
'The GetExtendedErrorMessage method on the ODBCResultSet object (res)

If res.GetError <> DBstsSuccess Then

Messagebox res.GetExtendedErrorMessage,,"Result Error"

res.Close(DB_CLOSE)
con.Disconnect
Exit Sub

End If

Figure 7: Inform the user if any errors have occurred with the result set.

'First we check to see if there is a result set

Else

If Not res.IsResultSetAvailable Then

Messagebox " No values found for your query"
'Now we process the result set using the GetValue() method on our ODBCResultSet, res

Else

Call uidoc.Fieldsettext("LastName",res.GetValue("lstnam"))

Call uidoc.Fieldsettext("Initials",res.GetValue("init"))

Call uidoc.Fieldsettext("Street",res.GetValue("street"))

Call uidoc.Fieldsettext("City",res.GetValue("city"))

Call uidoc.Fieldsettext("State",res.GetValue("state"))

Call uidoc.Fieldsettext("ZipCode",res.GetValue("zipcod"))

End If

End If

Figure 8: Set the Notes document field values from the value of the result set.

'Put cursor back in Customer Number field

Call uidoc.GotoField("CustomerNumber")
'close result set - it is very important to remember to close our result set

res.Close(DB_CLOSE)
'disconnect from data source - again, a vital step

con.Disconnect

How to Read AS/400 Data Using a Notes Agent 1

Figure 9: Close the result set, and disconnect from the data source.