5 XLA and TimesTen Event Management

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. You can also use the ttXlaGetVersion and ttXlaSetVersion XLA functions to interoperate with earlier XLA versions.

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 disk. When XLA is initialized, the XLA application does not have to be concerned with which portions of the transaction log are on disk 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 disk.

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 aborted transaction to the application.

Most of these basic XLA concepts are demonstrated in Example 5-1 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"

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. However, for asynchronous materialized views, be aware that the order of XLA notifications for an asynchronous materialized view is not necessarily the same as it would be for the associated detail tables, or the same as it would be for a synchronous materialized view. For example, if there are two inserts to a detail table, they may be done in the opposite order in the asynchronous materialized view. Furthermore, an update to a detail table of a materialized view may be reported by XLA as a delete followed by an insert. Also, multiple operations, such as multiple inserts or multiple deletes, may be combined into a single operation. Applications that depend on precise ordering should not use asynchronous materialized views.

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 a bookmark to maintain its position in the log update stream. 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.

The rest of this section covers the following:

• Creating or reusing a bookmark

• How bookmarks work

• Replicated bookmarks

• XLA bookmarks and 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, as shown in Figure 5-2 that follows.

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.

About XLA data types

Table 5-1 shows the data type mapping between internal SQL data types and XLA data types before release 7.0 and since release 7.0. For more information about TimesTen data types, see "Data Types" in Oracle TimesTen In-Memory Database SQL Reference.

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:

• ttXlaDateToODBCCType

• ttXlaDecimalToCString

• ttXlaNumberToCString

• ttXlaNumberToDouble

• ttXlaNumberToBigInt

• ttXlaNumberToInt

• ttXlaNumberToSmallInt

• ttXlaNumberToTinyInt

• ttXlaNumberToUInt

• ttXlaOraDateToODBCTimeStamp

• ttXlaOraTimeStampToODBCTimeStamp

• ttXlaRowidToCString

• ttXlaTimeToODBCCType

• ttXlaTimeStampToODBCCType

Access control impact on XLA

"Considering TimesTen features for access control" provides a brief overview of how TimesTen access control affects operations in the database. Access control impacts XLA as follows:

• 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 Chapter 9, "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 he or she has not otherwise been granted access to.

XLA limitations

Be aware of the following limitations when you use TimesTen XLA:

• XLA is available on all platforms supported by TimesTen. However, XLA does not support data transfer between different platforms or between 32-bit and 64-bit versions of the same platform.

• XLA support for LOBs is limited. See "Specifying which tables to monitor for updates" for information.

• XLA does not support applications linked with a driver manager library or the client/server library. (The TimesTen driver manager supplied with the Quick Start applications does support XLA but is not fully supported itself. See the note regarding this driver manager in "Linking with an ODBC driver manager".)

• An XLA reader cannot subscribe to a table that uses in-memory columnar 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.

XLA demo

TimesTen provides the xlaSimple demo showing how to use many of the XLA functions described in this chapter. It is located in the quickstart/sample_code/odbc/xla directory:

See "About the TimesTen C demos" for an overview of TimesTen demo programs for C developers. Refer to install_dir/quickstart.html for details. The README file in the odbc directory contains instructions for building and running xlaSimple, among others.

Most of this chapter, including the sample code shown in "Writing an XLA event-handler application" starting immediately below, is based on the xlaSimple demo. For this demo, a table MYDATA has been created in the APPUSER schema. While you are logged in as APPUSER, you are making updates to the table. While you are logged in as XLAUSER, the xlaSimple demo reports on the updates.

To run the demo, execute xlaSimple at one command prompt. You are prompted for the password of XLAUSER, which is specified when the sample database is created. Start ttIsql at a separate command prompt, connecting to the TimesTen sample database as APPUSER. Again, you are prompted for a password that is specified 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.

Obtaining a database connection handle

As with every ODBC application, an XLA application must initialize ODBC, obtain an environment handle (henv), and obtain a connection handle (hdbc) to communicate with the specific database.

Initialize the environment and connection handles:

SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;

Pass the address of henv to the SQLAllocEnv ODBC function to allocate an environment handle:

rc = SQLAllocEnv(&henv);

Pass the address of hdbc to the SQLAllocConnect ODBC function to allocate a connection handle for the database:

rc = SQLAllocConnect(henv, &hdbc);

Call the SQLDriverConnect ODBC function to connect to the database specified by the connection string (connStr), which in this example is passed from the command line:

static char connstr[CONN_STR_LEN];
...
rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)connstr, SQL_NTS, NULL, 0,
                      NULL, SQL_DRIVER_COMPLETE);

Call the SQLSetConnectOption ODBC function to turn autocommit off:

rc = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);

Initializing XLA and obtaining an XLA handle

After initializing ODBC and obtaining an environment and connection handle as described in the preceding section, "Obtaining a database connection handle", you can initialize XLA and obtain an XLA handle to access the transaction log. Create only one XLA handle per ODBC connection. If your application uses multiple XLA reader threads (each connected to its own XLA bookmark), create a separate XLA handle and ODBC connection for each thread.

This section describes how to initialize XLA. Before initializing XLA, initialize a bookmark. Then initialize an XLA handle as type ttXlaHandle_h:

unsigned char bookmarkName [32];
...
strcpy((char*)bookmarkName, "xlaSimple");
...
ttXlaHandle_h xla_handle = NULL;

Pass bookmarkName and the address of xla_handle to the ttXlaPersistOpen function to obtain an XLA handle:

rc = ttXlaPersistOpen(hdbc, bookmarkName, XLACREAT, &xla_handle);

The XLACREAT option is used to create a new non-replicated bookmark. Alternatively, use the XLAREPL option to create a replicated bookmark. In either case, the operation fails if the bookmark already exists.

To use a bookmark that already exists, call ttXlaPersistOpen with the XLAREUSE option, as shown in the following example.

#include <tt_errCode.h>      /* TimesTen Native Error codes */
...
    if ( native_error == 907 ) { /* tt_ErrKeyExists */
      rc = ttXlaPersistOpen(hdbc, bookmarkName, XLAREUSE, &xla_handle);
    ...
    }

If ttXlaPersistOpen is given invalid parameters, or the application was unable to allocate memory for the handle, the return code is SQL_INVALID_HANDLE. In this situation, ttXlaError cannot be used to detect this or any further errors.

If ttXlaPersistOpen fails but still creates a handle, the handle must be closed to prevent memory leaks.

Specifying which tables to monitor for updates

After initializing XLA and obtaining an XLA handle as described in the preceding section, "Initializing XLA and obtaining an XLA handle", you can specify which tables or materialized views you want to monitor for update events.

You can determine which tables a bookmark is subscribed to by querying the SYS.XLASUBSCRIPTIONS table. You can also use SYS.XLASUBSCRIPTIONS to determine which bookmarks have subscribed to a specific table.

The ttXlaNextUpdate and ttXlaNextUpdateWait functions retrieve XLA records associated with DDL events. DDL XLA records are available to any XLA bookmark. DDL events include CREATAB, DROPTAB, CREAIND, DROPIND, CREATVIEW, DROPVIEW, CREATSEQ, DROPSEQ, CREATSYN, DROPSYN, ADDCOLS, DRPCOLS, and TRUNCATE transactions. See "ttXlaUpdateDesc_t" for information about these event types.

The ttXlaTableStatus function subscribes the current bookmark to updates to the specified table. Or it determines whether the current bookmark is already monitoring DML records associated with the table.

Call the ttXlaTableByName function to obtain both the system and user identifiers for a named table or materialized view. Then call the ttXlaTableStatus function to enable XLA to monitor changes to the table or materialized view.

#define TABLE_OWNER "APPUSER"
#define TABLE_NAME "MYDATA"
...
SQLUBIGINT SYSTEM_TABLE_ID = 0;
...
SQLUBIGINT userID;

rc = ttXlaTableByName(xla_handle, TABLE_OWNER, TABLE_NAME,
                      &SYSTEM_TABLE_ID, &userID);

When you have the table identifiers, you can use the ttXlaTableStatus function to enable XLA update tracking to detect changes to the MYDATA table. Setting the newstatus parameter to a nonzero value results in XLA tracking changes made to the specified table.

SQLINTEGER oldstatus;
SQLINTEGER newstatus = 1;
...
rc = ttXlaTableStatus(xla_handle, SYSTEM_TABLE_ID, 0,
                      &oldstatus, &newstatus);

The oldstatus parameter is output to indicate the status of the table at the time of the call.

At any time, you can use ttXlaTableStatus to return the current XLA status of a table by leaving newstatus null and returning only oldstatus. For example:

rc = ttXlaTableStatus(xla_handle, SYSTEM_TABLE_ID, 0,
                      &oldstatus, NULL);
...
if (oldstatus != 0)
     printf("XLA is currently tracking changes to table %s.%s\n",
             TABLE_OWNER, TABLE_NAME);
else
     printf("XLA is not tracking changes to table %s.%s\n",
             TABLE_OWNER, TABLE_NAME);

Retrieving update records from the transaction log

Once you have specified which tables to monitor for updates, you can call the ttXlaNextUpdate or ttXlaNextUpdateWait function to return a batch of records from the transaction log. Only records for committed transactions are returned. They are returned in the order in which they were committed. You must periodically call the ttXlaAcknowledge function to acknowledge receipt of the transactions so that XLA can determine which records are no longer needed and can be purged from the transaction log. These functions impact the position of the application bookmark in the transaction log, as described in "How bookmarks work". Also see "ttLogHolds" in Oracle TimesTen In-Memory Database Reference for related information. That TimesTen built-in procedure returns information about transaction log holds.

Each update record in a transaction returned by ttXlaNextUpdate begins with an update header described by the ttXlaUpdateDesc_t structure. This update header contains a flag indicating if the record is the first in the transaction (TT_UPDFIRST) or the last commit record (TT_UPDCOMMIT). The update header also identifies the table affected by the update. Following the update header are zero to two rows of data that describe the update made to that table in the database.

Figure 5-5 that follows shows a call to ttXlaNextUpdate that returns a transaction consisting of four update records from the transaction log. Receipt of the returned transaction is acknowledged by calling ttXlaAcknowledge, which resets the bookmark.

Figure 5-5 Update records

Description of "Figure 5-5 Update records"

#define FETCH_WAIT_SECS 5
...
SQLINTEGER records;
ttXlaUpdateDesc_t** arry;
int j;

while (!StopRequested()) {

    /* Get a batch of update records */
    rc = ttXlaNextUpdateWait(xla_handle, &arry, 100,
                             &records, FETCH_WAIT_SECS);
    if (rc != SQL_SUCCESS {
      /* See "Handling XLA errors" */
    }

    /* Process the records */
    for(j=0; j < records; j++){
      ttXlaUpdateDesc_t* p;
      p = arry[j];
      HandleChange(p); /* Described in the next section */
    }

    /* After each batch, Acknowledge updates to reset bookmark.*/
    rc = ttXlaAcknowledge(xla_handle);
    if (rc != SQL_SUCCESS {
      /* See "Handling XLA errors" */
    }
} /* end while !StopRequested() */

The actual number of records returned by ttXlaNextUpdate or ttXlaNextUpdateWait, as indicated by the nreturned output parameter of those functions, may be less than the value of the maxrecords parameter. Figure 5-6 shows an example where maxrecords is 10, the transaction log contains transaction AT that is made up of seven records, and transaction BT that is made up of three records. In this case, both transactions are returned in the same batch and both maxrecords and nreturned values are 10. However, the next three transactions in the log are CT with 11 records, DT with two records, and ET with two records. Because the commit record for the DT transaction appears before the CT commit record, the next call to ttXlaNextUpdate returns the two records for the DT transaction and the value of nreturned is 2. In the next call to ttXlaNextUpdate, XLA detects that the total records for the CT transaction exceeds maxrecords, so it returns the records for this transaction in two batches. The first batch contains the first 10 records for CT (nreturned = 10). The second batch contains the last CT record and the two records for the ET transaction, assuming no commit record for a transaction following ET is detected within the next seven records.

See "ttXlaNextUpdate" and "ttXlaNextUpdateWait" for details of the parameters of these functions.

Figure 5-6 Records retrieved when maxrecords=10

Description of "Figure 5-6 Records retrieved when maxrecords=10"

XLA reads records from either a memory buffer or transaction log files on disk, as described in "How XLA reads records from the transaction log". To minimize latency, records from the memory buffer are returned as soon as they are available, while records not in the buffer are returned only if the buffer is empty. This design enables XLA applications to see changes as soon as the changes are made and with minimal latency. The trade-off is that there may be times when fewer changes are returned than the number requested by the ttXlaNextUpdate or ttXlaNextUpdateWait maxrecords parameter.

Inspecting record headers and locating row addresses

Now that there is an array of update records where the type of operation each record represents is known, the returned row data can be inspected.

Each record returned by the ttXlaNextUpdate or ttXlaNextUpdateWait function begins with an ttXlaUpdateDesc_t header that describes the following:

• The table on which the operation was performed

• Whether the record is the first or last (commit) record in the transaction

• The type of operation it represents

• The length of the returned row data, if any

• Which columns in the row were updated, if any

Figure 5-7 shows one of the update records in the transaction log.

Figure 5-7 Address of row data returned in an XLA update record

Description of "Figure 5-7 Address of row data returned in an XLA update record"

The ttXlaUpdateDesc_t header has a fixed length and, depending on the type of operation, is followed by zero to two rows (or tuples) from the database. You can locate the address of the first returned row by obtaining the address of the ttXlaUpdateDesc_t header and adding it to sizeof(ttXlaUpdateDesc_t):

tup1 = (void*) ((char*) ttXlaUpdateDesc_t + sizeof(ttXlaUpdateDesc_t));

This is shown in Example 5-4 below.

The ttXlaUpdateDesc_t ->type field describes the type of SQL operation that generated the update. Transaction records of type UPDATETTUP describe UPDATE operations, so they return two rows to report the row data before and after the update. You can locate the address of the second returned row that holds the value after the update by adding the address of the first row in the record to its length:

if (ttXlaUpdateDesc_t->type == UPDATETUP) {
  tup2 = (void*) ((char*) tup1 + ttXlaUpdateDesc_t->tuple1);
}

This is also shown in Example 5-4.

void HandleChange(ttXlaUpdateDesc_t* xlaP)
{
  void*  tup1;
  void*  tup2;
 
  /* First confirm that the XLA update is for the table we care about. */
  if (xlaP->sysTableID != SYSTEM_TABLE_ID)
    return ;
 
  /* OK, it's for the table we're monitoring. */
 
  /* The last record in the ttXlaUpdateDesc_t record is the "tuple2"
   * field.  Immediately following this field is the first XLA record "row". */
 
  tup1 = (void*) ((char*) xlaP + sizeof(ttXlaUpdateDesc_t));
 
  switch(xlaP->type) {
 
  case INSERTTUP:
    printf("Inserted new row:\n");
    PrintColValues(tup1);
    break;
 
  case UPDATETUP:
 
    /* If this is an update ttXlaUpdateDesc_t, then following that is
     * the second XLA record "row".  
     */
 
    tup2 = (void*) ((char*) tup1 + xlaP->tuple1);
    printf("Updated row:\n");
    PrintColValues(tup1);
    printf("To:\n");
    PrintColValues(tup2);
    break;
 
  case DELETETUP:
    printf("Deleted row:\n");
    PrintColValues(tup1);
    break;
 
  default:
    /* Ignore any XLA records that are not for inserts/update/delete SQL ops. */
    break;
 
  } /* switch (xlaP->type) */
}

Inspecting column data

As described in "Inspecting record headers and locating row addresses", zero to two rows of data may be returned in an update record after the ttXlaUpdateDesc_t structure. For each row, the first portion of the data is the fixed-length data, which is followed by any variable-length data, as shown in Figure 5-8.

Figure 5-8 Column offsets in a row returned in an XLA update record

Description of "Figure 5-8 Column offsets in a row returned in an XLA update record"

The procedures for inspecting column data are described in the following sections:

• Obtaining column descriptions

• Reading fixed-length column data

• Reading NOT INLINE variable-length column data

• Null-terminating returned strings

• Converting complex data types

• Detecting null values

• Putting it all together: a PrintColValues() function

Obtaining column descriptions

To read the column values from the returned row, you must first know the offset of each column in that row. The column offsets and other column metadata can be obtained for a particular table by calling the ttXlaGetColumnInfo function, which returns a separate ttXlaColDesc_t structure for each column in the table. You should call the ttXlaGetColumnInfo function as part of your initialization procedure. This call was omitted from the discussion in "Initializing XLA and obtaining an XLA handle" for simplicity.

When calling ttXlaGetColumnInfo, specify a colinfo parameter to create a pointer to a buffer to hold the list of returned ttXlaColDesc_t structures. Use the maxcols parameter to define the size of the buffer.

Example 5-5 Using column descriptions

The sample code from the xlaSimple demo below guesses the maximum number of returned columns (MAX_XLA_COLUMNS), which sets the size of the buffer xla_column_defs to hold the returned ttXlaColDesc_t structures. An alternative and more precise way to set the maxcols parameter would be to call the ttXlaGetTableInfo function and use the value returned in ttXlaColDesc_t ->columns.

#define MAX_XLA_COLUMNS 128
...
SQLINTEGER ncols;
...
ttXlaColDesc_t xla_column_defs[MAX_XLA_COLUMNS];
...
rc = ttXlaGetColumnInfo(xla_handle, SYSTEM_TABLE_ID, userID,
             xla_column_defs, MAX_XLA_COLUMNS, &ncols);
  if (rc != SQL_SUCCESS {
    /* See "Handling XLA errors" */
}

As shown in Figure 5-9, the ttXlaGetColumnInfo function produces the following output:

• A list, xla_column_defs, of ttXlaColDesc_t structures into the buffer pointed to by the ttXlaGetColumnInfo colinfo parameter

• An nreturned value, ncols, that holds the actual number of columns returned in the xla_column_defs buffer

Figure 5-9 ttXlaColDesc_t structures returned by ttXlaGetColumnInfo

Description of "Figure 5-9 ttXlaColDesc_t structures returned by ttXlaGetColumnInfo"

Each ttXlaColDesc_t structure returned by ttXlaGetColumnInfo has an offset value that describes the offset location of that column. How you use this offset value to read the column data depends on whether the column contains fixed-length data (such as CHAR, NCHAR, INTEGER, BINARY, DOUBLE, FLOAT, DATE, TIME, TIMESTAMP, and so on) or variable-length data (such as VARCHAR, NVARCHAR, or VARBINARY).

Reading fixed-length column data

For fixed-length column data, the address of a column is the offset value in the ttXlaColDesc_t structure, plus the address of the row.

Figure 5-10 Locating fixed-length data in a row

Description of "Figure 5-10 Locating fixed-length data in a row"

Example 5-6 Reading fixed-length column data

See Example 5-13 for a complete working example of computations such as those shown here.

The first column in the MYDATA table is of type CHAR. If you use the address of the tup1 row obtained earlier in the HandleChange() function (Example 5-4) and the offset from the first ttXlaColDesc_t structure returned by the ttXlaGetColumnInfo function (Example 5-5), you can obtain the value of the first column with computations such as the following:

char*  Column1;

Column1 = ((unsigned char*) tup1 + xla_column_defs[0].offset);

The third column in the MYDATA table is of type INTEGER, so you can use the offset from the third ttXlaColDesc_t structure to locate the value and recast it as an integer using computations such as the following. The data is guaranteed to be aligned properly.

int Column3;

Column3 = *((int*) ((unsigned char*) tup +
           xla_column_defs[2].offset));

The fourth column in the MYDATA table is of type NCHAR, so you can use the offset from the fourth ttXlaColDesc_t structure to locate the value and recast it as a SQLWCHAR type, with computations such as the following:

SQLWCHAR*  Column4;

Column4 = (SQLWCHAR*) ((unsigned char*) tup +
                      xla_column_defs[3].offset);

Unlike the column values obtained in the above examples, Column4 points to an array of two-byte Unicode characters. You must iterate through each element in this array to obtain the string, as shown for the SQL_WCHAR case in Example 5-13.

Other fixed-length data types can be cast to their corresponding C types. Complex fixed-length data types, such as DATE, TIME, and DECIMAL values, are stored in an internal TimesTen format, but can be converted by applications to their corresponding ODBC C value using the XLA conversion functions, as described in "Converting complex data types".

Note:

Strings returned by XLA are not null-terminated. See "Null-terminating returned strings".

Reading NOT INLINE variable-length column data

For NOT INLINE variable-length data (VARCHAR, NVARCHAR, and VARBINARY), the data located at ttXlaColDesc_t ->offset is a four-byte offset value that points to the location of the data in the variable-length portion of the returned row. By adding the offset address to the offset value, you can obtain the address of the column data in the variable-length portion of the row. The first n bytes (where n is 4 on 32-bit platforms or 8 on 64-bit platforms) at this location is the length of the data, followed by the actual data. For variable-length data, the ttXlaColDesc_t ->size value is the maximum allowable column size. Figure 5-11 shows how to locate NOT INLINE variable-length data in a row.

Figure 5-11 Locating NOT INLINE variable-length data in a row

Description of "Figure 5-11 Locating NOT INLINE variable-length data in a row"

Example 5-7 Reading NOT INLINE variable-length column data

See Example 5-13, "Complete PrintColValues() function" for a complete working example of computations such as those shown here.

Continuing with our example, the second column in the returned row (tup1) is of type VARCHAR. To locate the variable-length data in the row, first locate the value at the column's ttXlaColDesc_t ->offset in the fixed-length portion of the row, as shown in Figure 5-11 above. The value at this address is the four-byte offset of the data in the variable-length portion of the row (VarOffset). Next, obtain a pointer to the beginning of the variable-length column data (DataLength) by adding the VarOffset offset value to the address of VarOffset. Assuming the operation is performed on a 32-bit platform, the first four bytes at the DataLength location is the length of the data. The next byte after DataLength is the beginning of the actual data (Column2).

The sample code here assumes the operation is performed on a 32-bit platform, so DataLength is initialized as a 32-bit type. On a 64-bit platform, DataLength must be initialized as a 64-bit type and the Column2 data would appear 64 bits + 1 after the offset address, DataLength.

void*  VarOffset; /* offset of data */
long*  DataLength; /* length of data */
char*  Column2; /* pointer to data */

VarOffset = (void*) ((unsigned char*) tup1 +
             xla_column_defs[1].offset);
     /*
      * If column is out-of-line, pColVal points to an offset
      * else column is inline so pColVal points directly to the string length.
      */

      if (xla_column_defs[1].flags & TT_COLOUTOFLINE)
      DataLength = (long*)((char*)VarOffset + *((int*)VarOffset));
      else
      DataLength = (long*)VarOffset;
      Column2 = (char*)(DataLength+1);

VARBINARY types are handled in a manner similar to VARCHAR types. If Column2 were an NVARCHAR type, you could initialize it as a SQLWCHAR, get the value as shown in the above VARCHAR case, then iterate through the Column2 array, as shown for the NCHAR value, CharBuf, in Example 5-13.

Note:

In the preceding example, DataLength is type long, which is described as being a 64-bit (eight-byte) type on 64-bit systems and a 32-bit (four-byte) type on 32-bit systems. This is true on most UNIX systems; however, on Windows 64-bit systems long is a four-byte type.

char buffer[10+1];
int size;

size = xla_column_defs[0].size;
memcpy(buffer, Column1, size);
buffer[size] = '\0';

printf(" Row %s is %s\n", ((unsigned char*) xla_column_defs[0].colName), buffer);

char buffer[32+1];

memcpy(buffer, Column2, *DataLength);
buffer[*DataLength] = '\0';

printf(" Row %s is %s\n", ((unsigned char*) xla_column_defs[1].colName), buffer);

#define STACKBUFSIZE 10000
char VarStackBuf[STACKBUFSIZE];
char*  buffer;

buffer = (*DataLength+1 <= STACKBUFSIZE) ? VarStackBuf :
           malloc(*DataLength+1);

memcpy(buffer,Column2,*DataLength);
buffer[*DataLength] = '\0';

printf(" Row %s is %s\n", ((unsigned char*) xla_column_defs[1].colName), buffer);
if (buffer != VarStackBuf) /* buffer was allocated */
        free(buffer);

void*  Column5;
TIMESTAMP_STRUCT timestamp;

Column5 = (void*) ((unsigned char*) tup1 +
                  xla_column_defs[4].offset);

rc = ttXlaTimeStampToODBCCType(Column5, &timestamp);
  if (rc != SQL_SUCCESS) {
    /* See "Handling XLA errors" */
  }
printf(" %s: %04d-%02d-%02d %02d:%02d:%02d.%06d\n",
      ((unsigned char*) xla_column_defs[i].colName),
        timestamp.year,timestamp.month, timestamp.day,
        timestamp.hour,timestamp.minute,timestamp.second,
        timestamp.fraction);

char decimalData[50];

Column6 = (float*) ((unsigned char*) tup +
           xla_column_defs[5].offset);
precision = (short) (xla_column_defs[5].precision);
scale = (short) (xla_column_defs[5].scale);

rc = ttXlaDecimalToCString(Column6, (char*)&decimalData,
                           precision, scale);
  if (rc != SQL_SUCCESS) {
    /* See "Handling XLA errors" */
  }

printf(" %s: %s\n", ((unsigned char*) xla_column_defs[5].colName), decimalData);

if (xla_column_defs[5].nullOffset != 0) {
  if (*((unsigned char*) tup +
     xla_column_defs[5].nullOffset) == 1) {
         printf("Column6 is NULL\n");
  }
}

void PrintColValues(void* tup)
{ 
 
  SQLRETURN rc ;
  SQLINTEGER native_error;
 
  void* pColVal;
  char buffer[50+1]; /* No strings over 50 bytes */
  int i;
 
  for (i = 0; i < ncols; i++)
  {
 
    if (xla_column_defs[i].nullOffset != 0) {  /* See if column is NULL */
      /* this means col could be NULL */
      if (*((unsigned char*) tup + xla_column_defs[i].nullOffset) == 1) {
        /* this means that value is SQL NULL */
        printf("  %s: NULL\n", 
               ((unsigned char*) xla_column_defs[i].colName));
        continue; /* Skip rest and re-loop */
      }
    }
 
    /* Fixed-length data types: */
    /* For INTEGER, recast as int */
 
    if (xla_column_defs[i].dataType == TTXLA_INTEGER) {
 
      printf("  %s: %d\n",
             ((unsigned char*) xla_column_defs[i].colName),
             *((int*) ((unsigned char*) tup + xla_column_defs[i].offset)));
    }

    /* For CHAR, just get value and null-terminate string */
 
    else if (   xla_column_defs[i].dataType == TTXLA_CHAR_TT
             || xla_column_defs[i].dataType == TTXLA_CHAR) {
 
      pColVal = (void*) ((unsigned char*) tup + xla_column_defs[i].offset);
 
      memcpy(buffer, pColVal, xla_column_defs[i].size);
      buffer[xla_column_defs[i].size] = '\0';
 
      printf("  %s: %s\n", ((unsigned char*) xla_column_defs[i].colName), buffer);
    }
 
    /* For NCHAR, recast as SQLWCHAR.
       NCHAR strings must be parsed one character at a time */
 
    else if (   xla_column_defs[i].dataType == TTXLA_NCHAR_TT  
             || xla_column_defs[i].dataType == TTXLA_NCHAR ) {
      SQLUINTEGER j;
      SQLWCHAR* CharBuf;
 
      CharBuf = (SQLWCHAR*) ((unsigned char*) tup + xla_column_defs[i].offset);

      printf("  %s: ", ((unsigned char*) xla_column_defs[i].colName));
 
      for (j = 0; j < xla_column_defs[i].size / 2; j++)
      { 
        printf("%c", CharBuf[j]);
      }
      printf("\n");
    }
    /* Variable-length data types:
       For VARCHAR, locate value at its variable-length offset and null-terminate.
       VARBINARY types are handled in a similar manner.
       For NVARCHARs, initialize 'var_data' as a SQLWCHAR, get the value as shown 
       below, then iterate through 'var_len' as shown for NCHAR above */
 
    else if (   xla_column_defs[i].dataType == TTXLA_VARCHAR
             || xla_column_defs[i].dataType == TTXLA_VARCHAR_TT) {
 
      long*  var_len;
      char* var_data;
      pColVal = (void*) ((unsigned char*) tup + xla_column_defs[i].offset);
      /*
       * If column is out-of-line, pColVal points to an offset
       * else column is inline so pColVal points directly to the string length.
       */
      if (xla_column_defs[i].flags & TT_COLOUTOFLINE)
        var_len = (long*)((char*)pColVal + *((int*)pColVal));
      else
        var_len = (long*)pColVal;
 
      var_data = (char*)(var_len+1);
 
      memcpy(buffer,var_data,*var_len);
      buffer[*var_len] = '\0';
 
      printf("  %s: %s\n", ((unsigned char*) xla_column_defs[i].colName), buffer);
    }
    /* Complex data types require conversion by the XLA conversion methods
       Read and convert a TimesTen TIMESTAMP value.
       DATE and TIME types are handled in a similar manner  */
 
    else if (   xla_column_defs[i].dataType == TTXLA_TIMESTAMP
             || xla_column_defs[i].dataType == TTXLA_TIMESTAMP_TT) {
 
      TIMESTAMP_STRUCT timestamp;
      char* convFunc;
 
      pColVal = (void*) ((unsigned char*) tup + xla_column_defs[i].offset);
 
      if (xla_column_defs[i].dataType == TTXLA_TIMESTAMP_TT) {
        rc = ttXlaTimeStampToODBCCType(pColVal, &timestamp);
        convFunc="ttXlaTimeStampToODBCCType";
      }
      else {
        rc = ttXlaOraTimeStampToODBCTimeStamp(pColVal, &timestamp);
        convFunc="ttXlaOraTimeStampToODBCTimeStamp";
      }
 
      if (rc != SQL_SUCCESS) {
        handleXLAerror (rc, xla_handle, err_buf, &native_error);
        fprintf(stderr, "%s() returns an error <%d>: %s", 
                convFunc, rc, err_buf);
        TerminateGracefully(1);
      }

      printf("  %s: %04d-%02d-%02d %02d:%02d:%02d.%06d\n",
             ((unsigned char*) xla_column_defs[i].colName),
             timestamp.year,timestamp.month, timestamp.day,
             timestamp.hour,timestamp.minute,timestamp.second,
             timestamp.fraction);
    }
 
    /* Read and convert a TimesTen DECIMAL value to a string. */
 
    else if (xla_column_defs[i].dataType == TTXLA_DECIMAL_TT) {
 
      char decimalData[50]; 
      short precision, scale;
      pColVal = (float*) ((unsigned char*) tup + xla_column_defs[i].offset);
      precision = (short) (xla_column_defs[i].precision);
      scale = (short) (xla_column_defs[i].scale);
 
      rc = ttXlaDecimalToCString(pColVal, (char*)&decimalData, precision, scale);
      if (rc != SQL_SUCCESS) {
        handleXLAerror (rc, xla_handle, err_buf, &native_error);
        fprintf(stderr, "ttXlaDecimalToCString() returns an error <%d>: %s", 
                rc, err_buf);
        TerminateGracefully(1);
      }
 
      printf("  %s: %s\n", ((unsigned char*) xla_column_defs[i].colName),
             decimalData);
    }
    else if (xla_column_defs[i].dataType == TTXLA_NUMBER) {
      char numbuf[32];
      pColVal = (void*) ((unsigned char*) tup + xla_column_defs[i].offset);
 
      rc=ttXlaNumberToCString(xla_handle, pColVal, numbuf, sizeof(numbuf));
      if (rc != SQL_SUCCESS) {
        handleXLAerror (rc, xla_handle, err_buf, &native_error);
        fprintf(stderr, "ttXlaNumberToDouble() returns an error <%d>: %s",
                rc, err_buf);
        TerminateGracefully(1);
      }
      printf("  %s: %s\n", ((unsigned char*) xla_column_defs[i].colName), numbuf);
    }
 
  } /* End FOR loop */
}

Handling XLA errors

Each time you call an ODBC or XLA function, you must check the return code for any errors. If the error is fatal, terminate the program as described in "Terminating an XLA application".

An error can be checked using either its error code (error number) or tt_Err string. For the complete list of TimesTen error codes and error strings, see the install_dir/include/tt_errCode.h file. For a description of each message, see "List of errors and warnings" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

If the return code from an XLA function is not SQL_SUCCESS, use the ttXlaError function to retrieve XLA-specific errors on the XLA handle.

Also see "Checking for errors".

rc = ttXlaTableByName(xla_handle, TABLE_OWNER, TABLE_NAME,
                      &SYSTEM_TABLE_ID, &userID);
if (rc != SQL_SUCCESS) {
  handleXLAerror (rc, xla_handle, err_buf, &native_error);
  fprintf(stderr,
    "ttXlaTableByName() returns an error <%d>: %s", rc, err_buf);
  TerminateGracefully(1);
}

Your XLA error-handling function should repeatedly call ttXlaError until all XLA errors are read from the error stack, proceeding until the return code from ttXlaError is SQL_NO_DATA_FOUND. If you must reread the errors, you can call the ttXlaErrorRestart function to reset the error stack pointer to the first error.

The error stack is cleared after a call to any XLA function other than ttXlaError or ttXlaErrorRestart.

Depending on your application, you may be required to act on specific XLA errors, including those shown in Table 5-3.

void handleXLAerror(SQLRETURN rc, ttXlaHandle_h xlaHandle, 
                    SQLCHAR* err_msg, SQLINTEGER* native_error)
{
  SQLINTEGER retLen;
  SQLINTEGER code;
  char* err_msg_ptr;
 
  /* initialize return codes */
  rc = SQL_ERROR;
  *native_error = -1;
  err_msg[0] = '\0';
  
  err_msg_ptr = (char*)err_msg;
  
  while (1)
  {
    int rc = ttXlaError(xlaHandle, &code, err_msg_ptr,
                        ERR_BUF_LEN - (err_msg_ptr - (char*)err_msg), &retLen);
    if (rc == SQL_NO_DATA_FOUND)
    {
      break;
    }
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
      sprintf(err_msg_ptr,
              "*** Error fetching error message via ttXlaError(); rc=<%d>.",rc) ;
      break;
    }
    rc = SQL_ERROR;
    *native_error = code ; 
    /* append any other error messages */
    err_msg_ptr += retLen;
  }
}

Dropping a table that has an XLA bookmark

Before you can drop a table that is subscribed to by an XLA bookmark, you must unsubscribe the table from the bookmark. There are several ways to unsubscribe a table from a bookmark, depending on whether the application is connected to the bookmark.

If XLA applications are connected and using bookmarks that are tracking the table to be dropped, then perform the following tasks.

1. Each XLA application must call the ttXlaTableStatus function and set the newstatus parameter to 0. This unsubscribes the table from the XLA bookmark in use by the application.

2. Drop the table.

If XLA applications are not connected and using bookmarks associated with the table to be dropped, then perform the following tasks:

1. Query the SYS.XLASUBSCRIPTIONS system table to see which bookmarks have subscribed to the table you want to drop.

2. Use the ttXlaUnsubscribe built-in procedure to unsubscribe the table from each XLA bookmark with a subscription to the table.

3. Drop the table.

Deleting bookmarks also unsubscribes the table from the XLA bookmarks. See the next section, "Deleting bookmarks".

Deleting bookmarks

You may want to delete bookmarks when you terminate an application or drop a table. Use the ttXlaDeleteBookmark function to delete XLA bookmarks if the application is connected and using the bookmarks.

As described in "About XLA bookmarks", a bookmark may be reused by a new connection after its previous connection has closed. The new connection can resume reading from the transaction log from where the previous connection stopped. Note the following:

• If you delete the bookmark, subsequent checkpoint operations such as the ttCkpt or ttCkptBlocking built-in procedure free the disk space associated with any unread update records in the transaction log.

• If you do not delete the bookmark, when an XLA application connects and reuses the bookmark, all unread update records that have accumulated since the program terminated are read by the application. This is because the update records are persistent in the TimesTen transaction log. However, the danger is that these unread records can build up in the transaction log files and consume a lot of disk space.

if (deleteBookmark) {
    ttXlaDeleteBookmark(xla_handle);
    if (rc != SQL_SUCCESS) {
    /* See "Handling XLA errors" */
    }
    xla_handle = NULL; /* Deleting the bookmark has the */
                       /* effect of disconnecting from XLA. */
}
/* Close the XLA connection as described in the next section, 
"Terminating an XLA application". */

Terminating an XLA application

When your XLA application has finished reading from the transaction log, gracefully exit by rolling back uncommitted transactions and freeing all handles. There are two approaches to this:

• Unsubscribe from all tables and materialized views, delete the XLA bookmark, and disconnect from the database.

Or:

• Disconnect from the database but keep the XLA bookmark in place. When you reconnect at a later time, you can resume reading records from the bookmark.

For the first approach, complete the following steps.

1. Call ttXlaTableStatus to unsubscribe from each table and materialized view, setting the newstatus parameter to 0.

2. Call ttXlaDeleteBookmark to delete the bookmark. See "Deleting bookmarks".

3. Call ttXlaClose to disconnect the XLA handle.

4. Call the ODBC function SQLTransact with the SQL_ROLLBACK setting to roll back any uncommitted transaction.

5. Call the ODBC function SQLDisconnect to disconnect from the TimesTen database.

6. Call the ODBC function SQLFreeConnect to free memory allocated for the ODBC connection handle.

7. Call the ODBC function SQLFreeEnv to free the ODBC environment handle.

For the second approach, maintaining the bookmark, skip the first two steps but complete the remaining steps.

Be aware that resources should be freed in reverse order of allocation. For example, the ODBC environment handle is allocated before the ODBC connection handle, so for cleanup free the connection handle before the environment handle.

void TerminateGracefully(int status)
{
 
  SQLRETURN     rc;
  SQLINTEGER    native_error ; 
  SQLINTEGER    oldstatus;
  SQLINTEGER    newstatus = 0;
    
  /* If the table has been subscribed to through XLA, unsubscribe it. */
 
  if (SYSTEM_TABLE_ID != 0) {
    rc = ttXlaTableStatus(xla_handle, SYSTEM_TABLE_ID, 0,
                          &oldstatus, &newstatus);
    if (rc != SQL_SUCCESS) {
      handleXLAerror (rc, xla_handle, err_buf, &native_error);
      fprintf(stderr, "Error when unsubscribing from "TABLE_OWNER"."TABLE_NAME
              " table <%d>: %s", rc, err_buf);
    }
    SYSTEM_TABLE_ID = 0;
  }
 
  /* Close the XLA connection. */
 
  if (xla_handle != NULL) {
    rc = ttXlaClose(xla_handle);
    if (rc != SQL_SUCCESS) {
      fprintf(stderr, "Error when disconnecting from XLA:<%d>", rc);
    }
    xla_handle = NULL;
  }
 
  if (hstmt != SQL_NULL_HSTMT) {
    rc = SQLFreeStmt(hstmt, SQL_DROP);
    if (rc != SQL_SUCCESS) {
      handleError(rc, henv, hdbc, hstmt, err_buf, &native_error);
      fprintf(stderr, "Error when freeing statement handle:\n%s\n", err_buf);
    }
    hstmt = SQL_NULL_HSTMT;
  }
 
  /* Disconnect from TimesTen entirely. */
    
  if (hdbc != SQL_NULL_HDBC) {
    rc = SQLTransact(henv, hdbc, SQL_ROLLBACK);
    if (rc != SQL_SUCCESS) {
      handleError(rc, henv, hdbc, hstmt, err_buf, &native_error);
      fprintf(stderr, "Error when rolling back transaction:\n%s\n", err_buf);
    }
 
    rc = SQLDisconnect(hdbc);
    if (rc != SQL_SUCCESS) {
      handleError(rc, henv, hdbc, hstmt, err_buf, &native_error);
      fprintf(stderr, "Error when disconnecting from TimesTen:\n%s\n", err_buf);
    }
 
    rc = SQLFreeConnect(hdbc);
    if (rc != SQL_SUCCESS) {
      handleError(rc, henv, hdbc, hstmt, err_buf, &native_error);
      fprintf(stderr, "Error when freeing connection handle:\n%s\n", err_buf);
    }
    hdbc = SQL_NULL_HDBC;
  }
 
  if (henv != SQL_NULL_HENV) {
    rc = SQLFreeEnv(henv);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
      handleError(rc, henv, hdbc, hstmt, err_buf, &native_error);
      fprintf(stderr, "Error when freeing environment handle:\n%s\n", err_buf);
    }
    henv = SQL_NULL_HENV;
  }
  exit(status);
}

Checking table compatibility between databases

Before transferring update records from one database to the other, verify that the tables in the master and subscriber databases are compatible with one another:

• You can check the descriptions of a table and its columns by using the ttXlaTableByName, ttXlaGetTableInfo, and ttXlaGetColumnInfo functions. See "Checking table and column descriptions" immediately below.

• You can check the table and column versions of a specific XLA record by using the ttXlaVersionTableInfo and ttXlaVersionColumnInfo functions. See "Checking table and column versions", following shortly.

SQLINTEGER compat;
ttXlaTblDesc_t table;
ttXlaColDesc_t columns[20];

rc = ttXlaTableCheck(xla_handle, &table, columns, &compat);
if (compat) {
    /* Go ahead and start replicating */
}
else {
    /* Not compatible or some other error occurred */
}

BOOL CUTLCheckXlaTable (SCOMMAND* pCmd,
                        ttXlaHandle_h hXlaTarget,
                        const ttXlaUpdateDesc_t* pXlaRecord)
{
  /* locals */
  ttXlaTblVerDesc_t tblVerDescSource;
  ttXlaColDesc_t colDescSource [255];
  SQLINTEGER iColsReturned = 0;
  SQLINTEGER iCompatible = 0;
  SQLRETURN rc;

  /* only certain update record types should be checked */
  if (pXlaRecord->type == INSERTTUP ||
      pXlaRecord->type == UPDATETUP ||
      pXlaRecord->type == DELETETUP)
  {
     /* Get source table description associated with this record */
     /* from the time it was generated. */
     rc = ttXlaVersionTableInfo (pCmd->pCtx->con->hXla,
             (ttXlaUpdateDesc_t*) pXlaRecord, &tblVerDescSource);

     if (rc == SQL_SUCCESS)
     {
         /* Get the source column descriptors for this table */
         /* at the time the record was generated. */
         rc = ttXlaVersionColumnInfo (pCmd->pCtx->con->hXla,
                 (ttXlaUpdateDesc_t*) pXlaRecord,
                 colDescSource, 255, &iColsReturned);

         if (rc == SQL_SUCCESS)
         {
             /* Check compatibility. */
             rc = ttXlaTableCheck (hXlaTarget,
                     &tblVerDescSource.tblDesc, colDescSource,
                     &iCompatible);
         }
     }
  }
}

Replicating updates between databases

When you are ready to begin replication, use the ttXlaNextUpdate or ttXlaNextUpdateWait function to obtain batches of update records from the master database and ttXlaApply to write the records to the subscriber database. The following example shows this.

int j;
ttXlaHandle_h h;
SQLINTEGER records;
ttXlaUpdateDesc_t** arry;

  do {
    /* get up to 15 updates */
    rc = ttXlaNextUpdate(h,&arry,15,&records);
    if (rc != SQL_SUCCESS) {
      /* See "Handling XLA errors" */
    }
 
    /* print number of updates returned */
    printf("Records returned by ttXlaNextUpdate : %d\n",records);
 
    /* apply the received updates */  
    for (j=0;j < records;j++) {
      ttXlaUpdateDesc_t* p;
 
      p = arry[j];
      rc = ttXlaApply(h, p, 0);
      if (rc != SQL_SUCCESS){
      /* See "Handling XLA errors" and */
      /* "Handling timeout and deadlock errors" below */
      }
    }
 
    /* print number of updates applied */
    printf("Records applied successfully : %d\n",records);
 
  } while (records != 0);

Handling timeout and deadlock errors

The return code from ttXlaApply indicates whether the update was successful. If the return code is not SQL_SUCCESS, then the update may have encountered a transient problem, such as a deadlock or timeout, or a persistent problem. You can use ttXlaError to check for errors, such as tt_ErrDeadlockVictim or tt_ErrTimeoutVictim. Recovery from transient errors is possible by rolling back the replicated transaction and reexecuting it. Other errors may be persistent, such as those for duplicate key violations or key not found. Such errors are likely to repeat if the transaction is reexecuted.

If ttXlaApply returns a timeout or deadlock error before applying the commit record (ttXlaUpdateDesc_t ->flags = TT_UPDCOMMIT) for a transaction to the subscriber database, you can do either of the following:

• Use ttXlaRollback to roll back the transaction.

• Use ttXlaCommit to commit the changes in the records that have been applied to the subscriber database.

To enable recovery from transient errors, you should keep track of transaction boundaries on the master database and store the records associated with the transaction currently being applied to the subscriber in a user buffer, so you can reapply them if necessary. The transaction boundaries can be found by checking the flags member of the ttXlaUpdateDesc_t structure. Consider the following example. If this condition is true, then the record was committed:

(pXlaRecords [iRecordIndex]->flags & TT_UPDCOMMIT)

If you encounter an error that requires you to roll back a transaction, call ttXlaRollback to roll back the records applied to the subscriber database. Then call ttXlaApply to reapply all the rolled back records stored in your buffer.

Checking for update conflicts

If you have applications making simultaneous updates to both your master and subscriber databases, you may encounter update conflicts. Update conflicts are described in detail in "Resolving Replication Conflicts" in Oracle TimesTen In-Memory Database Replication Guide.

To check for update conflicts in XLA, you can set the ttXlaApply test parameter to compare the old row value (ttXlaUpdateDesc_t ->tuple1) in each record of type UPDATETUP with the existing row in the subscriber database. If the old row value in the update description does not match the corresponding row in the subscriber database, an update conflict is probably the reason. In this case, ttXlaApply does not apply the update to the subscriber and returns an sb_ErrXlaTupleMismatch error.

Replicating updates to a non-TimesTen database

If you are replicating changes to a non-TimesTen database, you can use the ttXlaGenerateSQL function to convert the record data into a SQL statement that can be read by the non-TimesTen subscriber. For update and delete records, ttXlaGenerateSQL requires a primary key or a unique index on a non-nullable column to generate the correct SQL.

The ttXlaGenerateSQL function accepts a ttXlaUpdateDesc_t record as a parameter and outputs its SQL equivalent into a buffer.

ttXlaUpdateDesc_t record;
char buffer[200];
SQLINTEGER actualLength;

rc = ttXlaGenerateSQL(xla_handle, &record, buffer, 200, &actualLength);

if (rc != SQL_SUCCESS) {
    handleXLAerror (rc, xla_handle, err_buf, &native_error);
    if ( native_error == 8034 ) { // tt_ErrXlaNoSQL
      printf("Unable to translate to SQL\n");
    }
}

Changing the location of a bookmark

At any point during a connection, you can call the ttXlaGetLSN function to query the system for the Current Read log record identifier. If you must replay a set of updates, you can use the ttXlaSetLSN function to reset the Current Read log record identifier to any valid value larger than the Initial Read log record identifier set by the last ttXlaAcknowledge call. In this context, "larger" only applies if the log record identifiers being compared are from records in the same transaction. If that is not the case, then any log record identifier from a transaction that committed before another transaction is the "smaller" log record identifier, even if the numeric value of the log record identifier is larger. The only way to enable the Initial Read log record identifier to move forward to the Current Read log record identifier is by calling the ttXlaAcknowledge function, which indicates that you have received and processed all transaction log records up to the Current Read log record identifier. Once you have called ttXlaAcknowledge on a particular bookmark, you can no longer access transaction log records with a log record identifier smaller than the Current Read log record identifier.

Passing application context

Although it is not an XLA function, writers to the transaction log can call the ttApplicationContext built-in procedure to pass binary data associated with an application to XLA readers. This procedure specifies a single VARBINARY value that is returned in the next update record produced by the current transaction. XLA readers can obtain a pointer to this value as described in "Reading NOT INLINE variable-length column data".

To set the context:

1. Declare two program variables for invoking the ttApplicationContext procedure. The variable contextBuffer is a CHAR array that is declared to be large enough to accommodate the longest application context that you use. The variable contextBufferLen is of type INTEGER and is used to convey the actual length of the context on each call to ttApplicationContext.

2. Initialize a statement handle with a compiled invocation of the ttApplicationContext built-in procedure:

   rc = SQLPrepare(hstmt, "call ttApplicationContext(?)", SQL_NTS);
   rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
                         SQL_VARBINARY, 0, 0, &contextBuffer,
                         sizeof contextBuffer, &contextBufferLen);
   

3. When the application context must be set later, copy the context value into contextBuffer, assign the length of the context to contextBufferLen, and invoke ttApplicationContext with the call:

   rc = SQLExecute(hstmt);
   

   The transaction is then committed with the usual call on SQLTransact:

   rc = SQLTransact(NULL, hdbc, SQL_COMMIT);
   

   Note:

   If a SQL operation fails after a call to ttApplicationContext, the context may not be stored in the next SQL operation and therefore may be lost. If this happens, the application can call ttApplicationContext again before the next SQL operation.