Overview of TimesTen XLA

XLA Basics

TimesTen XLA obtains update records directly from the transaction log buffer or transaction log files, so the records are available for as long as they are needed. The logging model also enables multiple readers to simultaneously read transaction log updates.

The ttXlaPersistOpen XLA function opens a connection to the database.

When initially created, TimesTen configures a transaction log handle for the same version as the TimesTen release to which the application is linked.

How XLA Reads Records From the Transaction Log

As applications modify a database, TimesTen generates transaction log records that describe the changes made to the data and other events such as transaction commits. New transaction log records are always written to the end of the log buffer as they are generated.

Transaction log records are periodically flushed in batches from the log buffer in memory to transaction log files on the file system. When XLA is initialized, the XLA application does not have to be concerned with which portions of the transaction log are on the file system or in memory. Therefore, the term "transaction log" as used in this chapter refers to the "virtual" source of transaction update records, regardless of whether those records are physically located in memory or on the file system.

Applications can use XLA to monitor the transaction log for changes to the database. XLA reads through the transaction log, filters the log records, and delivers to XLA applications a list of transaction records that contain the changes to the tables and columns of interest.

XLA sorts the records into discrete transactions. If multiple applications are updating the database simultaneously, transaction log records from the different applications are interleaved in the transaction log.

XLA transparently extracts all transaction log records associated with a particular transaction and delivers them in a contiguous list to the application.

Only the records for committed transactions are returned. They are returned in the order in which their final commit record appears in the transaction log. XLA filters out records associated with changes to the database that have not yet been committed.

If a change is made but then rolled back, XLA does not deliver the records for the canceled transaction to the application.

Most of these basic XLA concepts are demonstrated in the example that follows and summarized in the bulleted list following the example.

Consider the example transaction log illustrated in Figure 5-1.

Figure 5-1 Records Extracted From the Transaction Log

Description of "Figure 5-1 Records Extracted From the Transaction Log"

In this example, the transaction log contains the following records:

CT1 - Application C updates row 1 of table W with value 7.7.
BT1 - Application B updates row 3 of table X with value 2.
CT2 - Application C updates row 9 of table W with value 5.6.
BT2 - Application B updates row 2 of table Y with value "XYZ".
AT1 - Application A updates row 1 of table Z with value 3.
AT2 - Application A updates row 3 of table Z with value 4.
BT3 - Application B commits its transaction.
AT3 - Application A rolls back its transaction.
CT3 - Application C commits its transaction.

An XLA application that is set up to detect changes to tables W, Y, and Z would see the following:

BT2 and BT3 - Update row 2 of table Y with value "XYZ" and commit.
CT1 - Update row 1 of table W with value 7.7.
CT2 and CT3 - Update row 9 of table W with value 5.6 and commit.

This example demonstrates the following:

Transaction records of applications B and C all appear together.
Although the records for application C begin to appear in the transaction log before those for application B, the commit for application B (BT3) appears in the transaction log before the commit for application C (CT3). As a result, the records for application B are returned to the XLA application ahead of those for application C.
The application B update to table X (BT1) is not presented because XLA is not set up to detect changes to table X.
The application A updates to table Z (AT1 and AT2) are never presented because it did not commit and was rolled back (AT3).

About XLA and Materialized Views

You can use XLA to track changes to both tables and materialized views.

A materialized view provides a single source from which you can track changes to selected rows and columns in multiple detail tables. Without a materialized view, the XLA application would have to monitor and filter the update records from all of the detail tables, including records reflecting updates to rows and columns of no interest to the application.

In general, there are no operational differences between the XLA mechanisms used to track changes to a table or a materialized view.

For more information about materialized views, see the following:

CREATE MATERIALIZED VIEW in Oracle TimesTen In-Memory Database SQL Reference
Understanding Materialized Views in Oracle TimesTen In-Memory Database Operations Guide

About XLA Bookmarks

Each XLA reader uses XLA bookmarks to maintain its position in the log update stream.

These topics are covered:

XLA Log Record Identifiers

Each bookmark consists of two pointers that track update records in the transaction log by using log record identifiers.

An Initial Read log record identifier points to the most recently acknowledged transaction log record. Initial Read log record identifiers are stored in the database, so they are persistent across database connections, shutdowns, and failures.
A Current Read log record identifier points to the record currently being read from the transaction log.

Creating or Reusing a Bookmark

When you call the ttXlaPersistOpen function to initialize an XLA handle, you have a tag parameter to identify either a new bookmark or one that exists in the system, and an options parameter to specify whether it is a new non-replicated bookmark, a new replicated bookmark, or an existing (reused) bookmark.

See ttXlaPersistOpen and Initializing XLA and Obtaining an XLA Handle.

At this point, the Initial Read log record identifier associated with the bookmark is read from the database and cached in the XLA handle (ttXlaHandle_h). It designates the start position of the reader in the transaction log.

See ttLogHolds in Oracle TimesTen In-Memory Database Reference. That TimesTen built-in procedure returns information about transaction log holds.

How Bookmarks Work

When an application first initializes XLA and obtains an XLA handle, its Current Read log record identifier and Initial Read log record identifier both point to the last record written to the database.

Figure 5-2 Log Record Indicator Positions Upon Initializing an XLA Handle

Description of "Figure 5-2 Log Record Indicator Positions Upon Initializing an XLA Handle"

As described in Retrieving Update Records From the Transaction Log, use the ttXlaNextUpdate or ttXlaNextUpdateWait function to return a batch of records for committed transactions from the transaction log in the order in which they were committed. Each call to ttXlaNextUpdate resets the Current Read log record identifier of the bookmark to the last record read, as shown in Figure 5-3. The Current Read log record identifier marks the start position for the next call to ttXlaNextUpdate.

Figure 5-3 Records Retrieved by ttXlaNextUpdate

Description of "Figure 5-3 Records Retrieved by ttXlaNextUpdate"

You can use the ttXlaGetLSN and ttXlaSetLSN functions to reread records, as described in Changing the Location of a Bookmark. However, calling the ttXlaAcknowledge function permanently resets the Initial Read log record identifier of the bookmark to its Current Read log record identifier, as shown in Figure 5-4. After you have called the ttXlaAcknowledge function to reset the Initial Read log record identifier, all previously read transaction records are flagged for purging by TimesTen. Once the Initial Read log record identifier is reset, you cannot use ttXlaSetLSN to go back and reread any of the previously read transactions.

Figure 5-4 ttXlaAcknowledge Resets Bookmark

Description of "Figure 5-4 ttXlaAcknowledge Resets Bookmark"

Note:

A ttXlaAcknowledge call resets the bookmark even if there are no relevant update records to acknowledge. This may be useful in managing transaction log space, but should be balanced against the expense of the operation. Be aware that XLA purges transaction logs a file at a time. Refer to ttXlaAcknowledge for details on how the operation works.

The number of bookmarks created in a database is limited to 64. Each bookmark can be associated with only one active connection at a time. However, a bookmark over its lifetime may be associated with many connections. An application can open a connection, create a new bookmark, associate the bookmark with the connection, read a few records using the bookmark, disconnect from the database, reconnect to the database, create a new connection, associate this new connection with the bookmark, and continue reading transaction log records from where the old connection stopped.

Replicated Bookmarks

If you are using an active standby pair replication scheme, you have the option of using replicated bookmarks according to the options settings in your ttXlaPersistOpen calls.

See ttXlaPersistOpen.

For a replicated bookmark, operations on the bookmark are replicated to the standby database as appropriate. This results in more efficient recovery of your bookmark positions in the event of failover. Reading resumes from the stream of XLA records close to the point at which they left off before the switchover to the new active store. Without replicated bookmarks, reading must go through numerous duplicate records that were returned on the old active store.

To use replicated bookmarks, complete steps in this order:

Create the active standby pair replication scheme. (This is accomplished by the create active standby pair operation, or by the ttCWAdmin -create command in a Clusterware-managed environment.)
Create the bookmarks.
Subscribe the bookmarks.
Start the active standby pair, at which time duplication to the standby occurs and replication begins. (This is accomplished by the ttRepAdmin -duplicate command, or by the ttCWAdmin -start command in a Clusterware-managed environment.)

Be aware of the following usage notes:

The position of the bookmark in the standby database is very close to that of the bookmark in the active database; however, because the replication of acknowledge operations is asynchronous, you may see a small window of duplicate updates in the event of a failover, depending on how often acknowledge operations are performed.
You should close and reopen all bookmarks on a database after it changes from standby to active status, using the ttXlaClose and ttXlaPersistOpen functions. The state of a replicated bookmark on a standby database does change during XLA processing, as the replication agent automatically repositions bookmarks as appropriate on standby databases. If you attempt to use a bookmark that was open before the database changed to active status, you receive an error indicating that the state of the bookmark was reset and that it has been repositioned. While it is permissible to continue reading from the repositioned bookmark in this scenario, you can avoid the error by closing and reopening bookmarks.
It is permissible to drop the active standby pair scheme while replicated bookmarks exist. The bookmarks of course cease to be replicated at that point, but are not deleted. If you subsequently re-enable the active standby pair scheme, these bookmarks are automatically added to the scheme.
You cannot delete replicated bookmarks as long as the replication agent is running.
You can only read and acknowledge a replicated bookmark in the active database. Each time you acknowledge a replicated bookmark, the acknowledge operation is asynchronously replicated to the standby database.

XLA Bookmarks and Transaction Log Holds

When XLA is in use, there is a hold on TimesTen transaction log files until the XLA bookmark advances.

The hold prevents transaction log files from being purged until XLA can confirm it no longer needs them. If a bookmark becomes stuck, which can occur if an XLA application terminates unexpectedly or disconnects without first deleting its bookmark or disabling change tracking, the log hold persists and there may be an excessive accumulation of transaction log files. This accumulation may result in file system space being filled.

See Monitoring Accumulation of Transaction Log Files in Oracle TimesTen In-Memory Database Operations Guide.

XLA Data Types

There is a data type mapping between internal SQL data types and XLA data types before release 7.0 and since release 7.0.

See Data Types in Oracle TimesTen In-Memory Database SQL Reference.

Table 5-1 XLA Data Type Mapping

Internal SQL Data Type	XLA Data Type
`TT_CHAR`	`TTXLA_CHAR_TT`
`TT_VARCHAR`	`TTXLA_VARCHAR_TT`
`TT_NCHAR`	`TTXLA_NCHAR_TT`
`TT_NVARCHAR`	`TTXLA_NVARCHAR_TT`
`CHAR`	`TTXLA_CHAR`
`NCHAR`	`TTXLA_NCHAR`
`VARCHAR2`	`TTXLA_VARCHAR`
`NVARCHAR2`	`TTXLA_NVARCHAR`
`TT_TINYINT`	`TTXLA_TINYINT`
`TT_SMALLINT`	`TTXLA_SMALLINT`
`TT_INTEGER`	`TTXLA_INTEGER`
`TT_BIGINT`	`TTXLA_BIGINT`
`BINARY_FLOAT`	`TTXLA_BINARY_FLOAT`
`BINARY_DOUBLE`	`TTXLA_BINARY_DOUBLE`
`NUMBER`	`TTXLA_NUMBER`
`NUMBER(p,s)`	`TTXLA_NUMBER`
`FLOAT`	`TTXLA_NUMBER`
`TT_TIME`	`TTXLA_TIME`
`TT_DATE`	`TTXLA_DATE_TT`
`TT_TIMESTAMP`	`TTXLA_TIMESTAMP_TT`
`DATE`	`TTXLA_DATE`
`TIMESTAMP`	`TTXLA_TIMESTAMP`
`TT_BINARY`	`TTXLA_BINARY`
`TT_VARBINARY`	`TTXLA_VARBINARY`
`ROWID`	`TTXLA_ROWID`
`BLOB`	`TTXLA_BLOB`
`CLOB`	`TTXLA_CLOB`
`NCLOB`	`TTXLA_NCLOB`

XLA offers functions to convert between internal SQL data types and external programmatic data types. For example, you can use ttXlaNumberToCString to convert NUMBER columns to character strings. TimesTen provides the following XLA data type conversion functions:

XLA System Privilege

An XLA user must have the XLA system privilege.

Any XLA functionality, such as the following, requires the system privilege XLA:
- Connecting to TimesTen (which also requires the CREATE SESSION privilege) as an XLA reader, such as by the ttXlaPersistOpen C function
- Executing any other XLA-related TimesTen C functions, documented in XLA Reference
- Executing any XLA-related TimesTen built-in procedures
  
  The procedures ttXlaBookmarkCreate, ttXlaBookmarkDelete, ttXlaSubscribe, and ttXlaUnsubscribe are documented in Built-In Procedures in Oracle TimesTen In-Memory Database Reference.
A user with the XLA privilege has capabilities equivalent to the SELECT ANY TABLE, SELECT ANY VIEW, and SELECT ANY SEQUENCE system privileges, and can capture DDL statement records that occur in the database. Note that as a result, the user can obtain information about database objects that the user has not otherwise been granted access to.

XLA Limitations

This section lists TimesTen XLA limitations.

XLA is available on all platforms supported by TimesTen. However, XLA does not support data transfer between different platforms.
XLA support for LOBs is limited. See Specifying Which Tables to Monitor for Updates.
XLA does not support applications linked with a generic driver manager library or linked directly with the client/server library. (XLA supports applications linked directly with the direct driver library or linked with the TimesTen driver manager for direct connections.)
An XLA reader cannot subscribe to a table that uses in-memory column-based compression.
For autorefresh cache groups, the change-tracking trigger on Oracle Database does not have column-level resolution. (To have that would be very expensive.) Therefore, the autorefresh feature updates all the columns in the row, and XLA can only report that all the columns have changed, even if data did not actually change in all columns.

About the XLA Sample Application

The TimesTen Classic Quick Start provides the xlaSimple sample application showing how to use many of the XLA functions described in this chapter.

See About TimesTen Quick Start and Sample Applications.

Most of this chapter, including the sample code shown in Writing an XLA Event-Handler Application starting immediately below, is based on the xlaSimple application. For this application, a table MYDATA is created in the APPUSER schema. While you are logged in as APPUSER, you make updates to the table. While you are logged in as XLAUSER, the xlaSimple application reports on the updates.

To run the application, execute xlaSimple at one command prompt. You are prompted for the password of XLAUSER (determined when the sample database is created). Start ttIsql at a separate command prompt, connecting to the TimesTen sample database as APPUSER. You are prompted for the password of APPUSER (also determined when the sample database is created).

At the ttIsql command prompt you can enter DML statements to alter the table. Then you can view the XLA output in the xlaSimple window.