The configuration procedure for SQL passthrough depends on the backend and the type of ODBC connection you want to use.
Before you begin
Before enabling SQL passthrough, you should have your Universal Cache environment up and running.
1 Install the Universal Cache components.
2 Configure the frontend and backend IBM InfoSphere CDC instances.
3 Define at least one subscription with at least one table mapping from the frontend data server to the backend.
Procedure
1 Install and configure the backend ODBC driver for SQL passthrough.
▪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.
2 Configure the default SQL passthrough settings for your system.
For example, enable the SQL passthrough for your system using the Passthrough.PassthroughEnabled=yes parameter and define the default passthrough mode using the Passthrough.SqlPassthroughRead and Passthrough.SqlPassthroughWrite parameters.
Grant SQL passthrough rights to the appropriate users using the GRANT PASSTHROUGH statement.
Ensure login data for the backend data server is available.
3 Connect the solidDB® and backend datastores and start replication on a subscription where solidDB® database is the source datastore and the backend database is the target datastore.
4 Check that the solidDB® system table SYS_SERVER contains the correct login data.
▪In most cases, when mirroring or a refresh is started on the first subscription from solidDB® to the backend data server, the IBM InfoSphere CDC for solidDB® instance retrieves the login data from the backend IBM InfoSphere CDC instance and stores it in the solidDB® system table SYS_SERVER.
▪If the SYS_SERVER table contains incorrect or no login data, add or modify the login data manually.
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.
If your backend data server is an IBM data server, proceed as follows.
Copy the compressed file that contains the “IBM Data Server Driver for ODBC and CLI” onto the solidDB® node from the installation image.
Uncompress that file into your chosen install directory on the solidDB® node.
Optional: remove the compressed file.
If your frontend solidDB® data server is running on AIX: 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).
2 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.
3 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.
4 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
5 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.ODBCDefaultCharBinding or 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.
6 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.
7 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.
Example: Installing and configuring IBM Data Server Driver for Informix using direct linking
This example shows how to install and configure the IBM Data Server Driver for CLI and ODBC by linking to the dynamic driver library when the backend data server is IBM Informix Dynamic Server (IDS), V11.50 in Windows 32-bit operating systems.
1 Locate the installation package for IBM Data Server Driver for CLI and ODBC (ibm_data_server_driver_for_odbc_cli_win32_v97.zip) that contains the driver and copy it to an installation directory of your choice, for example, C:\solid.
The connect string must be given in double quotation marks, without any spaces between the first equal sign and the double quotation mark.
Example: Installing and configuring IBM Data Server Driver for DB2 using direct linking and UTF-8 support:
This example shows how to install and configure the IBM Data Server Driver for CLI and ODBC by linking to the dynamic driver library when the backend data server is DB2 V9.7 in Linux 32-bit operating systems. Additionally, the driver is configured to expect data from solidDB® in UTF-8 encoding.
Locate the installation package for IBM Data Server Driver for CLI and ODBC (ibm_data_server_driver_for_odbc_cli_32_linuxia32_v97.tar.gz) that contains the driver and copy it to an installation directory of your choice, for example, $HOME/solid.
Important: The connect string must be given in double quotation marks, without any spaces between the first equal sign and the double quotation mark.
4 Configure the driver to expect data from solidDB® in UTF-8 encoding.
▪Ensure that solidDB® database is a Unicode database (General.InternalCharEncoding=UTF8).
▪In C/ODBC environments, ensure that the solidDB® ODBC Driver is configured to handle code page conversions between the application and solidDB® for character data type columns.
For example, if the application uses UTF-8 encoding for character data types, the solidDB® ODBC Driver can be configured to expect character data types in UTF-8 encoding with the following parameter setting:
[Srv] ODBCDefaultCharBinding=utf8
For more information about the Srv.ODBCDefaultCharBinding parameter and solidDB® Unicode support in general, see “Using Unicode” in the solidDB® Programmer Guide.
▪Set a DB2-specific environment variable DB2CODEPAGE to 1208.
The value 1208 is the identifier for the UTF-8 code page in DB2 environments.
Example: Installing and configuring IBM Data Server Driver for DB2 using unixODBC DriverManager:
This example shows how to install and configure the IBM Data Server Driver for CLI and ODBC using unixODBC DriverManager when the backend data server is DB2 V9.7 in Linux 32-bit operating systems.
1 If not already installed, install the unixODBC DriverManager on the solidDB® node.
The unixODBC DriverManager is available for download at www.unixodbc.org.
Typically the unixODBC DriverManager installation path is /usr/lib/libodbc.so.
2 Locate the installation package for IBM Data Server Driver for ODBC and CLI for Linux 32-bit operating systems, V9.7 (ibm_data_server_driver_for_odbc_cli_32_linuxia32_v97.tar.gz) that contains the driver and copy it to an installation directory of your choice, for example, $HOME/solid.
3 Uncompress the installation package.
For example:
cd $HOME/solid/odbc_cli uncompress ibm_data_server_driver_for_odbc_cli_32_linuxia32_v97.tar.gz tar -xvf ibm_data_server_driver_for_odbc_cli_32_linuxia32_v97.tar
4 Set the DB2NOEXITLIST environment variable to ON.
export DB2NOEXITLIST=ON
5 In the unixODBC /etc/odbcinst.ini configuration file, define the driver path and name for the DB2 driver.
1 Enable SQL passthrough by setting the Passthrough.PassthroughEnabled parameter to yes (default is no).
Additionally, use the Passthrough.IgnoreOnDisabled parameter to set how the passthrough statements are handled if the passthrough is disabled PassthroughEnabled=no. If the value is ‘yes’ (default), all the statements related to passthrough (SET PASSTHROUGH ...) are ignored. If the value is no, an error is returned on any effort to execute those statements.
2 Set the default SQL passthrough mode.
▪Use Passthrough.SqlPassthroughRead parameter to set how read statements are passed from the solidDB® server to the backend.
▪Use Passthrough.SqlPassthroughWrite parameter to set how write statements are passed from the solidDB® server to the backend.
For both, the values None (default), Conditional, and Force are available.
Tip: You can override the default SQL passthrough mode using the SET PASSTHROUGH or SET TRANSACTION PASSTHROUGH commands or ODBC/JDBC connection settings. See Setting and modifying SQL passthrough mode for details.
3 Optional: Define the name and location of a file that maps native backend error codes to solidDB® error codes.
4 Create the mapping file.
The entries in the mapping file have the following format:
<backend_error><solidDB error> ; rest of the line is comment
For example:
; this file maps DB2 native errors to solidDB® native errors -207 13015 ; column not found -407 13110 ; NULL not allowed for non NULL column ; end of errormappings
For more examples on the mapping files, see the samples/sqlpassthrough directory in the solidDB® installation directory.
5 Define the mapping file name and location with the Passthrough.ErrorMapFileNameparameter.
If ErrorMapFileName is not defined or the error is not mapped, the native backend error codes are mapped to solidDB® error 13456 (Passthrough backend error: SQLState=<value>, NativeError=<backend error identifier>, MessageText=<backend error description>).
6 Optional: Define the complexity level of SQL statement at which the statement is always passed through to the back end.
Passthrough.ComplexNumTables – specifies the minimum number of tables in a complex statement. If a statement has less tables than specified with this parameter, the statement is not complex and it is not passed through to the backend.
Passthrough.ComplexNumNonindexedConstr – specifies the minimum number of non-indexed WHERE clause constraints in a complex statement. If a statement has less non-indexed constraints of the following type, the statement is not complex and it is not passed through to the backend: the WHERE clause constraint does not resolve with index, the index does not exist, or the optimizer chooses different index for constraint.
Passthrough.ComplexNumOrderedRows – specifies the minimum estimated number of rows which must be sorted in a complex statement. If a statement has less than the estimated number of sortable rows, the statement is not complex and it is not passed through to the backend.
Example
Windows 32-bit environment with driver manager (DB2):
You can set the login data for the backend manually using SQL statements.
About this task
In most cases, the IBM InfoSphere CDC technology is used for transferring backend login data to the frontend. When mirroring or a refresh is started on the first subscription from solidDB® server to the backend data server, the IBM InfoSphere CDC for solidDB® instance retrieves the login data from the backend IBM InfoSphere CDC instance and stores it in the solidDB® system table SYS_SERVER with the statement CREATE REMOTE SERVER. The password stored in the SYS_SERVER table is hidden.
The IBM InfoSphere CDC technology does not transfer the backend login data in the cases described below.
▪If the IBM InfoSphere CDC instance for the backend is running under a user ID that automatically has access to the database, the login data does not need to be stored.
▪If the backend data server is DB2 for z/OS or DB2 for iSeries, the login data cannot be fetched. To avoid errors, you need to set the IBM InfoSphere CDC for solidDB® system parameter retrieve_credentials to FALSE.
▪If you have upgraded your IBM InfoSphere CDC for solidDB® installation and subscription from V6.3, the 6.3 version of IBM InfoSphere CDC for solidDB® has not stored the backend login data in the SYS_SERVER table.
In the latter two cases, use the CREATE REMOTE SERVER (or ALTER REMOTE SERVER) statement to define the login data manually.
Creating login data
CREATE [OR REPLACE] REMOTE SERVER [USERNAME <username> PASSWORD <password>]
By default, username and password are stored in uppercase letters. To retain case sensitivity, enter the username and password in single quotation marks.
For example:
CREATE REMOTE SERVER USERNAME ’AdMin’ PASSWORD ’PwD123’
Deleting login data
DROP REMOTE SERVER
Modifying login data
ALTER REMOTE SERVER SET USERNAME | PASSWORD <value>