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