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

DatabaseRoute node

Use the DatabaseRoute node to route messages using information from a database in conjunction with XPath expressions.

This topic contains the following sections:

Purpose

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 W3C XPath 1.0 Specification.

The DatabaseRoute node is contained in the Database drawer of the message flow node palette, and is represented in the IBM® Integration Toolkit by the following icon:

DatabaseRoute node icon

Using this node in a message flow

Look at the following sample to see how to use this node:

Simplified Database Routing

You can view information about samples only when you use the product documentation that is integrated with the IBM Integration Toolkit or the online product documentation. You can run samples only when you use the product documentation that is integrated with the IBM Integration Toolkit.

Input parameter values that are acquired from message elements in the incoming message are supported for insertion into prepared statements that are used by this node. These values are acquired from name, value, and name-value elements in the incoming parsed input message. Elements are acquired initially in the form of a com.ibm.broker.plugin.MbElement object, therefore the range of supported Java™ object types that values can take is governed by this object's interface. When values are in the form of Java primitive types or Objects they are converted into their equivalent JDBC data type, as shown in the following table.

Java	JDBC
Integer	INTEGER
Long	BIGINT
Double	DOUBLE
BigDecimal	NUMERIC
Boolean	BIT
byte[]	VARBINARY or LONGVARBINARY
BitSet	VARBINARY or LONGVARBINARY
String	VARCHAR or LONGVARCHAR
MbTime	java.sql.Time
MbTimestamp	java.sql.Timestamp
MbDate	java.sql.Date

Values are used from an element only if the element is of a known type, and its value state is valid, otherwise an exception is issued. Output column values that are acquired in the result set from SQL queries that are carried out by this node are converted first into matching Java types, then into internal message element value types, as shown in the following table.

JDBC	Java	ESQL Type
SMALLINT	Integer	INTEGER
INTEGER	Integer	INTEGER
BIGINT	Long	DECIMAL
DOUBLE	Double	FLOAT
REAL	Double	FLOAT
FLOAT	Double	FLOAT
NUMERIC	BigDecimal	DECIMAL
DECIMAL	BigDecimal	DECIMAL
BIT	Boolean	BOOLEAN
BOOLEAN	Boolean	BOOLEAN
BINARY	byte[]	BLOB
VARBINARY	byte[]	BLOB
LONGVARBINARY	byte[]	BLOB
CHAR	String	CHARACTER
VARCHAR	String	CHARACTER
LONGVARCHAR	String	CHARACTER
TINYINT	byte[1]	BLOB
TIME	java.util.Date	TIME
TIMESTAMP	java.util.Date	TIMESTAMP
DATE	java.util.Date	DATE

You can route a message to the same location, whether a query is successful against a specified database, by wiring both of the non-failure output terminals to the same output location.

If an error is found in the XPath expression of a pattern, it is reported during validation in the IBM Integration Toolkit. The reported error message might include the incorrect expression string and its associated unique dynamic or static terminal name, or the string might be marked as broken within the table.

The node needs query information that is used to form an SQL select query, which can access multiple tables in a database using multiple test conditions. Sometimes, not all the information that you want to retrieve in a result set is in a single database table. To get the column values that you want, you might need to retrieve them from two or more tables. This node supports the use of SELECT statements that facilitate getting columns from one or more tables in a single result set. The typical join syntax that is supported is also referred to as inner join.

Inner join information that is collected to form a query includes a list of table qualified column values to retrieve and a list of test conditions, which form the WHERE clause of the SELECT statement. Table qualified column values can form the left operand in a test condition. Choose a comparison operator to apply to this operand and, optionally, specify a right operand to complete the test condition. The operator can be a null comparison test, in which no right operand is needed. The value of the right operand can be a database type (such as Integer, Boolean, or Long), another table qualified column, or a value that is acquired from an element in the incoming message, as expressed through an XPath 1.0 general expression.

When you deploy a DatabaseRoute node in a message flow, you can select a value that is associated with the Data Source Name property. The list of values contains references to existing IBM predefined JDBC provider entries that are defined when a broker is first created. These entries are incomplete, therefore you must modify them to access the data source definition with which you want to work. If an existing default IBM predefined JDBC provider is already referenced and in use by another JDBC database node that requires different settings, use the IBM Integration Explorer; see Using the IBM Integration Explorer to work with configurable services for more information, or the mqsicreateconfigurableservice command to specify a new JDBC provider entry.

Use the IBM Integration Explorer, or the mqsideleteconfigurableservice command to delete any unwanted JDBC provider entries.

You can delete only custom-named configurable services; you cannot delete IBM-defined configurable services.

The DatabaseRoute node has one input terminal and a minimum of four output terminals: Match, keyNotFound, Default, and Failure. The keyNotFound, Default, and Failure output terminals are static, therefore they are always present on the node. The dynamic Match terminal is created automatically each time a new DatabaseRoute node is selected and used in the Message Flow editor. This behavior means that you do not always need to 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. If the database query that is applied to the node's data source produces an empty result set (that is, no database rows are matched), a message is copied to the keyNotFound 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 specified terminal.

Each filter expression in the Filter table can be applied to:

The input message
The collection of named column values that are selected from a matched database row
Both the input message and the returned column values
Neither

because expressions can be any valid general XPath 1.0 expression.

As with the Route node, expressions are applied in the order that they are 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, the 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 character field) determines what is returned for a specified column from the database. White space padding occurs for fixed-length character 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.

For example, in a table called Employee, a database column called LastName that is defined as char(10) with the value 'Smith', is returned as 'Smith ', therefore the filter expression must be:

$Employee_LastName = 'Smith     '

which resolves to true. The expression:

$Employee_LastName = 'Smith'

resolves to false.

Alternatively, the XPath expression can use the following function:

Function: string normalize-space(string?)

The normalize-space function returns the argument string with white space normalized by stripping leading and trailing white space and replacing sequences of white space characters with a single space. Therefore the expression is:

normalize-space($Employee_LastName) = 'Smith'

Making the JDBC provider service available to the DatabaseRoute node

The DatabaseRoute node constructs its JDBC connections using connection details that are stored in the broker's registry as a configurable service. JDBCProvider configurable services are supplied for all supported databases. Use the mqsichangeproperties command to modify the settings of the supplied service for your chosen database, or create a new service using the mqsicreateconfigurableservice command. See Setting up a JDBC provider for type 4 connections for further information and assistance on working with JDBCProvider services. You must set up a different JDBCProvider service for each database to which you want to connect.

Note: The maxConnectionPoolSize property does not apply to the JDBC connections used by the DatabaseRetrieve or DatabaseRoute nodes.

When you have defined the service, set the Data Source Name property of this node to the name of the JDBCProvider service; the attributes of the service are used to establish connections for the DatabaseRoute node.

You must stop and restart the broker for your changes to take effect, unless you intend to create a new integration server on the broker to which you will deploy the message flow that contains this node.

Using the Data Source Explorer view to query data sources

Use the Data Source Explorer view to discover the names of tables within a target database, and the names of any columns in those tables. You must import database definitions for your databases into the IBM Integration Toolkit before you can view them in the Data Source Explorer view.

Switch to the Integration Development perspective.
In the Data Source Explorer view, expand Connections. The database connections are listed.
Expand a database connection to list the databases, then expand the appropriate database.
Expand Schemas to list the schemas, then expand the appropriate schema.
Expand Tables to list all the tables.
Click a table to show its properties in the Properties view.
In the Properties view, click the Columns tab to view the column names.

Configuring the DatabaseRoute node

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. The properties of the node are displayed in the Properties view.

All mandatory properties for which you must enter a value (those that do not have a default value defined) are marked with an asterisk.

Example

Consider the following example, which uses a database table called Employee.

EmployeeNumber	FamilyName	FirstName	Salary
00001	Smith	John	20000
00002	Jones	Harry	26000
00003	Doe	Jane	31000

The following DatabaseRoute node properties are set as specified:

Table Name	Column Name	Operator	Value Type	Value
Employee	FamilyName	ASC	None	None
Employee	Salary	ASC	None	None
Employee	EmployeeNumber	=	Element	$Body/EmployeeRecord/EmployeeNumber

The DatabaseRoute node connects to the Employee database table and extracts the value to compare from each incoming message. The XPath expression that is used to navigate to the message body is $Body/EmployeeRecord/EmployeeNumber. The SQL query is:

SELECT Employee.FamilyName, Employee.Salary
FROM Employee
WHERE EmployeeNumber=?
ORDER BY Employee.FamilyName ASC, Employee.Salary ASC

where ? is the value that is retrieved from the incoming message. This value is located through the Value property in the third row of the query elements table, which has a value type of Element.

If the value at this location is 00001, information for John Smith is retrieved. The first XPath expression, which is associated with the out_expr1 dynamic terminal, is not met, therefore it does not meet its condition, and no message is propagated to the Out terminal. The second XPath expression is met, so that a copy of the input message is routed to the out_expr2 dynamic terminal.
If the value at this location is 00002, information for Harry Jones is retrieved. The first XPath expression, which is associated with the out_expr1 dynamic terminal, is met, so that a copy of the input message is routed to the out_expr1 terminal. The second XPath expression is not processed because the Distribution Mode property is set to First.

Terminals

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	A dynamic output terminal to which the original message can be routed when processing completes successfully. You can create additional dynamic terminals; see Dynamic terminals.
Default	The static output terminal to which the message is routed if no filter expression resolves to true.
keyNotFound	The static output terminal to which the message is copied if no database rows are matched.
Failure	The static output terminal to which the message is routed if a failure is detected during processing.

Dynamic terminals

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. For more information about using dynamic terminals, see Using dynamic terminals.

Properties

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).

The DatabaseRoute node Description properties are described in the following table.

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.

The DatabaseRoute node Basic properties are described in the following table.

Property	M	C	Default	Description	mqsiapplybaroverride command property
Data Source Name	Yes	Yes	DB2	The alias that is used to locate JDBCProvider service definition that is stored in the broker registry. The alias is used to locate and build the JDBC connection URL for connection 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.	dataSource
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 a table qualified column value to be retrieved from a database. In this case, the element forms part of the SELECT and ORDER BY clauses in the generated query. Otherwise, the query element acts as a test condition that forms a predicate within the WHERE clause in the generated query.
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 in the WHERE clause.
Operator	Yes	No		A comparison operator to apply to a left operand (the table column that is specified in the row's first two columns) and optionally a right operand value. If you specify an Ascending 'ASC' or Descending 'DESC' operator value for this property, this row signifies the declaration of a table qualified column that forms part of the SELECT and ORDER BY clauses in the generated query and optionally can be referenced in future rows as a right operand value.
Value Type	Yes	No		A value that is either set to None, or that indicates the type of value that is expressed in the last column of this row. If this property is not set to None, it refers to a row that describes a test condition in the WHERE clause of the SQL select statement.
Value	Yes	No		A value that is either set to None, or that specifies one of a specified 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 an XPath 1.0 general expression. The value that is returned from the expression when it is applied to the node's incoming message is used as the right operand value to be compared through this predicate. The compared value of the right operand must match the type that is retrieved for the table column that is compared against as the left operand. Complex expressions are possible, where zero or more values can be acquired from the incoming message, and manipulated to form a single value for comparison. For example, the sum of multiple field values in the incoming message can be calculated by a general expression that is presented for a value type of Element.
Distribution mode	No	Yes	All	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.

The DatabaseRoute node Filter Expression Table properties are described in the following table.

Property	M	C	Default	Description
Filter table	Yes	No		A table of filters (XPath 1.0 general expressions) and associated terminal names that define any extra filtering that is performed by this node. The table consists of two columns and one or more rows. You must have at least one row in this table so that the node can perform routing logic. As with the Route node, expressions are evaluated in the order in which they are displayed in the table. To improve performance, put the XPath expressions that are satisfied most frequently at the top of the filter table.

The Monitoring properties of the node are described in the following table.

Property	M	C	Default	Description
Events	No	No	None	Events that you have defined for the node are displayed on this tab. By default, no monitoring events are defined on any node in a message flow. Use Add, Edit, and Delete to create, change or delete monitoring events for the node; see Configuring monitoring event sources using monitoring properties for details. You can enable and disable events that are shown here by selecting or clearing the Enabled check box.

ac37380_.htm |

Last updated Friday, 21 July 2017