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

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 can be an account number.

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

DatabaseRetrieve 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 to 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 broker in the table.

The DatabaseRetrieve node looks up values from a database and stores them as elements in the outgoing message assembly trees. The type of information that is obtained from the database in the form of output column values, which is acquired and passed back in the result set from SQL queries, is converted first into a matching Java type, then into an internal message element value type when it is finally stored in a location in am outgoing message assembly tree. If a message element already exists in the outgoing message tree, the new value overwrites the old value. If the target element does not exist, it is created, and the value is stored.

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 normal 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 hand operand in a test condition. Choose a comparison operator to apply to this operand and, optionally, specify an operand on the right to complete the test condition. The operator could be a null comparison test, in which case an operand on the right is not needed. The value of the operand on the right 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.

The application of the expression must result in a single element, double, Boolean, or string being returned, otherwise an exception occurs. If the query returns multiple rows, the first row is chosen and the rest are ignored, unless the Multiple rows option is selected. In this case, all rows are processed, and values in those rows are used to update the outgoing message assembly trees.

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 or a failure is detected during processing, the message is routed to the Failure terminal. If no rows are returned in the result set following execution of a specified SQL select query, the original message is routed to the KeyNotFound terminal.

Making the JDBC provider service available to the DatabaseRetrieve node

The DatabaseRetrieve 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 IBM Integration Explorer to modify or create the supplied service; see Using the IBM Integration Explorer to work with configurable services for more information.

You can also 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 DatabaseRetrieve 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 in 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 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. 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

The following example adds new elements (family name 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, select Copy message. When this property is selected, the node always creates an outgoing message assembly that is based on the incoming message assembly, and these trees in the new outgoing message assembly are modified and propagated to the node's Out terminal. This behavior enables modification of the outgoing message tree itself ($OutputRoot), in addition to the other logical trees in the message assembly: $OutputLocalEnvironment, $OutputDestinationList, $OutputExceptionList and $Environment. If the logical trees only in the message assembly are to be modified by this node, for performance reasons do not select Copy message. When this property is not selected, the node always works against the original incoming message assembly, and it expects that no updates are attempted against the message tree. If an XPath expression in the Data elements table tries to update this message tree through a reference to $OutputRoot, an MbReadOnlyMessageException occurs. The incoming message is:

<EmployeeRecord>
	<EmployeeNumber>00001</EmployeeNumber>
</EmployeeRecord>

Here is an example of the Query elements table.

Table name	Column name	Operator	Value Type	Value
Employee	FamilyName
Employee	Salary
Employee	EmployeeNumber	=	Element	$InputRoot/XMLNSC/EmployeeRecord/EmployeeNumber

Here is an example of the Data elements table.

Column name	Message element
Employee.FamilyName	$OutputRoot/XMLNSC/EmployeeRecord/FamilyName
Employee.Salary	$OutputRoot/XMLNSC/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 $InputBody/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, which 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 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 "FamilyName" 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>
	<FamilyName>Smith</FamilyName>
	<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>
	<FamilyName>Jones</FamilyName>
	<Wage>26000</Wage>
</EmployeeRecord>

If you select the Multiple rows property, and details of both of the employees are returned from a query in the form of two rows in the result set, the resulting outgoing message is:

<EmployeeRecord>
	<EmployeeNumber>00001</EmployeeNumber>
	<FamilyName>Smith</FamilyName>
	<Wage>20000</Wage>
	<EmployeeNumber>00002</EmployeeNumber>
	<FamilyName>Jones</FamilyName>
	<Wage>26000</Wage>
</EmployeeRecord>

Validating messages

Set the Validation properties to define how the message that is produced by the DatabaseRetrieve node is to be validated. These properties do not cause the input message to be validated. It is expected that, if such validation is required, the validation has already been performed by the input node or a preceding validation node.

For more details, see Validating messages and Validation properties.

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 the result set is empty.
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	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	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 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.	dataSource
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 in the message assembly. By default, this check box is cleared. For performance reasons, select this property only if the input message will be augmented.
Multiple rows	No	Yes	Cleared	This property indicates if all rows are processed when a query returns multiple rows. If you select Multiple rows, all rows are processed, and values in those rows are used to update the outgoing message assembly trees. If you do not select this property, the first row is chosen and the rest are ignored.
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 in 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 an operand on the left (the table column that is specified in the row's first two columns) and optionally a value to apply to an operand on the right. 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 value to an operand on the right. If you specify the operator LIKE or NOT LIKE, the operand on the right must be a string literal with a value type of String.
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. Use a value type of Element if the operand on the right is to be evaluated from the node's incoming message at runtime using an XPATH expression.
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 value of the operand on the right to be compared through this predicate. The compared value of the operand on the right must match the type that is retrieved for the table column that is compared against as the operand on the left. Complex expressions are possible, where zero or more values can be acquired from the incoming message, and manipulated to formed 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.

The DatabaseRetrieve node Data elements table properties are described in the following table.

Property	M	C	Description
Data elements	Yes	No	A list of data elements. A data element is described by the Column name and Message element properties.
Column name	Yes	No	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	No	An XPath 1.0 read-write path 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.

For a full description of these properties, see Validation properties.

Property	M	C	Default	Description
Validate	No	Yes	None	This property controls whether validation takes place. Valid values are None, Content and Value, Content, and Inherit.
Failure action	No	No	Exception	This property controls what happens if a validation failure occurs. You can set this property only if Validate is set to Content or Content and Value. Valid values are User Trace, Local Error Log, Exception, and Exception List.

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.

ac37390_.htm |

Last updated Friday, 21 July 2017