Call Procedure Activity

During run time, the Call Procedure activity runs a stored procedure in a database. The stored procedure must be written and installed in the database before you can complete the configuration of the Call Procedure activity in Studio.

Note: Function name overloading for stored procedures in the Informix® database is not supported. Stored procedures cannot have the same name in Informix even if their signatures are unique.
The steps for adding and configuring a Call Procedure activity to an orchestration are described in the following procedures:

Add a Call Procedure activity to the orchestration

  1. Create or open an orchestration. A graphical representation of the orchestration opens.
  2. Select the Activities tab and expand the Database folder.
  3. Drag the Call Procedure activity onto the orchestration.
  4. Select the Call Procedure activity.

Create, select, or edit a database endpoint

  1. Click Pick Endpoint task from the Checklist and choose from one of the following actions:
    • Select an existing endpoint:
      1. Select Browse. The Project Explorer opens.
      2. Select an existing endpoint and click OK. Skip to the Configure a Call Procedure activity activity procedure.
    • Select an existing endpoint and edit the endpoint:
      1. Select Browse. The Project Explorer opens.
      2. Select an existing endpoint and click OK.
      3. Click Edit. The Edit Endpoint pane shows.
    • Create a new endpoint:
      1. Select New. The Create Endpoint pane opens.
  2. Create or edit a database endpoint

Configure a Call Procedure activity

  1. Click Stored Procedure from the Checklist. The Stored Procedure pane opens.
  2. Click Browse... The Browse Stored Procedures dialog box shows.
  3. To see all the stored procedures in the database and not just those visible with the selected user schema, click Clear.
  4. Select a Stored Procedure from the list.
  5. Click OK. Parameter information for the selected stored procedure displays in the Stored Procedure pane.
    Note: Deleting stored procedure call parameters from the middle of the parameter list is not supported. If you want to specify parameters in a different order than they are displayed in the list, you can create a wrapper stored procedure (for example, WrapperStoredProcedure1) that doesn't have gaps in parameters (that is, contains the exact subset of the parameters needed), then have the new stored procedure call the underlying stored procedure (for example: BackendStoredProcedure1) using the @parameter = value form supported by the Microsoft SQL Server. You can then have your orchestration use the wrapper stored procedure with only the parameters needed. Alternatively, you can provide the full set of parameters without omissions and default values in Studio for the original underlying stored procedure (for example, BackendStoredProcedure1). Note that while deleting stored procedure call parameters from the middle of a parameter list is not supported, parameters displayed at the end of a parameter list can be deleted.
  6. To change the current character encoding (originally obtained from the database endpoint), select a different encoding option from the Encoding list. This changes the default character encoding for all the input parameters of the stored procedure.
  7. By default, all the input and output (IN, INOUT, RETURN, OUT) parameters of the stored procedure are selected. You do not need to include all the input and output parameters defined for a stored procedure. Clearing the Use check box for an output parameter (INOUT, RETURN, OUT) means that the output parameter is not available in the Map Inputs task. Clearing the Use check box for an input parameter (IN, INOUT) means that the input parameter is not available in the Map Inputs task. Clear the Use check box only for input parameters that you do not need to specify input values at run time. Some input parameters of the stored procedure may have default values.

    Most databases only return a single return parameter. For these databases, if the Use check box is selected for the return parameter, the return value is returned in the single results/result/RETURN_VALUE node in the From Activity pane of the Map Outputs task. The Informix database, however, supports multiple return parameters and for the Informix database, the return parameters are returned in the repeating results/result/result/resultsSets/any node. Even if one parameter is returned from the stored procedure, the return parameter is returned in the results/result/result/resultsSets/any node. Use the CopyOf function in the map to copy the contents of the result node (including the child node called resultSet) to a variable. In order for the CopyOf function to correctly copy the values of the source nodes to the destination nodes, the XML Schema of the destination variable must match the XML Schema of the source node. For more information, see CopyOf function.

  8. Click the heading of the column to sort the rows by the column value. For example, to sort the rows by their column name, click in the Column Name heading of the column. An arrow shows to the right of the column header name. An up arrow indicates the column is sorted by alphabetic order starting with a and continuing to z. A down arrow indicates the column is sorted by reverse alphabetic order starting with z and continuing to a. Click the arrow to reverse the alphabetic order.
  9. If necessary, change the settings of the Data type, Encoding, or Nullable values for each query input parameter. Click the field to make a new selection:
    • Data Type - Defaults to the Data Type of the parameter. Select an alternate data type for the parameter by clicking in the Data Type cell and selecting another Data Type from the list. from the corresponding cell. You must change any data types that are UNSUPPORTED.
      Note: (for MONEY and XMLTYPE data types)  For the XMLTYPE data type in an Oracle database and the MONEY data types in an SQL Server database, Studio does not automatically associate the correct data type so you must manually select the appropriate data type.
    • Encoding - Defaults to current default encoding that was set either at the database level or the table level. If the required encoding for the parameter is different from the current default encoding, select another encoding by clicking in the Encoding cell and selecting another Encoding from the list.
    • Size — Size of the column defined in the table. This size is retrieved from the database table and is not configurable.
  10. Click Reset to reset all parameter properties to their defaults. This also selects the Use option for all parameters.
  11. Configure Delivery Rules.
    Note: If you select the Exactly once option in the Delivery Rules task, you must create control tables that are used by the Integration Appliance during run time. You create the control tables after publishing the orchestration from Studio to the Integration Appliance. You create the control tables using the Web Management Console (WMC) before you deploy the project.
    Note: If you select the Exactly once option, you must enable persistence. For more information, see Enabling Persisence.
  12. Configure the Retry Options.
 

Map the input of the activity

  1. Select the Map Inputs task in the Checklist. The input parameters for the activity are displayed under the calls element in the To Activity pane. The input parameters displayed in the To Activity pane correspond to the IN and INOUT parameters you selected in the Stored Procedure task. If the Batch Multiple Rows Per Message check box is selected in the Delivery Rules, the multiple occurrences icon displays next to the call element.
  2. Map all the displayed input parameters. See Creating a map for general instructions on mapping.

Map the output of the activity

  1. Select the Map Outputs task in the Checklist. The output parameters for the activity are displayed under the results element in the From Activity pane. The output parameters displayed in the From Activity pane correspond to the OUT, RETURN, and INOUT parameters you selected in the Stored Procedure task.

    If the Batch Multiple Rows Per Message check box is selected in the Delivery Rules, the multiple occurrences icon displays next to the result element.

    A repeating node called resultSet displays in the From Activity pane of the Call Procedure activity even if the stored procedure has no return parameters. During run time, if the stored procedure started from the activity returns a result set, this result set is returned from the activity in the resultSet node. For example, if the stored procedure starts a select statement, the result set from the select statement is returned in the resultSet node. Use the CopyOf function in the map to copy the contents of the result node (including the child node called resultSet) to a variable.   
    Attention: In order for the CopyOf function to correctly copy the values of the source nodes to the destination nodes, the XML Schema of the destination variable must match the XML Schema of the source node. For more information, see CopyOf function.
  2. Map the required output parameters to variables. See Creating a map for general instructions on mapping. You are not required to map output parameters for this activity.
 



Feedback | Notices


Timestamp icon Last updated: Tuesday, 27 September 2016


https://www.ibm.com/support/knowledgecenter/en/SS3LC4_7.5.3/com.ibm.wci.doc/db_call_procedure_activity.html