IBM Integration Bus, Version 9.0.0.8 Operating Systems: AIX, HP-Itanium, Linux, Solaris, Windows, z/OS

See information about the latest product version

Connecting to a database from Windows systems

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

Before you start: Check that you have set up your environment so that the broker can connect to the database. Most database managers set up the required environment when you install, but others supply a database profile that you must run. For information about environments and running database profiles, see Setting up a command environment: Windows platforms.

For 32-bit Data Flow Engines (DFEs), you must use the 32-bit ODBC driver manager to make the 32-bit ODBC definitions. Similarly, for 64-bit DFEs, you must use the 64-bit ODBC driver manager to make the 64-bit ODBC definitions.

On Windows 64-bit, both the 64-bit and 32-bit Data Source Administrators are supplied, and their locations are:
  • Windows 64-bit: %SystemRoot%\system32\odbcad32.exe
  • Windows 32-bit: %SystemRoot%\sysWoW64\odbcad32.exe
The expected usage is that the 64-bit drivers are used with 64-bit applications through the 64-bit ODBC Administrator in the system32 directory. But if you are using 32-bit applications, then the 32-bit drivers are used, and the 32-bit ODBC Administrator is in the sysWoW64 directory.
Configure an ODBC data source by using the ODBC Data Source Administrator:
  1. Click Start > Control Panel > Administrative Tools > Data Sources (ODBC).

    If you are using 32-bit DFEs on Windows 64-bit systems, complete the following steps:

    1. Make a copy of Data Sources (ODBC).
    2. Right-click the Data Sources (ODBC) shortcut and select properties.
    3. In the 64-bit ODBC dialog, by default the target points to %SystemRoot%\system32\odbcad32.exe. Copy the shortcut, modify the target to point to %SystemRoot%\SysWoW64\Odbcad32.exe, and use the revised copy of the shortcut instead.
  2. Click the System DSN tab and click Add.
  3. Complete the steps in the following sections for the databases that you are working with.

    If you need more information about a particular database product, see the product-specific documentation.

DB2® UDB
Define a data source for DB2 UDB:
  1. Select the driver IBM DB2 ODBC DRIVER.
  2. Enter the data source name (DSN) and description.
  3. Select the correct database alias from the list.
  4. Click Finish to save your definition.
  5. Click OK to close the ODBC Data Source Administrator.
  6. If you need to use Global Coordination with your database from IBM Integration Bus on Windows systems, the next task is to set up the 32-bit environment that is needed by WebSphere® MQ, see Setting your environment to support 32-bit access to databases.
You must register the data source as a system data source.
If you prefer, you can use the Configuration Assistant instead of the ODBC Data Source Administrator:
  1. Open the DB2 Configuration Assistant.
  2. Right-click the database and select Change Database.
  3. Select Data Source.
  4. Select Register this database for ODBC. Select the system data source option.
  5. Click Finish.
  6. The Test Connection dialog opens automatically and you can test the various connections.
Informix® Dynamic Server
Define a data source for Informix Dynamic Server:
  1. Select the driver IBM INFORMIX ODBC DRIVER.
  2. On the Connection tab, specify:
    • The Informix server name.
    • The server host name.
    • The Informix network service name (as defined in the services file).
    • The network protocol (for example, olsoctcp).
    • The Informix data source name.
    • The user identifier to access the data source within.
    • The password for that user identifier.
  3. Click Apply.
  4. Click Test Connection to check your supplied values.
  5. Click OK to close the ODBC Data Source Administrator.
Microsoft SQL Server
Define a data source for Microsoft SQL Server:
  1. Select the driver for the version of SQL Server that you are using:
    • SQL Native Client for SQL Server 2005.
    • SQL Native Client 10.0 for SQL Server 2008.
  2. Specify a name and description.
  3. Select the correct server from the list.
  4. To specify the authentication mode that is used by the server:
    1. Click Next.
    2. Select the authentication mode.
    3. Click Back to move back to the first panel.
  5. Click Finish to save your definition.
  6. Click OK to close the ODBC Data Source Administrator.
Oracle
Define a data source for Oracle:
    • If you are using IBM Integration Bus for Windows 32-bit , select the driver IBM Integration Bus (9.0.0.0) DataDirect Technologies 7.0 32-BIT Oracle Wire Protocol.
    • If you are using IBM Integration Bus for Windows 64-bit , select the driver IBM Integration Bus (9.0.0.0) DataDirect Technologies 7.0 64-BIT Oracle Wire Protocol.

    The ODBC Oracle Driver Setup dialog box opens.

  2. On the General tab:
    1. Enter the DSN name, description, and host name of the machine where Oracle is running, the port number on which Oracle is listening, and the Oracle Service Name that you want to connect to.
  3. On the Advanced tab:
    1. Select Enable SQLDescribeParam.
    2. Select Procedure Returns Results. The resultant ODBC definition in the Windows registry has a string value that is called ProcedureRetResults with the value 1.
    3. Select Login Timeout and set the value to 0.
  4. Click OK to close the ODBC Data Source Administrator.
  5. Click Start > Run.
  6. Type REGEDIT in the Open field and click OK.
  7. In the Registry Editor, navigate to the correct location.
    • If you are using IBM Integration Bus for Windows 32-bit on Windows 32-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
    • If you are using IBM Integration Bus for Windows 32-bit on Windows 64-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI
    • If you are using IBM Integration Bus for Windows 64-bit on Windows 64-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
  8. Expand that location, and right-click your DSN entry. Select New > String Value.
  9. Specify WorkArounds for the string name.
    1. Right-click WorkArounds.
    2. Select Modify.
    3. Type the data value 536870912.
  10. Close the Registry Editor.
Oracle using Secure Socket Layer (SSL) authentication

Complete the steps for Oracle above.

Then, complete these additional steps:
  1. Reopen the ODBC Oracle Driver Setup dialog box, see Step 1 for Oracle above.
  2. On the Security tab:
    1. In the Authentication section, set the Authentication Method to Encrypt Password.
    2. In the Encryption Section, set the Encryption Method to SSL Auto.
    3. Select the check box if you want to Validate the Server certificate.
    4. Enter a fully qualified path for your Trust Store.
    5. Enter your Trust Store Password.
    6. Enter a fully qualified path for your Key Store.
    7. Enter your Key Store Password.
    8. Enter your SSL Key Password.
  3. Click OK to close the ODBC Data Source Administrator.
Sybase Adaptive Server Enterprise
Define a data source for Sybase Adaptive Server Enterprise:
    • If you are using IBM Integration Bus for Windows 32-bit , select the driver IBM Integration Bus (9.0.0.0) DataDirect Technologies 7.0 32-BIT Sybase Wire Protocol.
    • If you are using IBM Integration Bus for Windows 64-bit , select the driver IBM Integration Bus (9.0.0.0) DataDirect Technologies 7.0 64-BIT Sybase Wire Protocol.
  2. Enter the DSN name, description, and network address of the server, where the network address is made up of MyHostMachineName,MyHostMachinePortNumber.
  3. On the Advanced tab:
    • Select Enable Describe Parameter.
    • Select Login Timeout and set the value to 0.
  4. On the Performance tab:
    • Ensure that the Prepare Method setting is 1 - Partial.
  5. Click Start > Run.
  6. Type REGEDIT in the Open field and click OK.
  7. In the Registry Editor, navigate to the correct location:
    • If you are using IBM Integration Bus for Windows 32-bit on Windows 32-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
    • If you are using IBM Integration Bus for Windows 32-bit on Windows 64-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI
    • If you are using IBM Integration Bus for Windows 64-bit on Windows 64-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
  8. Expand that location, and right-click your DSN entry. Select New > String Value. Specify SelectUserName for the string, and set the value to 1.
  9. Right-click your DSN again, and select New > String Value. Specify EnableSPColumnTypes for the string, and set the value to 2.
  10. Right-click your DSN again, and select New > String Value. Specify TimestampTruncationBehavior for the string, and set the value to 1.
  11. Right-click your DSN again, and select New > String Value. Specify XAConnOptBehavior for the string, and set the value to 3.
  12. Close the Registry Editor.
solidDB®
Define a data source for solidDB:
  1. Select the driver IBM solidDB - (Unicode) DRIVER.
  2. Enter the description.
  3. Enter the communication port in the network location field, for example, tcp 2315.
  4. Click Finish to save your definition.
  5. Click OK to close the ODBC Data Source Administrator.

The solidDB is only supported on Windows 32-bit operating systems.

You have now configured your ODBC data source names on Windows.
Parent topic: Enabling ODBC connections to the databases
Related tasks:
Setting up a command environment
Related reference:
Supported databases
ah14442_.htm | Last updated Friday, 21 July 2017