Use the Database node to interact with a database in the specified ODBC data source.
You define the nature of the interaction by coding ESQL statements that specify the data from the input message, and perhaps transform it in some way (for example, to perform a calculation), and assign the result to a database table.
You can set a property to control whether the update to the database is committed immediately, or deferred until the message flow completes, at which time the update is committed or rolled back, according to the overall completion status of the message flow.
The Database node is contained in the Database drawer of the palette, and is represented in the IBM® Integration Toolkit by the following icon:
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.
Consider a situation in which you receive an order for 20 monitors. If you have sufficient numbers of monitors in your warehouse, you want to reduce the stock level on your stock database. You can use the Database node to check that you have enough monitors available, and reduce the value of the quantity field in your database.
When you have put an instance of the Database node into a message flow, you can configure it; 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 properties that do not have a default value defined) are marked with an asterisk.
The terminals of the Database node are described in the following table. For more information about the PROPAGATE statement, including its syntax, see PROPAGATE statement
Terminal | Description |
---|---|
In | The input terminal that accepts a message for processing by the node. |
Failure | The output terminal to which the input message is propagated if a failure is detected during the computation. If you have selected Treat warnings as errors, the node propagates the message to this terminal even if the processing completes successfully. |
Out | The output terminal to which the transformed message is routed when processing in the node is completed. The transformed message might also be routed to this terminal by a PROPAGATE statement. |
Out1 | The first alternative output terminal to which the transformed message might be routed by a PROPAGATE statement. |
Out2 | The second alternative output terminal to which the transformed message might be routed by a PROPAGATE statement. |
Out3 | The third alternative output terminal to which the transformed message might be routed by a PROPAGATE statement. |
Out4 | The fourth alternative output terminal to which the transformed message might be routed by a PROPAGATE statement. |
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 Description properties of the Database node are described in the following table.
Property | M | C | Default | Description |
---|---|---|---|---|
Node name | No | No | The node type, Database | 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 Database node Basic properties are described in the following table.
Property | M | C | Default | Description | mqsiapplybaroverride command property |
---|---|---|---|---|---|
Data Source | No | Yes | The ODBC data source name of the database that
contains the tables to which you refer in the ESQL that is associated
with this node (identified by the Statement property). This name identifies the appropriate database as it is known on the system on which this message flow is to run. The broker connects to this database with user ID and password information that you have specified on the mqsisetdbparms command. If the ESQL that is associated with this node includes a PASSTHRU statement or SELECT function and a database reference, you must specify a value for the Data Source property. |
dataSource | |
Statement | No | No | Database | The name of the module in the ESQL file that
contains the statements to use against the database. If you want the
module name to include one or more spaces, enclose the name in double
quotation marks. The ESQL file, which by default has the name <message_flow_name>.esql, contains ESQL for every node in the message flow that requires it. Each portion of code that is related to a specific node is known as a module. When you code ESQL statements that interact with tables, those tables are assumed to exist within this database. If they do not exist, a database error is generated by the broker at run time. Code ESQL statements to customize the behavior of the Database node in an ESQL file that is associated with the message flow in which you have included this instance of the Database node. If an ESQL file does not exist for this message flow, double-click the Database node, or right-click the node and click Open ESQL to create and open a new ESQL file in the ESQL editor view. If an ESQL file exists, click Browse beside the Statement property to display the Module Selection dialog box, which lists the available Database node modules that are defined in the ESQL files that are accessible by this message flow (ESQL files can be defined in other, dependent, projects). Select the appropriate module and click OK. If no suitable modules are available, the list is empty. If the module that you have specified does not exist, it is created for you and the editor displays it. If the file and the module exist, the editor highlights the correct module. If a module skeleton is created for this node in a new or existing ESQL file, it consists of the following ESQL. The default module name is shown in this example:
If you create your own ESQL module, create exactly this skeleton. You can update the default name, but ensure that the name that you specify matches the name of the corresponding node property Statement. To customize this node, add your own ESQL after the BEGIN statement and before RETURN TRUE. You can use all the ESQL statements including SET, WHILE, DECLARE, and IF in this module, but (unlike the Compute node) the Database node propagates, unchanged, the message that it receives at its input terminal to its output terminal. Therefore, like the Filter node, you have only one message to refer to in a Database node. |
|
Transaction | Yes | No | Automatic | The transaction mode for the node. The values
are:
|
|
Treat Warnings as Errors | Yes | No | Cleared | For database warning messages to be treated
as errors, and for the node to propagate the output message to the
Failure terminal, select Treat Warnings
as Errors. The check box is cleared initially. When you select the check box, the node handles all positive return codes from the database as errors, and generates exceptions in the same way that it does for the negative, or more serious, errors. If you do not select the check box, the node treats warnings as normal return codes, and does not raise an exception. The most significant warning raised is not found, which can be handled safely as a typical return code in most circumstances. |
|
Throw Exception on Database Error | Yes | No | Selected | For the broker to generate an exception when
a database error is detected, select Throw
Exception on Database Error. The check box is selected initially.
If you clear the check box, include ESQL to check for database errors that might be returned after each database call that you make (you can use SQLCODE and SQLSTATE to get this information). If an error has occurred, you must handle the error in the message flow to ensure the integrity of the broker and the database; the error is ignored if you do not handle it through your own processing because you have chosen not to use the default error handling by the broker. For example, you can include the ESQL THROW statement to throw an exception in this node, or you can use the Throw node to generate your own exception at a later point. |
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. |