Start of change
WebSphere Message Brokers
File: ac37380_
Writer: Karen Cameron

Reference topic

This build: July 31, 2007 21:20:50

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 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:

DatabaseRoute node icon

Using this node in a message flow

Before you use the DatabaseRoute node in a message flow, set up a database, data source, and any server authentication settings for the node to use. The node can read only from columns that contain supported JDBC types that are listed in the following conversion table.
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
If an error is found in the XPath expression of a pattern, it is reported during validation in the Message Broker 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.

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.

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 ', so 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 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'

Making the JDBC provider client available to the DatabaseRoute node

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.

Choose one of the following methods to make the JDBC provider available to the DatabaseRoute node.
  • Use the mqsicreateconfigurableservice command to create a new object name for the JDBC provider; see mqsicreateconfigurableservice command.
    For a JDBCProviders resource, the properties are described below.
    • databaseType. The database type; for example, DB2.
    • databaseVersion. The database version; for example, 8.1.
    • type4DriverClassName; The name of the JDBC driver class name that is used to establish a connection; for example, com.ibm.db2.jcc.DB2Driver.
    • connectionUrlFormat. A pattern that represents a connection URL definition; for example, jdbc:db2://serverName:portNumber/databaseName:user=user; password=password;
    • connectionUrlFormat Attr1-5. If the specified URL format contains non-standard JDBC data source properties, such as a server identifier, specify these properties in addition to the standard attributes using one of five general purpose connection URL attributes.

      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.

    • databaseName. The name of the database to which the data source entry enables connections; for example, employees.
    • description. An optional property to describe the data source definition.
    • jarsURL. The local directory path, on the system on which the broker is running, where the JAR file that contains the type4 driver class is located.
    • portNumber. The port number on which the database server is listening; for example, 50000.
    • serverName. The name of the server; for example, host1.
    • securityIdentity. A unique security key to perform a second broker registry lookup to find an entry under the broker's DSN entries, which store the encrypted password for the user on their associated host system; for example, jdbc::myuserid@myserver.

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

  • On distributed systems, copy the Java .jar files and any native libraries for the JDBC provider client into the broker shared-classes directory; for example, C:\Documents and Settings\All Users\Application Data\IBM\MQSI\shared-classes on Windows. Copying the files into the shared-classes directory ensures that the Java class path for the JMS nodes is set correctly.
  • On z/OS, no shared-classes directory is used; instead, perform the following steps:
    1. Specify each JDBC provider Java .jar file in the class path in the BIPPROF member of the broker’s Partitioned Data Set (PDS).
    2. Update the LIBPATH with any native libraries.
    3. Submit the BIPGEN JCL job to update the broker ENVFILE.

Using the Data perspective to query data sources

You can use the Data perspective to discover the names of tables within a target database, as well as the names of any columns in those tables.
  1. Switch to the Data perspective.
  2. In the Database Explorer view, expand Connections. The database connections are listed.
  3. Expand a database connection to list the databases, then expand the appropriate database.
  4. Expand Schemas to list the schemas, then expand the appropriate schema.
  5. Expand Tables to list all the tables.
  6. Click a table to show its properties in the Properties view.
  7. 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. 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.

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 is set to Employee.
  • Column Name is set to EmployeeNumber.
  • Operator is set to =.
  • Value Type is set to Element.
  • Value is set to /EmployeeRecord/EmployeeNumber.
  • Filter Table is set to
    $Employee_FamilyName="Jones",out_exp1
    $Employee_Salary>=2000 and
    $Employee_Salary<26000,out_exp2
  • Distribution Mode is set to First.
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 LastName, Salary
from Employee
where EmployeeNumber=x
where x is the value that is retrieved from the incoming message, which is located through the Value property.
  • 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, so it does not meet its condition, and no message is propagated to the Out terminal. The second XPath expression is met, so 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 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 and properties

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

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
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.
The DatabaseRoute node Filter Table properties are described in the following table.
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.
Related concepts
Message flows overview
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:20:50

ac37380_ This topic's URL is:
End of change