You must configure both your broker 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.
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 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 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, before constructing your ESQL. For more information, see Enabling ODBC connections to the databases.
- Configure the broker to be able to
connect to the database:
- 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.
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 broker 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 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.
- Define a user ID and password to be used by the broker to connect to the database
by using any of the following options:
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 broker raises an exception.
You can also access a database by using the Database Service. For more information, see Database Service.
Last updated Friday, 21 July 2017