Overview

This article describes how to configure SQL passthrough between a solidDB V6.5 database in an AIX® environment and a DB2® v9.7 database in a Windows® environment. I'll discuss the various parameters necessary to configure SQL passthrough and provide an example configuration. The article also includes an example of using solidDB SQL Editor to demonstrate how, depending on the passthrough mode, queries are either passed through to the back end or executed in the solidDB front end.

Typically SQL passthrough is used with the solidDB Universal Cache system. However, this article describes SQL passthrough use without the interaction of the InfoSphere® Change Data Capture (CDC) components that are included in the solidDB Universal Cache setup.

Back to top

What is SQL passthrough?

SQL passthrough is a feature in solidDB that allows the application to access data in both the front end and back end databases using a single connection. The feature can be enabled by default using configuration parameters, or for a single session or transaction using solidDB administration commands. This article shows both ways of configuring SQL passthrough.

After you enable SQL passthrough, when an SQL query is executed from the application, solidDB tries to search the referred tables in the front-end solidDB database. If it is not able to find the tables, it passes the SQL query to the back-end database and returns the results accordingly. Either the whole of the SQL query is passed through or none of it is passed through. The query is never partially executed in one database or the other.

You can configure SQL passthrough for one of the following three behaviors:

• NONE: The SQL statements are never passed to the back end.
• FORCE: Irrespective of whether the tables are present in the front end, the SQL statements are always executed in the back-end database.
• CONDITIONAL: If the table is present in the front-end database, the SQL statements are executed in the front end. If the table is not present, the statements are passed to the back end.

Back to top

Key parameters and considerations for setting up SQL passthrough

Keep these considerations in mind when you're preparing to use SQL passthrough:

• SQL passthrough is always configured in the front-end solidDB server.
• Before configuring SQL passthrough, the IBM Data Server Driver for ODBC and CLI must be installed on the front end. You can use the IBM Data Server Driver for ODBC and CLI with or without a driver manager (direct linking). The examples in this article use direct linking.

Configuration parameters for SQL passthrough

All the passthrough-related parameters are in the [Passthrough] section of the solid.ini file.

• RemoteServerDriverPath: This parameter specifies the driver path for the back-end data server-specific ODBC driver. The ODBC driver is loaded when solidDB is used to executed the query in the back end.
• RemoteServerDSN: This parameter specifies the back-end connect string (direct linking) or data source name (driver manager). In this article connect string is used.
• Force32bitODBCHandles: This parameter is required in 64-bit environments when the back-end server is DB2 for Linux®, UNIX®, and Windows (DB2 LUW), and IBM Data Server Driver For ODBC And CLI is used with direct linking. This parameter will force the passthrough module to use 32-bit ODBC handles for the back-end ODBC driver.
• SqlpassthroughRead/SqlpassthroughWrite: These parameters specify the default passthrough mode for read and write statements. The possible values are NONE, CONDITIONAL and FORCE, as described in the previous section.

Note: The passthrough mode can be changed at runtime with the SET PASSTHROUGH and SET TRANSACTION PASSTHROUGH statements.

Back to top

System requirements

The examples illustrated in this article use the following software and levels.

Back to top

Setting up SQL passthrough between solidDB and DB2 Linux, Unix, and Windows

Setting up the front end (AIX 6.1 machine)

1. Create a user "solid" and login as the new user.
2. Install solidDB 6.5.0.2 FP2 as the user "solid" under

   /home/solid/solidDB6.5.0.2

   
   
3. Create a directory solid_db under /home/solid. The database files are going to be created under this directory.
4. Copy the license file (solid.lic) to /home/solid/solid_db
5. Go to /home/solid/solid_db. Create a solidDB database named MYSOLDB with user name "mysoluser" and password "mysolpwd". Use the following command:

   solid -f -x exit -Umysoluser -Pmysolpwd -CMYSOLDB

   
   
6. Stop the solidDB server using the following command.

   solcon -eshutdown MYSOLDB mysoluser mysolpwd

   
   
7. Create an empty solid.ini file and copy the following entries into the file.
   
   Listing 1. Entries for solid.ini file
   

   [General] DefaultStoreIsMemory=yes [IndexFile] Cachesize=100M [LogReader] LogReaderEnabled=yes [Logging] DurabilityLevel=1 [Com] Listen=tcp 21315 [Data Sources] SOLDB=tcp 21315

   
   
8. Start the solidDB server once again from /home/solid/solid_db using the following command.

   solid

   
   The solidDB server starts with the entries defined in the solid.ini file.
9. Install IBM Data Server Driver for ODBC and CLI under

   /home/solid/software/odbc_cli/

   
   
10. Extract the shared library (/home/solid/software/odbc_cli/clidriver/lib/libdb2.a) to yield shr_64.o needed on 64-bit operating systems. To avoid confusion, rename the file to libdb2.so. Use the commands shown below"

    cd /home/solid/software/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. Later on, the libdb2.so file name is used with the Passthrough.RemoteServerDriverPath parameter.
11. Set the DB2NOEXITLIST environment variable to ON.

    export DB2NOEXITLIST=ON

    
    

Setting up the back end (Windows 2008 Server machine)

1. Create a Windows user "db2admin".
2. Install DB2 V9.7.0 Fix Pack 1.
3. Create a DB2 database named SAMPLE with user "db2admin" and password "db2admin". The user needs to have rights to create, modify, and delete tables in the SAMPLE database.

Configuring SQL passthrough in the solidDB configuration file

Before configuring the SQL passthrough mode and enabling the feature, you need to define the connection between the ODBC driver and back-end database in the solid.ini configuration file. The connection can be created in two ways: by direct linking or by using the driver manager.

In this article direct linking is used. For the example below, the IP address of the DB2 data server node is 9.126.84.161 and the port 50000 is used.

The following steps show how to configure the connection between the front-end solidDB and back-end DB2 server.

1. Log in to the AIX machine and open the solid.ini file.
2. Add the following lines under the SQL passthrough section:
   
   
   Listing 2. Entries for solid.ini file on AIX
   

   [Passthrough] RemoteServerDriverPath=/home/solid/solid_db/odbc_cli/clidriver/lib/libdb2.so RemoteServerDSN="Driver={IBM DB2 ODBC Driver};Database=sample;Hostname=9.126.84.161; Port=50000;Protocol=TCPIP;" Force32bitODBCHandles=yes passthroughEnabled=yes SqlpassthroughRead=Conditional SqlpassthroughWrite=Conditional

   
   
3. Save the file and exit.

Back to top

A demonstration showing SQL passthrough between solidDB and DB2 LUW using solidDB SQL Editor (solsql)

Example: CONDITIONAL passthrough mode

In this example, the SQLpassthroughRead and SQLpassthroughWrite parameters are set to CONDITIONAL.

Before performing the next steps, you need to make sure that the DB2 server is up and running.

1. Connect to DB2 database and create the test table using the following commands:
   
   Listing 3. Creating the test table
   

   1) Start->Run->db2cmd 2) db2 "connect to sample user db2admin using db2admin" 3) db2 "create table testtab(name varchar(20), id int)" 4) db2 "insert into testtab values ('John', 435617), ('Sally', 564876), ('Larry', 987290)" 5) db2 "commit" 6) db2 "select * from testtab" NAME ID -------------------- ----------- John 435617 Sally 564876 Larry 987290

   
   
2. Stop the solidDB server using this command, if the server is already started. Otherwise you can ignore this step.

   solcon -eshutdown SOLDB mysoluser mysolpwd

   
   
3. On the front end, go to /home/solid/solid_db and start the solidDB server using the following command:

   solid

   
   
4. Start solidDB SQL editor and connect to the solidDB database using the following command:

   solsql SOLDB mysoluser mysolpwd

   
   
5. Create login data for the back-end using the following commands:

   create remote server MYSERVER username 'db2admin' password 'db2admin'; commit work;

   
   The CREATE REMOTE SERVER statement stores the login data for the back-end server in the SYS_SERVER table. You can query the contents of the SYS_SERVER table but you cannot modify the SYS_SERVER table directly. Also, the PWD column for the SYS_SERVER table shows NULL because the passwords are hidden.

   Tip: If the SYS_SERVER already contains login data, you will get the following error:

   SOLID Table Error 13460: Server <server_name> already exists

   
   

   In this case, clear the login data with the DROP REMOTE SERVER statement and recreate the login data:

   
   
   Listing 4. Clearing and recreating login data
   

   drop remote server; commit work; select * from sys_server; NAME DRIVER CONNECT UID PWD ---- ------ ------- --- --- 0 rows fetched. create remote server MYSERVER username 'db2admin' password 'db2admin'; commit work;

   
   
6. To verify that the passthrough feature with CONDITIONAL mode works, execute the SQL statements on solidDB as shown below.
   
   1. Query the testtab table.
      
      
      Listing 5. Testing conditional passthrough
      

      select * from testtab; NAME ID ---- -- John 435617 Sally 564876 Larry 987290 3 rows fetched.

      
      

      Result: Since the SQLpassthroughRead parameter is set to CONDITIONAL and the front end does not have the 'testtab' table, solidDB will pass through the SQL statement and fetch the data from the back-end DB2 database.

   2. Create a testtab table in the solidDB database using the following command:
      

      create table testtab(name varchar(20), id int); commit work;

      
      
   3. Add the following data into the testtab table.
      

      insert into testtab values('Amit', 663456); commit work;

      
      
   4. Query the testtab table again.
      

      select * from testtab; NAME ID ---- -- Amit 663456 1 rows fetched. commit work;

      
      

      Result: The SQL query fetches a single row that is present in the front end. This is because solidDB is able to find the table referred in the SQL statement in the front-end database.

Example: Changing SQL passthrough mode dynamically

The default SQL passthrough mode that is specified in the configuration file can be overridden per session or per transaction using the SET TRANSACTION PASSTHROUGH and SET PASSTHROUGH statements.

1. To set the passthrough for a single transaction, use the following commands:
   
   1. Set the read-level passthrough mode to FORCE for the next transaction.
      

      set transaction passthrough read force;

      
      
   2. Query the testtab table.
      

      select * from testtab; NAME ID ---- -- John 435617 Sally 564876 Larry 987290 3 rows fetched.

      
      

      Result: The data from the back-end testtab table is shown because the passthrough Read level is set to FORCE. FORCE means that irrespective of whether the testtab table is present in the front-end database, the application will always query data from the back-end database.

   3. Commit the transaction and issue the SQL query again.
      

      commit work; select * from testtab; NAME ID ---- -- Amit 663456 1 rows fetched.

      
      

      Result: The query now shows data from the front end since the the Read passthrough mode was set to FORCE for a single transaction only.

2. To set the passthrough for a single session, use the following commands:
   
   1. Set the read-level passthrough mode to FORCE for the session.
      

      set passthrough read force; commit work;

      
      
   2. Query the testtab table.
      

      select * from testtab; NAME ID ---- -- John 435617 Sally 564876 Larry 987290 3 rows fetched.

      
      
   3. Commit the transaction and issue the SQL query again.

      commit work; select * from testtab; NAME ID ---- -- John 435617 Sally 564876 Larry 987290 3 rows fetched.

      
      

      Result: Executing the query multiple times returns the data from the back end. This is because the read-level passthrough mode is set to FORCE for the whole session.

Back to top

Conclusion

This article has presented the basic steps and parameters required to successfully set up SQL passthrough. It has shown step-by-step how the various parameters can be used and how they affect SQL passthrough. The reader should be able to configure SQL passthrough successfully after going through this article.

Back to top

Acknowledgements

My sincere thanks to Jussi Vuorento for his technical review and to Katriina Vakkila for reviewing the contents of this article. This article would not have been possible without their support and encouragement.

Resources

Learn

• Learn more about solidDB in the IBM solidDB 6.5 and IBM solidDB Universal Cache 6.5 Information Center.
  
  
• Learn more about DB2in the IBM DB2 Database for Linux, UNIX, and Windows Information Center.
  
  

Get products and technologies

• Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
  
  

About the author

Amit Bhattacharya works in the DB2 Functional Verification Test team in IBM India Software Lab, Bangalore. He works in the area of SQL compatibility. Prior to joining IBM, he worked with Microsoft and Sybase. Amit likes exploring new products and setting up demos for client engagements. He has conducted solidDB training programs and has set up solidDB installations for proof-of-concept demonstrations.

Rate this article

Comments

Back to top

Table of contents

• Overview
• What is SQL passthrough?
• Key parameters and considerations for setting up SQL passthrough
• System requirements
• Setting up SQL passthrough between solidDB and DB2 Linux, Unix, and Windows
• A demonstration showing SQL passthrough between solidDB and DB2 LUW using solidDB SQL Editor (solsql)
• Conclusion
• Acknowledgements
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers