This topic contains sections marked as revised for this release

WebSphere Message Brokers
File: ah25530_
Writer: Laura Cowen

Task topic

This build: July 31, 2007 21:30:44

Connecting to a database from Linux and UNIX systems: 32-bit considerations

To enable a broker to connect to a database, define the ODBC data source name (DSN) for the database.

Before you start:

On Linux and UNIX systems, an ODBC Driver Manager exists, but no graphical application is available to help you to configure the ODBC DSNs. To enable a ODBC connection, define each database as a DSN in a plain text file (called odbc.ini) on the computer that hosts the broker.

Define 32-bit DSNs in the following situations:
  • On any Linux or UNIX system, if a message flow application is deployed to a 32-bit execution group, define a 32-bit DSN for the broker database and any user databases.

To configure a 32-bit DSN for a database:

  1. Copy the odbc.ini sample file that is supplied in the merant directory of your WebSphere Message Broker installation to a location of your choice; for example, copy the file to your user ID's home directory. Copying this file enables each broker service user ID on the system to use its own DSN definitions.

    The sample file is shown in odbc.ini sample file.

  2. Ensure that the odbc.ini file has file ownership of mqm:mqbrkrs, and has the same permissions as the supplied odbc.inisample file.
  3. Set the ODBCINI32 environment variable to point to your odbc.ini file, specifying a full path and file name.

    If you have already run the mqsiprofile script, the ODBCINI32 environment variable is set to a default value. Change the value of the variable so that it points to the location of your odbc.ini file, ensuring that the fully-qualified file path is correct.

  4. If you are using a DB2 instance, set the environment variable MQSI_LIBPATH32 to include the 32-bitdatabase libraries. For example, on AIX:
    export MQSI_LIBPATH32=DB2_instance_directory/sqllib/lib32:$MQSI_LIBPATH32
  5. If you are using a DB2 database instance that is installed on AIX, and a single process will make more than ten connections using shared memory, use TCP/IP mode to connect to the database instance. For detailed instructions, see DB2 error message SQL1224N is issued when you connect to DB2.

    On AIX, a single process can make a maximum of ten connections using shared memory to a DB2 database. Therefore, 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.

  6. Edit the final stanza in the odbc.inifile (the [ODBC] stanza) to specify the location of the ODBC Driver Manager, and to control tracing. The exact details in the stanza depend on the operating system.
    To ensure that you edit the correct odbc.ini file, open the file in the vi text editor using the following command:
    vi $ODBCINI32
    1. In InstallDir, add the WebSphere Message Broker installation location to complete the fully qualified path to the ODBC directory that is shown in the sample odbc.ini file. You must specify this value, otherwise the ODBC definition will not work.
    2. In Trace, set the value to 0; if your IBM Service representative asks you to enable ODBC trace, set the value to 1.
    3. In TraceFile, type the fully-qualified path and file name to which the ODBC trace is written. Trace files can become large; specify a directory with plenty of free disk space.
    4. In TraceDll, add the WebSphere Message Broker installation location to complete the fully qualified path to the ODBC trace DLL that is shown in the sample odbc.ini file.
    5. Accept the default values that are shown in the sample odbc.ini file for all the other entries in the stanza; for example, on AIX:
      [ODBC]
      Trace=0
      TraceFile=<A Directory with plenty of free space>/odbctrace.out
      TraceDll=<Your_install_directory>/merant/lib/odbctrac.so
      InstallDir=<Your_install_directory>/merant
      UseCursorLib=0
      IANAAppCodePage=4
      UNICODE=UTF-8
  7. Edit the first stanza in the odbc.ini file (the [ODBC Data Sources] stanza) to list the DSN of each database; for example:
    [ODBC Data Sources]
    WBRKBKDB=IBM DB2 ODBC Driver
    MYDB=IBM DB2 ODBC Driver
    ORACLEDB=DataDirect
    5.2 Oracle
    SYBASEDB=DataDirect
    5.2 Sybase Wire Protocol
    SQLSERVERDB=DataDirect
    5.2 SQL Server Wire Protocol
    INFORMIXDB=IBM
    Informix ODBC Driver
    The DB2 database called WBRKBKDB in the example, is the broker database that is created by the Default Configuration wizard. If you are not using the default configuration, you do not need to list this database in the odbc.ini file.

    List all of your DSNs in your odbc.ini file, regardless of the database manager. Multiple DSNs can resolve to the same database, but be aware that this action can cause problems if you are using global coordination of transactions.

  8. For each database that you listed in the [ODBC Data Sources] stanza, create a stanza in the odbc.ini file after the [ODBC Data Sources] stanza. The entries in the stanza depend on the database manager. The information for different operating systems can differ; for example, the file paths to the drivers.
    For a DB2 database instance:
    1. In Driver, accept the value shown in the sample odbc.ini file.
    2. In Description, type a meaningful description of the database. This text field is for information only, and does not affect the connection.
    3. In Database, type the DB2 alias. The data source name must be the same as the database alias name. If you are using a remote DB2 database, set up your client-server connection to resolve this alias to the correct database. For more information, see the DB2 documentation.
      For example, on AIX:
      [MYDB]
      Driver=<Your_DB2_installation_directory>/lib/libdb2.a
      Description=MYDB DB2 ODBC Database
      Database=MYDB
    For an Oracle database:
    1. In Driver, add the WebSphere Message Broker installation location to complete the fully qualified path to the driver that is shown in the sample odbc.ini file.
    2. In Description, type a meaningful description of the database. This text field is for information only, and does not affect the connection.
    3. In HostName, type the IP address of the instance on which the Oracle database is running.
    4. In PortNumber, type the port number on which the Oracle database is listening.
    5. In SID, type the Oracle System Identifier of the database as known on the Oracle database server.
    6. Accept the default values that are shown in the sample odbc.ini file for all the other entries in the stanza; for example, on AIX:
      [ORACLEDB]
      Driver=<Your_install_directory>/merant/lib/UKor820.so
      Description=DataDirect 5.0 Oracle
      EnableDescribeParam=1
      OptimizePrepare=1
      ServerName=<Your Oracle host>
      WorkArounds=536870912
      ProcedureRetResults=1
      ColumnSizeAsCharacter=1
    For a Sybase database:
    1. In Driver, add the WebSphere Message Broker installation location to complete the fully qualified path to the driver that is shown in the sample odbc.ini file.
    2. In Description, type a meaningful description of the database. This text field is for information only, and does not affect the connection.
    3. In Database, type the name of the database to which to connect by default. If you do not specify a value, the default is the database that is defined by your system administrator for each user.
    4. In NetworkAddress, type the network address of your Sybase ASE server (which is required for local and remote databases). Specify an IP address in the following format:
      <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 named interfaces on UNIX and Linux systems.
    5. Accept the default values that are shown in the sample odbc.ini file for all the other entries in the stanza; for example, on AIX:
      [SYBASEDB]
      Driver=<Your_install_directory>/merant/lib/UKase20.so
      Description=DataDirect 5.0 Sybase Wire Protocol
      Database=<Your Database Name>
      ServerName=<YourServerName>
      EnableDescribeParam=1
      OptimizePrepare=1
      SelectMethod=0
      NetworkAddress=<YourServerName>,<YourPortNumber>
      SelectUserName=1
    For an SQLServer database:
    1. In Driver, add the WebSphere Message Broker installation location to complete the fully qualified path to the driver that is shown in the sample odbc.ini file.
    2. In Description, type a meaningful description of the database. This text field is for information only, and does not affect the connection.
    3. In Address, type the network address of your database server (which is required for local and remote databases). Specify an IP address in the following format:
      <servername or IP address>,<portnumber>
    4. In Database, type the name of the database to which to connect by default. If you do not specify a value, the default is the database that is defined by your system administrator for each user.
    5. Accept the default values that are shown in the sample odbc.ini file for all the other entries in the stanza; for example, on AIX:
      [SQLSERVERDB]
      Driver=<Your_install_directory>/merant/lib/UKmsss20.so
      Description=DataDirect 5.0 SQL Server Wire Protocol
      Address=<Your SQLServer Host>,<Your SQLServer server port>
      Database=<Your Database Name>
      AnsiNPW=Yes
      QuoteId=No
  9. Ensure that you have edited all three parts of the odbc.ini file:
    • The [ODBC Data Source] stanza at the top of the odbc.ini file.
    • A stanza for each data source.
    • The [ODBC] stanza at the end of the odbc.ini file.
    If you do not configure all three parts correctly, the ODBC DSNs will not work, and the broker will not be able to connect to the database.
You have now configured 32-bit ODBC database connections on Linux and UNIX systems.
Now configure the environment for issuing console commands, and for running the broker, so that it can access the required database libraries. For more information, see Setting your environment to access databases.
Related tasks
Creating a DB2 database on Linux and UNIX systems
Creating the databases
Changing the Database Heap Size on DB2 broker databases
Related reference
Supported databases
odbc.ini sample file
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:30:44

ah25530_ This topic's URL is: