Calling a stored procedure from a map

Use the Graphical Data Mapping editor to call a stored procedure by using the Database Routine transform.

Using the Graphical Data Mapping editor, complete the following steps:

1. With a graphical data map (.map) file open in the Graphical Data Mapping editor, right-click the canvas, and select Database > Call Database Routine.
   • Alternatively, select a schema element (or elements) as an input parameter value. Optionally, select a schema element (or elements) as an output value.
   • You can also use drag-and-drop to create the Database Routine transform. Connect the input object to the output object, and a transform is automatically created. Select the transform, and choose Database Routine from the Transforms list.
   • You can also click the Call a stored procedure in a database icon.
   The Database Routine wizard is displayed.
2. In the Database field, select the database that you want to call the routine from. To add a database definition file, or to discover a new database by connecting the IBM Integration Toolkit using JDBC to a database server, click Add database.... For more information, see Creating a database definition (.dbm file) by using the New Database Definition File wizard. You must select the Routines option during the discovery process.
   To use a different database name at run time than the name used in the IBM Integration Toolkit, you can override this value by setting the databaseName property of the JDBCProvider configurable service that defines how to connect to your database; see Setting up a JDBC provider for type 4 connections.
3. In the Schema field, select the database schema that defines the stored procedure that you want to call from the map.
   To call the stored procedure from a different database schema at run time, you can override this value by setting the databaseSchemaNames property of the JDBCProvider configurable service that defines how to connect to your database; see Setting up a JDBC provider for type 4 connections.
4. In the Routine field, select the stored procedure that you want to call into the Database Routine transform in the map.
   1. Optional: If the selected routine can return a value, a Return value check box is displayed. If you want to make the return value available for mapping in the Return transform, check this box.
   Note: The selected Routine then populates the following locked fields:

   • Type: states whether the type of the selected Routine is a stored procedure .
   • Parameters: details of the parameter names, mode, and type for the selected Routine.
   • Max ResultSets: if the Database definition file for the Routine provides this information, states the maximum Result sets. Otherwise, it is blank.

   These fields can be displayed by clicking Display Routine parameter details...

5. Optional: Select Treat warning as error.
   If this option is selected, and calling the Database Routine in the configured runtime database returns an SQL warning, it is handled as if the database is raising an exception. If the Failure transform is present, then it enters its nested mapping. Otherwise, the map execution stops, and an exception is raised from the Mapping node that is running the map.

   Important: Database warnings are vendor-specific. For more information about database warnings, see the documentation for your database product.
6. Optional: If the selected Routine can return Result sets, and you want to map values from them, you must define their order and column contents.
   1. If your Database definition file defined the number of Result sets, then Result sets list is pre-populated with one ResultSet, and displays the maximum number that can be returned. Use the Add and Delete buttons to populate the Result sets list, up to any maximum number defined in the database definition file. You must order the Result sets as they are defined in the Database Routine code.
      If you only want to map data from, for example, the second result set, you must still include the first result set in the table because they are accessed by their positional order.
   2. Select each Result set, and use the check boxes in Available table columns to add column definitions to the Result set to match what the Database Routine returns. You only need to define the Result set columns that you want to be available for mapping.
      Note: If you want to map from a column in a specific table, and other tables have a column with the same name, you must add column definitions for all the columns that have the same name as the column that you want to be available for mapping. You must also add the columns in the same order that the columns appear in the result set.
7. Click OK.
   The Database Routine, and its grouped Return transform are displayed in your graphical data map. If you made any selections in the mapping input/output, all selected inputs are wired into the Database Routine, and outputs are wired to the Return transform. If you made no selections, then the new transform appears in the map unconnected.
8. Provide any required values for IN and INOUT mode parameters for the Database Routine.
   The input parameters for the selected stored procedure are displayed as outputs in the nested Database Routine transform.
   1. Connect the required input elements to the Database Routine, and within the nested map provide transforms to set a value for each parameter.
      The Database Routine is entered only once, making one call to the database system. You must set cardinality for any repeating elements that are connected into the Database Routine transform, or use a function transform to provide a single value to the parameter.

      Note: If any parameter is not given a value, the database server might return an exception if it cannot provide a default value. If the resulting output value of the transforms setting the parameters is not the correct type for the Database Routine, or the content is invalid, for example, exceeding a maximum length, a database exception might occur.
9. Provide any required mapping for the output elements from data that is returned from the Database Routine. This data can include OUT and INOUT mode parameters, optional Routine return values, and one or more Result sets. The Routine outputs are displayed as inputs in the nested Return transform.
   1. Optional: Connect any additional input elements that you might require merged with the Database Routine data to the Return transform. Connect the Return transform to one or more output elements of the map. Provide transforms within the nested Return map to set the connected outputs from the provided Database Routine output values.