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

Reference topic

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

DatabaseRetrieve node

Use the DatabaseRetrieve node to ensure that information in a message is up to date.

This topic contains the following sections:

Purpose

Use the DatabaseRetrieve node to modify a message using information from a database. For example, you can add information to a message using a key that is contained in a message; the key could be an account number.

The DatabaseRetrieve node is contained in the Database drawer of the message flow node palette, and is represented in the workbench by the following icon:

DatabaseRetrieve node icon

Using this node in a message flow

Before you use the DatabaseRetrieve 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
You can route a message to the same location, whether or not a query is successful against a given 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 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 broker within the table.

The DatabaseRetrieve node looks up values from a database and stores them as elements in the message assembly trees. The type of information that is obtained from the database is checked to ensure that it is of the correct type to be stored in an existing location in the outgoing message. If it is not, an exception occurs. If a message element already exists in the message, the new value overwrites the old value. If the target element does not exist, it is created and the value is stored as a type that matches that of the database column from which the value was retrieved.

The node needs database information that is used to form a multi-table SQL select query, which can have multiple test conditions. 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 hand operand in a test condition. Choose a comparison operator to apply to this operand and, optionally, specify a right hand operand to complete the test condition. The operator could be a NULL comparison test, in which no right hand operand is needed. The value of the right hand operand can be a database type (such as Integer, Boolean, or Long), another table qualified column (specified as an integer index into the table where it is defined), or a value that is acquired from an element in the incoming message, as expressed through either an XPath path location expression, or an ESQL field reference expression. If the path does not exist in the input message, an exception occurs. If the query returns multiple rows, the first row is chosen and the rest are ignored.

It can be useful to combine a DatabaseRetrieve node with other message flow nodes. For example, you can use an XSLTransform node to manipulate data before or after the DatabaseRetrieve node is invoked.

The DatabaseRetrieve node has one input terminal (In) and three output terminals (Out, keyNotFound, and Failure). If the message is modified successfully, it is routed to the Out terminal. If the message is not modified successfully, it is routed, unchanged, to the keyNotFound terminal. If a failure is detected during processing, the message is routed to the Failure terminal.

Making the JDBC provider client available to 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 DatabaseRetrieve node

When you have put an instance of the DatabaseRetrieve 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

The following example adds new elements (surname and wage) to the incoming message structure. This example uses a database table called Employee.
EmployeeNumber FamilyName FirstName Salary
00001 Smith John 20000
00002 Jones Harry 26000
00003 Doe Jane 31000
To make a copy of the incoming message, the Copy Message check box is selected. The incoming message is:
<EmployeeRecord>
	<EmployeeNumber>00001</EmployeeNumber>
</EmployeeRecord>
The query elements table looks like this:
Table Name Column Name Operator ValueType Value
Employee FamilyName      
Employee Salary      
Employee EmployeeNumber = Element /EmpRecord/EmpNumber
and the data elements table looks like this:
Column Name Message element
Employee.FamilyName /EmployeeRecord/Surname
Employee.Salary /EmployeeRecord/Wage
The DatabaseRetrieve 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 /EmployeeRecord/EmployeeNumber. The SQL query is:
select FamilyName, 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 data element row says get the value of the FamilyName column that is returned from the query, and insert it into a new element named "Surname" under EmployeeRecord. The second data element row says get the value of the Salary column that is returned from the query, and insert it into a new element named "Wage" under EmployeeRecord. The resulting outgoing message is:
    <EmployeeRecord>
    	<EmployeeNumber>00001</EmployeeNumber>
    	<Surname>Smith</Surname>
    	<Wage>20000</Wage>
    </EmployeeRecord>
  • If the value at this location is 00002, information for Harry Jones is retrieved. The resulting outgoing message is:
    <EmployeeRecord>
    	<EmployeeNumber>00002</EmployeeNumber>
    	<Surname>Jones</Surname>
    	<Wage>26000</Wage>
    </EmployeeRecord>

Terminals and properties

The DatabaseRetrieve node terminals are described in the following table.

Terminal Description
In The input terminal that accepts a message for processing by the node.
Out The output terminal to which the outgoing message is routed when it has been modified successfully.
keyNotFound The output terminal to which the original message is routed, unchanged, when modification is unsuccessful.
Failure The output terminal to which the message is routed if a failure is detected during processing.

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 DatabaseRetrieve node Description properties are described in the following table.
Property M C Default Description
Node name No No The node type, DatabaseRetrieve 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 DatabaseRetrieve 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 DatabaseRetrieve node.

Copy Message No Yes Cleared This property indicates if a copy of the original incoming message is required because the message tree is to be updated, possibly in addition to logical trees within the message assembly.
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 DatabaseRetrieve node Data Element Table properties are described in the following table.
Property M C Default Description
Data Elements No No   A list of data elements. A data element is described by the Value Column Name and Message Element properties.
Value Column Name Yes Yes   The name of the database column from which to obtain the element value. The list of names is updated dynamically based on the column entries that are entered in the query elements table.
Message Element Yes Yes   An XPath 1.0 expression that describes the path location of a message element. The message element is where the database value is stored. The XPath expression must evaluate to a single element in the message.
The DatabaseRetrieve node Validation properties are described in the following table.
Property M C Default Description
Validate No Yes None This property indicates whether validation is required. Valid values are:
  • None
  • Content to perform content checks, such as Content Validation and Composition
  • Content and Value to perform content checks, such as Content Validation and Composition, and value checks, such as value data type, null permitted, length, range, and enumeration.
  • Inherit to use all the validation options that are provided with the input message tree in preference to any that are supplied on the node. Inherit resolves to None, Content, or Content and Value.
The DatabaseRetrieve node Exception properties are described in the following table.
Property M C Default Description
Failure Action No Yes Exception The action to take when a validation failure occurs. Valid values are:
  • Exception to throw an exception on the first validation failure. Validation stops and the failure is logged in the user trace, if you have asked for user tracing of the message flow. This value is most useful after you have completed debugging of your message flow and application.
  • Exception List to throw an exception if validation failures are encountered, but only when the current parsing or writing operation has completed. An exception list is produced.
  • User Trace to write all validation failures to the user trace, even if you have not asked for user tracing of the message flow. Validation continues after the first validation failure, so this value is useful when you set the Validate property to Content and Value.
  • Local Error Log to write all validation failures to the error log, such as the Event Log on Windows. Validation continues after the first validation failure, so this value is useful when you set the Validate property to Content and Value.
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:51

ac37390_ This topic's URL is:
End of change