Installing and configuring backend ODBC drivers for SQL passthrough
To use the SQL passthrough functionality, you must install and configure the backend ODBC driver on the solidDB® frontend node. You can link to the driver directly (using a dynamic driver library) or using a driver manager.
Before you begin
Locate the ODBC driver installation package as well as installation and configuration instructions for your backend data server.
▪If your backend data server is an IBM data server, use the” IBM Data Server Driver for ODBC and CLI” provided with the solidDB® Universal Cache installation images.
▪If your backend data server is not an IBM data server, use the native ODBC driver provided with your backend data server.
Procedure
1 Install the backend ODBC driver (client) on the solidDB® node.
2 If your backend data server is an IBM data server, proceed as follows.
a Copy the compressed file that contains the “IBM Data Server Driver for ODBC and CLI” onto the solidDB® node from the installation image.
b Uncompress that file into your chosen install directory on the solidDB® node.
c Optional: remove the compressed file.
d If your frontend solidDB® data server is running on AIX:
3 Extract the shared library (/odbc_cli/clidriver/lib/libdb2.a) to yield shr_64.o on 64-bit operating systems. To avoid confusion, rename the file to libdb2.so.
Issue the following commands:
cd odbc_cli/clidriver/lib ar -x -X 64 libdb2.a mv shr_64.o libdb2.so
These steps are necessary on AIX because solidDB® will load the driver dynamically.
Important: When referencing the driver library on AIX systems, remember to use the correct file name (libdb2.so).
4 Set the DB2NOEXITLIST environment variable to ON. Issue the following command on the solidDB® node:
export DB2NOEXITLIST=ON
This environment variable ensures that upon shutdown, the driver does not attempt to free resources that have already been freed by solidDB®.
If your backend data server is not an IBM data server, follow the instructions provided with your backend data server.
5 Define the connection settings between the ODBC driver and the backend data server.
The backend ODBC driver for SQL passthrough is configured in the same way as you would configure a regular remote connection with your backend database. You can link to the driver directly or using a driver manager.
Direct linking: Depending on your backend data server and operating system, you may need to set environmental variables or other setup parameters to enable direct linking.
For details, see the examples below or the instructions that came with your backend data server.
Driver manager: Depending on your backend data server, operating system, and driver manager, you need to configure such settings as data source name, login data, performance options, or connection options.
For details, see the examples below or the instructions that came with your backend data server.
6 Define the connection settings between solidDB® server and the driver or driver manager by modifying the [Passthrough] section of the solid.ini configuration file.
The format of the parameter values depends on whether you link to the driver directly or using a driver manager.
Direct linking
Use RemoteServerDriverPathto set the driver path.
Use RemoteServerDSNto set the driver connect string.
The exact connect string depends on the driver.
Example: IBM Data Server Driver for CLI and ODBC with DB2 or IDS on Linux operating systems
7 Configure the ODBC driver's code page support according to the solidDB® database mode (Unicode or partial Unicode).
Unicode databases: If your solidDB® database mode is Unicode (General.InternalCharEncoding=UTF8), configure the ODBC driver to expect data from solidDB® in UTF-8 encoding.
The procedure for configuring UTF-8 support depends on the driver. For details, see the instructions that came with your backend data server.
For example, in DB2 for Linux, UNIX, and Windows environments, the UTF-8 support is configured by setting the environment variable DB2CODEPAGE to 1208. (The value 1208 is the identifier for the UTF-8 code page in DB2 environments.)
Partial Unicode databases: If your solidDB® database mode is partial Unicode (General.InternalCharEncoding=Raw) and your application and solidDB® environments use ASCII or Latin-1 encoding (Western languages), the backend ODBC driver is likely to handle the character translations correctly without setting any code page support in the ODBC driver explicitly.
For example, if you have installed IBM Data Server Driver for ODBC and CLI in a system using ASCII encoding, the installation has set the driver to use the system locale of the installation node automatically.
If your backend database uses encoding other that ASCII or Latin-1, set the backend ODBC driver to expect data from solidDB® in ASCII or Latin-1 encoding.
The procedure for configuring ASCII or Latin-1 support depends on the driver. For details, see the instructions that came with your backend data server.
Important: The conversion between the application encoding and the solidDB® server is handled by the solidDB® ODBC Driver or solidDB® JDBC Driver.
▪In C/ODBC environments, the code page conversions between the application and solidDB® are controlled with the server-side parameter Srv.ODBCDefaultCharBindingor the client-side parameter Client.ODBCCharBinding.
▪In Java/JDBC environments, no settings are needed. The code page conversions are handled by the solidDB® JDBC Driver automatically.
For more information about setting the parameters and solidDB® Unicode support in general, see “Using Unicode” in the solidDB® Programmer Guide.
8 If you backend data server is DB2, running on a 64-bit system, and you are using the IBM Data Server Driver for CLI and ODBC with direct linking, set the solidDB® parameter Passthrough.Force32bitODBCHandles to yes.
9 If your backend data server is DB2 for iSeries or DB2 for z/OS, set the IBM InfoSphere CDC for solidDB® system parameter retrieve_credentials to false.