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
- Set the Data Source property
of each message flow node to the name (that is, the ODBC DSN)
of the database that you want to access.
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, 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.
- Configure the integration node to be able to connect
to the database:
- Create ODBC data source connections on the system on
which the integration node is running.
- Define a user ID and password to be used by the integration
node to connect to the database by using any of the following
options:
- To set a user ID and password for a particular database, use the
mqsisetdbparms command,
or submit the JCL member BIPSDBP in
the customization data set <hlq>.SBIPPROC on z/OS®.
- To define default values for user ID and password for
the integration node to use for all data source
names for which you have not set specific values, use the mqsisetdbparms command,
or the JCL file BIPSDBP, to specify dsn::DSN.
On Windows,
if Windows integrated authentication
is being used for SQL Server database access, then the service user
ID under which the broker process runs is used by Windows to access the SQL Server database.
That is, it ignores any user ID and password credentials that were
set using the mqsisetdbparms command
- If you have not set up a default user ID and password:
- On Windows, the service
user ID and password are used to connect to the database.
- On z/OS, the integration
node started task ID is used. The schema that is used is
the one defined for a specific DSN or a default DSN setup
by using the mqsisetdbparms command.
If neither exist, the value of CURRENTSQLID in
the BIPDSNAO file is used. If CURRENTSQLID is
not set, the schema defaults to the started task user ID
for the integration node.
- On other platforms, connection to the database fails.
- If you have set a specific user ID and password and want to check
what the values are, use the mqsireportdbparms command.
- Set up the authorization for the user ID to access the
database by using the administration facilities that are provided
by the database vendor. If you do not do so, the integration
node generates an error when the message flow runs.
What to do next
Note: With a single 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.