Creating the SimplifiedDBRouting message flow

Use the following instructions to create the SimplifiedDBRouting message flow. For more detailed instructions, click the links provided at the end of each step.

To create and configure the SimplifiedDBRouting message flow:

  1. Create a new message flow called SimplifiedDBRouting.
    For instructions, see Creating a message flow in the WebSphere Message Broker documentation.
  2. In the Message Flow editor, add and rename the nodes listed in the following table.
    For instructions, see Adding a message flow node in the WebSphere Message Broker documentation.
    Palette drawers Node type Node name (MQ Queue)
    WebSphere MQ MQInput MQInput
    WebSphere MQ MQOutput FailureQ
    WebSphere MQ MQOutput KeyNotFoundQ
    WebSphere MQ MQOutput OrderClockQ
    WebSphere MQ MQOutput SeniorStaffQ
    WebSphere MQ MQOutput DefaultQ
    Routing Route Route
    Database DatabaseRetrieve DatabaseRetrieve
    Database DatabaseRoute DatabaseRoute
    Construction Trace Trace
  3. Connect the nodes together as shown in the following table.
    For instructions, see Connecting message flow nodes in the WebSphere Message Broker documentation.
    To check that you have connected the nodes together correctly, see the diagram in About the Simplified Database Routing message flow.
    Node name Terminal Connect to this node
    MQInput Out Route
    Failure Trace
    Catch Trace
    Route Default DatabaseRetrieve
    Failure Trace
    Match DatabaseRoute
    DatabaseRetrieve Out DatabaseRoute
    Failure Trace
    KeyNotFound KeyNotFoundQ
    DatabaseRoute KeyNotFound KeyNotFoundQ
    Failure Trace
    TenYearsService OrderClockQ
    OlderThanMe SeniorStaffQ
    Default DefaultQ
    Trace Out FailureQ
  4. Configure the node properties as shown in the following table.
    Accept the default values for all properties unless an alternative value is shown in the table.
    For instructions, see Configuring a message flow node in the WebSphere Message Broker documentation.
    Node name Page Property Value
    MQInput Basic Queue name SIMPLERROUTEDB_IN
    This property is the local queue from which the message flow takes the message.
    Input Message Parsing Message domain XMLNSC : For XML messages
    This property tells the broker to use the XMLNSC parser, which is namespace aware, supports validation, and has low memory usage.
    Route Basic Filter pattern $Body/EmpRecord/WorkDept
    This property is an XPath 1.0 expression, specifying in this case a path location to an expected element within the input message to this node. The node attempts to find a work department field, and if not present resolves to false.
    Basic Routing output terminal

    Match
    This property is the name of a dynamic output terminal to which to propagate the input message if the filter expression resolves to true. The expression resolves to true if the expected element is located in the input message.
    Note: This terminal must first be created by right-clicking the node and clicking Add Output Terminal.
    For instructions, see Using dynamic terminals in the WebSphere Message Broker documentation.

    DatabaseRetrieve Basic Data source name

    SIMPLERROUTEDB
    The alias that is used to locate JDBC provider details that are stored in the broker registry. The alias is used to locate and build the JDBC connection URL that is used to connect to a DBMS.

    Basic Copy message Yes
    This property indicates that a copy of the original incoming message is required, because the message tree is to be updated.
    Basic Query elements
    Table name Column name Operator Value Type Value
    EMPLOYEE E LASTNM ASC None None
    EMPLOYEE E FIRSTNM ASC None None
    EMPLOYEE E YEARSSERVICE ASC None None
    EMPLOYEE E AGEINYRS ASC None None
    EMPLOYEE E WORKDEPT ASC None None
    EMPLOYEE E EMPNUM = Element $InputBody/EmpRecord/EmpNumber
    The contents of the preceding Query elements table produces the following query in the SQL statement window:
    SELECT  E.LASTNM,  E.FIRSTNM,  E.YEARSSERVICE,  E.AGEINYRS,  E.WORKDEPT
    FROM EMPLOYEE E
    WHERE  E.EMPNUM = ?
    ORDER BY  E.LASTNM ASC,  E.FIRSTNM ASC,  E.YEARSSERVICE ASC,  E.AGEINYRS ASC,  E.WORKDEPT ASC
    Data Element Table Data elements
    Column name Message element
    E.FIRSTNM $OutputRoot/XMLNSC/EmpRecord/FirstName
    E.LASTNM $OutputRoot/XMLNSC/EmpRecord/LastName
    E.YEARSSERVICE $OutputRoot/XMLNSC/EmpRecord/YrsInService
    E.AGEINYRS $OutputRoot/XMLNSC/EmpRecord/AgeInYrs
    E.WORKDEPT $OutputRoot/XMLNSC/EmpRecord/WorkDept
    Each row in the preceding Data elements table specifies a location in the output message where a retrieved column value is inserted before propagation of the message from this node.
    If the location does not exist in the output message (a copy of the input message), it is created. Because the default value for basic property Multiple rows is set to No, only the values returned in the first row of a result set are processed by this node.
    The result set is obtained by running the query specified in the SQL statement window and configured by using the basic Query elements table content.
    DatabaseRoute Basic Data source name

    SIMPLERROUTEDB
    The alias that is used to locate JDBC provider details that are stored in the broker registry. The alias is used to locate and build the JDBC connection URL that is used to connect to a DBMS.

    Basic Query elements
    Table name Column name Operator Value Type Value
    EMPLOYEE M EMPNUM ASC None None
    EMPLOYEE M LASTNM ASC None None
    EMPLOYEE M AGEINYRS ASC None None
    DEPARTMENT D DEPTNUM = Element $Body/EmpRecord/WorkDept
    DEPARTMENT D MGRNUM = Column (EMPLOYEE) M.EMPNUM
    The contents of the preceding Query elements table produces the following query in the SQL statement window:
    SELECT  M.EMPNUM,  M.LASTNM,  M.AGEINYRS
    FROM EMPLOYEE M, DEPARTMENT D
    WHERE  D.DEPTNUM = ?
    AND  D.MGRNUM =  M.EMPNUM
    ORDER BY  M.EMPNUM ASC,  M.LASTNM ASC,  M.AGEINYRS ASC 
    Basic Distribution mode All
    This property determines the routing behavior of this node when an inbound message matches multiple expressions. If Distribution mode is set to First, the message is propagated to the first matching output terminal. If Distribution mode is set to All, the message is propagated to all matching output terminals. If there is no matching output terminal, the message is sent to the Default terminal.
    Filter Expression Table Filter table
    Filter pattern Routing output terminal
    ($Body/EmpRecord/AgeInYears > $M_AGEINYRS) and ($M_LASTNM = 'KWAN') OlderThanMe
    $Body/EmpRecord/YrsInService >= 10 TenYearsService
    Each row in the preceding Filter expression table specifies an XPath 1.0 expression where retrieved column values are represented in the form of variable references.
    Each expression is cast as a Boolean. If the expression resolves to true, the node propagates the input message to the dynamic output terminal that is specified for the Routing output terminal value of the row.
    Note: This terminal must first be created by right-clicking on this node and clicking Add Output Terminal.
    For instructions, see Using dynamic terminals in the WebSphere Message Broker documentation.
    Trace Basic Destination Local Error Log
    This property instructs the node to write the trace information to the local error log. On Windows, the local error log is Event Viewer; on Linux, the local error log is syslog.
    Basic Pattern
    Root >
    ${Root}
    ExceptionList > ${ExceptionList}
    The trace pattern extracts the entire message tree information.
    Basic Message number 3051
    FailureQ Basic Queue name SIMPLERROUTEDB_FAILURE
    This property is the queue that the message flow puts the message if the processing fails.
    KeyNotFoundQ Basic Queue name
    SIMPLERROUTEDB_KEYNOTFOUND
    This property is the local queue to which the message is put by the message flow.
    OrderClockQ Basic Queue name
    SIMPLERROUTEDB_ORDERCLOCK
    This property is the local queue to which the message is put by the message flow.
    SeniorStaffQ Basic Queue name
    SIMPLERROUTEDB_SNRSTAFF
    This property is the local queue to which the message is put by the message flow.
    DefaultQ Basic Queue name
    SIMPLERROUTEDB_DEFAULT
    This property is the local queue on which the message flow puts the message.
  5. Save the message flow.

You have now created the SimplifiedDBRouting message flow.

Back to Building the Simplified Database Routing sample