Lotus Domino (formerly Lotus Notes) is an IBM server product that provides enterprise-grade e-mail, collaboration capabilities, and a custom application platform. The platform is used by many large corporations, and most of them also employ other enterprise systems that need to exchange data with the Lotus platform. This is where CloverETL can now be helpful.
Since version 3.1 CloverETL is able to connect to Lotus Domino servers and read and write its data, CloverETL’s Lotus Notes components help you connect the data-record CloverETL world with key-value concepts of Lotus Notes databases. This connectivity allows a lot easier exchange of Lotus data with Excel, relational databases or even with various WebServices. CloverETL Designer enables even less technical persons to setup connectivity to Lotus Domino and create simple data extraction or data ingest apps.
Lotus Notes (Domino) database stores data in documents. A document contains key-value pairs where the key is always a string and the value can be of various formats and types (number, string, image, OLE object, RTF, …). The data in Lotus Notes are therefore structured differently than in classical data-record world of CloverETL. Typically there are no guarantees of what values with what keys will be present in a document.
To overcome this gap, the concept of Lotus Notes Views is used to our advantage. A view in Lotus Notes provides tabular structure for the data in Lotus Notes database. Therefore, to read the data it is necessary to first define a view through which the data will be exported. This task needs to be done by the manager of the Lotus Notes database.
Finally, in the Lotus Domino server, remote access needs to be enabled. This is typically done by issuing the following commands in the server console:
load http load diiop
Connecting to Lotus Notes from CloverETL
The first task on the side of CloverETL is to define a Lotus Notes connection. The connection specifies the address of the Lotus Domino server and user credentials that will be used to log in. The user’s name should be in the format that can be found in the Person document in the Domino Directory. The database can be either specified by its file name (typically a *.nsf file) or by the Replica ID value.
Finally, a path to the Lotus Notes connection drivers (NCSO.jar) needs to be specified. These drivers are not part of CloverETL and can usually be found in your Lotus Domino server installation, e.g. C:\Program Files\Lotus\Domino\data\domino\java\NSCO.jar
First, choose the connection that you’ve created in the previous steps and also, at the bottom of the dialog, specify the view which will provide the input data. On the following page of the wizard you will see new metadata extracted from the view. Based on the preview of the data you can then manually fine-tune the types of the fields, as initially all field types will be set to string.
Reading from Lotus Notes
LotusReader is the name of the CloverETL component which reads data from Lotus Notes views. Thanks to the preparation steps above, configuring this component is fairly simple. Place this component into your graph, open its configuration and set the Domino connection created in previous steps and select the view from which the data will be extracted. Finally, connect this reader to your graph with an edge and assign metadata we extracted in the steps above.
Writing to Lotus Notes
Complementary to the reader component, there is also a component to import data into Lotus Notes. Basic configuration of the LotusWriter component is quite simple too. In the following paragraphs, we will pay better attention to the different ways the data can be written to Lotus Notes.
The default mode to write the data is insert. In this mode, LotusWriter will create new document in Lotus Notes database for every incoming record. After inserting the document, it will also launch form calculations. This is a procedure defined in the Lotus Notes database by the author of the database that can for example fill-in missing fields, fix formatting of specific string fields, check various constraints, and so on.
The computation can mark a record as invalid in the case of errors during the computation. You can choose to further process these records (e.g. dump them to a log file) by connecting any writer component to the only output port of LotusWriter. You also have the option not to save the invalid records, by enabling the Skip invalid documents option. By default, even documents marked as invalid will be stored into the Lotus Notes database.
The other write mode is update. In this mode, the LotusWriter tries to update existing documents in the database, instead of inserting new ones. The component will look up the documents by searching the view specified in the component configuration (required parameter for update mode). Each view marks certain columns as sorted. These columns will be used too look up the documents to update.
There are two more advanced and in certain occasions quite important concepts in writing to Lotus Notes database with CloverETL. The first one is lazy update replication, the other one is custom field mapping.
For the update mode, after the document fields are updated, save operation will be commenced. This operation may also launch the optional replication of the document. However, in case there were no actual changes in the contents of the document, the replication would be unnecessary. Therefore, the writer component uses lazy update mechanism – it will only launch the save operation on the document, if some of the fields’ values actually changed. This can save significant amount of time for large data manipulations.
Finally, the last feature that will be described here is custom field mapping. By default, the LotusWriter component will create documents with values based on the names of the input metadata fields. However, it might be preferable to create documents with the help of a Form. Forms often specify fields documents should contain and are a common way how to create new documents in Lotus Notes.
The mapping dialog helps you to map fields of input metadata to the fields of selected Form. This allows writing single field multiple times, skip fields, but most importantly choose proper names for the target document fields.