See information about the latest product version
Accessing databases from ESQL
Configure your broker and your database to support connections from message flows.
Before you start:
You must configure both your broker and your databases to support read, write, and update operations in your message flows.
- Set the Data Source property of each 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 accessed from the same node
must have the same ODBC functions as the database 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 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 node,
the broker 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, prior to constructing your ESQL; see Enabling ODBC connections to the databases for further information.
- Configure the broker to be able to connect to the database:
- Create ODBC data source connections on the system on which the broker is running.
- Define a user ID and password to be used by the broker 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 broker 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.
- 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 broker started task ID is used. The schema used is the one defined for a specific DSN or a default DSN set up 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 broker.
- 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 provided by the database vendor. If you do not do so, the broker generates an error when the message flow runs.
- 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 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 broker raises an exception.
For details of the ESQL statements and functions that you can use to access databases, see Interaction with databases using ESQL.