1. No category

Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
Let Your Relational Database
Do the Work — How to Invoke
Stored Procedures from
Domino 6 Applications
Joseph Pescatello and Sarah Boucher
For a Domino developer or administrator, invoking a procedure stored in
a relational database from a Domino application can mean faster application
performance, reduced network traffic, more efficient use of computing
resources, and enhanced application functionality.
Joe Pescatello
IBM Software Developer
IBM Lotus Workplace Messaging
Most enterprise-level relational database systems1 allow a thirdparty application like Domino to invoke a procedure that encapsulates
functionality like sorting, grouping, or performing calculations on stored
data. The procedure can be written in any of several different programming
languages2 and is compiled and stored in the relational database. When
invoked by name, the procedure executes using any input values received
from the invoking application; it can also return values.
For instance, let’s say you have managers working in Notes/Domino
who need salary data as they plan salaries and bonuses for employees.
When reviewing an individual employee’s record in a document, a manager
needs to know the current average salary for the company division in
which that employee works. Your first thought might be to transfer the
salary data for all employees in the division from the Human Resources
RDBMS to Domino and make the calculation locally, but that involves
sending a lot of data over your network.
You can use network resources more efficiently and build faster
performance into your application by enabling your application to invoke
a procedure stored in the RDBMS. The stored procedure will receive a
Sarah Boucher
Enterprise Integration Manager
IBM Lotus Software
1
DB2, Oracle, and Microsoft’s SQL Server, for example.
2
For example, the DB2 Stored Procedure Builder allows SQL (in version 7.1 or later), Java, C,
COBOL, and C++.
(complete bios appear on page 126)
No portion of this publication may be reproduced without written consent.
103
THE VIEW November/December 2003
division or group parameter from your application;
then it will calculate the average salary for that
organization and pass the value back to Notes. Only
one value — the current average — passes over the
network to the Domino application. The result is only
transferred when it has been requested by the invoking
application, so you avoid overloading the network
with data that the Domino application doesn’t need
to use or display. Because the stored procedure is
already compiled and optimized in the relational
system, it executes more quickly than standard SQL
queries. Another benefit is that you can make your
stored procedure accessible to other third-party or
SQL clients that access the RDBMS database, thus
extending its value.
Domino 6 provides broad support for stored
procedures, allowing you to off-load large data-processing
tasks to an enterprise database system and bring back
the results.3 In this article, you’ll learn how to create
stored procedures in a relational database and invoke
them from your release 6 applications.
We’ll concentrate on the basic mechanics of
connecting a Domino 6 application to a DB2 stored
procedure4 using three standard Domino integration
tools — Domino Enterprise Connectivity Services
(DECS), Lotus Enterprise Integrator (LEI), and Lotus
Connector LotusScript Extensions (LC LSX). With a
sample DB2 database, a couple of stored procedures,
and a simple Domino application, we’ll show you
how to use each of these tools to establish an activity
that connects your application to a stored procedure
and displays the output.
The knowledge you gain will enable you to build
faster, more efficient Domino applications. You’ll also
3
Prior to Domino 6, Lotus connectivity support for invoking stored
procedures and receiving their outputs is not fully supported in all
releases of Domino, nor is it supported in all releases of the Lotus
integration tools.
4
Invoking stored procedures on other database systems like Oracle,
Sybase, or Microsoft SQL Server is quite similar. If you are working
with a relational database system other than DB2, you may only need
to modify the SQL and stored procedure syntax in the examples.
(Consult your RDBMS documentation or your database administrator
for clarification.) If you are working in another type of enterprise
environment (such as SAP R/3), you can still benefit from learning the
principles of using stored procedures from Domino applications.
104
receive guidance on knowing when a stored procedure
is appropriate for your application and how to troubleshoot problems with stored procedures.
To get the most out of this article, you’ll need a
working knowledge of Domino development; a basic
understanding of DECS, LEI, and LC LSX; and
familiarity with database administration and concepts.
To create and run the demonstration exercises, you’ll
need the following resources:
•
A release 6 Notes client, Domino Designer client,
and Domino server
•
DECS or LEI release 6
•
DB2 7.2 or 8.1 (on iSeries, DB2 V5R1, or V5R2)
Let’s start by learning how to recognize when
it’s appropriate to employ a stored procedure.
When Should You Use a
Stored Procedure?
There are no hard and fast rules as to when to use
stored procedures in Domino applications. Still,
the following guidelines will help you identify
opportunities to cut down network traffic, make better
use of computing resources, and improve the overall
performance of your application. This list is not
exhaustive, but it should get you thinking about when
to use stored procedures.
✓ Any application that uses multiple database
records to compute a single value (such as
calculating the average salary in a division) is a
good candidate for a stored procedure. Let the
database engine churn through its records, perform
the calculation, and return the desired value. You
won’t increase network traffic, and the database
system will almost certainly perform the computation
faster than Domino.
✓ Similarly, any application that compares, sorts, or
groups database records together is a good candidate
for a stored procedure. Do you want to compare
inventory across warehouse locations? Or look at
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
sales revenue by region? To make the necessary
calculations, stored procedures are a good choice.
Now that you have some idea of when to use a stored
procedure, let’s set up an environment for the examples.
✓ If more than one client requires a particular
functionality, you should consider creating a
stored procedure. For example, if your Domino
application needs to perform the same
calculation as your data warehouse, you can
save time and maintenance headaches by coding
the calculation function in a stored procedure.
However, make sure you consider the caveats
discussed next.
The Caveats
While a stored procedure can bring a great many
benefits, we recommend employing one only after
careful consideration. There are a few potential
pitfalls, though you can avoid them easily with
some forethought:
•
•
•
If more than one application depends on a
stored procedure, make sure that the requirements
of each are compatible. If one application’s
requirements change, you may have to decouple
the altered application and write a new procedure
for it.
If your database team has already written a stored
procedure with the functionality you need to
expose in a Domino application, consider using it.
If your requirements match those of the procedure
owner, you may be able to take advantage of the
existing code. Just be aware that if you use
someone else’s stored procedure, you will be at
his or her mercy. As in the caveat above, make
sure your requirements match those of the
procedure owner.
Many database administrators and managers
wisely restrict programmers’ ability to write SQL
queries against the enterprise data store.
Inefficient SQL can bring a system to its knees
and can have devastating effects on the performance
of any applications that access the system. If your
organization demands it, you must rely on your
database programmers to write the stored procedures
you want to call from Domino applications.
No portion of this publication may be reproduced without written consent.
Setting Up the Examples
All of the examples in this article illustrate how
stored procedures can alter data available in your
Domino applications without changing any data in the
RDBMS system. We designed three examples to
highlight each of the basic Lotus integration technologies
— DECS, LC LSX, and LEI. (The sidebar on page
108 provides an overview of these three tools.) Each
Domino integration tool satisfies the requirements of
a different solution. All examples involve the use of a
stored procedure that calculates the bonus for any
employee in a sample DB2 database; some involve
the use of an additional procedure as well. Any
Domino 6 developer with proper authority to use a DB2
database should be able to execute all of the examples.
Note!
If you already have LEI, don’t skip the DECS
example — if you do, you’ll have to manually
create documents to use in the later examples.
We’ll tell you how to implement the DECS example
using LEI.
If you don’t have LEI, the sidebar on page 109
tells you how to obtain a sample copy to use in the
LEI example.
Since the procedure to calculate an employee’s
bonus is used in all three examples, we’ll show you
how to create it now, along with the sample DB2 database and a simple Domino application that will call our
stored procedures. We’ll start with the DB2 database.
The Example DB2 Database
The example stored procedures will operate on
DB2 records of employee data from a table called
“EMPLOYEES.” On your DB2 system, create a table
105
THE VIEW November/December 2003
with this name, using the SQL structure listed in
Figure 1.
Note!
We named our DB2 database DB2SAMPLE.
In all of our examples, the DB2 owner name is
JPESCATE. You should use the owner name
that’s appropriate for your system.
Use the code shown in Figure 2 to populate the
EMPLOYEES table with some dummy records for
testing. (Be sure to change the owner name.)
The Stored Procedure for Calculating a Bonus
Our first procedure, CALCBONUS (to calculate a
bonus), will take one input parameter that uniquely
identifies the employee (EMPNO) whose bonus the
end user (you, when testing the examples) wants to
determine. In the examples, this stored procedure
will calculate the bonus but will not write the value to
a DB2 database table, as is usually done.
Figure 1
Figure 3 contains the SQL code to create the
CALCBONUS procedure on your DB2 server. (Be
sure to check the DB2 documentation to make sure
your DB2 server has the necessary environment for
creating stored procedures.)
To build a stored procedure, the first thing
we do is issue the CREATE statement with the
owner name (JPESCATE in the sample code)
pre-pended to the procedure name, which in this
case is CALCBONUS.
Next, we define the input and output parameters.
The input parameter has the same data type and size
as the EMPNO column in the EMPLOYEES table;
this will ensure that a parameter of an invalid type
or size won’t make it through to the WHERE clause
later in the procedure.
The output parameters have the same data
types and sizes as their associated columns in the
EMPLOYEES table. These parameters will hold
the values that get passed back to any program
(DB2 or external) that calls the stored procedure —
in our case, a sample Domino application.
The DB2 “EMPLOYEES” Table
CREATE TABLE JPESCATE.EMPLOYEES
("EMPNO" CHARACTER (10) UNIQUE NOT NULL ,
"LAST_NAME" VARCHAR (40) ,
"FIRST_NAME" VARCHAR (40) ,
"DIV_NAME" VARCHAR (50) ,
"SALARY" DECIMAL (9, 2) ,
"BONUS" DECIMAL (9, 2))
Figure 2
Sample Employee Records for the “EMPLOYEES” Table
INSERT INTO JPESCATE.EMPLOYEES VALUES('1','SMITH','MARY','WESTERN', 45000.00,1000.00)
INSERT INTO JPESCATE.EMPLOYEES VALUES('2','JOHNSON','JOHN','WESTERN', 65000.00,1000.00)
INSERT INTO JPESCATE.EMPLOYEES VALUES('3','WILLIAMS','MARY','EASTERN', 100000.00,12000.00)
INSERT INTO JPESCATE.EMPLOYEES VALUES('4','REDMAN','JUNE','WESTERN', 25000.00,1000.00)
INSERT INTO JPESCATE.EMPLOYEES VALUES('5','PERRY','STEVE', 'EASTERN', 300000.00,1000.00)
106
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
The SELECT statement calculates the 10% bonus
(SALARY * .10) for the employee whose number
(EMPNO) is passed as an input parameter and assigns
the results of the calculation to the BONUS output
parameter. Once the database engine digests and
compiles a procedure, it stores the procedure as
another piece of metadata5 in its database, similar to a
DB2 table or view. You’ll see that Domino treats this
stored procedure like any other type of metadata when
you integrate it with your Domino application.
5
Metadata is a term used to describe the data, rules, constructs, and
operations that exist in an enterprise system.
Figure 3
The Sample Domino Application
You’ll need to create a Domino 6 application to
work with the DB2 data that you just created. The
premise for the sample application is quite simple —
you want to display some of the employee information
from the DB2 table and the values calculated by the
stored procedures. So, create a Domino database
with a form called “Employee” that includes the
fields shown in Figure 4. We called our application
“Employee Example.” Set up a view in your
application that displays the Employee form and
has columns that show the form’s fields.
SQL for the “CALCBONUS” Stored Procedure
CREATE PROCEDURE JPESCATE.CALCBONUS (IN EMPNO CHARACTER(10), OUT LAST_NAME VARCHAR(40), OUT FIRST_NAME VARCHAR(40), OUT DIV_NAME VARCHAR (50), OUT SALARY DECIMAL
(9,2), OUT BONUS DECIMAL (9,2))
LANGUAGE SQL
P1: BEGIN
DECLARE TEMP_EMPNO CHARACTER(10);
SET TEMP_EMPNO=EMPNO;
SELECT EMPNO, LAST_NAME, FIRST_NAME, DIV_NAME, SALARY, (SALARY*.10) INTO EMPNO,
LAST_NAME, FIRST_NAME, DIV_NAME, SALARY, BONUS
FROM JPESCATE.EMPLOYEES
WHERE EMPNO=TEMP_EMPNO
END P1
Figure 4
“Employee” Form Fields
Notes Form Field Label
Data Type
Editable/Computed for Display
EMPLOYEE NUMBER:
Text
Editable
FIRST NAME:
Text
Editable
LAST NAME:
Text
Editable
DIVISION:
Text
Editable
SALARY:
Number
Editable
BONUS:
Number
Editable
AVERAGE SALARY:
Number
Editable
No portion of this publication may be reproduced without written consent.
107
THE VIEW November/December 2003
In the first example, you’ll see how shell documents
are created automatically in this Domino database, one
for each employee record now in the DB2 table you just
created. When an employee document is opened, the
current values from DB2 will be dropped into the editable
fields (the data isn’t stored with the Domino database).
When the CALCBONUS procedure runs from the
Domino application, you’ll see the BONUS field filled
with the result. (Subsequent examples will use another
stored procedure to fill the AVERAGE SALARY field.)
Now that you’ve completed the preliminary setup of
the sample DB2 database, stored procedure, and
Domino application, let’s go over the basic principles
of establishing connections and using stored procedures.
Connections, Activities,
and Results
The two basic components for connecting a Domino
application to an external DBMS are the connection
and the activity. The principles behind the two are
integral to using stored procedures with Domino
applications, no matter which integration tool you use.
Establishing the Connection
All of the Lotus integration tools used in this article
connect to enterprise systems through the Lotus
The Lotus Integration Tools
Domino Enterprise Connectivity Services (DECS)
DECS allows Notes/Domino users to view data from
relational databases, ERP systems, and operatingsystem files as if it were Domino data. DECS works
in real time, so when a user opens a document in
Domino, DECS retrieves some or all of the data for
that document from one or more records in one or
more enterprise systems. DECS uses the same
Lotus base connectors that the other standard Lotus
integration tools use. (See the sidebar on page 110
for more on Lotus Connectors.)
With DECS, you can build point-and-click activities
that give users access to information in enterprise
systems. When a user adds or changes a document
in a DECS-enabled Domino database, DECS can
insert or update the data in the backend. And when
a user deletes a Domino document, DECS can
delete the equivalent data from the backend. You
can also use DECS to build applications that can
invoke stored procedures and receive the results.
You can use an interface called “DECS
Administrator” to associate fields in one or more
Domino forms with data in one or more enterprise
systems for specific document events that take place
in an application, such as Open or Delete. For a
good introduction to DECS and how it works, visit
http://media.lotus.com/appdev/decs/decs2.htm.
108
DECS has been a part of the Domino server since
release 4.6.5; it installs as an option. To see if it’s
running, issue a “show tasks” command at the
Domino server console and look for DECS. If you
don’t see it, consult the DECS Administrator Guide
for installation instructions.
Lotus Connectors LotusScript Extensions
(LC LSX)
Another option for accessing stored procedures in
Domino applications is to use LotusScript agents.
The Lotus scripting model enables developers to
access and call procedures with more granularity
and control than can be provided by a form-based
activity such as DECS. Rather than calling a procedure
with a document event like Open (as in this article’s
first example), you can create a script to call a
procedure exactly when and where you need it.
LC LSX is a set of LotusScript extension classes
that uses the standard Lotus connectors to open the
Lotus scripting model to external data. (Again, see
the sidebar on page 110 for more on Lotus
Connectors.) The LC LSX engine is already part of
both the Notes 6 client and the Domino 6 server —
you don’t need to take any special steps to install it.
One caveat about using an LC LSX script to access
external data is that connectivity must be established
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
Connectors that ship with the Domino server,
LEI, and the Notes client. For the examples,
we’ll use the Lotus Connector for DB2, one of
the standard connectors that come with Domino 6.
(For more on Lotus Connectors, see page 110.)
In each integration tool, you create a connection
specification to give Domino the information it
needs to connect to an enterprise system. In LEI
and DECS, the connection specification is a
document; in an LC LSX agent, it’s part of the script.
Note that DB2 connections always require,
at a minimum, the database name, a database
from the location where the script will run.
Traditionally, the script runs from the Notes client
if executed manually, as when invoked by a user
clicking a button. If you use the Run on Server
agent option or if you enable scheduling for your
agent, the connectivity must be installed on the
server where the agent executes. (See Domino
Designer Help for more information about the Run
on Server agent option.)
You can find the online Lotus Connector LotusScript
Extensions Guide at http://www-12.lotus.com/ldd/
doc/lei/6.0/lsxlc6.nsf.
Lotus Enterprise Integrator (LEI)
LEI 6 features a set of Data Management and
Advanced RealTime* activities that provide a pointand-click interface for moving or synchronizing data
between external data sources and Notes/Domino
applications. It’s a continuation of the technology
first introduced in Lotus NotesPump and is targeted
to help you to execute high-volume data transfers
or synchronizations without programming. Its
centralized LEI Administrator is similar to the DECS
*
RealTime refers to DECS and LEI Virtual Field, Virtual
Document, and Virtual Agent activities, in which data is drawn
into a Notes form from an enterprise system in real time.
No portion of this publication may be reproduced without written consent.
user ID with the correct privileges for the
actions required on the database (read, create,
delete), and the password for accessing the
database. For example, when creating a
Domino connection of any sort to the
CALCBONUS stored procedure, you’ll
need to build in the DB2 name of the
procedure (CALCBONUS), a valid user ID
that gives the calling mechanism access to
CALCBONUS, and the password (if required
by the DB2 system). If any of these
elements change, you’ll need to update all
connection documents or scripts that connect
to the procedure.
Administrator in that you create, modify, and control
the execution of your activities from one location.
When you use LEI to create access to a stored
procedure, you construct a Virtual Agent, Virtual
Fields (as in DECS), or a Virtual Document, all
components of LEI Advanced RealTime. The third
example in this article shows you how to build a
Virtual Agent into a Domino application; the method
is the same as adding traditional agents.
While DECS is available as a component of the Domino
server, LEI is a separate product with advanced
capabilities and is purchased separately. During
installation, LEI converts DECS activity and connection
documents to LEI versions. Once LEI is installed,
only the LEI Administrator is available; it replaces
the DECS Administrator.
LEI 6 requires matching versions of the Domino 6
server and Notes 6 client for administering LEI
connections and activities. If you do not already
have LEI 6 installed, you can download a trial copy
from the Lotus Developer Domain. (Visit
http://www.lotus.com/ldd/products.nsf/products/ei
and click on “Free Trials” in the Downloads section.)
You can also obtain more information on LEI and its
options at http://www.lotus.com/ei by selecting the
“Lotus Enterprise Integration” product page.
109
THE VIEW November/December 2003
Establishing an Activity
In the Lotus integration tools, an activity relates the
information in the source database to some aspect of
the Domino application. Using DECS, you can create
an activity as a document that, upon a document event
like Open or Create, sends or receives data in its form
fields, or you can specify an activity with finer detail in
a LotusScript agent (LC LSX) or a Virtual Agent (LEI).
Output Parameters and Return Values
In general, stored procedures can return values to
applications by using output parameters or by declaring
in their signature the type of data they return. When a
stored procedure declares that it returns a data value,
that value is called a “return value.”
Lotus Connectors don’t currently support
return values generated by stored procedures.
However, DECS, LC LSX, and LEI support the
return of output parameters, so you can accept
into Domino any output parameter values that
result from running a stored procedure. The Lotus
connectors pass your input parameters when the
stored procedure is called and pass back the output
parameters when the procedure is executed, thus
making the output values available to Domino
applications. (See the Lotus Connector documentation
for information regarding input- and outputparameter support for stored procedures on your
enterprise system.)
Those are the basics. Now it’s time to dive
into our first example.
Lotus Connectors
Lotus supports native access to a wide variety of
external data structures with a set of Lotus base
connectors that use the standard Lotus Connector
API. All Lotus base connectors work with all of the
Domino integration tools. For each Lotus Connector,
you must have the appropriate connectivity software
installed on the machine where DECS or LEI is
installed or at the location where LC LSX scripts will
be executed. Some of the Lotus Connectors and
the enterprise software versions that each supports
are listed below.
depending on the operating system where the DB2
server is located. On Windows, UNIX, or Linux
systems, connectivity to DB2 Universal Database
(UDB) versions 7.2 and 8.1 is supported, including
Workgroup, Enterprise, and Personal Editions. On
the iSeries, the Lotus Connector for DB2 supports
DB2/400. Access to DB2/390 is supported when
connecting to DB2 UDB for OS/390 Version 6, and
DB2 for z/OS and OS/390 Version 7. The Lotus
Connector for DB2 supports Connect Enterprise
and Personal Editions of DB2 UDB.
Note that a Lotus Connector may not give you access
to all features of the native DBMS, such as stored
procedures (not all native DBMSs allow full access to
their stored procedures). For online documentation
with full instructions on using the Lotus Connectors,
refer to the Lotus Connector and Connectivity Guide
at http://www-12.lotus.com/ldd/doc/lei/6.0.1a/
lccon6.nsf.
Oracle
DB2
The Lotus Connector for DB2 supports several
versions of IBM’s DB2 Universal Database (UDB),
110
Lotus has connectors for Oracle server versions 7
and 8, as well as an ODBC connector that can be
used to connect to any version of Oracle, including
Oracle 9. The Lotus Connector for Oracle 7 requires
Oracle server version 7.3.4 and SQL*Net client
connectivity on the Domino server where the application
will run. The Lotus Connector for Oracle 8 requires
Oracle server version 8.05 or 8i, plus the matching
version of the Oracle 8 client. The Lotus Connector
for Oracle 8 supports the initial release of Oracle 9,
but to date it has had no functionality added to cover
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
Example 1 — Making the
Connection in Real Time
with DECS
In this example, the objective is to display employee
records in the Employee Example Domino application
and to implement the activity that runs the CALCBONUS
stored procedure. We encourage you to try this example
because the other examples make use of the Domino
documents this exercise generates.
To execute this example, you’ll need access to
the DECS Administrator (decsadm.nsf), a Domino
application created during Domino setup.6 (The side6
DECS must be selected during the setup.
updates of Oracle 9. To take full advantage of
Oracle 9 functionality, we recommend using the
Lotus Connector for ODBC. (Note: The ODBC
connector does not support output parameters.)
bar on page 108 has information that can help you
find it.) If you use LEI instead of DECS, you can
follow along using similar tabs in LEI Administrator.
Be sure to start by selecting Virtual Fields under the
Add Activity tab.
A DECS connection document gives Domino
the information it needs to connect to an enterprise
system. A DECS activity document relates a connection
defined in a connection document to a form in a
Domino application; it also performs these tasks:
•
Specifies the data fields involved, mapping
the fields in the form to corresponding fields
in DB2
•
Specifies what document event triggers the
integration activity
Microsoft SQL Server, and other data sources
for which there is no native Lotus Connector
(MySQL, etc.). The ODBC connector requires a
configured ODBC data source for the system that
you’ll access.
Sybase SQL Server
The Lotus Connector for Sybase supports
Sybase SQL Server version 11.9.2 on Windows
and 11.1.1 on Solaris and AIX. We recommend
the Lotus Connector for ODBC for use with
newer versions of Sybase SQL Server.
Microsoft SQL Server
The Lotus Connector for OLE DB supports
Microsoft SQL Server 7, SQL Server 2000, and
Microsoft Access 2000. For connecting to SQL
Server, the Lotus Connector requires Microsoft
Data Access Components (MDAC) 2.0 or higher;
we recommend MDAC 2.5.
ODBC Data Access
The Lotus Connector for ODBC supports
connectivity to DB2, Oracle, Sybase SQL Server,
No portion of this publication may be reproduced without written consent.
As of this writing, the ODBC connector does not
support output parameters in stored procedures.
The examples in this article that use output
parameters should not be attempted when
using the ODBC connector. Support for output
parameters is planned for a future revision of
the connector.
Premium Connectors — SAP R/3, others
IBM also provides some premium connectors,
such as a connector to SAP R/3 that supports
output parameters. In SAP, these output parameters
are called output tables or export parameters of
Remote Function Calls (RFCs) or BAPIs. While
this connector works with DECS, LEI, and LC LSX,
it is not included with these products and is an
additional purchase.
111
THE VIEW November/December 2003
Your starting point for building DECS connections
and activities is the DECS Administrator. Figure 5
shows a typical main screen, including a view of
existing activities and a navigation bar.
There are three basic procedures to follow when
establishing a connection and activity with DECS:
1. Define the connection to the external system.
2. RealTime-enable your Domino application by
defining a DECS activity for it. In the activity,
choose the appropriate connection and the Domino
application; map the data between the external
system and the Domino application; select a trigger
event; and select the stored procedure to run.
3. Start the activity.
Let’s walk through each of these procedures as
you add CALCBONUS to your sample application.
Defining the DECS Connection to the
External System
Start by clicking Add Connection in the DECS
Administrator. Select “Connect to DB2” as your
connection type.
Remember that to make the DB2 connection,
you’ll need the name of the sample DB2 database, a
Figure 5
112
valid database user name, and a password with the
appropriate access. (We used the SAMPLE database
and the user name db2admin in the completed
Connectivity section shown in Figure 6.) If you’re
not sure what database user name or password to
include in the connection document, ask your DB2
administrator.
You can leave the Selection Type radio button at
the default (Table), since the DB2 data is in a table.
There are other connection options to further
refine or control data access, but they are not required
in this example.
Now that you have created a connection to
your DB2 system, you’re ready to create a DECS
activity that specifies a Domino application to use
with the connection.
Remember that to make the DB2
connection, you’ll need the name of
the sample DB2 database, a valid
database user name, and a password.
If you’re not sure what to include in
the connection document, ask your
DB2 administrator.
The DECS Administrator
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
Figure 6
Creating a DECS Connection to DB2
Enabling the Domino Application — Creating
an Activity Document Using DECS
To create the DECS activity, follow these steps:
1. Select “Add Activity” from the DECS
Administrator and name your new activity.
The Add Activity button is in the navigation
bar of the Activities view of your DECS
Administrator. (Refer to Figure 5.) If you do
not see this button, make sure that you are in
the “Activities” view.
A blank form called “Virtual Fields Activity”
will appear, in which you can create your activity
document. Specify a name for your activity
under the Identification section.
2. Select your Domino application and form.
Click on the browse button next to “Domino
Application” in your Virtual Fields Activity
form and find the Domino application that you
No portion of this publication may be reproduced without written consent.
A DECS connection document gives
Domino the information it needs to
connect to an enterprise system. A DECS
activity document relates a connection
defined in a connection document to a
form in a Domino application.
wish to associate with the DB2 connection (the
Employee Example database you created
earlier). This application must be on the same
server where DECS resides. Select the form
within the application that will be used by this
activity (Employee form, in this case).
3. Select your Lotus Connection. Click the
Browse button in the Lotus Connection section
to select the DB2 connection document that
will form the link between this activity and the
target DB2 database. You will be presented
113
THE VIEW November/December 2003
with a dialog box to select the metadata available
via that connection (see Figure 7). For this
example, you’ll use the DB2 connection created
earlier and specify the EMPLOYEES table.
You can see the activity form completed to this
point in Figure 8; we named it “Stored Procedure
Example.”
Completing the Activity — Mapping Fields,
Selecting the Event, Specifying the Stored
Procedure
Now we need to map the fields between the Domino
application and the DB2 table, select the document
event to monitor for this activity, and specify a
procedure to run when the event occurs.
To map the fields in the activity, go to the
Mapping area of the activity form and select a key
field or fields that will uniquely associate each
document in Domino with a table row (or record) in
Figure 7
114
DB2. Then, map the fields in your Domino form to
corresponding fields on your DB2 side.
Because “EMPNO” is the input parameter that
our procedure requires, it will also be our key field.
Map the fields LAST_NAME, FIRST_NAME,
DIV_NAME, SALARY, and BONUS on your
Domino form to their corresponding entries on the
DB2 side. All of these fields must be mapped
because they are returned as output parameters
from our sample procedure. Also, notice that the
Domino field names must match the DB2 table’s
column names. When you have completed your
mapping, your activity form should look like one
shown in Figure 9.
Next, you must specify an action in the
Domino application that will trigger the stored
procedure in the DB2 system. Document events
for the Employee form in Domino are the possible
triggers for this activity. First, in the Events area
of the Activity form, select the Event Options tab;
then, select which events to monitor. You can see
The LEI Administrator Dialog for Selecting Metadata in the DB2 Database
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
Figure 8
The Virtual Fields Activity Form — “Stored Procedure Example”
Figure 9
The Virtual Fields Activity Form — Mapping Fields
DECS
No portion of this publication may be reproduced without written consent.
LEI
115
THE VIEW November/December 2003
in Figure 10 the four document event options this
tab offers.
Here is how DECS behaves when monitoring for
each type of event:
•
A Create event will intercept any new document
created in Domino and insert a new record into
the external system.
•
An Open event will display enterprise data when
a user opens a Domino document and will ignore
any changes made to the data in Domino during
the same session.
•
An Update event will take any changes entered
into the Domino document and cascade them out
to the external system.
•
A Delete event will simply remove the corresponding
records for a deleted Domino document from the
external system.
You can select one event or any combination of
events to monitor in the same activity. You specify
the procedure you want to use as you define each
event. In this example, we’ll work with a procedure
on the Open event. (For more background on event
Figure 10
116
monitoring with stored procedures, see the sidebar
on page 121.)
Click the Open tab and look at the options
for entering formulas and procedures. Enter your
stored procedure name (JPESCATE.CALCBONUS
in our example) in the Stored Procedure field and
press F9. Pressing the F9 key displays the parameters
that are required by the stored procedure — in our
example, CALCBONUS requires EMPNO (see
Figure 10).
Initializing Keys and Starting the Activity
To make a DECS activity operational, the developer
must do two things: 1) initialize the key fields to
populate them in the Notes database, and 2) start the
activity in DECS Administrator.
To initialize the key field for the example, make
sure you save the activity document to accept
changes; then click once on the activity document in
the Activity view of DECS Administrator and select
“Initialize Keys” from the action bar. (If you are
using LEI Virtual Fields, you will need to save and
reopen your activity and click on the Initialize
Selecting Events to Monitor — The Events Tab
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
Keys button.) For each row in the table, DECS (or
LEI Virtual Fields) now creates one new Domino
document using the Employee form.
Each of the Employee documents will have data
in the EMPNO field (the key field you designated in
the activity document). The other fields will remain
empty. DECS draws the key-field data from each
row in the table; since our example uses a DB2
table containing five records, DECS should create
five documents in your Domino application.
Okay, your Domino application should now be
ready to show data returned from the stored procedure.
DECS only brings data back from the enterprise
system when the activity is running. If you don’t
start the activity, the user won’t see any data populating
the Domino documents generated for the activity
during initialization, other than the values in the
initialized key fields. To start the activity, select
your activity from the Activities view in the DECS
Administrator and click Start on the Action bar.
Once you start the DECS activity, two things
happen every time an end user opens an Employee
document in your Domino application. First, the
non-key fields in the document will become populated
as DECS pulls information from the external system
into your application. The end user doesn’t have to
know (and probably does not care) that the data in
the form is not native Domino data and that it
resides in an external enterprise system. Second,
the end user will see the value of BONUS populated
with the results of the stored procedure for calculating
10% of the salary.
To demonstrate that the procedure is simply
calculating a value and passing it off to Domino
without changing any existing data in DB2, stop the
DECS activity (in DECS Administrator, open the
activity and click Stop). Remove the stored procedure
value from the Event Options/Open tab; then save
and restart the activity. Now open an Employee
document in the application. You’ll see the native
value of BONUS (nil) in your Domino application.
In other words, DB2 doesn’t have a value for
BONUS, so the field is empty.
No portion of this publication may be reproduced without written consent.
Now you’ve seen how DECS can integrate realtime data access with stored procedures by linking
Domino documents with rows of DB2 data. You’ve
seen the basics of building a DECS-enabled activity
— the steps are the same regardless of what type of
data source you access. The major point to remember
when using DECS with stored procedures is that the key
fields you specify in your activity will be passed to the
procedure as input parameters. Keep this in mind when
designing your application, activities, and procedures.
To make a DECS activity operational,
the developer must initialize the key
fields to populate them in the Notes
database and start the activity in
DECS Administrator.
Example 2 — A Scripted Solution
Using LC LSX
In this example, we’ll show you how to use LC
LSX to connect the Employee form in Domino to a
new stored procedure. Remember the Average Salary
field you created in the Employee form? We’re going
to create a stored procedure for calculating the average salary of the employee’s division, so the manager
(the hypothetical end user of the sample Domino
application) can compare it with the employee’s
current salary during the review process. This
example demonstrates how you can use LotusScript
and the Lotus Connector for DB2 to collect input
values from a Domino document, pass them to an
external procedure, and then fetch an output parameter
from a procedure’s results.
Using Scripts to Call Stored Procedures
Our procedure will take one input parameter,
DIV_NAME, that specifies the division for which we
want to determine the average salary and one output
parameter, AVERAGE, which we’ll use to pass the
calculated value back to our Domino application.
117
THE VIEW November/December 2003
Figure 11
Stored Procedure for Calculating the Average Salary in a Division
CREATE PROCEDURE JPESCATE.SALARYAVERAGE (IN DIV_NAME VARCHAR(50), OUT AVERAGE
DECIMAL(9,2))
LANGUAGE SQL
P1: BEGIN
DECLARE TEMP_DIV VARCHAR(50);
SET TEMP_DIV = DIV_NAME;
SET AVERAGE = (SELECT AVG(SALARY) FROM JPESCATE.EMPLOYEES WHERE DIV_NAME =
TEMP_DIV GROUP BY DIV_NAME);
END P1
Create the SALARYAVERAGE stored procedure
shown in Figure 11 in your sample DB2 database.
Figure 12
“Compute Average Salary”
Agent Properties
Once you have the new DB2 procedure, you can
alter the Employee form to use a script to call the
procedure and accept the data. The script we’ll use
creates an agent using the Lotus Connector for DB2
and performs the following tasks:
•
Establishes connection properties to DB2
•
Connects to DB2
•
Sets up properties to pass to DB2
•
Fetches data from the Domino form
•
Passes data to DB2
•
Invokes the procedure
•
Fetches the resulting output parameter from the
procedure
•
Presents the value from the procedure to the user
via a dialog box
In the Domino database where you created your
Employee form, select View > Design and open the
Employee form in Designer. Create a new agent and
call it “Compute Average Salary.” Leave the rest of
the options at the default settings, as in Figure 12,
118
and close the dialog box. (Note that the Runtime
default in release 6, “Action menu selection,” is
equivalent to the release 5 default, “Manually from
Action Menu.”) Now you have a shell in which you
can create the LC LSX agent.
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
Select “LotusScript” as the agent type and copy
the script from Figure 13. The second line, Uselsx
"*lsxlc" , identifies the agent as an LC LSX type.
The script establishes a connection to the DB2 database, defines the fields and field lists for passing data
Figure 13
to and from the stored procedure, and displays the
results of the procedure in a message box. Be sure to
change the connection and procedure options within
the script to match your particular installation. Save
your script and exit.
LotusScript Code for the “Compute Average Salary” Agent
Option Public
Uselsx "*lsxlc"
Sub Initialize
Dim Con As New LCConnection ("db2")
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim DIVISION As String
Set uidoc = workspace.currentdocument
' set properties to connect to your DB2 datasource
Con.database = "SAMPLE"
Con.Userid = "db2user"
Con.Password = "password"
' now connect
con.connect
' set the connection property to the stored procedure name
con.owner="JPESCATE"
Con.Procedure = "SALARYAVERAGE"
'give it the names of the output parameters used by the procedure
Con.FieldNames = "AVERAGE"
Dim Parms As New LCFieldList
Dim Result As New LCFieldList
Dim out As Double
' append the fields with input parameters to the fieldlist
Set PARM1 = Parms.Append ("DIV_NAME", LCTYPE_TEXT)
PARM1.text = uidoc.FieldGetText( "DIV_NAME" )
'call your procedure
out = Con.Call (Parms,1,Result)
'fetch the output value
out = Con.Fetch (Result,1,1)
'output the average to a message box
Msgbox "The average salary for the division is $" & Result.AVERAGE (0)
End Sub
No portion of this publication may be reproduced without written consent.
119
THE VIEW November/December 2003
Figure 14
Result of Running the “SALARYAVERAGE” Procedure
in DB2 via the “Compute Average Salary” Agent
Before executing the agent, be sure you have
employee division data in your Employee form.
If your DECS activity (or its LEI equivalent) is
running, you can use the division data that it
provides from the DB2 table. If you didn’t create
the activity in the DECS example, you can still run
this LC LSX example by manually creating documents
with the Employee form in the Domino database.
If you are creating your own records, be sure that
the DIV_NAME field contains either EASTERN
or WESTERN as values.
the value produced by the stored procedure, and present
it to you in a dialog box within the Domino application.
Figure 14 shows a typical outcome.
When you have some Employee documents, you
can try running the agent. (Enable LotusScript
debugging first if you want to see variables as they
are populated.) Open one of your Employee documents
and select Compute Average Salary from the Domino
Actions menu. The agent will take the value of the
DIV_NAME field in your Domino document, pass it
to the SALARYAVERAGE procedure in DB2, fetch
This simple example shows how you can use
LotusScript to take advantage of pre-existing enterprise
business logic in stored procedures from within your
Domino applications. The end user isn’t aware of
anything other than the normal experience of executing
a Domino agent. In our next example, you’ll see
another way to allow end users of Domino applications to seamlessly access stored procedures.
120
As long as the LotusScript agent runs from a
location where connectivity to the external system is
established, you can use the agent to call a stored
procedure from a Notes client, a Web browser, or any
of the clients that Domino supports. You can also
schedule your LC LSX agents using the agent builder
in Designer.
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
Example 3 — Using an LEI
Virtual Agent
Now we’ll show you how to call the SALARYAVERAGE
procedure using an LEI Virtual Agent. You can run
an LEI Virtual Agent from the Actions menu of a
Domino application activity, just like the LC LSX
agent in the previous example; however, creating LEI
Virtual Agents does not require any Domino-side
programming. An advantage of creating LEI Virtual
agents is that instead of writing agent code, you simply
select a Domino application, the connection document,
and the stored procedures to call.
To complete this exercise, you’ll need Lotus
Enterprise Integrator 6.x installed on a Domino 6
server. If you don’t have LEI, the “Additional
Resources” sidebar on page 126 tells how to get a
demonstration copy that is adequate for this example.
You’ll also need the SALARYAVERAGE stored
procedure created in the last exercise (shown in
Figure 11) and some Employee documents in your
sample Domino application (populated from the first
example or created manually).
To start, open the LEI Administrator. The LEI
Administrator, like the DECS Administrator, is a
Domino application with the filename decsadm.nsf
(the same as the filename for DECS Administrator).
When you install LEI on your server, it automatically
upgrades the DECS Administrator database, converting
connections and activities to LEI versions.
The steps to create a Virtual Agent are similar to
those in the DECS example — create a connection
document; create a Virtual Agent (the activity document);
select the connection and stored procedure; start the
activity. Let’s begin by making the DB2 connection.
Event Monitoring with Stored Procedures
DECS and LEI RealTime activities allow you to
monitor different events that occur in a Domino
database. The type of event you monitor determines
how the Domino application interacts with stored
procedures. Keep the following points in mind when
designing your activities:
Open Event Procedures
Any field specified as a key in your activity will be
passed to the procedure as an input parameter.
Any fields designated as output parameters by the
procedure must be mapped as data fields in your
activity. This means that any parameters defined in
your procedure must exist as fields in both your
Domino form and the enterprise-database table
you’re using in the activity.
Create or Update Event Procedures
Any field mapped in your activity (key fields as well
as data fields) will be passed to the stored procedure
as input parameters. The output parameters passed
back from the procedure do not have to exist in the
No portion of this publication may be reproduced without written consent.
table used by the activity. For example, the table of
employee data does not have to contain a Bonus
field for the stored procedure in the first example
to calculate the bonus and pass it to the Domino
application. You must also have a field defined on
your Domino form if you want to capture the output
parameter value. The output parameter and Notes
field do not have to be mapped in your general
field mapping list in the LEI Virtual Agent activity
document, but they must be selected in the “Stored
Procedure Output Mapping” section of the DECS
activity document or the LEI Virtual Agent or Virtual
Field activity document.
Delete Event Procedures
If you are invoking a procedure on the Delete event,
no document is available to capture any output
parameters that would be defined by your procedure.
By the time the procedure has been executed, the
initiating data in your Domino application is gone.
Any fields designated as key fields in your activity
will be passed to the procedure as input parameters.
121
THE VIEW November/December 2003
Creating an LEI Connection Document
Creating a Virtual Agent Document
In the LEI Administrator, click Add Connection.
Following the same procedure you used in the
DECS example, create a connection document
to your DB2 database. You’ll need to specify
the DB2 database, user ID, and password in
the document.
To open the Virtual Agents form, click Add Activity
in LEI Administrator and select Virtual Agents (see
Figure 15), or click Virtual Agents from the Create
menu. The tasks to create a Virtual Agent are similar
to the ones you would use in DECS to establish an
activity. You can refer to Figure 16 to see how the
form should be configured as we go through the steps:
If you used LEI in the first example, you can
use the same connection document in this example.
Once an LEI connection document is complete, you
can use it repeatedly in multiple activities.
Figure 15
122
1. Select a Domino application.
Use the same application you created for the
other examples (Employee Example).
LEI Administrator — Creating a Virtual Agent
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
2. Select a Lotus Connection.
Use the connection you created for the
EMPLOYEES table in DB2.
3. Select a Stored Procedure.
LEI shows you the stored procedures by owner.
To select the procedure:
-
Select the External Owner Name you used
when creating the SALARYAVERAGE
procedure.
-
Click the Agent Pattern List browse button
and select the SALARYAVERAGE procedure.
Figure 16
4. Select “Save Output Parameters”.
Find it under the General Options tab. This LEI
option enables your Domino form to capture the
value of SALARYAVERAGE once it’s fetched
from the procedure. (See Figure 16.)
5. Name, save, and close the agent.
For this exercise, call the agent
SALARY_AVERAGE.
You may have noticed other activity options (such
as scheduling and SQL logging) in the connection
Virtual Agents Activity Form
No portion of this publication may be reproduced without written consent.
123
THE VIEW November/December 2003
document. We won’t go into these in this article; see
the LEI documentation for more details.
No Manual Initializing
You probably noticed that while you must select key
fields for DECS Virtual Fields, you do not need to
select key fields for LEI Virtual Agents. If the stored
procedure requires input parameters, the Virtual Agent
passes the correct input parameters from fields and
inserts the results in the selected Domino document.
If the stored procedure doesn’t require input parameters,
the Virtual Agent acts against all documents in the view
that is active at the time the Virtual Agent is invoked.
So in our example, the Virtual Agent runs against the
document you select because the SALARYAVERAGE
procedure takes the DIV_NAME field as an input parameter. If our sample procedure did not require any input
parameters, the Virtual Agent would run the procedure
and insert the results in all documents in the active view.
Troubleshooting
When an application misbehaves, it’s imperative
to isolate the problem before trying to fix it.
Troubleshooting a Domino application that’s linked
to a system that is using stored procedures is an order
of magnitude more difficult than working through a
bug in a stand-alone application. You must determine
if the problem is coming from the stored procedure,
the enterprise system, or the Domino application.
Depending on the complexity of each
of these levels, the debugging experience can be a
frustrating process. Fortunately, the nature of the
symptom can sometimes point you towards the area
where the problem lies. The following guidelines can
help you start the debugging process.
If a Domino application that uses a stored
procedure runs but the results are wrong:
•
Make sure the LEI or DECS activity is running.
This is tantamount to making sure your PC is
plugged in when it won’t power up, so it should
be the first thing you check.
•
Use the Domino Designer debugger to step through
LC LSX code. Make sure the fields and parameters
you’re sending and retrieving are correct.
•
Test the stored procedure outside of the Domino
application. Use SQL or a tool like Computer
Associates’ CleverPath Forest & Trees to make
sure the stored procedure is doing what you
expect it to do.
•
Look at the enterprise data against which the
stored procedure is run. If the underlying data is
bad, no amount of massaging will make it better.
Any SQL tool will allow you to examine the data
without invoking the stored procedure.
Since developers don’t have to select key fields,
Virtual Agents do not require a key initialization task.
Starting the Virtual Agent Activity
Once you’ve saved and closed the Virtual Agent, you
need to start it for end users. To start the agent from
LEI Administrator, select the activity document and
click the Start button.
Note that if your activities are set to autostart, you
don’t need to restart them if the Domino application
server goes down.
After you’ve started your Virtual Agents Activity
from LEI, end users can invoke it to run from the
sample Domino application as they would any
Domino agent. Test the agent by selecting an
Employee document in your Domino application and
then selecting the SALARY_AVERAGE agent from
the Actions menu. Clicking the Virtual Agent’s name
executes the stored procedure on the DB2 server and
populates the Average field in your open document.
If this example (or either of the others) didn’t work as
expected, the suggestions in the next section may help.
124
If the application doesn’t run at all:
•
Again, make sure that the LEI or DECS activity
is running and the Domino server on which the
application lives is up and running.
•
Make sure that the back-end system is running
and in good order.
www.eVIEW.com
©2003 THE VIEW. All rights reserved.
Let Your Relational Database Do the Work — How to Invoke Stored Procedures from Domino 6 Applications
•
•
Check any error messages for signs that the
problem is local to Domino or may be coming
from the backend. If you see anything with a
SQLState or SQL error, there’s a good chance
that the problem is with the enterprise system,
not Domino. If the errors are of the familiar
Domino ilk, it’s possible (but not certain) that the
problem is local. Domino treats data from stored
procedures in a manner similar to native data, so
the fact that you receive a Domino error doesn’t
mean the problem originates in Domino.
Make sure the information in the connection
document (user name, password, and so on) is
correct. If you can’t connect to the external
database, the Domino application will not behave.
Other Options for Optimizing
Performance and Security
Any time you start to write SQL (or other) code that
touches an enterprise system, you introduce the potential
for performance hits and security breaches. You have
several built-in options to address these concerns, depending on which type of connection tool you are using. We’ll
introduce you to the most frequently used ones here.
Performance
For better application performance, you’ll want to
ensure that the fields you have specified as keys in
your DECS or LEI activities or as input parameters
for your LC LSX scripts are indexed in the DBMS
database for faster access. As in our examples, the
less time it takes DB2 to find the records and values it
needs to execute your stored procedures, the greater
the performance gain on your application. Indexing
database records may require assistance from your
DBAs. If so, provide them with the name of the table
and key fields used in the activity.
If you’re using an LC LSX agent to call a stored
procedure, it could be a bit tricky to determine which
table and fields need to be indexed. Unlike DECS
and LEI, there is no LC LSX Administrator in which
to map them; with LC LSX, you’ll need to have a
No portion of this publication may be reproduced without written consent.
good understanding of what input fields the stored
procedure requires. Depending upon the composition
of your procedure, any table being referenced by an
activity or script should be indexed according to the
input parameters requested by the procedure. Again,
your DBA should be able to provide you with assistance.
In RealTime activities, another way to optimize
performance is with the Max Connections field on
the forms for the Virtual Field (DECS and LEI) and
Virtual Agent (LEI) activities. (You can see this field
in Figures 9 and 16.) In both cases, the value of this
field controls how many concurrent users can create,
open, update, or delete records simultaneously.
Lotus and other systems refer to the concept
underlying Max Connections as “parallel persistent
connections.” The system makes a connection to the
database for each user for each event and then releases
the connection back into the pool of available
connections as soon as processing is completed. If
more than one user requires a connection, or more than
one event is triggered at the same time, an additional
connection is created, used, and then released back
into the pool. As more concurrent users or events
require access, the system creates these connections
until the value specified in Max Connections is reached.
After that, DECS or LEI 6 queues user requests and
executes them as connections become available. For
example, say a user is accessing an application with a
Virtual Agent that calls a procedure in DB2 that
updates a record in a Domino application. When the
user invokes the agent by clicking an Update button
or selecting it from the Domino Action menu, LEI
makes a connection to DB2 and calls the stored
procedure; the stored procedure executes; and the LEI
agent then displays the results in the Domino form. As
soon as the action completes, LEI releases the connection
and the system makes it available for others to use.
Most applications are efficient with two (the
default) or three parallel connections, depending upon
the number of users accessing the particular agent and
how long it takes the agent to complete. If users
experience significant delays when invoking or executing
the agent, try increasing the number of maximum
connections in the corresponding activity document.
125
THE VIEW November/December 2003
Additional Resources
Lotus EI
For documentation on Lotus Enterprise Integration
products, go to the Lotus Developer Domain
Documentation Library at, http://www-10.lotus.com/
ldd/notesua.nsf. Click the by product link and
select from the following:
•
Domino Enterprise Connection Services
(DECS)
•
Enterprise Integrator (LEI) — We recommend
that you look at Installation of LEI and
configuration information for Connections
and Activities
•
Lotus Connectors LotusScript Extensions
Guide (LC LSX)
Security
When you set up Virtual Field or Virtual Agent activities
that use a DBMS, you specify one DBMS user name
and password. Every user calling your Virtual Agent
or working with RealTime-enabled documents uses
these DBMS credentials. DBMS administrators are
understandably wary if the DBMS user specified in
the activity’s connection document has global rights,
however. To enhance security, LEI 6 allows you to
have application users use their own credentials
against the enterprise system, rather than those specified
in the connection document. This functionality,
called “Integrated Credentials,” adds another layer of
security on top of the traditional Domino security
model. You can find more information on this new
functionality in the “LEI Activities and User Guide.”
Conclusion
With release 6 integration tools, using stored procedures
from Domino applications is easy and can vastly
increase your effectiveness and range as a developer.
By using stored procedures to harness the power of
your enterprise-database engine, you can reduce network
traffic, enhance functionality, and improve performance
126
For IBM’s Lotus Enterprise Integration pages, including
spec sheets, free downloads, success stories, and a
discussion forum, visit http://www.lotus.com/ei.
Another source of information is the developerWorks
Product Pages for Lotus Enterprise Integration at
http://www-10.lotus.com/ldd/products.nsf/products/ei.
These pages list technical information, downloads,
forums, white papers, and articles on the full suite of
Enterprise Integration products.
IBM DB2
The IBM DB2 Universal Database Web site,
http://www-3.ibm.com/software/data/db2/udb/, provides
technical information, documentation, downloads,
services, and support information for IBM DB2.
in your Domino applications. We look forward to
hearing what you do with stored procedures in your
release 6 applications.
Joe Pescatello is an IBM software developer
currently working on IBM Lotus Workplace
Messaging. Joe has more than 15 years of
experience in the industry. His areas of expertise
include Java, portlet development, and database
technologies. In addition to publishing technical
articles in a variety of publications, he has delivered
technical presentations in the US and across
Europe on LEI 6, DECS, and stored procedures.
Joe has filed four patents in the area of portlet
development and computer security for IBM.
Sarah Boucher manages the Enterprise
Integration team for IBM in the Lotus Software
division. She recently published an e-Pro Magazine
article on Advanced RealTime in LEI 6. Sarah
has also delivered presentations at LotusSphere
on using external data and business logic to
make Domino applications more powerful. You
can reach her at [email protected].
www.eVIEW.com
©2003 THE VIEW. All rights reserved.