Accessing databases from ESQL

Configure your integration server and your database to support connections from message flows.

Before you begin

About this task

You must configure your integration server 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.

  • Configure the integration server to be able to connect to the database:
    1. Create ODBC data source connections on the system on which the integration server is running.
    2. Define a user ID and password to be used by the integration server 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.
      • To define default values for user ID and password for the integration server to use for all data source names for which you have not set specific values, use the mqsisetdbparms command 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 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 server 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 server raises an exception.