Connecting to a database from Windows systems

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

Before you begin

Check that you have set up your environment so that the integration node 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 Running database setup scripts before starting an integration node.

About this task

Configure an ODBC data source by using the ODBC Data Source Administrator:
  1. Click Start > Control Panel > Administrative Tools > Data Sources (ODBC).
  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® App Connect Enterprise on Windows systems, the next task is to set up the 32-bit environment that is needed by IBM MQ, see Setting your environment to support 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.
Db2 on iSeries
Define a data source for Db2 on iSeries:
  1. Select the driver IBM i Access ODBC DRIVER.
  2. Enter the DSN and description.
  3. Select the correct system list or enter a system name.
  4. Click OK to save your definition.
  5. Click OK to close the ODBC Data Source Administrator.
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 SQL Native Client 11.0 driver for SQL Server.
  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:
    • Select the driver IBM App Connect Enterprise (12.0.n.0) DataDirect Technologies 64-BIT Oracle Wire Protocol where n is the level of the installed fix pack.

    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. If you are using TIMESTAMP WITH TIMEZONE columns, select Enable Timestamp With Timezone.
    5. In Extended Options, enter: 
      WorkArounds=536870912;EnableNcharSupport=0;
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.
Oracle using Advanced Security (OAS)

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 Advanced Security tab:
    1. Select the Encryption Level that you want to use. Choose from the following options:
      • 0 - Rejected. If rejected, or no match is found between the driver and server encryption types, data that is sent between the driver and the database server is not encrypted or decrypted. If the Oracle server has its sqlnet.encryption_server setting set to "REQUIRED" and this option is selected, then the connection to the Oracle database fails.
      • 1 - Accepted. Encryption is used on data that is sent between the driver and the database server if the database server requests or requires it.
      • 2 - Requested. Data that is sent between the driver and the database server is encrypted and decrypted if the database server permits it.
      • 3 - Required. Data that is sent between the driver and the database server must be encrypted and decrypted. If the Oracle server has its sqlnet.encryption_server setting set to "REJECTED" and this option is selected, then the connection to the Oracle database fails.
    2. Select the Encryption Types that you want to use.
    3. Select the Data Integrity Level you want to use. Choose from the following options:
      • 0 - Rejected. A data integrity check on data that is sent between the driver and the database server is refused. If the Oracle server has its sqlnet.crypto_checksum setting set to "REQUIRED" and this option is selected, then the connection to the Oracle database fails.
      • 1 - Accepted. A data integrity check can be made on data that is sent between the driver and the database server. Data integrity is used if the database server requests or requires it.
      • 2 - Requested. The driver enables a data integrity check on data that is sent between the driver and the database server if the database server permits it.
      • 3 - Required. A data integrity check must be performed on data that is sent between the driver and the database server. If the Oracle server has its sqlnet.crypto_checksum setting set to "REJECTED" and this option is selected, then the connection to the Oracle database fails.
    4. Select the Data Integrity Types that you want to use.
  3. Click OK to close the ODBC Data Source Administrator.
PostgreSQL
Define a data source for PostgreSQL:
  1. Select the driver IBM App Connect Enterprise (12.0.n.0) PostgreSQL Wire Protocol (DataDirect), where n is the level of the installed fix pack.

    The ODBC PostgreSQL Driver Setup window is displayed.

  2. On the General tab, enter the DSN name, description, and host name of the machine where PostgreSQL is running, the port number on which PostgreSQL is listening, and the name of the database to which you want to connect.
Sybase Adaptive Server Enterprise
Define a data source for Sybase Adaptive Server Enterprise:
    • Select the driver IBM App Connect Enterprise (12.0.n.0) DataDirect Technologies 64-BIT Sybase Wire Protocol where n is the level of the installed fix pack.
  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.
    • In Extended Options, enter:
      TimestampTruncationBehavior=1;EnableSPColumnTypes=2;XAConnOptBehavior=3;
  4. On the Performance tab:
    • Ensure that the Prepare Method setting is 1 - Partial.
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.

Results

You have now configured your ODBC data source names on Windows.