Show Subscriber Database Information

Replication uses the TimesTen transaction log to retain information that must be transmitted to subscriber sites. When communication to subscriber databases is interrupted or the subscriber sites are down, the transaction log data accumulates.

Part of the output from the queries described in this section enables you to see how much transaction log data has accumulated on behalf of each subscriber database and the amount of time since the last successful communication with each subscriber database.

Use the following methods to display information for subscriber databases:

Display Subscriber Status With the ttRepAdmin Utility

To display information about subscribers, use the ttRepAdmin utility with the -receiver -list options.

ttRepAdmin -dsn masterDSN -receiver -list

This example uses the ttRepAdmin utility to display information about the subscribers described in Multiple Subscriber Classic Replication Schemes.

> ttRepAdmin -dsn masterds -receiver -list
Peer name        Host name                Port   State   Proto
---------------- ------------------------ ------ ------- -----
subscriber1ds    server2                  Auto   Start      10

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
0:01:12       -             19.41 5       5        52    2

Peer name        Host name                Port   State   Proto
---------------- ------------------------ ------ ------- -----
subscriber2ds    server3                  Auto   Start      10

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
0:01:04       -             20.94         4        48    2

The first line of the display contains the subscriber definition. The following row of the display contains latency and rate information, as well as the number of transaction log files being retained on behalf of this subscriber. The latency for subscriber1ds is 19.41 seconds, and it is 2 logs behind the master. This is a high latency, indicating a problem if it continues to be high and the number of logs continues to increase.

Note:

See Subscriber Information.

If you have more than one scheme specified in the TTREP.REPLICATIONS table, you must use the -scheme option to specify which scheme you want to list. Otherwise you receive the following error:

Must specify -scheme to identify which replication scheme to use

See ttRepAdmin in the Oracle TimesTen In-Memory Database Reference.

Display Subscriber Status With the ttReplicationStatus Built-In Procedure

Within ttIsql, you can display status for a one or more subscriber databases by using the ttReplicationStatus built-in procedure, which reports only on the status of the subscribers for the master database on which this built-in procedure is called.

The following retrieves status for the subscriber master2 that is located on host1. If the host name is excluded, the subscriber is located solely on its name.

Command> call ttReplicationStatus('master2', 'host1');
< MASTER2, HOST1, 0, start, 1, 26, _ACTIVESTANDBY , TTREP >
1 row found.

The information shown is that the subscriber master2 located on host1 that is listening on an automatically assigned port. The TCP/IP port is used by the subscriber agent to receive updates from the master. However, since the value is zero, this indicates replication has automatically assigned the port.

This subscriber is in the start state. There is only one transaction log being held for this peer and 26 seconds have passed since the last replication. The name of the replication scheme is _ACTIVESTANDBY and the owner is TTREP.

If you do not provide either a subscriber or the subscriber host names, then the status for all subscribers of this master are returned. The following shows the same status as above, since there is only one subscriber set up for this master.

Command> call ttReplicationStatus();
< MASTER2, HOST1, 0, start, 1, 26, _ACTIVESTANDBY , TTREP >
1 row found.

See ttReplicationStatus in the Oracle TimesTen In-Memory Database Reference.

Display Information About Subscribers Through Querying Replication Tables

You can obtain the same information about a master's subscribers from a program by querying the TTREP.REPPEERS, TTREP.TTSTORES, and SYS.MONITOR tables with a SELECT statement.

SELECT t1.tt_store_name, t1.host_name, t1.rep_port_number,
p.state, p.protocol, p.timesend, p.timerecv, p.latency,
p.tps, p.recspersec, t3.last_log_file - p.sendlsnhigh + 1
  FROM ttrep.reppeers p, ttrep.ttstores t1, ttrep.ttstores t2, sys.monitor t3
  WHERE p.tt_store_id = t1.tt_store_id
    AND t2.is_local_store = 0X01
    AND p.subscriber_id = t2.tt_store_id
    AND p.replication_name = 'repscheme'
    AND p.replication_owner = 'repl'
    AND (p.state = 0 OR p.state = 1);

The following is sample output from the 3 statement above:

< subscriber1ds, server2, 0, 0, 7, 1003941635, 0, -1.00000000000000, -1, -1, 1 >
< subscriber2ds, server3, 0, 0, 7, 1003941635, 0, -1.00000000000000, -1, -1, 1 >

See Subscriber Information.

Subscriber Information

The output from either the ttRepAdmin utility or the SELECT statement contains fields describing the subscriber database.

Field	Description
`Peer name`	Name of the subscriber database
`Host name`	Name of the machine that hosts the subscriber.
`Port`	TCP/IP port used by the subscriber agent to receive updates from the master. A value of 0 indicates replication has automatically assigned the port.
`State`	Current replication state of the subscriber with respect to its master database (see Show Subscriber Database Information).
`Protocol`	Internal protocol used by replication to communicate between this master and its subscribers. You can ignore this value.
`Last message sent`	Time (in seconds) since the master sent the last message to the subscriber. This includes the "heartbeat" messages sent between the databases.
`Last message received`	Time (in seconds) since this subscriber received the last message from the master.
`Latency`	The average latency time (in seconds) between when the master sends a message and when it receives the final acknowledgement from the subscriber. (See note below.)
`Transactions per second`	The average number of transactions per second that are committed on the master and processed by the subscriber. (See note below.)
`Records per second`	The average number of transmitted records per second. (See note below.)
`Logs`	Number of transaction log files the master database is retaining for a subscriber.

Note:

Latency, TPS, and RecordsPS report averages detected while replicating a batch of records. These values can be unstable if the workload is not relatively constant. A value of -1 indicates the master's replication agent has not yet established communication with its subscriber replication agents or sent data to them.