Using XLA in TTClasses

About TimesTen XLA

XLA is a set of functions that enable you to implement applications that monitor TimesTen for changes to specified database tables and receive real-time notification of these changes.

The primary purpose of XLA is as a high-performance, asynchronous alternative to triggers.

XLA returns notification of changes to specific tables in the database and information about the transaction boundaries for those database changes. This section shows how to acknowledge updates only at transaction boundaries (a common requirement for XLA applications), using one example that does not use and one example that does use transaction boundaries.

Important notes:

As discussed in Considerations when Using an ODBC Driver Manager (Windows), XLA functionality cannot be used in an application connected to an ODBC driver manager.
If an XLA bookmark becomes stuck, which can occur if an XLA application terminates unexpectedly or disconnects without first deleting its bookmark or disabling change tracking, 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.

Additional notes:

You can subscribe to tables containing LOB columns, but information about the LOB value itself is unavailable.
Columns containing LOBs are reported as empty (zero length) or null (if the value is actually NULL). In this way, you can tell the difference between a null column and a non-null column.
An XLA reader cannot subscribe to a table that uses in-memory column-based compression.

See XLA and TimesTen Event Management in Oracle TimesTen In-Memory Database C Developer's Guide. In addition, the TTClasses sample applications, provided with TimesTen Classic Quick Start, include XLA applications. See About TimesTen Quick Start and Sample Applications.

Acknowledging XLA Updates Without Using Transaction Boundaries

Inside the HandleChange() method, depending on whether the record is an insert, update, or delete, the appropriate method from among the following is called: HandleInsert(), HandleUpdate(), or HandleDelete().

It is inside HandleChange() that you can access the flag that indicates whether the XLA record is the last record in a particular transaction. Thus there is no way in loop in the example in this section for the HandleChange() method to pass the information about the transaction boundary to the loop, so that this information can influence when to call conn.ackUpdates().

This is not an issue under typical circumstances of only a few records per transaction. Usually only a few records are returned when you ask XLA to return at most 1000 records with a fetchUpdatesWait() call. XLA returns records as quickly as it can, and even if huge numbers of transactions are occurring in the database, you usually can pull the XLA records out quickly, a few at a time, and XLA makes sure that the last record returned is on a transaction boundary. For example, if you ask for 1000 records from XLA but only 15 are returned, it is highly probable that the 15th record is at the end of a transaction.

XLA guarantees one of the following:

A batch of records ends with a completed transaction (perhaps multiple transactions in a single batch of XLA records).

Or:

A batch of records contains a partial transaction, with no completed transactions in the same batch, and subsequent batches of XLA records are returned for that single transaction until its transaction boundary has been reached.

This example shows a typical main loop of a TTClasses XLA program. (It also assumes a signal handler is in place.)

TTXlaPersistConnection conn; // XLA connection
TTXlaTableList list(&conn); // tables being monitored
ttXlaUpdateDesc_t ** arry; // pointer to returned XLA records
int records_fetched;
// ...

while (!signal_received) {
  // fetch the updates
  conn.fetchUpdatesWait(&arry, MAX_RECS_TO_FETCH, &records_fetched, ...); 

  // Interpret the updates
  for(j=0;j < records_fetched;j++){
    ttXlaUpdateDesc_t *p;
    p = arry[j];
    list.HandleChange(p, NULL);
  } // end for each record fetched

  // periodically call ackUpdates()
  if (/* some condition is reached */) {
    conn.ackUpdates(); 
  }
}

Acknowledging XLA Updates at Transaction Boundaries

XLA applications should verify whether the last record in a batch of XLA records is at a transaction boundary, and call ackUpdates() only on transaction boundaries. This way, when the application or system or database fails, the XLA bookmark is at the start of a transaction after the system recovers.

This is especially important when operations involve a large number of rows. If a bulk insert, update, or delete operation has been performed on the database and the XLA application asks for 1000 records, it may or may not receive all 1000 records. The last record returned through XLA probably does not have the end-of-transaction flag. In fact, if the transaction has made changes to 10,000 records, then clearly a minimum of 10 blocks of 1000 XLA records must be fetched before reaching the transaction boundary.

Calling ackUpdates() for every transaction boundary is not recommended, however, because ackUpdates() is a relatively expensive operation. Users should balance overall system throughput with recovery time and file system space requirements. (Recall that a TimesTen transaction log file cannot be deleted by a checkpoint operation if XLA has a bookmark that references that log file. See ttLogHolds in Oracle TimesTen In-Memory Database Reference.) Depending on system throughput, recovery time, and file system space requirements, some applications may find it appropriate to call ackUpdates() once or several times per minute, while other applications may need only call it once or several times per hour.

The HandleChange() method has a second parameter to allow passing information between HandleChange() and the main XLA loop. Compare the example in the preceding section to the example below, specifically the do_acknowledge setting and the &do_acknowledge parameter of the HandleChange() call.

In this example, ackUpdates() is called only when the do_acknowledge flag indicates that this batch of XLA records is at a transaction boundary. (The example also assumes a signal handler is in place.)

TTXlaPersistConnection conn; // XLA connection
TTXlaTableList list(&conn); // tables being monitored
ttXlaUpdateDesc_t ** arry; // ptr to returned XLA recs
int records_fetched;
int do_acknowledge;
int j;

// ...
while (!signal_received) {
  // fetch the updates
  conn.fetchUpdatesWait(&arry, MAX_RECS_TO_FETCH, &records_fetched, ...); 

  do_acknowledge = FALSE;

  // Interpret the updates
  for(j=0;j < records_fetched;j++){
    ttXlaUpdateDesc_t *p;
    p = arry[j];
    list.HandleChange(p, &do_acknowledge);
  } // end for each record fetched

  // periodically call ackUpdates()
  if (do_acknowledge == TRUE  /* and some other conditions ... */ ) {
    conn.ackUpdates();
  }
}

In addition to this change to the XLA main loop, the HandleChange() method must be overloaded to have two parameters (ttXlaUpdateDesc_t*, void* pData). See HandleChange(). The TimesTen Classic Quick Start xlasubscriber1 sample application shows the use of a pData parameter. (See About TimesTen Quick Start and Sample Applications.)

XLA System Privilege

The system privilege XLA is required for any XLA functionality, such as connecting to TimesTen (which also requires the CREATE SESSION privilege) as an XLA reader, executing XLA-related TimesTen C functions, and executing XLA-related TimesTen built-in procedures.

Refer to XLA System Privilege in Oracle TimesTen In-Memory Database C Developer's Guide.

Note:

A user with the XLA privilege can be notified of any DML statement that executes in the database. As a result, the user with XLA privilege can obtain information about database objects that the user has not otherwise been granted access to. In practical terms, the XLA privilege is effectively the same as the SELECT ANY TABLE, SELECT ANY VIEW, and SELECT ANY SEQUENCE privileges.