IBM®
Skip to main content
    Country/region [select]      Terms of use
 
 
      
     Home      Products      Services & solutions      Support & downloads      My account     

developerWorks > Lotus
developerWorks
Using DB2 stored procedures with LSX LC, LEI, and DECS
e-mail it!
Contents:
LSX LC
LEI and DECS
Virtual fields
Virtual Documents
Virtual Agents
Direct Transfer activity with stored procedure as source
Direct Transfer activity with stored procedure as target
Summary
Resources
About the author
Rate this article
Related content:
Sample code in the Sandbox
LEI 6 Technical Overview
Integrating the enterprise
New features in Notes/Domino 6.5
Subscriptions:
dW newsletters
dW Subscription
(CDs and downloads)

Level: Advanced

Scott Morris
Senior Software Engineer, IBM Corporation
8 December 2003
Updated 9 December 2003

Add Notes/Domino collaborative capabilities to DB2 with Lotus Connectors LotusScript Extensions (LSX LC), Lotus Enterprise Integrator (LEI), or Domino Enterprise Connection Services (DECS). Use one of these three products with DB2 stored procedures.

Using DB2 stored procedures in your Domino applications can greatly expand your possibilities. Imagine having all of the abilities of Lotus Notes and Domino (such as collaboration or working off-line) available for DB2. The details required to make this happen, however, can be problematic because few Domino application developers are also expert DB2 developers. In this article, we discuss examples of how to work with Domino 6.x and DB2 stored procedures. First, we show how to use DB2 stored procedures with Lotus Connector LotusScript Extensions (LSXLC), and then with Lotus Enterprise Integrator (LEI) and Domino Enterprise Connection Services (DECS). (You can download the code examples used in the article from the Sandbox.)

This article assumes that you're an experienced Notes/Domino programmer, ideally with some familiarity with DB2. It also assumes knowledge of Lotus Connectors LotusScript Extensions, LEI, and DECS.

LSX LC
There are three types of examples for LSXLC covered in this article:

  • Calling a DB2 stored procedure that has no input or output parameters and returns no result set. Such a procedure can be used for starting dependent processes, for setting flags that could be monitored from a trigger, and so on.
  • Calling a DB2 stored procedure that has input parameters and returns a result set. In this case, the input parameters are generally used to select a result set. The beauty of the stored procedure is that the complexity of the SQL used to generate the final result set has virtually no limits.
  • Calling a DB2 stored procedure that has input and output parameters. This is essentially a scaled-down version of returning a result set. If you are working with scalar values, it is the easiest way to get a result from DB2.

We'll look at each of these three cases in turn.

A DB2 stored procedure that has no input or output parameters and returns no result set
This is the easiest case to implement. The first thing that we need is our stored procedure definition:

Listing 1. Stored procedure definition

CREATE PROCEDURE DB2ADMIN.NOUI (  )
LANGUAGE SQL

P1: BEGIN
INSERT INTO DB2ADMIN.FLAG_TABLE (FLAG) VALUES (1);
COMMIT;
END P1

You can use this procedure in a number of ways. Let's assume that executing this acts as a trigger for some other process. When the other process sees the single row in this table (which has only one column FLAG with the value of 1), it should execute its commands, after it deletes the one row from the FLAG_TABLE. The LotusScript for this is as follows:

Listing 2. LotusScript

Option Public
Uselsx "*LSXLC"

Sub Initialize

'Nice feature, makes the code much cleaner
On Error Goto errorhandler          

'Used for error reporting
Dim session As New LCSession         
'Our connection to DB2
Dim conn As New LCConnection("db2") 

'Name of our database as defined in DB2 client
conn.database = "toolsdb"           
conn.userid = "db2admin"
conn.password = "DominoRules"
'Must always be user name/schema. procedure name
conn.procedure = "DB2ADMIN.NOUI"    

'Log onto DB2
conn.Connect                        

'We know that our procedure has no result set, so count will always be 0
Dim count As Integer              
                                           	
'Use Call method for stored procedures, 
'and Nothing to denote LCFieldlists that we don't need
count = conn.call(Nothing,1,Nothing)
                                    
'If we are here, it worked!
Exit Sub                            

errorhandler:

Dim Msg As String
Dim Msgcode As Long
Dim status As Integer
Dim result As String

If session.status >< LCSUCCESS Then
'Turn the error info into a string
status = session.GetStatus(result,Msgcode,Msg) 
End If
'Display error text
Msgbox result   

End Sub

A DB2 stored procedure that has input parameters and returns a result set
This type of stored procedure can basically do anything we want. It can perform dynamic SQL, complex joins, simple selects, updates—anything! The way to have a result set available to the DB2 connector is to open a cursor in the procedure and not close it. This example performs a simple select based on the input parameter:

Listing 3. Simple select

CREATE PROCEDURE DB2ADMIN.GET_EMPLOYEE_NAME (IN EMPID INT)
RESULT SETS 1
LANGUAGE SQL

P1: BEGIN
DECLARE EMPID_VAL INT;
/* Here we declare the cursor so that we can use it later */
DECLARE cursor1 CURSOR WITH RETURN FOR      
SELECT FNAME,LNAME FROM DB2ADMIN.EMPLOYEES  
/* No where clause would give us a bigger result set */ 
/* or we can restrict it as our application requires */
WHERE DB2ADMIN.EMPLOYEES.EMPID = EMPID_VAL;  

/* We are not allowed to use the import parameters directly, */	
/* so we have a local copy to use instead. Our parameters come */
/* from using the same names as the table columns. SQL can't */
/* distinguish if I am referring to the in parm or the table column */ 
/* when I use the value EMPID. We don't have to do this here */
/*  (i.e. the table column names don't have to match our */
/* parameters in this case, but we are really setting the stage */
/* for the situation we must enforce in the DECS/LEI situation). */			
SET EMPID_VAL = EMPID;      
			
/* This line actually runs the select */
OPEN cursor1;       
END P1 

The important parts of this simple stored procedure are:

  • We must have a local copy of import parameters to use them.
  • We must correctly declare the cursor.
  • We must leave the cursor open.

The LotusScript needed is now more complicated than in the first example because we need an input LCFieldlist to hold the values of the stored procedure input parameters as well as an output one:

Listing 4. LotusScript

Option Public
Uselsx "*LSXLC"

Sub Initialize

'Nice feature, makes the code much cleaner
On Error Goto errorhandler	

'Used for error reporting
Dim session As New LCSession	 
'Our connection to DB2
Dim conn As New LCConnection("db2")	

'Name of our database as defined in DB2 client
conn.database = "toolsdb"	
conn.userid = "db2admin"
conn.password = "DominoRules"
'Must always be user name/schema.procedure name
conn.procedure = "DB2ADMIN.GET_EMPLOYEE_NAME"	

'Log onto DB2
conn.Connect	

'As the name suggests, will hold our input parameters
Dim input_parameters As New LCFieldList  
'Will hold our result set
Dim result_set As New LCFieldList	
'Our input parameter
Dim empid As LCField	

'Add our input parameter to the input field list
'The string used for the field name - EMPID - must
'exactly match the name in the procedure
Set empid = input_parameters.Append("EMPID",LCTYPE_INT)			
'Set its value. A real application would probably get this from the user.
empid.Value = 1	

Dim count As Integer
'Execute the stored procedure
count = conn.call(input_parameters,1,result_set)	

'Will hold employee's first name
Dim fname As LCField	
'Will hold employee's last name
Dim lname As LCField	

'Point our FNAME field to the FNAME column in the result set
Set fname = result_set.Lookup("FNAME")		
'Point our LNAME field to the LNAME column in the result set
Set lname = result_set.Lookup("LNAME")		

'Get the results. If there is more than one record, loop.
While(conn.Fetch(result_set))	
Print "FNAME = " + fname.Text(0) + " LNAME = " + lname.Text(0)
Wend

'If we are here, it worked!
Exit Sub		

errorhandler:

Dim Msg As String
Dim Msgcode As Long
Dim status As Integer
Dim result As String

If session.status >< LCSUCCESS Then
'Turn the error info into a string
status = session.GetStatus(result,Msgcode,Msg)		
End If
'Display error text
Msgbox result   

End Sub

Note that the input parameter names must exactly match those of the procedure; we can't map them to some other name.

We now have a result set to fetch from. We set up the fields that we want from the result set before the first fetch; don't do this inside of the fetch loop.

A DB2 stored procedure that has input and output parameters
The DB2 connector has the limitation that if there are output parameters available, any result set that may also be available is ignored. Keep this in mind when you are developing DB2 procedures to use with Domino.

This example is a repeat of the last procedure used in the preceding example, except that the FNAME and LNAME are output parameters instead of a result set. This limits us to—at most—a single record or row of data to return:

Listing 5. LotusScript

CREATE PROCEDURE DB2ADMIN.GET_EMPLOYEE_NAME2 (IN EMPID INT, OUT FNAME CHAR(32), 
  OUT LNAME CHAR(32))
LANGUAGE SQL
P1: BEGIN
DECLARE EMPID_VAL INT;
 /* Local copy of import parameter */
SET EMPID_VAL = EMPID;      

/* Place values into output parameters */
SELECT FNAME, LNAME INTO FNAME, LNAME FROM DB2ADMIN.EMPLOYEES   
WHERE EMPID = EMPID_VAL;
END P1

To call this, we need the following LotusScript:

Listing 6. LotusScript

Option Public
Uselsx "*LSXLC"

Sub Initialize

'Nice feature, makes the code much cleaner
On Error Goto errorhandler          

'Used for error reporting 
Dim session As New LCSession        
'Our connection to DB2
Dim conn As New LCConnection("db2") 


'Name of our database as defined in DB2 client
conn.database = "toolsdb"	
conn.userid = "db2admin"		      
conn.password = "DominoRules"
'Must always be user name/schema . procedure name
conn.procedure = "DB2ADMIN.GET_EMPLOYEE_NAME"	

'Log onto DB2
conn.Connect	

'As the name suggests, will hold our input parameters
Dim input_parameters As New LCFieldList	
'Will hold our output parameters
Dim output_parameters As New LCFieldList	
'Our input parameter
Dim empid As LCField	

'Add our input parameter to the input field list
'The string used for the field name - EMPID - must
'exactly match the name in the procedure
Set empid = input_parameters.Append("EMPID",LCTYPE_INT)		
					
'Set its value. A real application would probably get this from the user.
empid.Value = 1   

'We must set the output parameter names here. The sum of the fields found 
'in the input_parameters field list and the field names here must equal the
'input and output parameters of the procedure
conn.fieldnames = "FNAME,LNAME"     
Dim count As Integer
'Execute the stored procedure
count = conn.call(input_parameters,1,output_parameters)  

'Will hold employee's first name
Dim fname As LCField   
'Will hold employee's last name
Dim lname As LCField   

'Point our FNAME field to the FNAME column in the result set
Set fname = result_set.Lookup("FNAME")  
'Point our LNAME field to the LNAME column in the result set
Set lname = result_set.Lookup("LNAME")  

'Get the output parameters. No loop - only one "row" of data
Call conn.Fetch(result_set))  
Print "FNAME = " + fname.Text(0) + " LNAME = " + lname.Text(0)

'If we are here, it worked!
Exit Sub		

errorhandler:

Dim Msg As String
Dim Msgcode As Long
Dim status As Integer
Dim result As String

If session.status >< LCSUCCESS Then
'Turn the error info into a string
status = session.GetStatus(result,Msgcode,Msg)		
End If
'Display error text
Msgbox result   

End Sub

The important things to notice here are:

  • LCConnection.fieldnames must be set to the names of any output parameters (or IN/OUT parameters).
  • We only fetch once because there is at most one row of data.

These are the different possibilities for using DB2 stored procedures with the LSXLC and the DB2 connector. We hope that you find these examples useful.

LEI and DECS
Now let's look at how to use DB2 stored procedures together with LEI 6 and DECS 6. The RealTime Services which are DECS are also available in LEI. Because of this, there is no need to show separate DECS examples. Our LEI 6 Virtual Fields examples also apply to DECS. (In one case, there is a feature available to LEI that is not available in DECS, which we point out when we encounter it.) For more information about LEI and how you can use it, see the LDD Today article, "LEI 6 Technical Overview."

This section is organized by the types of LEI activities being used. We cover the following activities:

  • Virtual Fields
  • Virtual Documents
  • Virtual Agents
  • Direct Transfer

For each activity, we show the place where a DB2 stored procedure can be used. Each example is based on a very simple Domino database. It has a form called Employee with three fields named EMPID, FNAME, and LNAME.

Virtual fields
A Virtual Fields (VF) activity has four different events that occur in a Domino database and that can be monitored. These events are Document Create, Open, Update, and Delete. For each of these events, you can use DB2 stored procedures, although each event has its restrictions as to the design of supported DB2 procedures. As the first step, you build a VF activity that doesn't use any stored procedures. Then you add procedures to each event, one at a time, to show how it can be done.

First, you need to create a DB2 connection document in the LEI administration database that defines how to access DB2:

Figure 1. DB2 Connection document
DB2 Connection document

In your Connection document, do not select any tables, views, or procedures. This allows you to use this connection document with any of the three. Then do the following:

  1. Define a VF activity to work with the Employee database and the Employee form.
  2. Select the Domino database and the form in this database that should be monitored.
  3. Select the DB2 table that you want to work with.
  4. Map the keys field(s) from the Domino form to the DB2 table. The keys define which record to select from the DB2 table.
  5. Map the fields (known as RealTime fields) which are the fields/columns that we want from a given record.
  6. Finally, tell the activity which events to monitor, which in this case means all four possible events: Create, Open, Update and Delete.

Here is how it should look:

Figure 2. Virtual Fields Activity document
Virtual Fields Activity document

As you can see, this is a very simple example. You use the Initialize Keys feature of the activity, which creates documents in the Domino database containing the field EMPID. The FNAME and LNAME fields are the RealTime fields, which are never stored in Domino. They are always kept in DB2.

Virtual Fields activity Create event
The first event that you work with is the Create event. The Create event has the following restrictions for DB2 procedures:

  • The procedure must have as many input parameters as there are values in the fields Key(s) and Field(s).
  • Any result set is ignored.

Go to the Event Options - Create tab and enter a procedure name in the Stored Procedure field. Then press F9 to display the following:

Figure 3. Event Options - Create tab
Event Options - Create tab

The important thing to note here is that the input field names for the procedure are displayed next to the procedure name after you press F9. The activity is telling us exactly which input parameters must be available on our procedure. Here is the procedure definition:

Listing 7. Procedure definition

CREATE PROCEDURE DB2ADMIN.CREATE_EMPLOYEE (IN EMPID INT, IN FNAME CHAR(32), 
   IN LNAME CHAR(32))
LANGUAGE SQL
P1: BEGIN
DECLARE EMPID_VAR INT;
DECLARE FNAME_VAR CHAR(32);
DECLARE LNAME_VAR CHAR(32);

/* Remember to make local copies of the imports. */
/* Now it becomes clear why this was done in prior */
/* procedures. Parm names are forced by activity */
/* mapping, therefore, they match table columns. */
/* Need to be explicit in proc when we are referring */ 
/* to column names and when we are referring to */
/* parms. Because they are the same, local copies */
/* with different names are needed. */
SET EMPID_VAR = EMPID;   	
SET FNAME_VAR = FNAME;
SET LNAME_VAR = LNAME;

INSERT INTO DB2ADMIN.EMPLOYEES (EMPID, FNAME, LNAME) VALUES (EMPID_VAR, FNAME_VAR, 
LNAME_VAR);
COMMIT;
END P1

Admittedly, this is not a sophisticated example; this stored procedure is doing exactly what would have been done directly with DB2ADMIN.EMPLOYEES had no stored procedure been called. But it shows how it works.

Under the Stored Procedure field in the VF activity form, see the Stored Procedure Output. This means that you can return output parameters (if available) to the source Domino document. Building on the previous example, you can change your procedure as follows:

Listing 8. Procedure definition

CREATE PROCEDURE DB2ADMIN.CREATE_EMPLOYEE (IN EMPID INT, IN FNAME CHAR(32), 
   IN LNAME CHAR(32), OUT STATUS CHAR(10))
LANGUAGE SQL
P1: BEGIN
DECLARE EMPID_VAR INT;
DECLARE FNAME_VAR CHAR(32);
DECLARE LNAME_VAR CHAR(32);

/* Remember to make local copies of the imports */
SET EMPID_VAR = EMPID;  
SET FNAME_VAR = FNAME;
SET LNAME_VAR = LNAME;

INSERT INTO DB2ADMIN.EMPLOYEES (EMPID, FNAME, LNAME) VALUES (EMPID_VAR, FNAME_VAR, 
   LNAME_VAR);
COMMIT;

SET STATUS = 'SUCCESS';
END P1

Then add the STATUS field to the Employee form and update the VF activity as follows:

Figure 4. Event Options - Create tab
Event Options - Create tab

Now you have a new section with the field mapping of the output parameter to your Employee form STATUS field. With the activity running, when you enter EMPID, FNAME, and LNAME and save the document without closing, the STATUS field is automatically filled:

Figure 5. Employee form
Employee form

Virtual Fields active Open event
The Open event of the VF activity must conform to the following:

  • Stored procedure has input parameters that match the keys.
  • Stored procedure has no in/out or output parameters.
  • Stored procedure must return a result set which has columns named exactly the same as the values found in Field(s).

Note: With LEI 6.0, 6.0.1, and 6.0.2, this feature does not work properly; you cannot return a result set here. This issue is fixed in LEI 6.5.

Under the VF Event Options - Open tab, enter the DB2ADMIN.GET_EMPLOYEE_NAME procedure, which is defined in an earlier section of this article:

Figure 6. Event Options - Open event
Event Options - Open tab

(EMPID) shows up after the procedure name, telling you that this procedure must have the key field as an input parameter, which our example does.

Virtual Fields activity Update event
The Update event of the VF activity must conform to the following:

  • Stored procedure has input parameters just like with the Create event, meaning all keys and data (also known as RealTime) fields must be available as inputs.
  • Result sets, if available, are ignored.

Here is the DB2 procedure:

Listing 9. DB2 Procedure

CREATE PROCEDURE DB2ADMIN.UPDATE_EMPLOYEE_NAME (IN EMPID INT, IN FNAME CHAR(32), 
   IN LNAME CHAR(32))
LANGUAGE SQL
P1: BEGIN
DECLARE EMPID_VAR INT;
DECLARE FNAME_VAR CHAR(32);
DECLARE LNAME_VAR CHAR(32);

/* Remember to make local copies of the imports */
SET EMPID_VAR = EMPID;    
SET FNAME_VAR= FNAME;
SET LNAME_VAR = LNAME;

UPDATE DB2ADMIN.EMPLOYEES SET FNAME = FNAME_VAR, LNAME = LNAME_VAR
WHERE EMPID = EMPID_VAR;
COMMIT;
END P1 

And here is the Event Options - Update tab:

Figure 7. Event Options - Update tab
Event Options - Update tab

Notice that all mapped fields of the VF activity are required as input parameters to the procedure. In the same way that the Create event allowed access to the output parameters, they are also available here. Using the same example as in Create, our procedure could become:

Listing 10. Procedure definition

CREATE PROCEDURE DB2ADMIN.UPDATE_EMPLOYEE_NAME (IN EMPID INT, IN FNAME CHAR(32), 
   IN LNAME CHAR(32), OUT STATUS CHAR(10))
LANGUAGE SQL
P1: BEGIN
DECLARE EMPID_VAR INT;
DECLARE FNAME_VAR CHAR(32);
DECLARE LNAME_VAR CHAR(32);

/* Remember to make local copies of the imports */
SET EMPID_VAR = EMPID;    
SET FNAME_VAR= FNAME;
SET LNAME_VAR = LNAME;

UPDATE DB2ADMIN.EMPLOYEES SET FNAME = FNAME_VAR, LNAME = LNAME_VAR
WHERE EMPID = EMPID_VAR;
COMMIT;
/* Provide a value for the output parameter */
SET STATUS = 'SUCCESS';   
END P1 

And the Event Options - Update tab would become:

Figure 8. Event Options - Update tab
Event Options - Update tab

This has the mapping of the output parameter to the field in the Employee form. Any in/out parameter and output parameter is available for mapping here, but you have to enter the field names yourself.

Virtual Fields activity Delete event
The requirements here are:

  • Stored procedure must have input parameters which match keys.
  • Stored procedure has no output parameters.
  • Any result set is ignored.

Here is the stored procedure:

Listing 11. Stored procedure

CREATE PROCEDURE DB2ADMIN.DELETE_EMPLOYEE (IN EMPID INT)
LANGUAGE SQL
P1: BEGIN
DECLARE EMPID_VAR INT;
SET EMPID_VAR = EMPID;

DELETE FROM DB2ADMIN.EMPLOYEES WHERE EMPID = EMPID_VAR;
COMMIT;
END P1

And here is the Event Options - Delete tab:

Figure 9. Events Options - Delete tab
Event Options - Delete tab

EMPID, our key, must be an input parameter in the procedure.

This covers all of the possibilities for using DB2 stored procedures with Virtual Fields activities in both LEI and DECS. Of course, your procedures may have more meat to them, but we hope that you can see what the interface of your procedures must comply with in order to be used here and how to correctly configure your activities.

Virtual Documents
Currently, only Virtual Documents (VD) activities with integrated keys give you access to DB2 stored procedures. If you have an external key table, this access is not available. Using a VD activity with a stored procedure is exactly like it is in the Virtual Fields activity with one difference: The key to find a record in your data table is now EINOTEID. This field must be present as an input parameter in any stored procedure that you want to access. This article does not show VD examples because they are quite similar to Virtual Fields and also because Chapter 13 of the LEI Activities and User Guide has a section called Using Stored Procedures in Virtual Documents Events with examples for each event.

Virtual Agents
Currently, only Virtual Documents (VD) activities with integrated keys give you access to DB2 stored procedures. If you have an external key table, this access is not available. Using a VD activity with a stored procedure is exactly like it is in the Virtual Fields activity with one difference: The key to find a record in your data table is now EINOTEID. This field must be present as an input parameter in any stored procedure that you want to access. This article does not show VD examples because they are quite similar to Virtual Fields and also because Chapter 13 of the LEI Activities and User Guide has a section called Using Stored Procedures in Virtual Documents Events with examples for each event.

In the case of native Domino documents being acted upon, it is more often the case that you are taking Domino field values and sending them to a DB2 table. In our Employees database, we want an agent that writes field EMPID to a table, DB2ADMIN.FLAG_TABLE, used as a flag for another process. Here is a sample procedure:

Listing 12. Sample procedure

CREATE PROCEDURE DB2ADMIN.FLAG_EMPID (IN EMPID INT)
LANGUAGE SQL
P1: BEGIN
DECLARE EMPID_VAR INT;
/* Remember to make local copies of the imports */
/* even if we don't need to here because column */
/* and parameter aren't the same name. If we always */
/* do this, we NEVER have trouble.*/
SET EMPID_VAR = EMPID;  	

INSERT INTO DB2ADMIN.FLAG_TABLE (FLAG) VALUES (EMPID_VAR);
COMMIT;
END P1

Now here is the LEI VA activity form:

Figure 10. Virtual Agents Activity document
Virtual Agents Activity document

In the Employee form, we have EMPID, FNAME, LNAME, and STATUS fields. Any of these field names found in the interface of the procedure can be sent to the procedure. So in this example, EMPID is used in the procedure. We have an Output Parameters section of this form that works just like it did in the VF activities. You can map from the output parameters to fields in the source document. This feature only works with native Domino documents. For Virtual Documents, it makes more sense to update the data directly in the DB2 table with the stored procedure than to send back new field values, only to be saved to DB2 again via the normal VF or VD activity services.

A more interesting example occurs when the source document to be acted upon is a Virtual Document of some kind, where our VA updates data used in a VD or VF activity. We added a new column to the DB2 table EMPLOYEE called SALARY. Our goal now is to have an agent that gives a standard 20 percent raise (remember the '90s?) to an employee. We can do this two ways: We can send the EMPID value to the procedure, use it to fetch the current SALARY, and then update the SALARY; or we can send the EMPID and SALARY and perform the update. The second option is easier, so we'll do that. Here is the stored procedure code:

Listing 13. Stored procedure

CREATE PROCEDURE DB2ADMIN.STANDARD_RAISE (IN EMPID INT, IN SALARY DEC(8,2))
LANGUAGE SQL
P1: BEGIN
DECLARE EMPID_VAR INT;
DECLARE NEW_SALARY DEC(8,2);

SET EMPID_VAR = EMPID;
SET NEW_SALARY = SALARY + SALARY * .2;

UPDATE DB2ADMIN.EMPLOYEES SET SALARY = NEW_SALARY WHERE EMPID = EMPID_VAR;
COMMIT;
END P1

The VA activity looks like our last example, except for the different procedure name. After the VA acts on a given Virtual Document in Domino, subsequent opens have a new value for SALARY.

Direct Transfer activity with stored procedure as source
The final area that we want to show you is the Direct Transfer (DT) activity. There are two places where we can use stored procedures. The first is for the data source; the second is for the data target. In the first instance, your DB2 procedure may not have any input, in/out, or output parameters. Any select statement defined in the DT activity is ignored. The procedure must also return a result set. Our procedure here returns the values from the EMPLOYEES table.

Listing 14. Stored procedure

CREATE PROCEDURE DB2ADMIN.GET_EMPLOYEES ()
RESULT SETS 1
LANGUAGE SQL

P1: BEGIN
/* Here we declare the cursor so that we can use it later */
DECLARE cursor1 CURSOR WITH RETURN FOR      
SELECT EMPID,FNAME,LNAME,SALARY FROM DB2ADMIN.EMPLOYEES;  
/* This line actually runs the select */
OPEN cursor1;                               
END P1 

Again, it is a simple example. To ensure that the metadata selection and field mapping of the DT activity function correctly, it is best to define the DB2 connection document fully as follows:

Figure 11. DB2 Connection document
DB2 Connection document

Select the GET_EMPLOYEES procedure; it has no parameters. You also need a connection to the Employee database:

Figure 12. Notes Connection document
Notes Connection document

Here is the Direct Transfer Activity form:

Figure 13. Direct Transfer Activity document
Direct Transfer Activity document

There are several things to notice here. First, under the connection, the metadata is a procedure and not a table. Second, you may not browse the columns of the result set that are returned from the DB2 procedure. You must use automatic field mapping, which means that the field names of the result set must match those of the Domino form. That also means that you must have all of the fields returned from the procedure available in the Domino form (but the Domino form may have additional fields that won't have data placed in them). Notice that the Select Statement has the value FOO in it. Even though select statements are ignored when the source is a stored procedure, there is a bug in this form that makes you enter a value here. We entered FOO to work around this bug. Finally, under Direct Transfer Options - Source Data Options, you must select the Source Metadata is Stored Procedure option.

Why use a stored procedure here instead of directly selecting from the EMPLOYEES table? One reason is if the result set is the product of a join. Another is if you want to use dynamic SQL to generate the result set. You may want to log elsewhere that this table was read. The reasons are probably endless as to why you may want a stored procedure as a data source.

Direct Transfer activity with stored procedure as target
When you want your data target to be a DB2 procedure, it must meet the following requirements:

  • Each field to be sent is an input parameter.
  • Result sets are ignored.
  • Output parameters are available.
  • Array read/write feature must not be active.

Try Update Before Insert is a DT option under the Target Data Options tab. You should not select this option when your target is a stored procedure. Instead, build this logic into your procedure if you need it. We do not show you a specific example here because the description in the first section of this article applies here in exactly the same way, including the procedure design. The only thing that we want to add is this:

Figure 14. Performance Options tab
Performance Options tab

When your target is a stored procedure, the Number of Records to Transfer Concurrently (under Performance Options) must be set to 1. A value greater than 1 (array read/write) does not work because your input parameters accepts only one value at a time.

Summary
There are many places where you can use DB2 stored procedures with Domino. LSXLC and LEI/DECS together with the DB2 connector give you the access and control that you need to do this. For more information, check out the Enterprise Integration forum on developerWorks Lotus. That is a good source for help and advice.

We are currently planning possible future articles about accessing RDBMS stored procedures with the Lotus Connectors together with LEI, DECS, and the Lotus Connectors LotusScript Extensions (LSXLC). Let us know whether these topics are of interest to you.

Resources

About the author
Scott Morris is a Senior Software Engineer with IBM. He has been with Lotus/IBM since 1990. He is currently a developer on the Domino Enterprise Integration team. 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.


e-mail it!

What do you think of this document?
Killer! (5)Good stuff (4)So-so; not bad (3)Needs work (2)Lame! (1)

Comments?



developerWorks > Lotus
developerWorks
  About IBM  |  Privacy  |  Terms of use  |  Contact