Stored Procedures

You 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 HCL Enterprise Integrator (HEI) 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 Connector for DB2® supports the return of output parameters from stored procedures. The following scenarios describe what is returned from a stored procedure call:

  • If the stored procedure returns a result set only, the connector will return that result set fieldlist.
  • If the stored procedure returns multiple result sets, the connector will return the first result set fieldlist only.
  • If the stored procedure returns output parameters only, the connector will return a fieldlist of those output parameters.
  • If the stored procedure returns output parameters and a result set, the connector will return a fieldlist of the output parameters only.
  • If the stored procedure returns output parameters and multiple result sets, the connector will return a fieldlist of the output parameters only.

The 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 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.