Skip Headers
Oracle® TimesTen In-Memory Database C Developer's Guide
11g Release 2 (11.2.2)

E21637-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

5 XLA and TimesTen Event Management

The Transaction Log API (XLA) is a set of C language functions that enable you to implement applications to perform the following:

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

Note:

In the unlikely event that TimesTen replication solutions described in Oracle TimesTen In-Memory Database Replication Guide do not meet your needs, it is possible to use XLA functions to build a custom data replication solution.

This chapter includes the following topics:

For a complete description of each XLA function, see Chapter 9, "XLA Reference".

XLA concepts

This section includes the following topics:

XLA functions mentioned here are documented in Chapter 9, "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. 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 follows
Description of "Figure 5-1 Records extracted from the transaction log"

Example 5-1 Reading transaction log records

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. 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:

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

As described in "Initializing XLA and obtaining an XLA handle", 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. At this time, 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 for related information. 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, 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 follows
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 follows
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 follows
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. 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.

When you use replicated bookmarks, steps must be taken in the following order:

  1. 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.)

  2. Create the bookmarks.

  3. Subscribe the bookmarks.

  4. 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 normal 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

You should be aware that 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 disk space being filled.

For information about monitoring and addressing this situation, see "Monitoring accumulation of transaction log files" in Oracle TimesTen In-Memory Database Operations Guide.

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.

Table 5-1 XLA data type mapping

Internal SQL data type XLA data type before Release 7.0 XLA data type since Release 7.0

TT_CHAR

SQL_CHAR

TTXLA_CHAR_TT

TT_VARCHAR

SQL_VARCHAR

TTXLA_VARCHAR_TT

TT_NCHAR

SQL_WCHAR

TTXLA_NCHAR_TT

TT_NVARCHAR

SQL_WVARCHAR

TTXLA_NVARCHAR_TT

CHAR

-

TTXLA_CHAR

NCHAR

-

TTXLA_NCHAR

VARCHAR2

-

TTXLA_VARCHAR

NVARCHAR2

-

TTXLA_NVARCHAR

TT_TINYINT

SQL_TINYINT

TTXLA_TINYINT

TT_SMALLINT

SQL_SMALLINT

TTXLA_SMALLINT

TT_INTEGER

SQL_INTEGER

TTXLA_INTEGER

TT_BIGINT

SQL_BIGINT

TTXLA_BIGINT

BINARY_FLOAT

SQL_REAL

TTXLA_BINARY_FLOAT

BINARY_DOUBLE

SQL_DOUBLE

TTXLA_BINARY_DOUBLE

TT_DECIMAL

SQL_DECIMAL

TTXLA_DECIMAL_TT

NUMBER

-

TTXLA_NUMBER

NUMBER(p,s)

-

TTXLA_NUMBER

FLOAT

-

TTXLA_NUMBER

TT_TIME

SQL_TIME

TTXLA_TIME

TT_DATE

SQL_DATE

TTXLA_DATE_TT

TT_TIMESTAMP

SQL_TIMESTAMP

TTXLA_TIMESTAMP_TT

DATE

-

TTXLA_DATE

TIMESTAMP

-

TTXLA_TIMESTAMP

TT_BINARY

SQL_BINARY

TTXLA_BINARY

TT_VARBINARY

SQL_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:

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.

Writing an XLA event-handler application

This section describes the general procedures for writing an XLA application that detects and reports changes to selected tables in a database. With the possible exception of "Inspecting column data", the procedures described in this section are applicable to most XLA applications.

The following procedures are described:

The example code in this section is based on the xlaSimple demo application.

XLA functions mentioned here are documented in Chapter 9, "XLA Reference".

Important:

In addition to files noted in "TimesTen include files", an XLA application must include tt_xla.h.

Note:

To simplify the code examples, routine error checking code for each function call has been omitted. See "Handling XLA errors" for information on error handling.

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);

Note:

After an ODBC connection handle is opened for use by an XLA application, the ODBC handle cannot be used for ODBC operations until the corresponding XLA handle is closed by calling ttXlaClose.

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.

Note:

LOB support in XLA is limited, as follows:
  • You can subscribe to tables containing LOB columns, but information about the LOB value itself is unavailable.

  • ttXlaGetColumnInfo returns information about LOB columns.

  • 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.

Example 5-2 Specifying a table to monitor for updates

This example tracks changes to the MYDATA table:

#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.

Note:

The ttXlaAcknowledge function is an expensive operation and should be used only as necessary.

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.

Note:

This example is simplified for clarity. An actual XLA application would likely read records for multiple transactions before calling ttXlaAcknowledge.

Figure 5-5 Update records

Description of Figure 5-5 follows
Description of "Figure 5-5 Update records"

Example 5-3 Retrieving update records from the transaction log

The xlaSimple demo continues to monitor our table for updates until stopped by the user.

Before calling ttXlaNextUpdateWait, the example initializes a pointer to the buffer to hold the returned ttXlaUpdateDesc_t records (arry) and a variable to hold the actual number of returned records (records). Because the example calls ttXlaNextUpdateWait, it also specifies the number of seconds to wait (FETCH_WAIT_SECS) if no records are found in the transaction log buffer.

Next, call ttXlaNextUpdateWait, passing these values to obtain a batch of ttXlaUpdateDesc_t records in arry. Then process each record in arry by passing it to the HandleChange() function described in Example 5-4. After all records are processed, call ttXlaAcknowledge to reset the bookmark position.

#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 follows
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.

Note:

For optimal throughput, XLA applications should make the "fetch" and "process record" procedures asynchronous. For example, you can create one thread to fetch and store the records and one or more other threads to process the stored records.

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 follows
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.

Example 5-4 Inspecting record headers for SQL operation type

This example passes each record returned by the ttXlaNextUpdateWait function to a HandleChange() function, which determines whether the record is related to an INSERT, UPDATE, or CREATE VIEW operation. To keep this example simple, all other operations are ignored.

The HandleChange() function handles each type of SQL operation differently before calling the PrintColValues() function described in Example 5-13.

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 follows
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

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 follows
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 follows
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 follows
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 (8-byte) type on 64-bit systems and a 32-bit (4-byte) type on 32-bit systems. This is true on most UNIX systems; however, on Windows 64-bit systems long is a 4-byte type.

Null-terminating returned strings

Strings returned from record row data are not terminated with a null character. You can null-terminate a string by copying it into a buffer and adding a null character, '\0', after the last character in the string.

The procedures for null-terminating fixed-length and variable-length strings are slightly different. The procedure for null-terminating fixed-length strings is described in Example 5-8. Example 5-9 that follows describes the procedure for null-terminating variable-length strings of a known size. Example 5-10 then describes the procedure for strings of an unknown size.

Example 5-8 Null-terminating fixed-length strings

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

To null-terminate the fixed-length CHAR(10) Column1 string returned in Example 5-6, establish a buffer large enough to hold the string plus null character. Next, obtain the size of the string from ttXlaColDesc_t ->size, copy the string into the buffer, and null-terminate the end of the string, using computations such as the following. You can now use the contents of the buffer. In this example, the string is printed:

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);

Null-terminating a variable-length string is similar to the procedure for fixed-length strings, only the size of the string is the value located at the beginning of the variable-length data offset, as described in "Reading NOT INLINE variable-length column data".

Example 5-9 Null-terminating variable-length strings of known size

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

If the Column2 string obtained in Example 5-7 is a VARCHAR(32), establish a buffer large enough to hold the string plus null character. Use the value located at the DataLength offset to determine the size of the string, using computations such as the following:

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);

If you are writing general purpose code to read all data types, you cannot make any assumptions about the size of a returned string. For strings of an unknown size, statically allocate a buffer large enough to hold the majority of returned strings. If a returned string is larger than the buffer, dynamically allocate the correct size buffer, as shown in Example 5-10.

Example 5-10 Null-terminating variable-length strings of unknown size

If the Column2 string obtained in Example 5-7 is of an unknown size, you might statically allocate a buffer large enough to hold a string of up to 10000 characters. Then check that the DataLength value obtained at the beginning of the variable-length data offset is less than the size of the buffer. If the string is larger than the buffer, use malloc() to dynamically allocate the buffer to the correct size.

#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);

Converting complex data types

Values for complex data types such as TT_DATE, TT_TIME, and TT_DECIMAL are stored in an internal TimesTen format that can be converted into corresponding ODBC C types using the XLA type conversion functions. Table 5-2 contains descriptions of these conversion functions.

Table 5-2 XLA data type conversion functions

Function Converts

ttXlaDateToODBCCType

Internal TT_DATE value to an ODBC C value

ttXlaTimeToODBCCType

Internal TT_TIME value to an ODBC C value

ttXlaTimeStampToODBCCType

Internal TT_TIMESTAMP value to an ODBC C value

ttXlaDecimalToCString

Internal TT_DECIMAL value to a string value

ttXlaDateToODBCCType

Internal TTXLA_DATE_TT value to an ODBC C value

ttXlaDecimalToCString

Internal TTXLA_DECIMAL_TT value to a character string

ttXlaNumberToBigInt

Internal TTXLA_NUMBER value to a TT_BIGINT value

ttXlaNumberToCString

Internal TTXLA_NUMBER value to a character string

ttXlaNumberToDouble

Internal TTXLA_NUMBER value to a long floating point number value

ttXlaNumberToInt

Internal TTXLA_NUMBER value to an integer

ttXlaNumberToSmallInt

Internal TTXLA_NUMBER value to a TT_SMALLINT value

ttXlaNumberToTinyInt

Internal TTXLA_NUMBER value to a TT_TINYINT value

ttXlaNumberToUInt

Internal TTXLA_NUMBER value to an unsigned integer

ttXlaOraDateToODBCTimeStamp

Internal TTXLA_DATE value to an ODBC timestamp

ttXlaOraTimeStampToODBCTimeStamp

Internal TTXLA_TIMESTAMP value to an ODBC timestamp

ttXlaTimeToODBCCType

Internal TTXLA_TIME value to an ODBC C value

ttXlaTimeStampToODBCCType

Internal TTXLA_TIMESTAMP_TT value to an ODBC C value


These conversion functions can be used on row data in the ttXlaUpdateDesc_t types: UPDATETUP, INSERTTUP and DELETETUP.

Example 5-11 Converting complex data types

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

If you use the address of the tup1 row obtained earlier in the HandleChange() function (Example 5-4) and the offset from the fifth ttXlaColDesc_t structure returned by the ttXlaGetColumnInfo function (Example 5-5), you can locate a column value of type TIMESTAMP. Use the ttXlaTimeStampToODBCCType function to convert the column data from TimesTen format and store the converted time value in an ODBC TIMESTAMP_STRUCT. You could use code such as the following to print the values:

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);

If you use the address of the tup1 row obtained earlier in the HandleChange() function (Example 5-4) and the offset from the sixth ttXlaColDesc_t structure returned by the ttXlaGetColumnInfo function (Example 5-5), you can locate a column value of type DECIMAL. Use the ttXlaDecimalToCString function to convert the column data from TimesTen decimal format to a string. You could use code such as the following to print the values.

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);

Detecting null values

For nullable table columns, ttXlaColDesc_t ->nullOffset points to the column's null byte in the record. This field is 0 (zero) if the column is not nullable, or greater than 0 if the column can be null.

For nullable columns (ttXlaColDesc_t ->nullOffset > 0), to determine if the column is null, add the null offset to the address of ttXlaUpdate_t* and check the (unsigned char) byte there to see if it is 1 (NULL) or 0 (NOT NULL).

Example 5-12 Detecting null values

Check whether Column6 is null as follows:

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

Putting it all together: a PrintColValues() function

Example 5-13 shows a function that checks the ttXlaColDesc_t ->dataType of each column to locate columns with a data type of CHAR, NCHAR, INTEGER, TIMESTAMP, DECIMAL, and VARCHAR, then prints the values. This is just one possible approach. Another option, for example, would be to check the ttXlaColDesc_t ->ColName values to locate specific columns by name.

The PrintColValues() function handles CHAR and VARCHAR strings up to 50 bytes in length. NCHAR characters must belong to the ASCII character set.

Example 5-13 Complete PrintColValues() function

The function in this example first checks ttXlaColDesc_t ->nullOffset to see if the column is null. Next it checks the ttXlaColDesc_t ->dataType field to determine the data type for the column. For simple fixed-length data (CHAR, NCHAR, and INTEGER), it casts the value located at ttXlaColDesc_t ->offset to the appropriate C type. The complex data types, TIMESTAMP and DECIMAL, are converted from their TimesTen formats to ODBC C values using the ttXlaTimeStampToODBCCType and ttXlaDecimalToCString functions.

For variable-length data (VARCHAR), the function locates the data in the variable-length portion of the row, as described in "Handling XLA errors".

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 */
}

Notes:

  • In the preceding example, var_len is type long, assumed to be a 64-bit (8-byte) type on 64-bit systems and a 32-bit (4-byte) type on 32-bit systems. This is true on most UNIX systems; however, on Windows 64-bit systems long is a 4-byte type.

  • See "Terminating an XLA application" for a sample TerminateGracefully() method.

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".

Example 5-14 Checking the return code and calling the error-handling function

This example, after calling the XLA function ttXlaTableByName, checks to see if the return code is SQL_SUCCESS. If not, it calls an XLA error-handling function followed by a function to terminate the application. See "Terminating an XLA application".

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.

Note:

In cases where ttXlaPersistOpen cannot create an XLA handle, it returns the error code SQL_INVALID_HANDLE. Because no XLA handle has been created, ttXlaError cannot be used to detect this error. SQL_INVALID_HANDLE is returned only in cases where no memory can be allocated or the parameters provided are invalid.

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

Table 5-3 XLA errors and codes

Error Code

tt_ErrDbAllocFailed

802 (transient)

tt_ErrCondLockConflict

6001 (transient)

tt_ErrDeadlockVictim

6002 (transient)

tt_ErrTimeoutVictim

6003 (transient)

tt_ErrPermSpaceExhausted

6220 (transient)

tt_ErrTempSpaceExhausted

6221 (transient)

tt_ErrBadXlaRecord

8024

tt_ErrXlaBookmarkUsed

8029

tt_ErrXlaLsnBad

8031

tt_ErrXlaNoSQL

8034

tt_ErrXlaNoLogging

8035

tt_ErrXlaParameter

8036

tt_ErrXlaTableDiff

8037

tt_ErrXlaTableSystem

8038

tt_ErrXlaTupleMismatch

8046

tt_ErrXlaDedicatedConnection

8047


Example 5-15 Calling the handleXLAerror() function

This example shows handleXLAerror(), the error function for the xlaSimple demo program.

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.

Notes:

  • You cannot delete replicated bookmarks while the replication agent is running.

  • When you reuse a bookmark, you start with the Initial Read log record identifier in the transaction log file. To ensure that a connection that reuses a bookmark begins reading where the prior connection left off, the prior connection should call ttXlaAcknowledge to reset the bookmark position to the currently accessed record before disconnecting.

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

  • Be aware that ttCkpt and ttCkptBlocking require ADMIN privilege. TimesTen built-in procedures and any required privileges are documented in "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.

Example 5-16 Deleting bookmarks

The InitHandler() function in the xlaSimple demo deletes the XLA bookmark upon exit, as shown in the following example.

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". */

If the application is not connected and using the XLA bookmark, you can delete the bookmark either of the following ways:

  • Close the bookmark and call the ttXlaBookmarkDelete built-in procedure.

  • Close the bookmark and use the ttIsql command xladeletebookmark.

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.

Example 5-17 Terminating an XLA application

This example shows TerminateGracefully(), the termination function in the xlaSimple Quick Start demo.

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);
}

Using XLA as a replication mechanism

TimesTen replication as described in Oracle TimesTen In-Memory Database Replication Guide is sufficient for most customer needs; however, it is also possible to use XLA functions to replicate updates from one database to another. Implementing your own replication scheme on top of XLA in this way is fairly complicated, but can be considered if TimesTen replication is not feasible for some reason.

Note:

You cannot use XLA to replicate updates between different platforms or between 32-bit and 64-bit versions of the same platform.

In this section, the sending database is referred to as the master and the receiving database as the subscriber. To use XLA to replicate changes between databases, first use the ttXlaPersistOpen function to initialize the XLA handles, as described in "Initializing XLA and obtaining an XLA handle".

After the XLA handles have been initialized for the databases, take the steps described in the following sections:

XLA functions mentioned here are documented in Chapter 9, "XLA Reference".

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:

Checking table and column descriptions

Use the ttXlaTableByName, ttXlaGetTableInfo, and ttXlaGetColumnInfo functions to return ttXlaTblDesc_t and ttXlaColDesc_t descriptions for each table you want to replicate. These operations are described in "Specifying which tables to monitor for updates" and "Obtaining column descriptions". You can then pass these descriptions to the ttXlaTableCheck function. The output parameter, compat, specifies whether the tables are compatible. A value of 1 indicates compatibility and 0 indicates non-compatibility. The following example demonstrates this.

Example 5-18 Checking table and column descriptions for compatibility

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 */
}

Checking table and column versions

Use the ttXlaVersionTableInfo and ttXlaVersionColumnInfo functions to retrieve the table structure information of an update record at the time the record was generated.

The following example verifies that the table associated with the pXlaRecord update record from the pCmd source is compatible with the hXlaTarget target.

Example 5-19 Checking table and column versions for compatibility

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.

Example 5-20 Replicating updates between databases

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);

Important:

If you are packaging data to be replicated across a network, or anywhere between processes not using the same memory space, you must ensure that the ttXlaUpdateDesc_t data structure is shipped in its entirely. Its length is indicated by ttXlaUpdateDesc_t ->header.length, where the header element is a ttXlaNodeHdr_t structure that in turn has a length element. Also see "ttXlaUpdateDesc_t" and "ttXlaNodeHdr_t".

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.

Note:

An alternative to buffering the transaction records in a user buffer is to call ttXlaGetLSN to get the transaction log record identifier of each commit record in the transaction log, as described in "Changing the location of a bookmark". If you encounter an error that requires you to roll back a transaction, you can call ttXlaSetLSN to reset the bookmark to the beginning of the transaction in the transaction log and reapply the records. However, the extra overhead associated with the ttXlaGetLSN function may make this a less efficient option.

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.

Important:

The SQL returned by ttXlaGenerateSQL uses TimesTen SQL syntax. The SQL statement may fail on a non-TimesTen subscriber if there are SQL syntax incompatibilities between the two systems. In addition, the SQL statement is encoded in the connection character set associated with the XLA handle.

Example 5-21 Replicating updates to a non-TimesTen database

This example translates a record (record) and stores the resulting SQL output in a 200-character buffer (buffer). The actual size of the buffer is returned in the actualLength parameter.

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");
    }
}

Other XLA features

The following sections describe how to use additional XLA features:

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".

Note:

A context value is applied to only one update record. After it has been applied it is reset. If the same context value should be applied to multiple updates, then it must be reestablished before each update.

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.