Offloading Lotus Enterprise Integration tasks to the server

This article discusses several different ways of dealing with this problem by moving the actual database work to the Domino server – either in real time or in batch mode.

Share:

Andre Guirard (Andre_Guirard@us.ibm.com)), EI Product Developer, EMC

Andre Guirard is a member of the Enterprise Integration team of IBM Lotus Software, the developers of Lotus Enterprise Integrator (LEI) and other products that let you connect disparate data sources with each other and with Lotus Notes. Andre has made occasional appearances as a speaker at IBM conferences, and his articles have previously appeared on LDD, as well as in The View magazine and elsewhere.


developerWorks Contributing author
        level

05 January 2004

Also available in Japanese

Lotus Notes lets you use several different programming interfaces to access relational data. These include the Lotus Connectors LotusScript Extensions (LC LSX), the Notes formula language functions @DbColumn/@DbLookup with ODBC, the LotusScript Data Object (LS:DO) with ODBC, and JDBC. However, developers who want to use these tools for live data access in an application may run into a big administration problem: the need to set up every user workstation with connectivity to the relational databases. This article discusses ways of dealing with this problem by moving the relational database work to the Domino server, either in real-time or in batch mode. We cover several techniques:

  • LEI replication or a custom-programmed replication to copy reference information into Notes and to keep it up-to-date.
  • Scheduled agents that accomplish Enterprise Integration tasks in batch mode
  • DECS or LEI "Virtual Fields" activities to make "live" reference data available via a normal @DbLookup

Server agents invoked remotely from the Notes client to retrieve or update information Which technique you use depends on the requirements of your application. To keep things simple, we’ve prepared a single application example implemented in several different ways.

Accompanying this article is a sample Lotus Notes database containing the code we discuss. This application is designed to run on Domino 6.0 or later; however, you can adapt the techniques for use in R5. Our examples use @DB commands and LC LSX. The specifics of LC LSX programming are beyond the scope of this article, but the recently published Redbook, Implementing IBM Lotus Enterprise Integrator 6 contains a good introduction to the subject. This Redbook also covers techniques for managing virtual documents and keeping virtual fields "key" documents up to date.

This article assumes that you're an experienced Notes application developer.

Setting up

The LC LSX code in this article is designed to run on a Domino server. To edit these scripts in Domino Designer or to run them locally, you must have the LC LSX installed locally on your workstation. If the file nlsxlc.dll is not already present in your Notes program directory, run the Notes client installer, select Modify, and enable installation of Domino Enterprise Connection Services (DECS). To prepare the sample application for use, download it, install it on a Domino server where DECS or LEI is active, and sign the design with an ID that’s authorized to run unrestricted agents on that server.

The application uses a relational table which it can create for itself. First, you must create a DECS or LEI connection document to a relational database that you can use as a test area (for example, Microsoft Access). Then, run the Create Sample Data agent. It prompts you to select the connection and to enter the name for the new table. This agent is an example of off-loading processing to the server—you don't have to connect to the relational database from the Notes workstation where you run the agent.

The original application

The application is a work order for a company that does custom graphics design. When an order is received, the order taker uses the Notes client to fill in a form detailing the customer’s requirements, using a Notes rich text field to include any preliminary sketches or other material supplied by the customer:

Figure 1. Sample application work order
Sample application work order

The customer information in the grey shaded area is stored in a separate relational database. The application uses @DbColumn(ODBC) to retrieve the keyword list of customer account numbers. When the user chooses an account number, the form uses @DbLookup(ODBC) to retrieve the field values. For instance, the formula for the keyword lookup is:

Listing 1. Keyword lookup formula
				If(!@IsDocBeingEdited;
   @Unavailable;
   @Text(
      @IfError(@DbColumn("ODBC":"NoCache"; "Offload Sample"; "userid";
      " password " ; "Customers" ;  "ID":"Discard"; "Ascending");
      "No customer information available|") 
) )

and for the CustName computed field:

Listing 2. CustName computed field formula
				@If(Customer = ""; 
   "";
   @IfError(
      @DbLookup("ODBC":""; "Offload Sample"; "userid"; "password";
      "Customers"; "CustomerName"; "ID"; @ToNumber(Customer));
      "unknown"
) )

In our example, each user must have the ODBC data source Offload Sample defined on his or her computer. The only way to eliminate the administrative overhead and performance cost of giving each user a connection to the database is to move the relational database work off the computer.


Using replication to duplicate reference data

If you have LEI installed, you can use a replication activity to have the server copy the reference information from the relational database into Notes. Then write your lookups against the Notes database instead of going directly to the relational database. If you use timestamp replication, you can also arrange for Notes users to update the relational information by editing Notes records.

If you don’t have LEI installed, you can write your own custom replication in LotusScript using the LC LSX from a scheduled server agent. The Replicate Customers agent shows how to perform a simple "primary key" replication in LotusScript. This doesn’t give you all the options of LEI replication, and it’s not as fast, but it gets the basic job done.

Regardless of how you replicate the data, the following design elements in the sample database show how the replicated data are used:

  • WorkOrder2 form is a version of the original work order form that uses Notes @DbColumn and @DbLookup instead of the ODBC versions of these commands.
  • CustomerRecord form contains fields that are mapped to the columns in the Customers relational table. A CustomerRecord document in Notes corresponds to one row in the Customers table.
  • WorkOrder2 form uses the (Customers) view to read the keyword list of customer numbers and to look up detailed information for a particular customer.
  • (CustReplicate) view is created automatically by the LEI replication activity. It contains the CustomerRecord documents sorted by their ID field.

Because all the data is in Notes, the WorkOrder2 form is just straightforward Notes design. For instance, the keyword formula is now:

Listing 3. Keyword formula for the WorkOrder2 form
				If(!@IsDocBeingEdited; 
   @Unavailable;
   @Text(
      @IfError( @DbColumn("":"NoCache"; ""; "(Customers)" ;
      2) ; "No customer information available|")
) )

The (Customers) view contains three columns:

  • Column 1 includes just the ID field (the customer number) for sorting.
  • Column 2 calculates the keyword values. For ease of use, we define a keyword synonym that displays the customer name, but stores only the number.
  • Column 3 includes all the other fields in a multivalue list so that they can be read with just one @DbLookup instead of the nine required for the original version of the form. We could have done this with the original example also by using a relational view with computed columns. That would have improved performance, but not solved the main problem of configuring user workstations.

Using LEI replication to make a copy of the data also lets mobile users operate completely off-line. Because their local replicas contain all the data they need to get their work done, they can review or enter orders while sitting in the customer’s office without needing to dial in. And though we didn’t do that here, if you set up your application to replicate both ways, off-line updates to the Notes customer data get written into the relational database eventually.

The downside is that the data in the Notes records—and especially in users’ local replicas—is not necessarily up to date with the relational database. It may not make sense to use replication in cases where the relational data are changing frequently and consistency is critical.

Also, of course, the Notes database containing replicated information is a larger file than the same application that references the information "live" from the relational database. In the case of our small customer database, this is not an issue. But if your relational databases contain millions of records, you could have a very large and potentially very slow Notes database.

Note that you need to be very careful setting up your replication activity, so that the record lists in the two data sources are processed in the exact same order. If not, it’s easy to get a lot of excess inserts and deletes. This is a real performance killer in Notes, especially for mobile users who replicate their databases by phone line. See the Lotus Tech Note, Implementing IBM Lotus Enterprise Integrator 6.


Live access using DECS/LEI virtual fields

To avoid storing quite so much information in the Notes database (and to make sure your lookups return up-to-date information), you can use DECS or LEI to virtualize fields of the customer reference records. This lets you store only the key fields in Notes (ID in this case) and any other fields only if you choose to. When you use @DbLookup to read fields from the documents, the Virtual Fields activity on the server supplies live information from the relational database. The Virtual Fields activity is filled in as shown below. Note that the Virtual Fields activity was referred to as RealTime Notes in versions prior to 6.0.

Figure 2. Virtual Fields Activity
Virtual Fields Activity

The VFCustomerRecord form is the same as CustomerRecord from the last section, but with a different name. Because we’re not interested in letting users of the work order database edit the customer information, we only monitor the Open event.

With replicated documents, we can use a Notes view to concatenate field values and to read them with a single lookup. That’s not possible with virtualized fields because they don’t show up in views. However, there is an alternate way to get the same effect. In the Event Options tab of the Virtual Fields form, you can enter Notes formulas that execute after the virtual fields are read. In this case, we choose to add a couple of calculated field values for use in lookups:

Figure 3. Post-Open formula
Post-Open formula

In the preceding illustration, we have entered the following into the Post-Open field:

Listing 4. Post-Open field formula
FIELD Keywords := @Text(ID) + "." +@Trim(@ReplaceSubstring(CustomerName; "," ; " "))
+ "|" + @Text(ID); FIELD LookupValue := CustomerName : BillingAddr1 : BillingAddr2 :
BillingCity : BillingCountry : BillingState : BillingZip : MainPhone : Sector; 0

Whenever you access a document on the server that uses the VFCustomerRecord form, the Virtual Fields activity is triggered, reading the field values from the relational table (and in addition, using those values to calculate the fields Keywords and LookupValue). These values are not stored in Notes; they’re always calculated "live" when the document is accessed. You can read their values using @DbLookup.

The fact that Notes fields don’t show up in views means that you can’t read them with @DbColumn. However, @DbLookup works if you read a field value instead of a column value. The sample database shows how to use a categorized view (VFCustomersKeywords ) to get a keyword list by reading the Keywords field out of each document in the view. The first column contains a constant value ("All") and is sorted. This lets you read the same field out of each document with this keyword lookup formula:

Listing 5. Keyword lookup formula: reading the same field from each document
				@If(!@IsDocBeingEdited; 
@Unavailable;
   @IfError(
   @DbLookup("":"NoCache"; ""; "(VFCustomersKeywords)"; "Al""; "Keywords"); 
      "No customer information available|"
) )

The formula refers not to the ID field, but to the Keywords field defined in the Post-Open formula of the activity document. We use a field name, not a column number, because this information does not appear in the view. (Be aware that @DbLookup sometimes reads information from a view column even if you give a field name instead of a column number. This happens if there’s a column that contains only that field value. Since virtualized fields don't appear in the view, you should avoid that situation in cases like this.)

When the user selects a customer ID, the application fills in the customer information fields by using @DbLookup in a different view (VFCustomersByID) sorted by the ID field. As with the original form, however, there’s a performance issue if you have to perform nine lookups to retrieve each of the nine virtualized fields. So we use the LookupValue field calculated by the Post-Open formula to read nine fields with a single lookup.

Because you don’t have to store copies of all the fields in the reference documents, Virtual Fields makes for a smaller NSF file (though performance issues remain if you have a large number of "key" documents). It also insures that the information is up-to-date for all users of the server replica.

When mobile users create a local replica, they receive the complete reference documents. So these users can still use the application off-line. However, as with replication, their information is not necessarily up-to-date. Also, because their copies of the records contain all the fields (not just the key fields) the NSF file may be considerably larger than the one on the server. They may want to use selective replication to receive only the records they need. .

If the data in the relational database is modified in a way other than through the Notes client, the modification date/time of the Notes document doesn’t change. Unless the Notes documents are also modified, users with local replicas do not receive updates to virtual fields in existing records when they replicate, unless the corresponding Notes documents also have been modified. If this is a problem, you can do the following:

  • Use LEI replication instead, as described in the previous section.
  • Or, if there’s a "last modified" timestamp in the relational record, use the LC LSX to compare it with the last modified time of the Notes document. If the relational record was modified more recently, save the Notes document without making any changes. This makes everyone get a new copy of the data the next time they replicate.
  • Or, if you have a replica of the Notes database on another server, create a server agent to compare the values in the two replicas. Resave any document in the original database that shows a difference from its replica copy.

Live access using LEI virtual documents

If you run LEI 6.0 or later, you can use a Virtual Documents activity to create Notes lookup records that are stored entirely in the relational database. This requires either extra fields in the relational table to store Notes header information or a separate table that’s related to the data table with key fields. .

The Notes design is the same as the WorkOrder2 form and (Customers) view. The Virtual Document activity document in the LEI Administrator database is similar to the Virtual Fields activity shown previously. Because there’s little change from earlier examples, we don’t provide a separate example for this approach. .

The advantages to using virtual documents are:

  • Users of the server replica always have up-to-date information.
  • Virtual documents can be replicated to local replicas, allowing off-line access for mobile workers. Updates to existing documents replicate also.
  • No special action needs to be taken to make new relational records show up in Notes. (With virtual fields you need a process to generate new key documents.)
  • Virtual document fields can be used in views, so there’s no need for extra calculated Post-Open fields (though you can use them if you want).
  • Because none of the information is stored in the NSF file, you can have lots of records without using so much space on the Domino server. (Note, however, that view indexes and the full-text index may still be large).

One thing to note when using virtual documents: If the relational data are modified by external applications, these applications need to update a timestamp field. Otherwise, the Domino server will not know that the record has been edited. This prevents the changes from showing up in views unless the view index is completely rebuilt (press Shift+F9), and it also causes replication of updates to local replicas to fail.


Calling server agents remotely

The final technique we'll discuss makes use of the NotesAgent.RunOnServer method to make the Domino server perform the Enterprise Integration work and then return the result to a LotusScript program running on the client. This technique is most appropriate when there’s significant or complex processing to be done beyond the capability of DECS or LEI activities and @DbLookups. In practice, it’s more work than we want to do for our simple example form, but we’re going to do it anyway as a demonstration of the technique.

The key to making this work is the ability to pass information to the server agent and to receive information back. The RunOnServer method accepts an argument that is the note ID of a parameter document. The code running on the workstation stores field values in this document that tell the agent what it should do, and the agent can write results to the same document.

For best performance of your application, particularly if this server process is called often, you should not create a new parameter document each time and delete it when done. This leaves a lot of deletion stubs behind, which slows view indexing and replication. On the other hand, you also shouldn’t have only one parameter document, unless you’re certain that only one user at a time will use the server agent. Otherwise, users may interfere with each other. A good compromise is to have a separate parameter document for each user who runs the server processing, and do not delete them when the server agent is done. If they contain confidential information, such as passwords, you may want to have the server agent erase that information after it’s been read.

Creating or locating the parameter document isn’t entirely simple. However, the script library RunOnServerCall in the sample database contains a reusable function named GetParameterDoc that performs this task for you.

A word of caution: Don't be tempted to use a profile document as your parameter document. Profile documents are cached in memory, so it’s difficult to make your code notice when changes have been made by another process. Use a Notes document instead.

Because a Notes document can contain fields with any name your code chooses, you don’t need a form for the parameter document, unless you want to display it to the user as a way of giving them the agent result. You can just make up field names and use them in your code.

The WorkOrder4 form contains the calling end of the code in this example; it calls the RemoteLookup agent on the server. We chose the following field names in the parameter document to send data to the agent:

  • Connection (string) is the name of a DECS/LEI connection from which data are to be read.
  • Condition (string) is an SQL where clause to select the records to be returned.
  • Fieldnames (string) contains the names of the fields that are to be read with commas between. Leave blank to read all fields.
  • Ordernames (string) is the SQL order by clause of a Select statement.
  • Limit (number) is the maximum number of rows to be read.

If the agent encounters an error, it stores an error message in the Status text field of the parameter document. Otherwise, it sets Status to "" (empty string) and creates a Notes field for each field read from the relational table with the same name as the table column. If multiple rows were read, the Notes field is multivalued. Again, these choices are arbitrary, dictated by the needs of the application. So, at the calling end, here’s the code to read a list of keyword values:

Listing 6. Code to read a list of keyword values
				Use "RunOnServerCall"
Function LoadKeywords(uidoc As NotesUIDocument) As Boolean
   ‘returns True if the keyword load succeeded, else shows error
   ‘message and returns False. 
   On Error Goto errortrap
   Dim session As New NotesSession
   Dim db As NotesDatabase
   Dim agent As NotesAgent 
      Dim docParm As NotesDocument

   Set db = session.CurrentDatabase 
      Set agent = db.GetAgent("RemoteLookup")
      Set docParm = GetParameterDoc(session, db)
      docParm.Connection = "Offload Customers"
   docParm.Limit = 1000
   docParm.Condition = "" 
      docParm.Fieldnames = "ID,CustomerName"
   docParm.Ordernames = "ID" 
      docParm.Save True, False, True
      If agent.RunOnServer(docParm.NoteID) [not equal] Then
         Msgbox "Cannot run agent!" 
   Else
            Set docParm = GetParameterDoc(session, db) ' reload
            If docParm.Status(0) [not equal] "" Then
               Msgbox "Server agent error: " [ampersand] docParm.Status(0)
            Else
               uidoc.Document.KeywordChoices = Evaluate( _
{ @Text(ID) + ": " + CustomerName + "|" + @Text(ID) }, docParm)
         End If
   End If
   LoadKeywords = True
   Exit Function
 
ErrorTrap:
         Msgbox Err, Error [ampersand] " // LoadKeywords:" [ampersand] Erl
Exit Function
End Function

The function GetParameterDoc locates or creates a document to pass information to the agent. We assign the fields that tell the agent what information we want, then save the document so that the agent can see the changes. If the agent returns a successful result, we read the document and use the fields it set (ID and CustomerName) to update a field in the current document. Here’s the agent code to retrieve the parameter document:

Listing 7. Agent code to retrieve the parameter document
				Sub Initialize
   Dim lcses As New LCSession 
      lcses.ConnectionPooling = True
   Dim session As New NotesSession 
      Dim db As NotesDatabase
   Dim agent As NotesAgent
   Dim strNoteID As String
   Dim docParm As NotesDocument 
 
      ' get information about the environment.
   Set db = session.CurrentDatabase 
      Set agent = session.CurrentAgent
   strNoteID = agent.ParameterDocID 
 
      ' locate the document that has the parameters we need to do our stuff. 
   On Error Goto ErrorTrap 
      Set docParm = db.GetDocumentByID(strNoteID)
   If docParm Is Nothing Then 
         Print "A valid parameter document was not supplied."
         Exit Sub
   End If

Then the agent reads the fields in docParm to get the value of the Connection, Condition, and other fields assigned by the caller. You may find it useful to look at the agent code to see how it does its database access task, but that goes beyond the scope of this article.

It's also possible to use the current document as your parameter document (if you’ve saved it with the fields the agent looks for). However, the NotesSession.DocumentContext property and the NotesDatabase.UnprocessedDocuments property do not retrieve the current document. The server has no way to tell which document is open on your workstation, unless you use ParameterDocID to tell it.

RunOnServer requires that you use a server replica of the agent. If the user is in a local replica, but has connectivity to the server, you must find the server replica so that you can use the agent and parameter document in that replica. Some processes may need to run on a particular Domino server (for example, because only that server has connectivity to the relational database). The RunOnServerCall script library contains a function GetServerDB, which you can use to find a replica of the current database on a particular server, in case the current database is local or on a different server. In the code to run on the Notes client, you can write:

Listing 8. Client-side code
				Dim dbCurrent As NotesDatabase 
Dim dbServer As NotesDatabase 
Dim agent As NotesAgent 
Set dbCurrent = session.CurrentDatabase
Set dbServer = GetServerDB(dbCurrent, " servername", False)
Set agent = dbServer.GetAgent("agentname")

and so on using dbServer instead of db. The third argument to GetServerDB tells if you must use the specific server named; if False, any server will do. Of course, if you require a specific server, you can also hard code the database file path, but if an administrator moves the database, your code will fail.

Using RunOnServer is a solution if there is no need for off-line use of a local replica. Though the code can be a bit long, it’s a good way to launch any complex tasks where the user needs to see the results right away. For instance, this would be a good approach to implementing a run on demand report that compiles statistics from the relational data and displays them to the user. The agent could create the report in a rich text field in the parameter document.


Other options

If you enable the Domino server’s HTTP service, then you can also write Domino agents in LotusScript that accept parameters either on the URL or by reading data from an HTTP put operation. The agent can use the Print statement to return data to the caller.

You might also use a Domino Web service. You can implement both the service and the consumer in LotusScript; the Domino Designer help contains documentation and an example.


Conclusion

We hope you find these techniques a handy addition to your repertoire. Remember what your options are for off-loading database work; you have to consider the needs of your application to decide which is right for you:

  • Is it necessary to read or write the relational database immediately, or can you handle the work in batch mode with a server agent or LEI Replication activity?
  • How current does the information need to be? How often does it change?
  • Must the application support mobile users?
  • Must it support connected users with local replicas?
  • Are you allowed to add or change tables in the relational database (to create Virtual Documents)?
  • How large is your data set?
  • Can the server handle the extra work?

The answers to these questions determine which (if any) of the approaches discussed in this article makes the most sense for you.

Resources

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 IBM collaboration and social software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Lotus
ArticleID=12864
ArticleTitle=Offloading Lotus Enterprise Integration tasks to the server
publish-date=01052004