DataBase Interaction Uses ESQL statements in DataBase Node

-Use the Database node to interact with a database in the specified ODBC data source.

You can use specialized forms of this node to:

• Update values within a database table (the DataUpdate node)
• Insert rows into a database table (the DataInsert node)
• Delete rows from a database table (the DataDelete node)
• Store the message, or parts of the message, in a warehouse (the Warehouse node)

DataBase Interaction With DataBaseRetrieve Node :

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

- 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(Select Query Elements & DataElements).

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

- Uses specified Configurable Service Name as a Data Source Name.

- Run mqsisetdbparms command if the Configurable service is newly created.

DataBase Interaction With DataBaseRoute Node :

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

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

- Uses specified Configurable Service Name as a Data Source Name.

- Run mqsisetdbparms command if the Configurable service is newly created.

DataBase Interaction Uses PASSTHRU statement in Compute Node

The main use of the PASSTHRU statement is to issue administrative commands to databases (for example, to create a table).

Note: Do not use PASSTHRU to call stored procedures; instead, use the CALL statement because PASSTHRU imposes limitations (you cannot use output parameters, for example).Uses specified ODBC data source Name.

- Only DDL Statements (CREATE, DROP , ALTER) requires PASSTHRU in a compute node.

Examples :

The following example creates the table Customers in schema Shop in database DSN1:

PASSTHRU 'CREATE TABLE Shop.Customers (  CustomerNumber INTEGER,  FirstName      VARCHAR(256),  LastName       VARCHAR(256),  Street         VARCHAR(256),  City           VARCHAR(256),  Country        VARCHAR(256) )' TO Database.DSN1;

If, as in the last example, the ESQL statement is specified as a string literal, you must put single quotation marks around it. If, however, it is specified as a variable, omit the quotation marks.

For example:

SET myVar = 'SELECT * FROM user1.stocktable'; SET OutputRoot.XMLNS.Data[] = PASSTHRU(myVar);

The following example "drops" (that is, deletes) the table Customers from schema Shop in database DSN1:

PASSTHRU 'DROP TABLE Shop.Customers' TO Database.DSN1;