A layer in the solidDB® server called SQL passthrough mediator is responsible for handling the passthrough of SQL statements to the backend, according to a chosen passthrough mode. The SQL passthrough mode can be changed dynamically at runtime. The access to the backend server is facilitated with a backend ODBC driver that is linked with the solidDB® server. The login data (username and password) for the backend is transferred through the IBM InfoSphere CDC components.
SQL passthrough architecture
Passthrough modes
The passthrough mode defines how read and write statements are be passed to the backend. The passthrough mode is set separately for read statements (SELECT) and write statements (non-read statements including INSERT, UPDATE, DELETE). Three passthrough modes are available:
▪FORCE: all read or write statements are passed to the backend.
▪NONE (default): read or write statements are never passed to the backend.
▪CONDITIONAL: if the statement results in, for example, a missing table error or a syntax error, the statement is passed to the back end.
The conditional passthrough mode is based on a logic utilizing error messages:
▪A missing table error or a syntax error invokes passthrough (prepare phase).
If the passthrough mode is changed after a statement has been prepared, it is re-prepared at a new location if needed.
▪Privilege violation errors do not invoke passthrough.
▪The errors that occur in the execution phase do not invoke passthrough. For example, if a write statement fails on an integrity constraint violation, it is not passed through.
Additionally, complexity of the SQL statements can be used for defining that long-running statements are always passed through to the backend.
Note SQL passthrough does not use any information about the data ranges or population of the frontend and backend databases. Specifically, if a query executes successfully in the frontend but returns no data (or little data), it is not redirected to the backend even though the data might be there.
Transactions and isolation levels
The transaction model for SQL passthrough is designed to preserve consistency of the backend database; the backend transactions can be made to meet the highest isolation levels (REPEATABLE READ or SERIALIZABLE). To preserve consistency of the backend database when using SQL passthrough, the isolation level of the frontend is set to the same (or similar) or higher level than in the backend.
In general, individual transactions execute and commit fully either in the frontend or backend. As local transactions, they preserve the database consistency, given the limitations of the desired isolation levels. However, consecutive transactions can be temporally mutually inconsistent because of the delay induced by the asynchronous replication from the backend to the frontend. For example, a transaction may not see the replicated results of the previous transaction, if the latter executed a passed-through write operation on the backend database.
Only whole statements are passed through, no statement can span both the frontend and back end. This means that distributed queries are not possible.
In certain cases, a transaction reads from either the frontend or the backend database and writes to the other one. Such a transaction may be considered to be composed of two sub-transactions. In such cases, the transaction is committed only if the write subtransaction is committed successfully. If the commit of the write subtransaction fails, the overall transaction fails also.
The execution of transactions depends on the isolation level:
▪Execution rules at READ COMMITTED isolation level
– A transaction can always read from the backend regardless of where it writes to.
– A transaction can always read from the frontend regardless of where it writes to.
– A transaction can write only either to the frontend or the back end.
▪Execution rules at the REPEATABLE READ (or higher) isolation level
– A transaction can always read from backend regardless of where it writes to.
– If a transaction reads from the frontend, it must also write to the frontend.
– A transaction can only write either to the frontend or backend.
If a transaction violates any of the above rules, solidDB® returns error 13455 at the return of the violating statement.
Accessing data in the backend
The connection between the frontend and backend is made with a backend ODBC driver which is installed on the solidDB® node and loaded dynamically in solidDB® server. solidDB® server uses this driver to execute the passthrough statements directly in the backend data server.
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.
