Connecting to a database from Linux and UNIX systems

To enable a broker to connect to a database, 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 on Itanium, you must define the 32-bit ODBC data source name (DSN) that the broker will use to connect to the broker database, even if you are connecting to a 64-bit database, because the broker is a 32-bit application.
  • 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 databases; for more information, see Enabling ODBC connections to the databases.
  • The broker on HP-UX on Itanium is a 64-bit application, therefore you must define a 64-bit ODBC DSN for the broker database (see Connecting to a database from Linux and UNIX systems: 64-bit considerations).

The ODBC Driver Manager has no graphical application to help you to configure the ODBC DSNs. You must define each database 32-bit ODBC connection as a DSN in a plain text file called odbc.ini on the computer that hosts the broker.

To configure a 32-bit DSN for a database:

  1. Copy the odbc.ini sample file that is supplied in the install_dir/merant directory to a location of your choice; for example, to your user ID's home directory. Each broker service user ID on the system can therefore use its own DSN definitions.

    See the sample file contents 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 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 WebSphere MQ commands in the same environment. For more information, see 'Implications of a 64-bit queue manager' in the Quick Beginnings section for your operating system in the WebSphere MQ Version 6 information center online, or in the WebSphere MQ Version 5.3 book on the 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 on your platform:
    • Linux platformSolaris platform On Linux® and Solaris: LD_LIBRARY_PATH
    • HP-UX platform On HP-UX: SHLIB_PATH
    • AIX platform 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 database, 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, a single process can make a maximum of 10 connections using shared memory to a DB2 database. Use TCP/IP mode to connect to the database instance; see DB2 error message SQL1224N is issued when you connect to DB2.
  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. Start of changeIn InstallDir, add the WebSphere Event Broker installation location to complete the fully qualified path to the ODBC directory shown in the sample odbc.ini file.End of change If you do not specify this value correctly, 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. Start of changeIn TraceDll, add the WebSphere Event Broker installation location to complete the fully qualified path to the ODBC trace DLL.End of change
    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 on AIX:
    [ODBC Data Sources]
    WBRKBKDB=IBM DB2 ODBC Driver
    MYDB=IBM DB2 ODBC Driver
    ORACLEDB=DataDirect 5.0 Oracle
    SYBASEDB=DataDirect 5.0 Sybase Wire Protocol
    SQLSERVERDB=DataDirect 5.0 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 (available on Linux on x86 only). If you are not using the default configuration, you do not need to list this database in the odbc.ini file.

    List all your DSNs in your odbc.ini file, regardless of the database manager. You can define multiple DSNs to resolve to the same database; however, if you are using global coordination of transactions, do not use this option because it might cause data integrity problems.

  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. Slight differences also occur 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 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, 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=libdb2Wrapper64.so
      Description=MYDB DB2 ODBC Database
      Database=MYDB
    For an Oracle database:
    1. Start of changeIn Driver, add the WebSphere Event Broker installation location to complete the fully qualified path to the driver shown in the sample odbc.ini file. End of change
    2. In Description, type a meaningful description of the database. This field is 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. Start of changeIn Driver, add the WebSphere Event Broker installation location to complete the fully qualified path to the driver shown in the sample odbc.ini file. End of change
    2. In Description, type a meaningful description of the database. This field is 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 value 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 address is required for local and remote databases). Specify an IP address or server name 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 that is named interfaces.

    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=<Your Server Name>
      EnableDescribeParam=1
      OptimizePrepare=1
      SelectMethod=0
      NetworkAddress=<YourServerName>,<YourPortNumber>
      SelectUserName=1
    For an SQL Server database
    1. In Driver, add the WebSphere Event Broker installation location to complete the fully qualified path to the driver shown in the sample odbc.ini.
    2. In Description, type a meaningful description of the database. This field is for information only and does not affect the connection.
    3. In Address, type the network address of your database server (this address is required for local and remote databases). Specify an IP address or server name 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 value 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 Driver
      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 Event 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 field is 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:
      # Informix stanza
      [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 do not work and the broker is unable to connect to the database.
  10. If you are running DB2 Version 9.1 on HP-UX on PA-RISC, 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 Linux and UNIX®.
Next: Configure the environment for issuing console commands, and for running the broker, so that the broker can access the required database libraries. For more information, see Setting your environment to support access to databases.
Related tasks
Creating the broker databases
Changing the database heap size on DB2 broker databases
Related reference
Supported databases
odbc.ini sample file
Related information
WebSphere MQ Version 6 information center online
WebSphere MQ library Web page
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2009Copyright IBM Corporation 1999, 2009. All Rights Reserved.
Last updated : 2009-01-07 15:40:29

ah14444_