Overview of TimesTen XLA
There are ways to use XLA in TimesTen Classic.
XLA functions mentioned here are documented in XLA Reference.
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
- ApplicationC
updates row 1 of tableW
with value 7.7.BT1
- ApplicationB
updates row 3 of tableX
with value 2.CT2
- ApplicationC
updates row 9 of tableW
with value 5.6.BT2
- ApplicationB
updates row 2 of tableY
with value "XYZ".AT1
- ApplicationA
updates row 1 of tableZ
with value 3.AT2
- ApplicationA
updates row 3 of tableZ
with value 4.BT3
- ApplicationB
commits its transaction.AT3
- ApplicationA
rolls back its transaction.CT3
- ApplicationC
commits its transaction.
An XLA application that is set up to detect changes to tables W
, Y
, and Z
would see the following:
BT2
andBT3
- Update row 2 of tableY
with value "XYZ" and commit.CT1
- Update row 1 of tableW
with value 7.7.CT2
andCT3
- Update row 9 of tableW
with value 5.6 and commit.
This example demonstrates the following:
-
Transaction records of applications
B
andC
all appear together. -
Although the records for application
C
begin to appear in the transaction log before those for applicationB
, the commit for applicationB
(BT3
) appears in the transaction log before the commit for applicationC
(CT3
). As a result, the records for applicationB
are returned to the XLA application ahead of those for applicationC
. -
The application
B
update to tableX
(BT1
) is not presented because XLA is not set up to detect changes to tableX
. -
The application
A
updates to tableZ
(AT1
andAT2
) 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 thettCWAdmin -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 thettCWAdmin -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
andttXlaPersistOpen
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 |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 thettXlaPersistOpen
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
, andttXlaUnsubscribe
are documented in Built-In Procedures in Oracle TimesTen In-Memory Database Reference.
-
-
A user with the
XLA
privilege has capabilities equivalent to theSELECT ANY TABLE
,SELECT ANY VIEW
, andSELECT 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.