IBM Integration Bus, Version 9.0.0.8 Operating Systems: AIX, HP-Itanium, Linux, Solaris, Windows, z/OS

See information about the latest product version

Calling a stored procedure from a map

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

Before you start:

You must complete the following task:

Create a graphical data map by using the Graphical Data Mapping editor. For information, see Creating a message map.

You can use the Database Routine transform to call a stored procedure from a database.

Note: Only IBM DB2® stored procedures are supported in IBM Integration Bus v9.0.

The Database Routine transform acts as a nested mapping, into which you can wire inputs to construct mappings to set the input parameter values for the stored procedure call. The routine input parameters are displayed as outputs of the Database Routine nested mapping. You cannot wire any output from the Database Routine transform.

Use the Return transform within the Transform group of the Database Routine to map any output parameters, return values, or ResultSets produced by calling the Database Routine. You can wire any additional inputs that you want to map when the Database Routine call completes successfully. The output parameters, any return value, and any returned Result sets you defined for the Database Routine are provided as inputs in the nested Return mapping. You can wire the Return to a single or multiple sibling output elements to enable the returned data to be mapped to the map output.

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

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.
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 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.
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.
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...
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.
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.
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.
Provide any required values forIN 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.
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.

Next:

If you want exceptions that are returned from the database server when the Database Routine is called to be handled by the map, instead of having such exceptions stop the map and being reported, you can add a Failure transform into the transform group; see Handling database exceptions in a graphical data map.
Set up a JDBC connection to the database that you want the run time to call the Database Routine into; see Enabling JDBC connections to the databases.
If you want to modify the available columns in the Result sets for mapping, use the Properties view of the Database Routine transform, and then update the Return nested mapping.

cm28838_.htm |

Last updated Friday, 21 July 2017