Connecting to a database from Linux and UNIX systems by using the IBM Integration ODBC Database Extender

IBM® Integration ODBC Database Extender encapsulates the unixODBC driver manager. You must set up and configure the integration node to use it.

Before you begin

The following information applies to all supported databases.

  • Read the information about the unixODBC Project.
  • Create the database.
  • Ensure that your database is set up so that the integration node is authorized to access the database.
  • Check that you have set up your environment so that the integration node can access the database. You might have to run a database profile that is supplied by the database vendor. For more information, see Running database setup scripts.
Note: In IBM Integration Bus Version 10.0, you do not need to install the IBM Integration ODBC Database Extender program to use database nodes in your applications. The program code is installed as part of the IBM Integration Bus installation.

Procedure

  1. Copy the odbc.ini sample file that is supplied in the install_dir/server/ODBC/unixodbc/ directory to a location of your choice.
    Each integration node service user ID on the system can therefore use its own data source name (DSN) definitions.
    Note: To prevent problems with the backup and restore procedures, we recommend that the copy of the sample file is placed into the /var/mqsi directory rather than the home directory for your user ID.
  2. Ensure that the odbc.ini file is owned by the mqbrkrs group, and has 664 permissions.
  3. Set the ODBCINI environment variable to point to your odbc.ini file, specifying a full path and file name. Make sure that you point to the copy, do not point to the odbc.ini file in the installation directories.
  4. Copy the odbcinst.ini sample file that is supplied in the install_dir/server/ODBC/unixodbc/ directory to a location of your choice. See Note: in step 1.
  5. Ensure that the odbcinst.ini file is owned by the mqbrkrs group and has 664 permissions.
  6. Set the ODBCSYSINI environment variable to point to the directory that contains the odbcinst.ini file, specifying a full path name. Make sure that you point to the directory containing the copy, do not point to the directory containing the odbcinst.ini file in the installation directories.
  7. If you are connecting to IBM DB2® solidDB, or Informix® databases, set the library search path environment variable to show the location of the libraries for the database manager that you are using.

    For more information about the library search path, ask your database administrator (DBA), or see the documentation for your database manager.

    The library search path environment variable depends on your platform:
    • Linux platformSolaris platformOn Linux® and Solaris, set LD_LIBRARY_PATH.
    • HP-UX platformOn HP-UX, set SHLIB_PATH.
    • AIX platformOn AIX®, set LIBPATH.

    Updates to the library search path are not required for other supported databases.

  8. If you are using an IBM Db2 database instance that is installed on AIX, a single process can make a maximum of 10 connections that use shared memory to an IBM Db2 database. Use TCP/IP mode to connect to the database instance; see IBM Db2 error message SQL1224N is issued when you connect to IBM Db2.
  9. Edit the final stanza in the odbc.ini file, the [ODBC] stanza, to specify the location of the installed DataDirect ODBC Drivers.
    To ensure that you edit the correct odbc.ini file, you can open the file in the vi text editor by using the following command:
    vi $ODBCINI
    1. In InstallDir, add the IBM Integration Bus installation location to complete the fully qualified path to the ODBC directory.
      If you do not specify this value correctly, the ODBC definition does not work.
    2. For InstallDir, ensure that the path points to the ODBC directory in the IBM Integration Bus installation location.
      If this value is not specified correctly, the ODBC definition does not work.
    3. Accept the default values shown in the sample odbc.ini file for all the other entries in the stanza.
      For example, on AIX:
      ;##########################################
;###### Mandatory information stanza ######
;##########################################

[ODBC]
InstallDir=/usr/opt/IBM/mqsi/10.0.0.2/server/ODBC/drivers
UseCursorLib=0
IANAAppCodePage=4
UNICODE=UTF-8
  10. Edit the first stanza in the odbc.ini file, the [ODBC Data Sources] stanza, to list the DSN of each database.
    For example:
    ;##########################################
;###### List of data sources stanza #######
;##########################################
[ODBC Data Sources]
DB2DB=IBM DB2 ODBC Driver
ORACLEDB=DataDirect ODBC Oracle Wire Protocol
ORACLERACDB=DataDirect ODBC Oracle RAC Wire Protocol
ORACLESSLDB=DataDirect ODBC Oracle SSL Wire Protocol
SYBASEDB=DataDirect ODBC Sybase Wire Protocol
SYBASEDBUTF8=DataDirect ODBC Sybase UTF8 Wire Protocol
SQLSERVERDB=DataDirect ODBC SQL Server Wire Protocol
INFORMIXDB=IBM Informix ODBC Driver
SOLIDDB_DB=IBM Solid DB ODBC Driver
DB2ISERIES=IBM i Access for Linux 64-bit ODBC Driver

    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 with an Oracle database, do not use this option because it might cause data integrity problems.

  11. For each database that you listed in the [ODBC Data Sources] stanza, within the odbc.ini file, create a data source stanza in the odbc.ini file. The entries in the stanza depend on the database manager.
    For an IBM Db2 database instance:
    1. In Driver, add the full path of 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 IBM Db2 alias. The data source name must be the same as the database alias name. If you are using a remote IBM Db2 database, you must set up your client/server connection to resolve this alias to the correct database. For more information, see the IBM Db2 documentation.

      If the requirement is to have multiple stanzas that refer to the same IBM Db2 database, aliases must be created in IBM Db2 by using the IBM Db2 CATALOG command. These aliases can then have their own stanza in the ODBCINI file.

      The ODBCINI file cannot be used to set up aliases for IBM Db2.

      For example, on AIX:

      ;# DB2 stanza
[MYDB2DB]
DRIVER=/opt/IBM/db2/V11.1/lib64/db2o.o
Description=IBM DB2 ODBC Database
Database=MYDB2DB

      On UNIX and Linux systems:

      ;# DB2 stanza
[MYDB2DB]
DRIVER=/opt/IBM/db2/V11.1/lib64/libdb2o.so
Description=IBM DB2 ODBC Database
Database=MYDB2DB
    For an IBM Db2 database instance on an iSeries system accessed from Linux x64:
    ;# DB2 on iSeries
[DB2ISERIES]
Driver=<Your i access client install directory>/lib64/libcwbodbc.so
Description=IBM i Access for Linux 64-bit ODBC Driver
System=<Your i Series system name>
Naming=0
DefaultLibraries=QGPL
Database=<Your Database Name or leave blank to use the user-profile's default setting for database>
ConnectionType=0
CommitMode=2
ExtendedDynamic=0
DefaultPkgLibrary=QGPL
DefaultPackage=A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression=1
LibraryView=0
AllowUnsupportedChar=0
ForceTranslation=0
Trace=0
    For an Oracle database:
    For all platforms:
    1. For Driver, ensure that the path points to the driver file in the IBM Integration Bus installation location, as shown in the following example.
    2. In Description, type a meaningful description of the database. This field is for information only and does not affect the connection.
    3. In HostName, type the name or IP address of the machine that is hosting your Oracle system.
    4. In PortNumber, type the number of the port on which your Oracle server is listening on the machine you specified in HostName.
    5. In ServiceName, type the Oracle service name that you want to connect to on the system you specified in HostName.
    6. If you are using TIMESTAMP WITH TIMEZONE columns, uncomment the EnableTimestampwithTimezone setting.
    7. Accept the default values shown in the sample odbc.ini file for all the other entries in the stanza.
      For example, on AIX:
      ;# Oracle stanza
[MYORACLEDB]
Driver=/usr/opt/IBM/mqsi/10.0.0.2/server/ODBC/drivers/lib/UKora95.so
Description=DataDirect ODBC Oracle Wire Protocol
HostName=my-machine.hursley.ibm.com
PortNumber=1521
ServiceName=my-oracle-service
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=0
EnableNcharSupport=0
;# Un-comment the next setting if you wish to use Oracle TIMESTAMP WITH TIMEZONE columns
;# EnableTimestampwithTimezone=1
    For an Oracle database that uses Real Application Clusters:
    For all platforms:
    1. For Driver, ensure that the path points to the driver file in the IBM Integration Bus installation location, as shown in the following example.
    2. In Description, type a meaningful description of the database. This field is for information only and does not affect the connection.
    3. In HostName, type the name or IP address of the machine that is hosting your primary (preferred) Oracle instance.
    4. In PortNumber, type the number of the port on which your Oracle server is listening on the machine you specified in HostName.
    5. In ServiceName, type the Oracle Real Application Cluster service name that you want to connect to on the system you specified in HostName.
    6. If you are using TIMESTAMP WITH TIMEZONE columns, uncomment the EnableTimestampwithTimezone setting.
    7. In AlternateServers, provide a list of alternative locations for this service for situations when the primary location, which is defined in HostName, is unavailable. Each location specification consists of three parts, which are separated by colons. Enter these values as one continuous string; the text in this example has been split to improve readability. 
      HostName=<Alternative host name>
:PortNumber=<Oracle listner port on alternative server>
:ServiceName=<Service name on the alternative server>
      If you want to specify more than one AlternateServer, separate each additional location specification with a comma. Whenever a new database connection is required, for example after an Oracle instance failover, the primary location will be tried first. However, if the primary location is unavailable, the driver will try the list of alternative locations in turn.
    8. Accept the default values shown in the sample odbc.ini file for all the other entries in the stanza.
      For example, on AIX:
      ;# Oracle Real Application Clusters stanza
[MYORACLERACDB]
Driver=/usr/opt/IBM/mqsi/10.0.0.2/server/ODBC/drivers/lib/UKora95.so
Description=DataDirect ODBC Oracle RAC Wire Protocol
HostName=my-primary-machine.hursley.ibm.com
PortNumber=1521
ServiceName=my-oracle-rac-service
;#This shows one alternate server definition. Add extra ones using a ',' to seperate each definition.
AlternateServers=(HostName=my-first-backup-machine.hursley.ibm.com:PortNumber=1521:ServiceName=my-oracle-rac-first-backup-service,HostName=my-second-backup-machine.hursley.ibm.com:PortNumber=1521:ServiceName=my-oracle-rac-second-backup-service)
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=0
EnableNcharSupport=0
;# Un-comment the next setting if you wish to use Oracle TIMESTAMP WITH TIMEZONE columns
;# EnableTimestampwithTimezone=1
    For an Oracle database that uses Secure Socket Layer (SSL):
    For all platforms:
    1. For Driver, ensure that the path points to the driver file in the IBM Integration Bus installation location, as shown in the following example.
    2. In Description, type a meaningful description of the database. This field is for information only and does not affect the connection.
    3. In HostName, type the name or IP address of the machine that is hosting your primary (preferred) Oracle instance.
    4. In PortNumber, type the number of the port on which your Oracle server is listening for SSL connections on the machine you specified in HostName.
    5. In ServiceName, type the Oracle SSL service name that you want to connect to on the system you specified in HostName.
    6. If you are using TIMESTAMP WITH TIMEZONE columns, uncomment the EnableTimestampwithTimezone setting.
    7. In KeyPassword, type your SSL key password.
    8. In KeyStore, type the fully qualified name of your SSL key store.
    9. In KeyStorePassword, type your SSL key store password.
    10. In TrustStore, type the fully qualified name of your SSL trust store.
    11. In TrustStorePassword, type your SSL trust store password.
    12. In EncryptionMethod, type the method to use to encrypt data sent between the driver and the database server. Valid values are as follows:
      • 0 No encryption. This value is the default.
      • 1 SSL. If the server supports protocol negotiation, the driver and server negotiate the use of the SSL protocols specified in the CryptoProtocolVersion connection option.
      • 3 SSL3.
      • 4 SSL2.
      • 5 TLS1.
    13. If EncryptionMethod has been set to 1, use CryptoProtocolVersion to specify a comma-separated list of the cryptographic protocols to use. Valid protocols are TLSv1.2, TLSv1.1, TLSv1, SSLv3 and SSLv2. When multiple protocols are specified, the driver uses the highest version supported by the server. The default value is TLSv1.2, TLSv1.1, TLSv1.
    14. In ValidateServerCertificate, type 1 to enable validation of the certificate that is sent by the database server when SSL encryption is enabled.
    15. Accept the default values shown in the sample odbc.ini file for all the other entries in the stanza.
      For example, on AIX:
      ;# Oracle using SSL stanza
[MYORACLESSLDB]
Driver=/usr/opt/IBM/mqsi/10.0.0.0/server/ODBC/drivers/lib/UKora95.so
Description=DataDirect ODBC Oracle Wire Protocol
HostName=my-machine.hursley.ibm.com
PortNumber=2484
ServiceName=my-oracle-ssl-service
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=0
AuthenticationMethod=1
KeyPassword=my-password
KeyStore=/Development/ssl/my-store.p12
KeyStorePassword=my-password
TrustStore=/Development/ssl/my-store.p12
TrustStorePassword=my-password
EncryptionMethod=1
CryptoProtocolVersion=TLSv1.2
ValidateServerCertificate=1
EnableNcharSupport=0
;# Un-comment the next setting if you wish to use Oracle TIMESTAMP WITH TIMEZONE columns
;# EnableTimestampwithTimezone=1
    For an Oracle database that uses Advanced Security (OAS):
    For all platforms:
    1. For Driver, ensure that the path points to the driver file in the IBM Integration Bus installation location, as shown in the following example.
    2. In Description, type a meaningful description of the database. This field is for information only and does not affect the connection.
    3. In HostName, type the name or IP address of the machine that is hosting your Oracle system.
    4. In PortNumber, type the number of the port on which your Oracle server is listening on the machine you specified in HostName.
    5. In ServiceName, type the Oracle service name that you want to connect to on the system you specified in HostName.
    6. If you are using TIMESTAMP WITH TIMEZONE columns, uncomment the EnableTimestampwithTimezone setting.
    7. In EncryptionLevel enter the level of encryption you are using. Choose one value 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.
    8. In DataIntegrityLevel, choose one value 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.
    9. Accept the default values shown in the sample odbc.ini file for all the other entries in the stanza.

      For example, on AIX:

      ;# Oracle using SSL stanza
[MYORACLEOASDB]
Driver=/usr/opt/IBM/mqsi/10.0.0.2/server/ODBC/drivers/lib/UKora95.so
Description=DataDirect ODBC Oracle Wire Protocol
HostName=my-machine.hursley.ibm.com
PortNumber=1586
ServiceName=my-oracle-oas-service
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=0
EncryptionTypes=AES128,AES192,AES256,RC4_40,RC4_56,RC4_128,RC4_256,DES,3DES112,3DES168
EncryptionLevel=3
DataIntegrityTypes=SHA1,MD5
DataIntegrityLevel=3
EnableNcharSupport=0
;# Un-comment the next setting if you wish to use Oracle TIMESTAMP WITH TIMEZONE columns
;# EnableTimestampwithTimezone=1
    Optional Oracle database connection properties:
    KeepAlive
    This property specifies whether the driver enables TCP keepalive. TCP keepalive maintains idle TCP connections by periodically passing packets between the client and server. If either the client or server does not respond to a packet, the connection is considered inactive and is terminated. In addition, TCP keepalive prevents valid idle connections from being disconnected by firewalls and proxies by maintaining network activity. Valid values are 0, which is the default, and means that this function is disabled, or 1, which means that this function is enabled.
    LDAPDistinguishedName
    This property specifies the distinguished name for the LDAP entry that contains your database connection information. Using an LDAP entry simplifies maintenance because it allows you to centrally store and access connection information. LDAP entries specify the hostname, port number, and service name or SID for the target database.
    For example:
    LDAPDistinguishedName=cn=MYORACLEDSN,cn=OracleContext,dc=acme,dc=com

    This property is mutually exclusive with the ServiceName property. If you specify a value for LDAPDistinguishedName, the HostName and PortNumber options are used to specify the hostname and port number for the LDAP directory server.

    QueryTimeout

    This property specifies the number of seconds for the default query timeout for all SQL statements that are created by a connection. The default is 0, meaning that the query does not time out.

    For a Sybase database:
    For all platforms except Linux on Z:
    1. For Driver, ensure that the path points to the driver file in the IBM Integration Bus installation location, as shown in the following example.
    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 that is defined by your system administrator for each user.
    4. 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: 
      <Your Sybase server name or IP address>,<Your Sybase port number>

      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.

    5. Accept the default values shown in the sample odbc.ini file for all the other entries in the stanza.
      For example, on AIX:
      ;# Sybase Stanza
[MYSYBASEDB]
Driver=/usr/opt/IBM/mqsi/10.0.0.2/server/ODBC/drivers/lib/UKase95.so
Description=DataDirect ODBC Sybase Wire Protocol
Database=SYBASEDB1
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
SelectMethod=0
NetworkAddress=my-machine.hursley.ibm.com:4100
SelectUserName=1
ColumnSizeAsCharacter=1
EnableSPColumnTypes=2
LoginTimeout=0
TimestampTruncationBehavior=1
XAConnOptBehavior=3
      If you want to use a UNICODE UTF8 Sybase data source, add the following line to the end of your Sybase stanza:
      Charset=UTF8
    For remote access to an SQL Server database
    For all platforms:
    1. In Driver, add the IBM Integration Bus 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 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 that is defined by your system administrator for each user.
    4. In HostName, type the name or IP address of the server to which you want to connect.
    5. In PortNumber, type the number of the port of the server listener.
    6. To specify a named instance of SQL Server, replace the HostName and PortNumber lines with HostName=Your SQLServer Machine Name\Your SQLServer Instance Name
    7. Accept the default values shown in the sample odbc.ini file for all the other entries in the stanza.
      For example, on AIX:
      ;# UNIX to SQLServer stanza
[MYSQLSERVERDB]
Driver=/usr/opt/IBM/mqsi/10.0.0.2/server/ODBC/drivers/lib/UKsqls95.so
Description=DataDirect ODBC SQL Server Wire Protocol
Database=SQLSERVERDB
HostName=my-machine.hursley.ibm.com
PortNumber=1433
AnsiNPW=1
LoginTimeout=0
QueryTimeout=0
;# To specify a named instance of SQL Server replace the HostName and PortNumber lines with
;# HostName=<Your SQLServer Machine Name>\<Your SQLServer Instance Name>
;# To use Integrated Windows Authentication, reinstate the following line:
;# AuthenticationMethod=9
;# Domain=<Your Windows Domain Name>
    8. If you want to use Integrated Windows Authentication (IWA) for access to the remote SQL Server database, reinstate and complete the lines at the end of the stanza.
    For an Informix database:
    1. In Driver, add the full path of your Informix Client library.
    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 to which you want to connect by default. If you do not specify a value, the default value is the database that is defined by your system administrator for each user.
      For example, on AIX:
      ;# Informix Stanza
[MYINFORMIXDB]
Driver=/opt/IBM/informix/lib/cli/iclit09b.so
Description=IBM Informix ODBC Database
ServerName=my-machine
Database=MYDB
    For a solidDB database:
    For all platforms except Linux on POWER® and Linux on Z:

    Client side

    odbc.ini file

    1. In Driver, add the full path of your solidDB Client library.
    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 that is defined by your system administrator for each user.
      For example, on AIX:
      ;# SolidDB Stanza
[SOLID_DB]
Driver=/opt/solidDB/bin/soca5x6465.so
Description=IBM Solid DB ODBC database
Database=SOLIDDB_DB

      Note: all additional information is ignored.

    solid.ini file

    1. This configuration file is located in the directory that is referenced by the environment variable SOLIDDIR.
    2. The solid.ini mapping is from the data source name (as defined in ODBCINI) to the solidDB connection string.
    3. The connection string takes the form <logical name of the driver> = <physical solidDB connect string>.
    4. The Physical connection string specifies the:
      • Protocol
      • Machine name or IP address
      • Port number to use
      For example, on AIX:
      [Data Sources] 
SOLIDDB_DB=tcp my_aix_system 1964

    Server side

    solid.ini file

    1. This configuration file is located in the installation directory for solidDB.
    2. Set the Data Source as for the Client side solid.ini file.
    3. Set Listen to where the listener for the server is located.
    4. Set CharPadding=yes and NumericPadding=yes to turn on padding.
      For example, on AIX:
      [Data Sources] 
SOLIDDB_DB=tcp my_aix_system 1964

[COM]
Listen=tcpip 1964

[SQL]
CharPadding=yes
NumericPadding=yes
  12. Ensure that you have edited all necessary parts of all of the relevant .ini files:
    • The [ODBC Data Source] stanza at the top of the odbc.ini file.
    • A stanza for each data source in the odbc.ini file.
    • The [ODBC] stanza at the end of the odbc.ini file.
    • Additionally, for solidDB, both the client and server-side solid.ini files.
    If you do not configure all parts correctly, the ODBC DSNs do not work and the integration node is unable to connect to the database.
  13. Optional: On AIX, database connect times can sometime take marginally longer than on other Linux and UNIX platforms due to the IBM Integration ODBC Database Extender searching for unicode conversion libraries that do not typically exist on AIX. You can set the following property in the [ODBC] stanza of the odbcinst.ini file to prevent this automatic search: 
    IconvEncoding=UCS-2

Results

You have now configured database connections on Linux and UNIX. You can check that the ODBC environment is configured correctly by running the mqsicvp command. For more information, see mqsicvp command.