In IBM Integration Toolkit,
double-click StoredProcedures_Flow.msgflow to
open the message flow from the application that you imported earlier.
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.
From the Transformation drawer, drag a Mapping node onto
the canvas, and double-click the node.
On the "Specify a new message map file" window,
click Next.
For both map inputs and map outputs, fully expand StoredProcedures_Lib.
For input, select the Employee schema.
For output, select the EmployeeData schema.
Click Next, and then Finish. The map opens.
Connect Employee to EmployeeData. A local map is created.
Click Local map.
Right-click a blank area of the canvas, and select Database > Call Database Routine.
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.
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.
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.
Click ResultSet1.
Expand the EMPLOYEE table, and select the EMPNO, FIRSTNME, LASTNAME,
and WORKDEPT columns.
In the Description field, enter Select
from EMPLOYEE.
Your screen looks as shown in this screen capture:
Configure ResultSet2 as follows:
Expand table DEPARTMENT, and then select columns DEPTNO and DEPTNAME.
In the Description field, type Select
from Department.
Configure ResultSet3 as follows:
Expand table EMPPROJACT, and then select
column PROJNO.
In the Description field, type Select
from EMPPROJACT.
Configure ResultSet4 as follows:
Expand table PROJECT, and then select column PROJNAME.
In the Description field, type Select
from PROJECT.
Click OK. You
return to the map, which now contains Database Routine and Return submaps.
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.
Click Database Routine, and connect
EMPL_NUM to EMPLIKENOIN (the input parameter for the routine).
Click the yellow arrow that is above EmployeeData to
go up a level.
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.
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.
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.
Save and close the map.
Wire the MQ Input node and the MQ
Output node to the Mapping node.
Connect the MQ Input node Out terminal to the Mapping
node In terminal.
Connect the Mapping node Out terminal to the MQ Output
node In terminal.