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.
- Click
If you are using 32-bit DFEs on Windows 64-bit systems, complete the following steps:
- Make a copy of Data Sources (ODBC).
- Right-click the Data Sources (ODBC) shortcut and select properties.
- 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.
. - Click the System DSN tab and click Add.
- 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:
- Select the driver IBM DB2 ODBC DRIVER.
- Enter the data source name (DSN) and description.
- Select the correct database alias from the list.
- Click Finish to save your definition.
- Click OK to close the ODBC Data Source Administrator.
- If you need to use Global Coordination with your database from WebSphere Message Broker 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.
If you prefer, you can use the Configuration Assistant instead of the ODBC Data Source Administrator:- Open the DB2 Configuration Assistant.
- Right-click the database and select Change Database.
- Select Data Source.
- Select Register this database for ODBC. Select the system data source option.
- Click Finish.
- The Test Connection dialog opens automatically and you can test the various connections.
- Informix® Dynamic Server
- Define a data source for Informix Dynamic
Server:
- Select the driver IBM INFORMIX ODBC DRIVER.
- 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.
- Click Apply.
- Click Test Connection to check your supplied values.
- Click OK to close the ODBC Data Source Administrator.
- Microsoft SQL Server
- Define a data source for Microsoft SQL
Server:
- 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.
- Specify a name and description.
- Select the correct server from the list.
- To specify the authentication mode that
is used by the server:
- Click Next.
- Select the authentication mode.
- Click Back to move back to the first panel.
- Click Finish to save your definition.
- Click OK to close the ODBC Data Source Administrator.
- Select the driver for the version of SQL Server that you are using:
- Oracle
- Define a data source for Oracle:
- If you are using WebSphere Message Broker for Windows 32-bit , select the driver WebSphere Message Broker (8.0.0.2) DataDirect Technologies 6.0 32-BIT Oracle Wire Protocol.
- If you are using WebSphere Message Broker for Windows 64-bit , select the driver WebSphere Message Broker (8.0.0.2) DataDirect Technologies 6.0 64-BIT Oracle Wire Protocol.
The ODBC Oracle Driver Setup dialog box opens.
- On the General tab:
- 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.
- On the Advanced tab:
- Select Enable SQLDescribeParam.
- Select Procedure Returns Results. The resultant ODBC definition in the Windows registry has a string value that is called ProcedureRetResults with the value 1.
- Select Login Timeout and set the value to 0.
- Click OK to close the ODBC Data Source Administrator.
- Click .
- Type REGEDIT in the Open field and click OK.
- In the Registry Editor, navigate to the correct location.
- If you are using WebSphere Message Broker for Windows 32-bit on Windows 32-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
- If you are using WebSphere Message Broker for Windows 32-bit on Windows 64-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI
- If you are using WebSphere Message Broker for Windows 64-bit on Windows 64-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
- Expand that location, and right-click your DSN entry. Select .
- Specify WorkArounds for the string name.
- Right-click WorkArounds.
- Select Modify.
- Type the data value 536870912.
- Close the Registry Editor.
- Oracle using Secure Socket Layer (SSL) authentication
- Note: For WebSphere Message Broker Version 8.0, support of Oracle SSL is only available if you have upgraded to the DataDirect Version 7.x ODBC drivers. For more information, see Switching WebSphere Message Broker to use the DataDirect V7 drivers.
Complete the steps for Oracle above.
Then, complete these additional steps:- Reopen the ODBC Oracle Driver Setup dialog box, see Step 1 for Oracle above.
- On the Security tab:
- In the Authentication section, set the Authentication Method to Encrypt Password.
- In the Encryption Section, set the Encryption Method to SSL Auto.
- Select the check box if you want to Validate the Server certificate.
- Enter a fully qualified path for your Trust Store.
- Enter your Trust Store Password.
- Enter a fully qualified path for your Key Store.
- Enter your Key Store Password.
- Enter your SSL Key Password.
- 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 WebSphere Message Broker for Windows 32-bit , select the driver WebSphere Message Broker (8.0.0.2) DataDirect Technologies 6.0 32-BIT Sybase Wire Protocol.
- If you are using WebSphere Message Broker for Windows 64-bit , select the driver WebSphere Message Broker (8.0.0.2) DataDirect Technologies 6.0 64-BIT Sybase Wire Protocol.
- Enter the DSN name, description, and network address of the server, where the network address is made up of MyHostMachineName,MyHostMachinePortNumber.
- On the Advanced tab:
- Select Enable Describe Parameter.
- Select Login Timeout and set the value to 0.
- On the Performance tab:
- Ensure that the Prepare Method setting is 1 - Partial.
- Click .
- Type REGEDIT in the Open field and click OK.
- In the Registry Editor, navigate to the correct location:
- If you are using WebSphere Message Broker for Windows 32-bit on Windows 32-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
- If you are using WebSphere Message Broker for Windows 32-bit on Windows 64-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI
- If you are using WebSphere Message Broker for Windows 64-bit on Windows 64-bit editions: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
- Expand that location, and right-click your DSN entry. Select SelectUserName for the string, and set the value to 1. . Specify
- Right-click your DSN again, and select EnableSPColumnTypes for the string, and set the value to 2. . Specify
- Right-click your DSN again, and select TimestampTruncationBehavior for the string, and set the value to 1. . Specify
- Right-click your DSN again, and select XAConnOptBehavior for the string, and set the value to 3. . Specify
- Close the Registry Editor.
- solidDB®
- Define a data source for solidDB:
- Select the driver IBM® solidDB - (Unicode) DRIVER.
- Enter the description.
- Enter the communication port in the network location field, for example, tcp 2315.
- Click Finish to save your definition.
- Click OK to close the ODBC Data Source Administrator.
The solidDB is only supported on Windows 32-bit operating systems.