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

See the sample file contents in Sample IBM Integration Bus ODBC configuration files.

See the sample file contents in Sample IBM Integration Bus ODBC configuration files.

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

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

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. 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/9.0/ODBC/V7.0
   UseCursorLib=0
   IANAAppCodePage=4
   UNICODE=UTF-8

;##########################################
;###### List of data sources stanza #######
;##########################################
[ODBC Data Sources]
DB2DB=IBM DB2 ODBC Driver
ORACLEDB=DataDirect 7.1 ODBC Oracle Wire Protocol
ORACLERACDB=DataDirect 7.1 ODBC Oracle RAC Wire Protocol
ORACLESSLDB=DataDirect 7.1 ODBC Oracle SSL Wire Protocol
SYBASEDB=DataDirect 7.1 ODBC Sybase Wire Protocol
SYBASEDBUTF8=DataDirect 7.1 ODBC Sybase UTF8 Wire Protocol
SQLSERVERDB=DataDirect 7.1 ODBC SQL Server Legacy Wire Protocol
SQLSERVERWP=DataDirect 7.1 ODBC SQL Server Wire Protocol
INFORMIXDB=IBM Informix ODBC Driver
SOLIDDB_DB=IBM Solid DB ODBC Driver
IDSDB=IBM Informix Data Server Driver for ODBC and CLI

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.

For a DB2 database instance:

For Linux on x86:

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 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.

   If the requirement is to have multiple stanzas that refer to the same DB2 database, aliases must be created in DB2 by using the 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 DB2.

   For more information, see the DB2 documentation.

   For example:

   # DB2 stanza
   [MYDB2DB]
   Driver=/opt/IBM/db2/V9.7/lib32/libdb2.so
   Description=IBM DB2 ODBC Database
   Database=MYDB2DB

For all other platforms:

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 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.

   If the requirement is to have multiple stanzas that refer to the same DB2 database, aliases must be created in DB2 by using the 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 DB2.

   For example, on AIX:

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

For an Oracle 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 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. 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/9.0/ODBC/V7.0/lib/UKora24.so
   Description=DataDirect ODBC 7.1 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

For an Oracle database that uses Real Application Clusters:

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 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. 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.
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 Real Application Clusters stanza
   [MYORACLERACDB]
   Driver=/usr/opt/IBM/mqsi/9.0/ODBC/V7.0/lib/UKora24.so
   Description=DataDirect 7.1 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

For an Oracle database that uses Secure Socket Layer (SSL):

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 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. In KeyPassword, type your SSL key password.
7. In KeyStore, type the fully qualified name of your SSL key store.
8. In KeyStorePassword, type your SSL key store password.
9. In TrustStore, type the fully qualified name of your SSL trust store.
10. In TrustStorePassword, type your SSL trust store password.
11. 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 TLS v1, SSL v3, or SSL v2 in that order
    • 3 SSL3.
    • 4 SSL2.
    • 5 TLS1.
12. In ValidateServerCertificate, type 1 to enable validation of the certificate that is sent by the database server when SSL encryption is enabled.
13. 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/9.0/ODBC/V7.0/lib/UKora26.so
    Description=DataDirect 7.1 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
    EncryptionMethod=1
    KeyPassword=my-password
    KeyStore=/Development/ssl/my-store.p12
    KeyStorePassword=my-password
    TrustStore=/Development/ssl/my-store.p12
    TrustStorePassword=my-password
    ValidateServerCertificate=1

For an Oracle database that uses Advanced Security (OAS):

For all platforms:

1. In 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. In EncryptionLevel, type the level of encryption that you are using. Choose one value from the following options:
   • 0 Rejected. If rejected, or if no match is found between the driver and server encryption types, then 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.
7. 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.
8. In EncryptionType, specify a comma-separated list of the encryption algorithms to use. Valid values are AES128,AES192,AES256,RC4_40,RC4_56,RC4_128,RC4_256,DES,3DES112,3DES168
9. In DataIntegrityTypes, specify the method that the driver uses to protect against attacks that intercept and modify data that is being transmitted between the client and server. Valid values are SHA1, MD5, or both.
   For example, on AIX:

   ;# Oracle using SSL stanza
   [MYORACLEOASDB]
   Driver=/usr/opt/IBM/mqsi/9.0/ODBC/V7.0/lib/UKora26.so
   Description=DataDirect 7.1 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

For a Sybase database:

For all platforms except Linux on IBM z Systems:

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 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/9.0/ODBC/V7.0/lib/UKase24.so
   Description=DataDirect 7.1 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 a SQL Server database

Two ODBC drivers are provided for connectivity to a SQL Server database:

1. DataDirect SQL Server (Native) Wire Protocol driver
2. DataDirect SQL Server Legacy Wire Protocol driver

The DataDirect SQL Server (Native) Wire Protocol driver uses the SQL Server 2008 TDS protocol and provides support for new features and data types.

To configure a data source stanza to use the DataDirect SQL Server (Native) Wire Protocol driver, complete the following actions:

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. 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 using the SQL Server Wire Protocol driver
   [SQLSERVERWP]
   Driver=/usr/opt/IBM/mqsi/9.0/ODBC/V7.0/lib/UKsqls26.so
   Description=DataDirect 7.1 SQL Server Wire Protocol
   Database=<Your Database Name>
   HostName=<Your SQLServer Machine Name>
   PortNumber=<Your SQLServer Port Number>
   AnsiNPW=1
   LoginTimeout=0
   QueryTimeout=0

   SQL Server driver UKsqls26.so was introduced with IBM Integration Bus V9.0.0.3. If you are at an earlier version than V9.0.0.3, SQL Server driver UKmsss26.so should be used.

To configure a data source stanza to use the DataDirect SQL Server Legacy Wire Protocol driver:

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.
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, either:
   1. Specify an IP address or server name and a port number to locate the SQLServer database you want to connect to, as follows:

      <Your SQLServer machine name or IP address>,<Your SQLServer port number>

      or
   2. Specify an IP address or server name and an instance name to locate the SQLServer database you want to connect to, as follows:

      <Your SQLServer machine name or IP address>\<Your SQLServer instance name>

      If your instance name is blank, specify <Your SQLServer machine name or IP address>
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.
5. 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/9.0/ODBC/V7.0/lib/UKmsss24.so
   Description=DataDirect 7.1 ODBC SQL Server Wire Protocol
   Address=my-machine.hursley.ibm.com:1433
   AnsiNPW=Yes
   Database=MYSQLSERVERDB
   QuotedId=No
   ColumnSizeAsCharacter=1

For an Informix database

1. In Driver:
   • If you are using the Informix Client SDK 4.10, add the full path of your Informix Client library: Driver=/full_path_of_your_Informix_Client_installation/lib/cli/iclit09b.so
   • If you are using an older client SDK, use Driver=libinfWrapper.so
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.

   Examples:

   ;# Informix Stanza for Informix Client SDK 4.10
   [MYINFORMIXDB]
   Driver=/Informix/client/lib/cli/iclit09b.so
   Description=IBM Informix ODBC Database
   ServerName=my-machine
   Database=MYDB

   ;# Informix Stanza for Informix Client SDK 3.70 or earlier
   [MYINFORMIXDB]
   Driver=libinfWrapper.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 IBM z Systems:

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

• 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.

IconvEncoding=UCS-2