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

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

  • Configure the integration node to be able to connect to the database:
    1. Create ODBC data source connections on the system on which the integration node is running.
    2. 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.
      • Windows platformOn 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.