Check the Status of Return Service Transactions

You can determine the status of a return service or to find out what the last returned status was.

Determine If Return Service Is Disabled

You can determine whether the return service for a particular subscriber has been disabled by the DISABLE RETURN failure policy by calling the ttRepSyncSubscriberStatus built-in procedure.

The ttRepSyncSubscriberStatus built-in procedure returns a value of '1' to indicate the return service has been disabled for the subscriber, or a value of '0' to indicate that the return service is still enabled.

This example uses ttRepSyncSubscriberStatus to obtain the return receipt status of the subscriberds database with respect to its master database, masterDSN.

> ttIsql masterDSN

Command> CALL ttRepSyncSubscriberStatus ('subscriberds');
< 0 >
1 row found.

This result indicates that the return service is still enabled.

See Disabling Return Service Blocking Manually.

Check Last Returned Status for a Return Service

You can check the status of the last return receipt or return twosafe transaction processed on the connection handle by calling the ttRepXactTokenGet and ttRepXactStatus built-in procedures.

First, call the ttRepXactTokenGet built-in procedure to get a unique token for the last return service transaction. If you are using return receipt, the token identifies the last return receipt transaction committed on the master database. If you are using return twosafe, the token identifies the last twosafe transaction on the master that, in the event of a successful commit on the subscriber, is committed by the replication agent on the master. However, in the event of a timeout or other error, the twosafe transaction identified by the token is not committed by the replication agent on the master.

Next, pass the token returned by ttRepXactTokenGet to the ttRepXactStatus built-in procedure to obtain the return service status. The output of the ttRepXactStatus built-in procedure reports which subscriber or subscribers are configured to receive the replicated data and the current status of the transaction (not sent, received, committed) with respect to each subscriber. If the subscriber replication agent encountered a problem applying the transaction to the subscriber database, the ttRepXactStatus built-in procedure also includes the error string. If you are using return twosafe and receive a timeout or other error, you can then decide whether to unconditionally commit or retry the commit. This is described in Using a Return Service.

Note:

If ttRepXactStatus is called without a token from ttRepXactTokenGet, it returns the status of the most recent transaction on the connection which was committed with the return receipt or return twosafe replication service.

The ttRepXactStatus built-in procedure returns the return service status for each subscriber as a set of rows formatted as:

subscriberName, status, error

You can call the ttRepXactTokenGet and ttRepXactStatus built-in procedures in a GetRSXactStatus function to report the status of each subscriber in your replicated system:

SQLRETURN GetRSXactStatus (HDBC hdbc)
{
  SQLRETURN rc = SQL_SUCCESS;
  HSTMT hstmt = SQL_NULL_HSTMT;
  char xactId [4001] = "";
  char subscriber [62] = "";
  char state [3] = "";

  /* get the last RS xact id processed on this connection */
  SQLAllocStmt (hdbc, &hstmt);
  SQLExecDirect (hstmt, "CALL ttRepXactTokenGet ('R2')", SQL_NTS);

  /* bind the xact id result as a null terminated hex string */
  SQLBindCol (hstmt, 1, SQL_C_CHAR, (SQLPOINTER) xactId,
    sizeof (xactId), NULL);

  /* fetch the first and only row */
  rc = SQLFetch (hstmt);

  /* close the cursor */
  SQLFreeStmt (hstmt, SQL_CLOSE);

  if (rc != SQL_ERROR && rc != SQL_NO_DATA_FOUND)
  {
    /* display the xact id */
    printf ("\nRS Xact ID: 0x%s\n\n", xactId);

    /* get the status of this xact id for every subscriber */
    SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
      SQL_VARBINARY, 0, 0,
     (SQLPOINTER) xactId, strlen (xactId), NULL);

    /* run */
    SQLExecDirect (hstmt, "CALL ttRepXactStatus (?)", SQL_NTS);

   /* bind the result columns */
   SQLBindCol (hstmt, 1, SQL_C_CHAR, (SQLPOINTER) subscriber,
     sizeof (subscriber), NULL);

   SQLBindCol (hstmt, 2, SQL_C_CHAR, (SQLPOINTER) state,
     sizeof (state), NULL);

   /* fetch the first row */
   rc = SQLFetch (hstmt);

   while (rc != SQL_ERROR && rc != SQL_NO_DATA_FOUND)
   {
     /* report the status of this subscriber */
     printf ("\n\nSubscriber: %s", subscriber);
     printf ("\nState: %s", state);

     /* are there more rows to fetch? */
     rc = SQLFetch (hstmt);
     }
  }

  /* close the statement */
  SQLFreeStmt (hstmt, SQL_DROP);

  return rc;
}