Stored Procedures

Yo can use a DB2® stored procedure wherever procedures are allowed, such as the destination of a Direct Transfer, within an LSX script, or in an Advanced RealTime activity such as Virtual Agents or Virtual Documents. The stored procedure being called must have its parameters set up correctly for the call.

Input and output values are provided to DB2 procedures as named parameters. This requires that the parameters in DB2 use the same names as the fields being provided as the parameters unless explicitly mapped in the activity. The parameters being provided should include key values when being used in the context of a keyed operation (selection, update, or delete contexts), and data values when relevant (insert or update contexts). The DB2 parameter data types should be selected as the closest match to the data type in the LC API. See the "DB2 Data Types" section, later in this chapter, for more information.

Note: When using a stored procedure with the Delete event in IBM® Lotus Enterprise Integrator® (LEI) or Domino® Enterprise Connection Services (DECS), the stored procedure need only declare the fields specified under "Key(s):" in the activity. Since a Delete event will not return any output parameters in any case, the activity will only pass the key fields to the stored procedure. Furthermore, an activity connecting to a Stored Procedure cannot support only the Delete event.

Any result set output from a DB2 stored procedure must be returned through a DB2 cursor left open in the stored procedure. The result set produced by the stored procedure will be retrieved with this cursor.

The Lotus® Connector for DB2 supports the return of output parameters from stored procedures. The following scenarios describe what is returned from a stored procedure call:

The Lotus Connector for DB2 will not return multiple fieldlists in any scenario and a fieldlist of output parameters will always have return precedence.

Considerations when Using Stored Procedures

DB2 Universal Database™ for iSeries® (DB2 UDB for iSeries) -- (IBM i) -- does not support stored procedure result sets from remote data sources.

When using DB2 stored procedures and the Call method in LotusScript®, the "Fieldnames" property must be set. The "Fieldnames" property must contain all of the parameters declared OUT or INOUT in the DB2 stored procedure.

Stored procedure return values are not supported with the Lotus Connector for DB2.

Stored procedures in DB2 can be written in Java™, C, and C++. DB2 Universal Database. You can use a stored procedure builder to create procedures more easily.

Additional Documentation | Trademarks |