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
- 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.
- Ensure that the odbc.ini file
is owned by the mqbrkrs group, and has 664 permissions.
- 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.
- 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.
- Ensure that the odbcinst.ini file
is owned by the mqm:mqbrkrs group and has the same
permissions as the supplied sample file.
- 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.
- If you are connecting to 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.
Updates to the library search path are not required
for other supported databases.
- If you are using a DB2 database
instance that is installed on AIX,
a single process can make a maximum of 10 connections that use shared
memory to a DB2 database. Use
TCP/IP mode to connect to the database instance; see DB2 error message SQL1224N is issued when you connect to DB2.
- 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
- 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.
- 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.
- 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
- Edit the first stanza in the odbc.ini file, the [ODBC Data Sources] stanza, to list the DSN
of each database.
For example, on
AIX:
;##########################################
;###### 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
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 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 a DB2 database instance:
-
- In Driver, add the full path of your DB2 installation.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- 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:
- For Driver, ensure that the path points to
the driver file in the IBM Integration Bus installation
location, as shown in the following example.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- In HostName, type the name or IP address
of the machine that is hosting your Oracle system.
- In PortNumber, type the number of the port
on which your Oracle server is listening on the machine you
specified in HostName.
- In ServiceName, type the Oracle service name
that you want to connect to on the system you specified in HostName.
- 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
- For an Oracle database that uses Real Application Clusters:
- For all platforms:
- For Driver, ensure that the path points to
the driver file in the IBM Integration Bus installation
location, as shown in the following example.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- In HostName, type the name or IP address
of the machine that is hosting your primary (preferred)
Oracle instance.
- In PortNumber, type the number of the port
on which your Oracle server is listening on the machine you
specified in HostName.
- In ServiceName, type the Oracle Real Application
Cluster service name that you want to connect to on the
system you specified in HostName.
- 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.
- 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
- For an Oracle database that uses Secure Socket Layer (SSL):
- For all platforms:
- For Driver, ensure that the path points to
the driver file in the IBM Integration Bus installation
location, as shown in the following example.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- In HostName, type the name or IP address
of the machine that is hosting your primary (preferred)
Oracle instance.
- 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.
- In ServiceName, type the Oracle SSL service
name that you want to connect to on the system you specified
in HostName.
- In KeyPassword, type your SSL key password.
- In KeyStore, type the fully qualified name
of your SSL key store.
- In KeyStorePassword, type your SSL key store
password.
- In TrustStore, type the fully qualified name
of your SSL trust store.
- In TrustStorePassword, type your SSL trust
store password.
- 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.
- In ValidateServerCertificate,
type 1 to enable validation of the certificate
that is sent by the database server when SSL encryption is enabled.
- 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/driversUKase95.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
ValidateServerCertificate=1
- For an Oracle database that uses Advanced Security (OAS):
- For all platforms:
- For Driver, ensure that the path points
to the driver file in the IBM Integration Bus installation
location, as shown in the following example.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- In HostName, type the name or IP address
of the machine that is hosting your Oracle system.
- In PortNumber, type the number of the port
on which your Oracle server is listening on the machine you specified
in HostName.
- In ServiceName, type the Oracle service
name that you want to connect to on the system you specified in HostName.
- 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.
- 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.
- 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
- For a Sybase database:
- For all platforms except Linux on IBM z Systems:
- For
Driver, ensure that the path points
to the driver file in the IBM Integration Bus installation
location, as shown in the following example.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- 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.
- 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.
- 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:
- For Driver, ensure that the path points to
the driver file in the IBM Integration Bus installation
location, as shown in the following example.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- In Address, either:
- 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
- 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>
- 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.
- 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
QuotedId=No
ColumnSizeAsCharacter=1
LoginTimeout=0
QueryTimeout=0
;# To use Integrated Windows Authentication, reinstate the following line:
;# AuthenticationMethod=9
- If you want to use Integrated Windows Authentication
(IWA) for access to the remote SQL Server database, reinstate
the AuthenticationMethod=9 line that is
in comments in the stanza.
- For an Informix database:
-
- In Driver, add the full path of your Informix Client library.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- In ServerName, type the name of the Informix IDS server.
- 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 IBM z Systems:
Client side
odbc.ini file
- In Driver, add the full path of your solidDB Client library.
- In Description, type a meaningful description
of the database. This field is for information only and
does not affect the connection.
- 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
- This configuration file is located in the directory that is referenced
by the environment variable SOLIDDIR.
- The solid.ini mapping
is from the data source name (as defined in ODBCINI) to the solidDB connection string.
- The connection string takes the form <logical name of the driver>
= <physical solidDB connect
string>.
- 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
- This configuration file is located in the installation directory
for solidDB.
- Set the Data Source as for the Client side solid.ini file.
- Set Listen to where the listener for the
server is located.
- 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
- 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.
- 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.