Configuring access to databases

The broker uses ODBC to access its database and you must set up an ODBC Start of changedata source name (DSN) definitionEnd of change on each broker system. Start of changeEach program that accesses a database through ODBC creates a connection to do this. At any one time there can be multiple connections using the same DSN definition.End of changeThis topic describes the connections that a broker database needs, and the ODBC parameters that you must tailor for the connections:

If you are using the Default Configuration Wizard or the database commands to create a broker or a database on Windows, the ODBC Start of changeDSNsEnd of change are automatically created for you.

Start of changeTo define the DSN explicitly, link to the topic for your operating system and data source type. End of change

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.

ODBC.ini Parameters

Configure the template files by updating the following list of configurable parameters. Some parameters are not applicable to all databases, as shown.

[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.
Start of changeAddress (SQL Server wire protocol definitions)End of change
Start of changeSpecify the network address of your server (this is required for local and remote databases). Specify an IP address as follows:
<servername or IP address>, <portnumber>
End of change
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 (DB2, SybaseStart of change, SQL Server accessed from UNIXEnd of change, Informix)
  • 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 SybaseStart of change or SQL Server accessed from UNIXEnd of change, 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.
  • Start of changeIf you are using Informix, specify the name of the database on the IDS server.End of change
Start of changeHostName (Oracle 64-bit wire protocol)End of change
Start of changeSpecify the IP address on which the Oracle database is running.End of change
NetworkAddress (Sybase only)
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.

Start of changePortNumber (Oracle 64-bit wire protocol)End of change
Start of changeSpecify the port number on which the Oracle database is running.End of change
ServerName (Sybase, OracleStart of change, InformixEnd of change)
  • 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. Start of changeThis is applicable only to the 32-bit client driver.End of change
  • If you are using Sybase, specify the name of the Sybase database server that you have defined on the server computer.
  • Start of changeIf you are using Informix, specify the name of the Informix IDS server.End of change
Start of changeSID (Oracle 64-bit wire protocol)End of change
Start of changeSpecify the Oracle System Identifier of the database on that server.End of change

Setting your environment to access databases

Once you have configured your ODBC Start of changedata source namesEnd of change, you must also configure the environment for issuing console commands, and for running the broker, so that it can access the required database libraries. For example, if you have a DB2 broker database, you must add the DB2 client libraries to your library search path.

On Windows platforms, this is likely to have been done for you when you installed the database product. On UNIX systems, and Linux, you need to run a profile for each database you need to access. For example, on DB2 you must run db2profile; other database vendors have similar profiles.

Start of changeIf you are using a 64-bit DB2 instance, ensure that you add <DB2 instance directory>/sqllib/lib32 to the start of the library search path environment variable after running db2profile. If you are using a 64-bit Oracle instance, ensure that you add $ORACLE_HOME/lib32 to the start of the library search path environment variable. If you are using 64-bit execution groups, ensure that you set the environment variable MQSI_LIBPATH64 to include the regular 64-bit database libraries.End of change

Additionally, ensure that you access the correct set of libraries (32-bit) as this might not be the default.

Start of changeIf you are using WebSphere MQ Version 6, refer to the section 'Implications of a 64-bit queue manager' in the WebSphere MQ Quick Beginnings book for your operating environment.End of change