Accessing databases from ESQL
Configure your integration node and your database to support connections from message flows.
Before you begin
About this task
You must configure both your integration node and your databases to support read, write, and update operations in your message flows.
For details of the ESQL statements and functions that you can use to access databases, see Interaction with databases using ESQL.
Procedure
You can access more than one database by using the FROM
clause
in your ESQL statement, but all databases that are accessed from
the same message flow node must have the same ODBC functions as
the database that is specified on the Data Source property on that node.
This requirement is always satisfied if the databases are of the
same type (for example, IBM
DB2® or
Oracle), at the same release level (for example, release 9.1),
and on the same platform. Other database combinations might have
the same ODBC functions. If a message flow node tries to access a
database that does not have the same ODBC functions as the database
specified on the Data Source property
on that message flow node, the integration node generates an error
message.
You can use the mqsicvp command as an ODBC test tool. This test tool can be run against two databases simultaneously, and tells you whether those two datasources are eligible to be used together in the same node.
The test tool is also useful in displaying any limitations there might be in your datasource, before constructing your ESQL. For more information, see Enabling ODBC connections to the databases.
What to do next
SELECT FROM
clause,
you can access only tables that exist in a single database.If
you access database columns that have names that are composed of only
numeric characters, you must enclose the names in double quotation
marks; for example, "0001". Because of this restriction, you cannot
use a SELECT *
statement, which returns the names
without quotation marks; the names are therefore invalid and the
integration node raises an exception.
You can also access a database by using the Database Service. For more information, see Database Service.