Use the DatabaseRoute node to route messages using information from a database in conjunction with XPath expressions.
This topic contains the following sections:
The DatabaseRoute node uses a collection of named column values from a selected database row and synchronously applies one or more XPath expressions to these acquired values to make routing decisions.
For more information about XPath 1.0 query syntax, see XPath.
The DatabaseRoute node is contained in the Database drawer of the message flow node palette, and is represented in the workbench by the following icon:
JDBC Type | Java™ Type | ESQL Type |
---|---|---|
CHAR | String | CHARACTER |
VARCHAR | String | CHARACTER |
LONGVARCHAR | String | CHARACTER |
NUMERIC | java.math.BigDecimal | DECIMAL |
DECIMAL | java.math.BigDecimal | DECIMAL |
BIT | Boolean | BOOLEAN |
BOOLEAN | Boolean | BOOLEAN |
TINYINT | byte | INTEGER |
SMALLINT | short | INTEGER |
INTEGER | int | INTEGER |
BIGINT | long | INTEGER |
REAL | float | FLOAT |
FLOAT | double | FLOAT |
DOUBLE | double | FLOAT |
BINARY | byte[] | BLOB |
VARBINARY | byte[] | BLOB |
LONGVARBINARY | byte[] | BLOB |
DATE | java.sql.Date | DATE |
TIME | java.sql.Time | TIME |
TIMESTAMP | java.sql.Timestamp | TIMESTAMP |
When you deploy a DatabaseRoute node in a message flow, use the Data Source Name property to reference and use existing IBM® predefined JDBC provider entries that are defined when a broker is created. These entries are incomplete so you must modify them to set the database name at which a data source definition points. If the JDBC provider is not listed for the Data Source Name property, use the mqsicreateconfigurableservice command to specify a new one. You can also use the mqsideleteconfigurableservice command to delete any unwanted JDBC provider entries.
The DatabaseRoute node has one input terminal and a minimum of three output terminals: Match, Default, and Failure. The Default and Failure output terminals are static, so they are always present on the node. The dynamic Match terminal is created automatically each time a new Route node is selected and used in the Message Flow editor. This behavior means that you do not need to always create this node's first dynamic output terminal, which is the minimum number of terminals needed for this node to operate. You can rename this dynamic terminal if "Match" is not an appropriate name.
A message is copied to the Default terminal if none of the filter expressions are true. If an exception occurs during filtering, the message is propagated to the Failure terminal. The DatabaseRoute node can define one or more dynamic output terminals. For all terminals, the associated filter expression is applied to the input message and if the result is true, a copy of the message is routed to the given terminal.
Each filter expression is applied to the input message in the order that is given in the filter table. If the result is true, a copy of the message is routed to its associated dynamic output terminal. If you set the Distribution Mode property to First, application of all filter expressions might not occur.
The filter expression can fail if you compare a returned column value to a string literal. How a column entry is stored (for example, a fixed length char field) determines what is returned for a given column from the database. Whitespace padding occurs for fixed length char fields that are retrieved from a database, where the value that is stored is less than the specified column character storage length. In this case, padding occurs to the right of the character string that is returned, forming part of the string. You should remember this when comparing such a column value to a string literal, because an equality comparison expression might fail if the literal does not contain the exact same string including padding characters.
$Employee_LastName = 'Smith 'which resolves to true. The expression:
$Employee_LastName = 'Smith'resolves to false.
Function: string normalize-space(string?)The normalize-space function returns the argument string with whitespace normalized by stripping leading and trailing whitespace and replacing sequences of whitespace characters with a single space. So the expression would be:
normalize-space($Employee_LastName) = 'Smith'
The DatabaseRoute node constructs its JDBC connections using connection details that are stored in the broker's registry, which contains standard DBMS JDBC provider settings for appropriate vendors. You can use the mqsichangeproperties command to modify these settings, and the mqsicreateconfigurableservice command to create new JDBC provider entries. You can then set the Data Source Name property to identify which JDBC provider settings are used to establish connections for the DatabaseRoute node.
For example, if connectionURLFormat = jdbc:oracle:thin:@serverName:portNumber:connectionUrlFormatAttr1, and connectionUrlFormatAttr1 = Oracle SID, the server identifier property is not included as part of the standard set of JDBC data source properties within the registry, so for the Oracle-specific connection URL pattern, the general purpose connection URL attribute connectionUrlFormatAttr1 is used to represent this non-standard property in the URL definition. This attribute is substituted with its assigned value, Oracle SID, which is a user-defined SID.
The DSN entry provides a user ID and password value pair, which are used to access the specified data source for a given JDBC provider entry. This property is ignored if the connection URL does not contain both a user ID and password pair, which require property values to be substituted for such inserts. If the securityIdentify is blank or left as the default (jdbc::myuserid@myserver), but it is required for the connection URL pattern, or if a valid unique security identity key is entered and it cannot be found under the DSN key, the DataSourceUserId and DataSourcePassword properties under which the broker was created are used.
Use the -c parameter on the mqsichangeproperties command to change the property settings for an object name that already exists in the broker persistent store. For a JDBC provider, this object name is a JDBC provider name.
Use the mqsideleteconfigurableservice command to delete a JDBC provider resource that was created by the mqsicreateconfigurableservice command.
Use the -c parameter on the mqsireportproperties command to report on the properties of any JDBC provider resource (both default and user-defined).
When you have put an instance of the DatabaseRoute node into a message flow, you can configure it. For more information, see Configuring a message flow node. Depending on the preferences that you have set, you can view the node's properties in the Properties view below the Message Flow editor or in the Properties dialog box. Either click the Properties view tab, or right-click the node and click Properties. All mandatory properties for which you must enter a value (those that do not have a default value defined) are marked with an asterisk.
EmployeeNumber | FamilyName | FirstName | Salary |
---|---|---|---|
00001 | Smith | John | 20000 |
00002 | Jones | Harry | 26000 |
00003 | Doe | Jane | 31000 |
$Employee_FamilyName="Jones",out_exp1 $Employee_Salary>=2000 and $Employee_Salary<26000,out_exp2
select LastName, Salary from Employee where EmployeeNumber=xwhere x is the value that is retrieved from the incoming message, which is located through the Value property.
The DatabaseRoute node terminals are described in the following table.
Terminal | Description |
---|---|
In | The static input terminal that accepts a message for processing by the node. |
Match | The dynamic output terminal to which the original message is routed when processing completes successfully. |
Default | The static output terminal to which the message is routed if no filter expression resolves to true. |
Failure | The static output terminal to which the message is routed if a failure is detected during processing. |
The DatabaseRoute node can have further dynamic output terminals. Not all dynamic output terminals that are created on a DatabaseRoute node need to be mapped to an expression in the filter table. For unmapped dynamic output terminals, messages are never propagated to them. Several expressions can map to the same single dynamic output terminal.
The following tables describe the node properties. The column headed M indicates whether the property is mandatory (marked with an asterisk if you must enter a value when no default is defined); the column headed C indicates whether the property is configurable (you can change the value when you add the message flow to the bar file to deploy it).
Property | M | C | Default | Description |
---|---|---|---|---|
Node name | No | No | The node type, DatabaseRoute | The name of the node. |
Short Description | No | No | A brief description of the node. | |
Long Description | No | No | Text that describes the purpose of the node in the message flow. |
Property | M | C | Default | Description |
---|---|---|---|---|
Data Source Name | Yes | Yes | DB2 | 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. The connection
URL is driver specific, but it includes the database name to which to connect. If connection to the database is by a login account and password, the node also uses this property as a lookup key, through which these values can be acquired from an expected matching broker registry DSN entry. If the DBMS is password protected, define the -n parameter on the mqsisetdbparms command for the JDBC unique security key before you deploy the message flow that contains this DatabaseRoute node. |
Query Elements | Yes | No | A table of query elements that are used to compose a single SQL select statement. The table consists of five columns and one or more rows. The columns are Table Name, Column Name, Operator, Value Type, and Value. These five properties describe a query element, indicating the database tables and columns that are retrieved. | |
Table Name | Yes | No | The name of a database table that forms part of the SQL select statement, including the schema name; for example, myschema.mytable. | |
Column Name | Yes | No | The name of the column in the database table to be retrieved in the results set, as qualified by the value of the Table Name property. This SELECT clause can refer to this name as a column value to return from a query or to be referenced in a test condition within the WHERE clause. | |
Operator | No | No | An optional comparison operator to apply to a left hand operand (the table column specified in the row's first two columns) and a right hand operand value. If you do not specify a value for this property, this row signifies the declaration of a table and column that can be referenced in future rows as a right hand operand value. | |
Value Type | No | No | An optional value type that indicates the type of value that is expressed in the last column of this row. A row that describes a test condition within the WHERE clause of the SQL select statement. | |
Value | No | No | This property specifies one of a given set of property types as expressed by the Value Type property. For example, if the Value Type property is set to Element, the Value property collects tan XPath 1.0 path location or ESQL field reference expression. The value that is returned from the expression when applied to the node's incoming message is used as the right hand operand value to be compared via this predicate. The compared value of the right hand operand must match the type that is retrieved for the table column that is compared against as the left hand operand. |
Property | M | C | Default | Description |
---|---|---|---|---|
Filters | No | Yes | A table of XPath filter expressions and associated terminal names that define any extra filtering that is performed by this node. The table consists of two columns and zero or more rows. Expressions are evaluated in the order in which they appear in the table. To improve performance, put the XPath expressions that are satisfied most frequently at the top of the filter table. | |
Distribution Mode | No | Yes | First | This property specifies the routing behavior of this node when an inbound message matches multiple expressions. If the Distribution Mode property is set to First, the message is propagated to the first matching output terminal. If the Distribution Mode property is set to All, the message is propagated to all matching output terminals. If there is no matching output terminal, the message is propagated to the Default terminal. |