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


Configuring the message flow

Set the domain for the input node; add and configure a mapping node.

Before you begin

Complete the tasks in Creating a database definition.

Procedure

  1. In IBM Integration Toolkit, double-click StoredProcedures_Flow.msgflow to open the message flow from the application that you imported earlier.
  2. Click the MQ Input node, and view its basic properties. The queue name is set to STORED.PROCEDURES.IN. On the Input Message Parsing tab, the domain is set to XMLNSC.
  3. From the Transformation drawer, drag a Mapping node onto the canvas, and double-click the node.
  4. On the "Specify a new message map file" window, click Next.
  5. For both map inputs and map outputs, fully expand StoredProcedures_Lib.
    1. For input, select the Employee schema.
    2. For output, select the EmployeeData schema.
    Screen capture showing map input and outputs
  6. Click Next, and then Finish. The map opens.
  7. Connect Employee to EmployeeData. A local map is created.
  8. Click Local map.
  9. Right-click a blank area of the canvas, and select Database > Call Database Routine.
  10. In the opened editor, if the CLIENTS database is not displayed, click Add database and select it from the data design projects. Then click Make available, and then OK.
    Screen capture showing how to add a data design project
  11. In the opened editor, expand Stored Procedure, and then click EMPLOYEEDETAILS(2).
    Notice the following points:
    • The four result sets (cursors) that are defined in the procedure are now shown in the Result sets list.
    • For each result set, you can select the table columns to be defined in the set.
    • You can reorder result sets.
    Screen capture of select database routine
  12. You now define the columns that ResultSet1 returns. The columns that you select correspond to the columns that you declared in the cursors in the stored procedure; see Creating and deploying a stored procedure.
    1. Click ResultSet1.
    2. Expand the EMPLOYEE table, and select the EMPNO, FIRSTNME, LASTNAME, and WORKDEPT columns.
    3. In the Description field, enter Select from EMPLOYEE.
    Your screen looks as shown in this screen capture:
    Screen capture showing columns to be returned for resultset1
  13. Configure ResultSet2 as follows:
    • Expand table DEPARTMENT, and then select columns DEPTNO and DEPTNAME.
    • In the Description field, type Select from Department.
  14. Configure ResultSet3 as follows:
    • Expand table EMPPROJACT, and then select column PROJNO.
    • In the Description field, type Select from EMPPROJACT.
  15. Configure ResultSet4 as follows:
    • Expand table PROJECT, and then select column PROJNAME.
    • In the Description field, type Select from PROJECT.
  16. Click OK. You return to the map, which now contains Database Routine and Return submaps.
  17. Connect EMPL_NUM to Database Routine, and connect Return to EmployeeData, as shown in the following figure. The Database Routine nested map runs if the Mapping node successfully calls to the database to run the stored procedure. You use the Return nested map to map the information that the stored procedure returns into your message. If the call to the database fails, the Mapping node throws an exception that includes the returned database error.
  18. Click Database Routine, and connect EMPL_NUM to EMPLIKENOIN (the input parameter for the routine).
  19. Click the yellow arrow that is above EmployeeData to go up a level.
  20. Click the Return transform to navigate into the nested return mapping, and expand the result sets. They contain the columns that you selected in steps 12 to 14.
  21. Make the following mappings.
    • DESCRIPTIONOUT -> ProcedureOutput

    • EMPNO -> EmployeeNo

    • FIRSTNME -> FirstName

    • LASTNAME -> LastName

    • DEPTNAME -> Department

    • PROJNO -> ProjectNo

    • PROJNAME -> ProjectName

    When you finish, your screen looks like the following image.
    Screen capture showing mappings
  22. Optional: Click the light bulb against each mapping to remove the warning triangles. The warnings appear because the mapper does not know the limitations on the database rows. For example, in the output, a single value is expected for EmployeeNo, but the stored procedure could return multiple rows of the mapped input column EMPNO. The design of the stored procedure ensures that only one row is returned, and so you can set the cardinality to 1.
  23. Save and close the map.
  24. Wire the MQ Input node and the MQ Output node to the Mapping node.
    1. Connect the MQ Input node Out terminal to the Mapping node In terminal.
    2. Connect the Mapping node Out terminal to the MQ Output node In terminal.
    The flow looks like the following image.
    Screen capture showing connections between the nodes.
  25. Save the message flow.

What to do next

Configuring a JDBC provider configurable service

sp_01_18_.htm | Last updated 2015-11-25 08:25:32