PassThrough

It specifies which SQL statements are executed only in the cache database and which SQL statements are passed through to the Oracle database. For more information see Setting a Passthrough Level in the Oracle TimesTen In-Memory Database Cache Guide and CREATE CACHE GROUP in Oracle TimesTen In-Memory Database SQL Reference.

The execution of a prepared PassThrough command assumes that the schema of dependent objects in the Oracle database has not changed since the prepare. If the schema has changed the PassThrough command may cause unexpected results from the Oracle database.

When passing SQL statements through to the Oracle database, use only TimesTen supported data types in column definitions. If the specified data type is not supported in TimesTen, the passthrough statement fails.

For information on changing the isolation level on the Oracle database connection, when using this attribute, see Isolation.

Required Privilege

No privilege is required to change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PassThrough as follows.

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems odbc.ini file in TimesTen Classic	PassThrough	0 (default) - SQL statements are executed only on TimesTen. 1 - INSERT, UPDATE and DELETE statements are executed on TimesTen unless they reference one or more tables that are not in TimesTen. If they reference one or more tables not in TimesTen, they are passed through to the Oracle database. DDL statements are executed on TimesTen. Other statements are passed through to the Oracle database if they generate a syntax error in TimesTen or if one or more tables referenced within the statement are not in TimesTen. 2 - INSERT, UPDATE and DELETE statements performed on tables in read-only cache groups or user managed cache groups with the READONLY cache table attribute are passed through to the Oracle database. Passthrough behavior for other cache group types is the same as PassThrough=1. 3 - All statements are passed through to the Oracle database for execution.
Windows ODBC Data Source Administrator	Not applicable

Restrictions

Certain restrictions must be considered when using the passthrough feature. They include:

• If the PassThrough attribute is set so that a query must be executed in the Oracle database, the query is sent to the Oracle database without any changes. If the query uses a synonym for a table in a cache group, then a synonym with the same name must be defined for the corresponding Oracle database table for the query to be successful.

• In the case that a SQL statement that uses TimesTen only syntax is passed through to the Oracle database, TimesTen returns an error message that indicates the syntax is not supported in the Oracle database.

• Execution of a prepared passthrough command assumes that the schema of dependent objects in the Oracle database have not changed after the prepare. If the schema has changed, unexpected results can occur.

• TimesTen does not include a cache invalidation feature. TimesTen does not verify that the cached tables are up to date. When a query is syntactically correct in TimesTen and the cache contains all the tables referenced in the query, the query is executed in TimesTen regardless of whether the cache is up to date.

• The passthrough of Oracle INSERT, UPDATE, or DELETE operations depends on the setting of the PassThrough attribute as described in the table above. TimesTen Cache cannot detect INSERT, UPDATE and DELETE operations that are hidden in a trigger or stored procedure. Therefore, TimesTen cannot enforce the passthrough rule on hidden operations.

• You cannot pass PL/SQL blocks through to the Oracle database.

• The effects of a passthrough INSERT, UPDATE, or DELETE operation on a read-only cache group are only seen after the transaction is committed and after the next autorefresh operation is completed.

• There is no mechanism to detect or block updates on an Oracle database table that is cached in a TimesTen synchronous writethrough cache group. Whether the updates are made by statements passed through the cache or from other Oracle database applications, the changes are never reflected in TimesTen.

• Oracle Call Interface (OCI) does not support a mechanism to describe the binding type of the input parameters. Ensure that your application supplies the correct SQL types for passthrough statements. The ODBC driver converts the C and SQL types and presents the converted data and the SQL type code to TimesTen. TimesTen presents the information to OCI. The length of the input binding values is restricted to 4000 for LONG and LONG RAW types.

• At all passthrough levels, passthrough execution of DDL statements does not result in commits on the TimesTen side.

• A transaction that contains operations that are replicated with RETURN TWOSAFE cannot have a PassThrough setting greater than 0. If PassThrough is greater than 0, an error is returned and the transaction must be rolled back.

• When PassThrough is set to 0, 1, or 2, the following behavior occurs when a dynamic load condition exists:

  • A dynamic load can occur for a SELECT operation on cache tables in any dynamic cache group type.

  • A dynamic load for an INSERT, UPDATE, or DELETE operation can only occur on cached tables with dynamic asynchronous or synchronous writethrough cache groups.

Refer to SQL Statements in Oracle TimesTen In-Memory Database SQL Reference for details about the INSERT, UPDATE, DELETE, and SELECT statements.