Many people have documents with attachments in them that they would like to transfer to their RDBMS systems to be accessed by other RDBMS applications. Getting this done via LotusScript is possible, but there is definitely an easier way. This article shows how the Lotus Enterprise Integrator (LEI) 6.x can move your attachments from your Domino database into an RDBMS table where they will become available to whomever wants to check them out in the RDBMS itself. If the goal is to perform a one time transfer, then it would be safe to have read/write access to files stored in the RDBMS system. If the goal is to continue to access the attachments in the RDBMS system from Domino, then the attachments in the RDBMS system should be considered read-only for RDBMS applications.

This article assumes that you're familiar with Lotus Enterprise Integrator and Domino.

The procedure

The steps to move your attachments to an RDBMS table are simple for the experienced LEI user, and not too difficult for the inexperienced one. Here they are (we subsequently explain each step later in this article):

1. Use a selective replication formula to make a replica of your Domino database that includes all the design elements, but does not contain data documents.
2. Create a table in your RDBMS (in this example, DB2 UDB is used) to hold the data fields of your Domino documents.
3. Create a Virtual Documents activity in LEI with an External Key table (needed to support Virtual Documents) in the RDBMS and an Attachments table (where the binary attachments are stored) in the RDBMS.
4. Start the Virtual Documents activity.
5. Remove the selective replication formula from your replica database (allowing all documents to replicate, including data ones) and replicate the original database with the new replica.

When you complete the last step, the replica database is passed the data documents, which are virtualized along with their attachments into your RDBMS tables. After that point, the fields of the Attachments table can be read for their contents by other RDBMS applications. They cannot, however, be updated because this breaks the Virtual Documents activity. Fields stored in the External Keys table know about the files too, and only the Notes C API provides access to these data structures that are stored there.

If you set up Domino to regularly replicate the two Domino NSF files, then the Virtual Documents activity keeps your RDBMS table current. You may decide that you no longer need the original source Domino database and that working with only the virtual database meets all of your needs.

We keep the example as simple as possible. Here is a sample document from a Domino database, based on the form ATTACH:

Notice the document has only three fields:

• DOCID holds a key to find the document
• AUTHOR lists the document's author
• ATTACH is a Rich Text field where the files may be attached

Creating the replica

To create a new selective replica that has only the design, but not the data, select the database from your Notes workspace from which you will make a replica, then select File - Replication - New Replica. In the Create Replica for Database dialog box, click More Settings. In the Replication Settings dialog box, click the Advanced tab to enter a selective replication formula:

Select the Documents by selection formula option, and enter the formula SELECT Form = "foo". Because the form is not named foo, data documents will not replicate. Click OK, and use the Notes client to force the replication to happen now.

Creating the RDBMS table

Next you need to define an RDBMS table to hold the data fields of the form ATTACH.

You can define the column ATTACHMENT as BLOB, but because this field holds only the icon and related text for the attachment and not the actual attachment, you don't need it to be so large. Using VARCHAR for bit data allows DB2 to perform better when working with the records in this table.

Creating the LEI Virtual Documents activity

You now have your RDBMS table and your Domino database (the replica), so it is time to create an LEI Virtual Documents activity. (The LEI Virtual Fields activity also supports virtual attachments and can be used in place of Virtual Documents to move attachments to your RDBMS system.) To do this, you must first define a connection to your RDBMS system. For DB2, the connection looks like this:

The connection specifies the DB2 database that you want to access as defined in the DB2 client. It also lists the user name and password with which to connect to this database. You actually don't need anything else. By leaving the other fields empty, this becomes a generic DB2 connection to your database, allowing this connection document to provide access to all of the tables available in the database.

Here is the Virtual Documents activity:

Figure 4 shows the name of the activity, which in this case is Documents with Attachments. On the left side of the form is listed the Domino application you want to work with. This database must be local to the server where LEI is installed (as is true for all RealTime functionality in LEI). After you choose the replica database, select the form from that database that you want to monitor (in this case, ATTACH).

Select the Lotus Connection on the right side of this form. The data source is the title of your DB2 connection document. Because no table was selected in that connection document, you are prompted to pick one. In this example, this is the table DB2ADMIN.DOCIDS described previously.

Next you must map the fields, which have similar names. Each field in the ATTACH form is mapped to a column in the DB2 table.

Creating an External Key table

The External Key table holds fields needed to let LEI virtualize the data. (You may also use integrated keys to achieve the desired results. See the LEI user's guide for a complete description of Integrated and External Keys for Virtual Documents.) Create this table using the button provided in your LEI Virtual Documents form:

In the Key Table Option field, select the Use External Key Table option. Then enter a name for the new table (in this example, DB2ADMIN.DOCID_KEYS) and select a Key Field. This field is used to join a record in the External Key Table with a record in the table DB2ADMIN.DOCIDS. Next click the Create External Key Table button to display a dialog box that confirms your choices and creates the table. Here is the table created in DB2:

This table allows you to store additional information needed to describe what a Domino note really is.

Creating an Attachments table

The final step needed to set up this activity is to turn on Virtual Attachments. We do this under the Options - Virtual Attachments tab:

In the field Attachments, select the Virtualize Attachments option. Then provide a name for this new table. Now click the Create Virtual Attachment Table button. Doing so for DB2 presents this dialog box:

Change the default settings here to define that the DB2 table that holds attachments supports only one Notes database. Also change the Max attachment size from 100 MB to 10 MB. Click OK to create the table in DB2.

Here is how the example table is defined in DB2:

This activity is now properly defined, so save it. Now turn on the activity in LEI.

You have two stacked icons on your workspace for your Domino databases: One has data and one doesn't. For the replica without data (the one that is being watched by the Virtual Documents activity), remove the selective replication formula, so that your data documents can replicate. After doing that, using Notes (or issuing a replication command from your Domino server console), replicate the two databases.

Ta da - done! The data documents and their attachments from the original database are now replicated with the virtual replica database, placing all that data into the RDBMS. Any other RDBMS application can now read from the table holding the attachments. If they want, they can join with the DOCID table for additional information. How much scripting was needed? None! How long did it take to build this simple example from scratch? Personally, it took us perhaps 20 minutes -- but we had to take a phone call during that time.

Conclusion

LEI can definitely help to make our lives easier when we have tasks like this. We may need to do them once, or we may need to set them up for permanent synchronization. It is worth a visit to the Lotus Enterprise Integration Web page to check out LEI, if you haven't yet. You can get demo versions there and find plenty to read. Also, the Enterprise Integration forum is a good place to post questions and receive expert answers. Good luck and happy integrating!

Resources

• Learn more about LEI by visiting the Lotus Enterprise Integration Web page.
  
  
• You can also communicate with other LEI users in the Enterprise Integration forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• The procedure
• Creating the replica
• Creating the RDBMS table
• Creating the LEI Virtual Documents activity
• Creating an External Key table
• Creating an Attachments table
• Conclusion
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Lotus on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers