Using XLA as a Replication Mechanism

This section discusses using XLA as a substitute for TimesTen replication.

• About XLA as a Replication Mechanism

• Checking Table Compatibility Between Databases

• Replicating Updates Between Databases

• Handling Timeout and Deadlock Errors

• Checking for Update Conflicts

• Replicating Updates to a Non-TimesTen Database

XLA functions mentioned here are documented in XLA Reference.

About XLA as a Replication Mechanism

TimesTen replication 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. See Overview of TimesTen Replication in the Oracle TimesTen In-Memory Database Replication Guide

Note:

You cannot use XLA to replicate updates between different platforms.

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, the sections that follow describe these parts of the process:

• Check table compatibility between databases.

• Replicate updates between databases.

• Handle timeout and deadlock errors.

• Check for update conflicts.

Throughout the discussion, the sending database is referred to as the master and the receiving database as the subscriber.

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.

• You can check the table and column versions of a specific XLA record by using the ttXlaVersionTableInfo and ttXlaVersionColumnInfo functions.

These approaches are described in the sections that follow:

• Checking Table and Column Descriptions

• Checking Table and Column Versions

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.

See ttXlaTblDesc_t, ttXlaColDesc_t, 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.

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.

See ttXlaVersionTableInfo and ttXlaVersionColumnInfo.

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

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.

See ttXlaNextUpdate, ttXlaNextUpdateWait, and ttXlaApply.

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 XLA Error Handling */
    }
 
    /* 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 XLA Error Handling and */
      /* Handling Timeout and Deadlock Errors below */
      }
    }
 
    /* print number of updates applied */
    printf("Records applied successfully : %d\n",records);
 
  } while (records != 0);

Tip:

• To ensure that you are sending XLA updates between databases that have compatible versions of XLA records, use the ttXlaGetVersion and ttXlaVersionCompare functions on all databases.

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

See ttXlaApply.

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.

See ttXlaGenerateSQL.

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

Note:

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.

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