WebSphere Message Brokers
File: ah14444_
Writer: Laura Cowen

Task topic

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

Connecting to a database from Linux and UNIX systems

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

Before you start:

Important: Before you can create a broker on any platform except HP-UX (Integrity platform), you must define the 32-bit ODBC data source name (DSN) that the broker will use to connect to the broker database.

On all platforms except HP-UX (Integrity platform), you must define a 32-bit ODBC DSN for the broker database (even if the broker is connecting to a 64-bit database) because parts of the broker need 32-bit access to the data source. For more information, see Broker database connections. The broker on HP-UX (Integrity platform) is a 64-bit application, so you must define a 64-bit ODBC DSN for the broker database (see Connecting to a database from Linux and UNIX systems: 32-bit considerations).

If the message flows that are deployed to the broker access one or more user databases, you must define a 32-bit DSN for each user database too. On UNIX systems, if you are using 64-bit execution groups or if you are globally coordinating transactions using a 64-bit queue manager, you might also need to define a 64-bit DSN for your broker and user databases; for more information, see Enabling connections to the databases.

On Linux (x86 platform) and UNIX systems, there is an ODBC Driver Manager but no graphical application to help you to configure the ODBC DSNs. To enable a 32-bit ODBC connection, you must define each broker database as a DSN in a plain text file called odbc.ini on the system that hosts the broker. For information about Linux (zSeries platform) and Linux (POWER platform), see Enabling connections to the 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, to your user ID's home directory. This 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.ini sample file.
  3. Set the ODBCINI 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 ODBCINI environment variable is set to a default value. In this case, just change the value of the variable so that it points at the location of your odbc.ini file, ensuring that the fully-qualified file path is correct (the default environment variable names the file .odbc.ini).
  4. Set the library search path environment variable to show the location of the 32-bit libraries for the database manager that you are using. Ask your database administrator (DBA) for information about the database manager that you are using.

    If you are using a 64-bit queue manager (all WebSphere MQ Version 6 queue managers on 64-bit platforms are 64-bit) to globally coordinate transactions, setting the library search path might prevent you running any WebSphere MQ commands in the same environment. For more information, see 'Implications of a 64-bit queue manager' in the WebSphere MQ Quick Beginnings book for your platform, which is available online at WebSphere MQ library Web page.

    For more information about the library search path, see the database manager's documentation.

    The library search path environment variable depends to your platform:
    • On Solaris and Linux (x86 platform): LD_LIBRARY_PATH
    • On HP-UX: SHLIB_PATH
    • On AIX: LIBPATH
    1. If you are connecting to a 64-bit DB2 database instance, add DB2 instance directory/sqllib/lib32 to the start of the library search path environment variable.
      For example, on Solaris:
      export LD_LIBRARY_PATH=DB2 instance directory/sqllib/lib32:$LD_LIBRARY_PATH

      This step is necessary because some parts of the broker must see a 32-bit environment. Completing this step, however, might prevent you from running DB2 commands in this environment shell; to enter DB2 commands, start a separate environment shell and run db2profile for the relevant database instance.

    2. If you are using a 64-bit Oracle instance, add $ORACLE_HOME/lib32 to the start of the library search path environment variable.
      For example, on HP-UX:
      export SHLIB_PATH=$ORACLE_HOME/lib32:$SHLIB_PATH

      This step is necessary because some parts of the broker must see a 32-bit environment.

  5. If you are using a DB2 database instance that is installed on AIX, and a single process will make more than 10 connections using shared memory, use TCP/IP mode to connect to the database instance. For detailed instructions on how to do this, see DB2 error message SQL1224N is issued when you connect to DB2.

    On AIX, a single process can make a maximum of 10 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.ini file, 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 platform.
    To ensure that you edit the correct odbc.ini file, you can open the file in the vi text editor using the following command:
    vi $ODBCINI
    1. In InstallDir, add the WebSphere Message Broker installation location to complete the fully qualified path to the ODBC directory 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 quite 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 shown in the sample odbc.ini file.
    5. Accept the default values 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; this can cause problems, however, 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. There are also slight differences between operating systems, for example the file paths to the drivers.

    For a  DB2  database instance:

    1. In Driver, type the location of the 32-bit driver library that matches your DB2 installation.
    2. In Description, type a meaningful description of the database. This is a text field 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, you must 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 shown in the sample odbc.ini file.
    2. In Description, type a meaningful description of the database. This is a text field for information only and does not affect the connection.
    3. In ServerName, type the Oracle Service Name or Connect Descriptor that resolves to the target Oracle database; for example through a mapping in the TSNAMES.ORA file.
    4. Accept the default values 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 shown in the sample odbc.ini file.
    2. In Description, type a meaningful description of the database. This is a text field for information only and does not affect the connection.
    3. In Database, type 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.
    4. In ServerName, type the name of the Sybase ASE server that you have defined on the server computer and which hosts the database.
    5. In NetworkAddress, type the network address of your Sybase ASE 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 named interfaces on UNIX and Linux.
    6. Accept the default values 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 shown in the sample odbc.ini file.
    2. In Description, type a meaningful description of the database. This is a text field for information only and does not affect the connection.
    3. In Address, type the network address of your database server (this is required for local and remote databases). Specify an IP address as follows:
      <servername or IP address>,<portnumber>
    4. In Database, type 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.
    5. Accept the default values 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
    For an  Informix  database:
    1. In Driver, add the WebSphere Message Broker installation location to complete the fully qualified path to the driver shown in the sample odbc.ini file.
    2. In Description, type a meaningful description of the database. This is a text field for information only and does not affect the connection.
    3. In ServerName, type the name of the Informix IDS server.
    4. In Database, type the name of the database on the IDS server.
      For example, on AIX:
      [INFORMIXDB]
      Driver=<Your Informix Client Directory>/lib/cli/iclit09b.so
      Description=IBM Informix ODBC Driver
      ServerName=<YourServerName>
      Database=<Your Database Name>
  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.
  10. If you are running DB2 version 9 on HP-UX (PA-RISC platform), export environment variable MQSI_SIGNAL_EXCLUSIONS in the broker environment:
    export MQSI_SIGNAL_EXCLUSIONS=10
You have now configured database connections from 32-bit applications on UNIX or Linux.
Now, you must 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 the databases
Changing the Database Heap Size on DB2 broker databases
Connecting to a database from Linux and UNIX systems: 32-bit considerations
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:12

ah14444_ This topic's URL is: