Connecting to the databases

The broker uses ODBC to access its database and you must set up an ODBC connection on each broker system. This topic describes the connections that a broker database needs, and how to define the connections that are required:

Broker database connections

The number of connections required by the broker to its database depend to some extent on the actions of the message flows it is processing. The following connections are required for each broker:

  • Five by internal broker threads.
  • One for each Publish/Subscribe neighbor, if the topology has been deployed.
  • One for each message flow thread that contains a publication node.
  • One for each message flow thread that parses MRM messages.
  • A further number if you are using SCADA nodes with WebSphere MQ Everyplace. The exact number to add depends on whether thread pooling is being used (determined by the Use Thread Pooling property of the SCADAInput node):
    • If Use Thread Pooling is not selected (the default setting) add the number of SCADA clients that will connect to the SCADAInput node.
    • If Use Thread Pooling is selected, add the value in the Max Threads property of the SCADAInput node. The default value is 500.

If you are using the same database for several brokers, you must take account of all brokers in your calculations.

The connections for neighbors and publication nodes are only required if you are using retained publications.

When you start a broker, it opens all connections that it requires to the broker database for its own operation. When you stop the broker, it releases all current database connection handles.

If you are using DB2 for your database, the default action taken by DB2 is to limit the number of concurrent connections to a database to the value of the maxappls configuration parameter. The default for maxappls is 40. If you believe the connections that the broker might require exceed the value for maxappls, increase this and the associated parameter maxagents to new values based on your calculations.

Defining an ODBC connection on UNIX platforms

Linux platform: No ODBC configuration is required on the Linux platform. On this platform, WebSphere Business Integration Message Broker connects directly to DB2, the only supported database.

In the UNIX environment, there is no ODBC Administrator or Driver Manager. To configure an ODBC data source name (DSN) definition, you must edit the required system information, which is held in a plain text file called .odbc.ini (note that the name of this file starts with a period).

This file must be created in the directory identified by the ODBCINI environment variable. If the variable is not set, the directory defaults to the /var/wmqi/odbc directory, which is created when WebSphere Business Integration Message Broker is installed. The file must have file permissions of mqm:mqbrkrs.

A sample template is provided as the file <install_dir>/wmqi/merant/odbc.ini which contains examples of how to configure a DSN residing in each of the databases supported by WebSphere Business Integration Message Broker. The sample template files for the supported platforms are displayed below. They show the recommended configuration for the supported databases.

The entries in the stanzas that you must configure to match your local requirements are shown in italics (descriptions of these entries follow the examples below, see ODBC.ini Parameters). You must retain all the entries shown, but you can remove a complete stanza if you do not require it for your configuration (for example, if you are using an Oracle database, you can remove the stanzas shown for DB2 and Sybase).

Make a copy of the default file, set ODBCINI to point to this copy, and make any changes to the copied file rather than to the default.

Ensure that the appropriate library search path environment variable (LD_LIBRARY_PATH on Solaris) is set to reflect the given database products to be used. Refer to your database product documentation for more details.

Start of changeAIX platform: On AIX, if you are using DB2 as your database, a maximum of 10 connections using shared memory are allowed from a single process. This means that if you deploy more than one or two message flows at the same time, you might see connection failures characterized by the DB2 error message SQL1224N. The connection errors are reported in the system log from the broker's execution group. To avoid this problem, you must use TCP/IP mode to connect to DB2. For detailed instructions on how to do this, see You get DB2 error message SQL1224N when connecting to DB2.End of change

AIX
[ODBC Data Sources]
WBRKBKDB=IBM DB2 ODBC Driver
MYDB=IBM DB2 ODBC Driver
ORACLEDB=DataDirect 410 Oracle Driver
SYBASEDB=DataDirect 410 Sybase ASE Driver
 
[WBRKBKDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.a
Description=WBRKBKDB DB2 ODBC Database
Database=WBRKBKDB
 
[MYDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.a
Description=MYDB DB2 ODBC Database
Database=MYDB

[ORACLEDB]
Driver=/usr/opt/wmqi/merant/lib/UKor818.so
Description=Oracle
ServerName=YourServerName
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
WorkArounds2=2
 
[SYBASEDB]
Driver=/usr/opt/wmqi/merant/lib/UKase18.so
Description=Sybase12
Database=sybasedb
ServerName=YourServerName
WorkstationID=id
EnableDescribeParam=1
OptimizePrepare=1
SelectMethod=0
NetworkAddress=10.30.14.72,5000
SelectUserName=1 
 
[ODBC]
Trace=0
TraceFile=/var/wmqi/odbc/odbctrace.out
TraceDll=/usr/opt/wmqi/merant/lib/odbctrac.so
InstallDir=/usr/opt/wmqi/merant
HP-UX
[ODBC Data Sources]
WBRKBKDB=IBM DB2 ODBC Driver
MYDB=IBM DB2 ODBC Driver
ORACLEDB=DataDirect 410 Oracle Driver
SYBASEDB=DataDirect 410 Sybase ASE Driver
 
[WBRKBKDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.sl
Description=WBRKBKDB DB2 ODBC Database
Database=WBRKBKDB
 
[MYDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.sl
Description=MYDB DB2 ODBC Database
Database=MYDB
 
[ORACLEDB]
Driver=/opt/wmqi/merant/lib/UKor818.sl
Description=Oracle
ServerName=YourServerName
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
WorkArounds2=2
 
[SYBASEDB]
Driver=/opt/wmqi/merant/lib/UKase18.sl
Description=Sybase12
Database=sybasedb
ServerName=YourServerName
WorkstationID=id
EnableDescribeParam=1
OptimizePrepare=1
SelectMethod=0
NetworkAddress=10.30.14.72,5000
SelectUserName=1 
 
[ODBC]
Trace=0
TraceFile=/var/wmqi/odbc/odbctrace.out
TraceDll=/opt/wmqi/merant/lib/odbctrac.sl
InstallDir=/opt/wmqi/merant
Solaris
[ODBC Data Sources]
WBRKBKDB=IBM DB2 ODBC Driver
MYDB=IBM DB2 ODBC Driver
ORACLEDB=DataDirect 410 Oracle Driver
SYBASEDB=DataDirect 410 Sybase ASE Driver
 
[WBRKBKDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.so
Description=WBRKBKDB DB2 ODBC Database
Database=WBRKBKDB
 
[MYDB]
Driver=/u/db2inst1/sqllib/lib/libdb2.so
Description=MYDB DB2 ODBC Database
Database=MYDB
 
[ORACLEDB]
Driver=/opt/wmqi/merant/lib/UKor818.so
Description=Oracle
ServerName=YourServerName
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
WorkArounds2=2
 
[SYBASEDB]
Driver=/opt/wmqi/merant/lib/UKase18.so
Description=Sybase12
Database=sybasedb
ServerName=YourServerName
WorkstationID=id
EnableDescribeParam=1
OptimizePrepare=1
SelectMethod=0
NetworkAddress=10.30.14.72,5000
SelectUserName=1 
 
[ODBC]
Trace=0
TraceFile=/var/wmqi/odbc/odbctrace.out
TraceDll=/opt/wmqi/merant/lib/odbctrac.so
InstallDir=/opt/wmqi/merant

If you are going to access Oracle databases on AIX or Solaris, you must set up symbolic links:

  • On AIX:
    ln -s <ORACLE_INSTALL_DIR>/lib/libclntsh.a /usr/lib/libclntsh.so
  • On Solaris:
    ln -s <ORACLE_INSTALL_DIR>/lib/libclntsh.so /usr/lib/libclntsh.a

(where <ORACLE_INSTALL_DIR> is the directory in which Oracle is installed on your system).

This step is not required on HP-UX.

If you fail to set up this link correctly, XA coordination will not work. The broker's queue manager starts (either when you start the broker, or when you issue strmqm, if you choose to start it independently), but records error AMQ7604 in the WebSphere MQ queue manager error log, indicating that Oracle has returned error XAER_RMFAIL or XAER_RMERR.

Defining an ODBC connection on Windows

On Windows, you can configure an ODBC data source using the ODBC Data Source Administrator. (Select Start > Settings > Control Panel and then ODBC.) When you define a new data source, select the appropriate driver for your database and complete the dialog which is displayed. Refer to your relevant database product documentation for more information.

Setup considerations specific to WebSphere Business Integration Message Broker are described below for the supported databases:

  • DB2 UDB

    When you define a data source for DB2 UDB you must choose the driver named IBM DB2 ODBC DRIVER. Enter the data source name (DSN) and description, and select the correct database alias from the drop-down list.

    For DB2 you can do this more easily as follows:

    1. Open the DB2 Configuration Assistant
    2. Right-click on the database, and select Change Database
    3. Select Data Source
    4. Select Register this database for ODBC
    5. Click Finish
    6. The Test Connection dialog opens automatically and you can test the various connections.
  • Microsoft SQL Server

    When you define a data source for Microsoft SQL Server you must choose the driver named SQL Server. The driver level must be Version 3.60 or later. Specify a name and description, and select the correct server from the drop-down list.

  • Oracle

    When you define a data source for Oracle you must choose the driver named MQSeries DataDirect 4.10 32-BIT Oracle. When you configure this driver you must:

    • Enter the DSN name, description, and server name (where the server name is the "Service Name" that resolves to a "Connect Descriptor", for example through a mapping in the TSNAMES.ORA file).
    • Ensure that the Enable SQLDescribeParam box is checked. This parameter is on the Advanced tab of the driver settings.
    • Create a new registry subkey for each of your DSNs that reference an Oracle database, by using REGEDIT to navigate to:
      HKEY_LOCAL_MACHINE
          SOFTWARE
              ODBC
                  ODBC.INI

      Right-click the DSN, and select New >String Value. Specify WorkArounds for the string, and set the value to 536870912.

  • Sybase Adaptive Server Enterprise

    When you define a data source for Sybase Adaptive Server Enterprise you must choose the driver named MQSeries DataDirect 4.10 32-BIT Sybase. When you configure this driver for use with WebSphere Business Integration Message Broker, you must:

    • Enter the DSN name, description, network address of the server (for a description of the format of this address, see the explanation for NetworkAddress= in ODBC.ini Parameters).
    • Ensure the Enable Describe Parameter box is checked. This parameter is on the Advanced tab.
    • Ensure the Prepare Method setting is 1 - Partial. This parameter is on the Performance tab.

ODBC.ini Parameters

You configure the template files by updating the following parameters:

[ODBC Data Sources]
This stanza describes the data source names (DSNs) for the databases that are configured in the .odbc.ini file. You must configure this stanza to show all your databases, and you must include a stanza for each one.
Driver=
  • If you are using DB2, specify the location of the driver library that matches your DB2 installation.
  • If you are using Oracle or Sybase, use the path exactly as shown.
Description=
You can change this if you choose. This is a text field for information only and has no bearing on operation.
Database=
  • If you are using DB2, specify the database alias. If you are using a remote DB2 database, you must set up your client-server connection to resolve this alias to the correct database. For more information, see the DB2 documentation.
  • If you are using Sybase, specify the name of the database to which you want to connect by default. If you do not specify a value, the default is the database defined by your system administrator for each user.
ServerName=
  • If you are using Oracle, specify the "Service Name" that resolves to a "Connect Descriptor", for example through a mapping in the TSNAMES.ORA file.
  • If you are using Sybase, specify the name of the Sybase database server that you have defined on the server machine.
NetworkAddress=
Specify the network address of your Sybase server (this is required for local and remote databases). Specify an IP address as follows:
<servername or IP address>, <portnumber>

For example Sybaseserver, 5000. You can also specify the IP address directly, for example 199.226.224.34, 5000. You can find the port number in the Sybase interfaces file which is typically named interfaces, interfac, or sql.ini, depending on the operating system.

WorkstationID=
Type the workstation identifier. For example, mqsiuid.
SelectUserName=
This must be set to 1.

Related tasks
Configuring component databases
Creating the databases
Customizing DB2 databases
Authorizing access to the databases

Related reference
Supported databases
User database connections
Database connections for coordinated message flows