Optimizing LEI 6 performance with Virtual Documents and DB2

Optimizing LEI 6 performance with Virtual Documents and DB2

by Scott Morris
and John Ferreira

Level: Advanced
Works with: LEI 6, DB2
Updated: 08-Sep-2003

Advanced RealTime, a feature in Lotus Enterprise Integrator for Domino 6 (LEI 6), lets you access and manage data in its native format as though it were Domino data. Advanced RealTime allows Domino applications to work with documents, views, fields, and agents without needing to keep track of whether these are contained in a Domino database or in some other external format. With LEI 6 and Advanced RealTime, you can bring together data from various sources and file types into a single application.

An important component of Advanced RealTime is the Virtual Document. Virtual Documents enable users and applications to work with external documents as if they were Domino documents. For example, you can view, sort, search, and manage DB2 documents from a Domino view. This can be a very useful feature—but as with any powerful functionality, it can come at a cost in system resources and performance.

This article offers some tuning tips and techniques to minimize the impact of using LEI 6 Advanced RealTime Virtual Documents with DB2. We begin with a little background information about how Advanced RealTime Virtual Documents work internally. We then describe a few system settings you can tweak to help optimize how Virtual Documents indexing performs at your site. We assume that you're an experienced Notes/Domino and DB2 administrator with LEI experience.

How Virtual Documents work
The Virtual Documents feature completely "virtualizes" a Domino document so that it is stored in your RDBMS, yet appears to be stored in your Domino application. Your views are complete with data from these virtual documents, your agents work, and your full text indexer works. But the data is not stored in Domino.

To do this, LEI 6 stores the information needed to make a complete Domino document in columns in the RDBMS tables. There are two ways to create the columns:

Manually add the needed columns to your data table
Build a new table (called an External Key Table) containing these columns plus a keys column to be used to join with your data table

Either way, the definition of the columns needed by LEI 6 to make things work is the same. By default (for example, when you use the Create External Key Table button in the LEI 6 Virtual Documents Activity dialog box), these columns are defined as follows:

Column name	DB2 definition	Comments
EINOTEID	INTEGER, NOT NULL, DEFAULT 0	The actual Domino NOTEID. This is a key used to find records in the table and should therefore be indexed.
EIUNID	CHAR (32)	An encoded version of the UNID of a Domino document. This is used heavily when HTTP is in use and should be indexed.
EIMODIFIED	TIMESTAMP	The last modified date/time value of a Domino document, used to refresh Domino views. It should also be indexed.
EINOTEPROPS	BLOB (2 GIG)	This is everything not stored in a field that you map in your activity that is needed to make a Domino document valid. For example, all of the $ fields are stored here as well as Domino control information.

You can modify these settings to optimize Virtual Documents indexing performance as explained in the following section.

Optimizing Virtual Documents indexing performance
EINOTEPROPS, when defined as a BLOB of any size, can impact performance. Each item is a file I/O action on the DB2 system, and DB2 is unable to execute several performance-enhancing actions (such as reading ahead and anticipating the likely outcome) found in Domino. Typically, there is 2 to 4 KB of data stored in EINOTEPROPS. So the DB2 development team recommends you declare EINOTEPROPS as follows:

Column Name	DB2 Definition
EINOTEID	INTEGER, NOT NULL, DEFAULT 0
EIUNID	CHAR (32)
EIMODIFIED	TIMESTAMP
EINOTEPROPS	VARCHAR (10K) for bit data

This allows DB2 to eliminate all disk I/O operations needed for BLOBs and buffer records ahead of time in case they are needed shortly. This significantly helps Virtual Documents indexing performance.

To define EINOTEPROPS this way, you need to set up a DB2 tablespace with a 32 KB page size. If you don't have one already, here is a sample SQL command to create one for a Windows 32 DB2 system, which assumes that BP32K0000 (a buffer pool set up with 32 KB pages) already exists:

CREATE REGULAR TABLESPACE "32KTBLSPACE" PAGESIZE 32 K MANAGED BY SYSTEM USING ('C:\DB2\NODE0000\32KTBLSPACE' ) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.33 BUFFERPOOL BP32K0000 DROPPED TABLE RECOVERY OFF;

Moving to the new EINOTEPROPS definition
So what do you do if you have an LEI 6.x Virtual Documents activity with the old definition of EINOTEPROPS? First, if the EI fields are in your data table, you cannot change them. Second, if they are in an external key table, you do not want to drop the key table and recreate it—the information stored in EINOTEPROPS (for example, $UpdatedBy, $FILE, and doclinks) would be lost. So the best way to move to the new EINOTEPROPS definition is to follow these steps.

If your Virtual Documents activity uses an external key table (here named DB2ADMIN.KEYS)
Before starting this procedure, use the DB2 Control Center to make copies of your data and external keys tables. These can serve as backups if something goes wrong.

Make sure that at least one userspace exists in DB2 with a 32 KB page size.
If you run the LEI 6.5 Beta 2 or later, create DB2ADMIN.KEYS_NEW via the Create External Key Table button in the Virtual Documents Activity dialog box, but don't save the Virtual Documents Activity document after the table is created. This means that the old table name DB2ADMIN.KEYS remains the external key table field of your Virtual Documents activity. Check that the column names in the KEYS_NEW table occur in the same order as in the KEYS table. If this is not the case, record the table structure for KEYS_NEW, drop the table, and then manually recreate it with the columns in the same order as they occur in KEYS. (If you do not have LEI 6.5 Beta 2 or later, create KEYS_NEW manually with the columns in the same order as they appear in KEYS.)

Here is sample SQL code for creating an external key table with one column, EMPID (employee ID), used to join with the data table:

CREATE TABLE DB2ADMIN.KEYS_NEW (EMPID INT NOT NULL, EINOTEID INT DEFAULT 0 NOT NULL , EIUNID CHAR(32), EIMODIFIED TIMESTAMP, EINOTEPROPS VARCHAR (10240) FOR BIT DATA)

Here is the SQL code for creating the indexes for this external key table for best performance:

CREATE INDEX DB2ADMIN_IXD1 on DB2ADMIN.KEYS_NEW (EMPID)
CREATE INDEX DB2ADMIN_IDX2 on DB2ADMIN.KEYS_NEW (EINOTEID)
CREATE INDEX DB2ADMIN_IDX3 on DB2ADMIN.KEYS_NEW (EIUNID)
CREATE INDEX DB2ADMIN_IDX4 on DB2ADMIN.KEYS_NEW (EIMODIFIED)
Execute this SQL command from your DB2 Command line processor:

EXPORT TO C:\TEMP\TEMPCOPY.IXF OF IXF SELECT * FROM DB2ADMIN.KEYS;
Execute this SQL command:

IMPORT FROM C:\TEMP\TEMPCOPY.IXF OF IXF INSERT INTO DB2ADMIN.KEYS_NEW;
Drop DB2ADMIN.KEYS after you confirm that your KEYS_NEW table is intact.
Rename DB2ADMIN.KEYS_NEW to DB2ADMIN.KEYS.

Note that whichever platform you run this on must have enough space to create the temp file TEMPCOPY.IXF. Also, DB2 must have enough space for the backups.

If the keys are integrated keys located in your DB2 data table
Again, before starting this procedure, use the DB2 Control Center to make a copy of your data table as a backup. Then proceed as follows:

Ensure that at least one userspace exists in DB2 with a 32 KB page size.
Create DB2ADMIN.DATA_COPY. Make the table identical to your source data table, except for the column EINOTEPROPS. This column should be created as VARCHAR (10K) for bit data. Check that the column names occur in the DATA_COPY table in the same order that they appear in the data table. The DATA_COPY table is automatically created in the 32 KB page size userspace.
Execute this SQL command from the DB2 Command line processor:

EXPORT TO C:\TEMP\TEMPCOPY.IXF OF IXF SELECT * FROM DB2ADMIN.DATA;
Execute this SQL command:

IMPORT FROM C:\TEMP\TEMPCOPY.IXF OF IXF INSERT INTO DB2ADMIN.DATA_COPY;
Drop DB2ADMIN.DATA after you confirm that your DATA_COPY table is intact.
Rename DB2ADMIN.DATA_COPY to DB2ADMIN.DATA.

Note that the database must have enough space to hold both the data table and DATA_COPY simultaneously.

After you have completed these steps, you will indeed see significant indexing performance improvements, typically 10 to 15 percent less time than with default settings.

Modifying the EIReadBlockSize Notes.ini variable
LEI 6.5 has an additional new feature to further improve DB2 Virtual Documents indexing operations, a Notes.ini variable called EIReadBlockSize. On the iSeries platform, this value defaults to 100, and on all other platforms, it defaults to 1. This setting controls how many records are fetched at one time from DB2 when servicing Domino indexing requests. You may need to experiment to discover the optimal setting for EIReadBlockSize because factors such as record size, processor power, available RAM, and network bandwidth all influence this setting. For Windows 32 Domino installations, we recommend starting with EIReadBlockSize=25 and checking the performance. (Each modification requires that you stop and restart your Virtual Documents activity.) Then try setting this variable to 50, 75, 100, 250, and 500, and after each change check the performance of your Domino application to determine which setting is optimal for your environment. (You must wait a bit until the activity actually fully starts up. You receive an error opening the database if you try before the activity is fully underway.) You can press Shift+F9 to rebuild the index from scratch. Time the results until you get the best number. This helps speed up performance in large environments with tens of thousands of records.

Conclusion
One final performance note: LEI 6.5 has an updated DB2 connector. This connector has changed its fetch model from being unbounded to bounded, which should boost all DB2 connector performance by as much as 25 percent. (You won't have to do anything to take advantage of this, except upgrade to LEI 6.5/Domino 6.5 when it becomes available.) With this change, and the performance suggestions contained in this article, you should be able to reduce the amount of time needed to build a Domino index based on Virtual Documents by more than half! And even if you don't yet have LEI 6.5 installed, your performance time should still be reduced by about 15 percent—a significant improvement.

ABOUT THE AUTHORS
Scott Morris is a Senior Software Engineer with IBM. He has been with Lotus/IBM since 1990. Since 1996, he has worked on developing Domino-SAP integration products offered by the Domino Enterprise Integration group. He was previously a member of the Notes API team, the Notes (before there was a Domino) server team, and somewhere in the deep dark past, the 1-2-3 for Macintosh team. Scott has an MS in Computer Science from Boston University and a BS in Mathematics from Carnegie Mellon University.

John Ferreira was hired by Lotus in 1986 as a Customer Support Representative and moved over to development as a Software Quality Assurance Engineer in 1987. Prior to joining the Enterprise Integration Team in 2000, he was a member of the Lotus SmartSuite Team and worked on various releases of Lotus 1-2-3. John is currently a Staff Software Engineer working out of the Portsmouth lab and has been responsible for QE testing of both RealTime and Data Management Activities in LEI.