2 Replication Tables

TimesTen stores metadata about replication in replication tables in your database.

Your applications can read the replication tables, but it cannot update them. If your application defines a table with the same name as a replication table, then your application can read a replication table by prefixing the replication table name with TTREP. For example, SELECT * FROM TTREP.REPTABLES selects rows from the REPTABLES replication table.

Information specific to replication tables:

Locks acquired by users on replication tables may prevent others from defining data or executing the SQLPrepare ODBC function or the Connection.prepareStatement JDBC method.
The last character in name columns is always a space. Therefore, while the column length for name columns is 31, the maximum object name length is 30.
On 64-bit systems, TimesTen replication tables declare certain fields as data type TT_BIGINT. When retrieving these columns with an ODBC program, the application must bind them using SQL_C_BINARY. For information about SQL_C_BINARY, see ODBC documentation.

Note:
Some tables contain columns named SYSnumber. Because these columns contain values used internally by TimesTen, they are not documented in this chapter.

Replication tables reserved for internal or future use

The TTREP.CLIENTFAILOVER table is reserved for internal or future use.

Required privileges to access replication tables

By default PUBLIC has SELECT privileges on various system and replication tables and EXECUTE privileges on various PL/SQL objects. You can see the list of objects by using this query:

SELECT * FROM sys.dba_tab_privs WHERE grantee='PUBLIC';

The ADMIN or SELECT ANY TABLE privilege is required to access other system and replication tables and views.

TTREP.REPELEMENTS

The REPELEMENTS table describes elements in a replication scheme.

Columns

Column name	Type	Description
`REPLICATION_NAME`	`TT_CHAR(31) NOT NULL`	Name for a replication scheme
`REPLICATION_OWNER`	`TT_CHAR(31) NOT NULL`	The replication scheme's owner
`ELEMENT_NAME`	`TT_CHAR(31) NOT NULL`	The replication name for this element This is logically different from the `DS_OBJ_NAME` of the underlying data base object. For example, the `ELEMENT_NAME` for a replicated table may differ from the table name. This name must be unique in a replication scheme.
`ELEMENT_TYPE`	`TT_CHAR(1) NOT NULL`	The type of this replication element '`T`' – table '`D`' – database '`S`' – sequence
`OWNED_BY_SYSTEM`	`BINARY(1) NOT NULL`	`0x01` - if element is maintained by the system and cannot be directly referenced by SQL statements `0x00` - if element is defined and maintained by a user
`MASTER_ID`	`TT_BIGINT NOT NULL`	The `TT_STORE_ID` for the master or propagator of this element
`OLD_MASTER_ID`	`TT_BIGINT NOT NULL`	The `TT_STORE_ID` for the immediately preceding `MASTER` for this element -1 if none
`IS_PROPAGATOR`	`BINARY(1) NOT NULL`	Propagator flag 0 - The `MASTER_ID` identifies a true `MASTER` database. 1 - The `MASTER_ID` identifies a `PROPAGATOR`.
`DS_OBJ_NAME`	`TT_CHAR(31) NOT NULL`	If this replication refers to a single, underlying data base object: name of the object Specifically, it is the name of the replicated table if `ELEMENT_TYPE = 'T'`. It is `NULL` if `ELEMENT_TYPE = 'D'`. `DS_OBJ_OWNER.DS_OBJ_NAME` need not be unique in a replication scheme, but each occurrence must be associated with a distinct `ELEMENT_NAME`.
`DS_OBJ_OWNER`	`TT_CHAR(31) NOT NULL`	The owner of the replication element – if defined `NULL` otherwise This is always the owner of the table. `DS_OBJ_OWNER.DS_OBJ_NAME` need not be unique in a replication scheme, but each occurrence must be associated with a distinct `ELEMENT_NAME`.
`DS_OBJ_ID`	`TT_INTEGER` for 32-bit systems `TT_BIGINT` for 64 bit systems	Object ID or flag If the `ELEMENT_TYPE = 'T'`: Table ID - If the table is in the owning (master or propagator) database, then this is the table ID. 1 - Table is in the subscriber database. If the `ELEMENT_TYPE = 'D'`: 0 - Database is a master or propagator. 1 - Database is a subscriber. `NULL` - Database has been migrated, restored, or upgraded from an earlier version.
`DURABLE_TRANSMIT`	`BINARY(1) NOT NULL`	Durable transaction status 0 - Transactions are not made durable before they are transmitted. 1 - Transactions are made durable before they are transmitted (default).
`CONFLICT_CHECKS`	`BINARY(8) NOT NULL`	A bit map indicating which conflict detectors are enabled 0x0000000000000000 - no configured conflict detector (default) 0x0000000000000001 - `ROW TIMESTAMP` conflict detector
`TS_COLUMN_NAME`	`TT_CHAR(31)`	The name of the timestamp column specified in the `CHECK CONFLICT`S clause of a `CREATE REPLICATION` statement This column must be of type `BINARY(8)` and permit `NULL` values.
`TS_EXCEPTION_ACTION`	`TT_CHAR(1) NOT NULL`	The action to take upon detecting a conflict by a timestamp-based detector The action is specified by the `ON EXCEPTION` clause in the `CHECK CONFLICTS` clause of a `CREATE REPLICATION` statement. They appear in this column as: '`\0`' - action not defined '`N`' - `NO ACTION` '`R`' - roll back transaction (default)
`TS_UPDATE_RULE`	`TT_CHAR(1) NOT NULL`	The rule for maintaining the timestamp for a timestamp-based conflict detector '`\0`' - rule not defined '`U`' - by user '`S`' - by system (default)
`TS_REPORT_FILE`	`TT_VARCHAR(1000) NOT INLINE`	The name of the file to which the replication agent reports timestamp conflicts This file is specified by the `REPORT TO` clause in the `CHECK CONFLICTS` clause of a `CREATE REPLICATION` statement.
`IS_MASTER_PROPAGATOR`	`BINARY(1)`	Indication of whether the database is both a master and a propagator
`EXTERNAL_DB`	`TT_CHAR(1)`	Indication of replication to a database that is not TimesTen `NULL` - no replication to another kind of database `O` - replication to Oracle database, which occurs in a TimesTen database with an AWT cache group
`REPORT_FORMAT`	`TT_CHAR(1)`	The report format for the replication conflict file '`S`' - standard format '`X`' - XML format `NULL` - no report file specified, therefore no format

TTREP.REPLICATIONS

The REPLICATIONS table collects together general information about all replication schemes in which the local database participates. The table indicates whether a replication scheme was created by ttRepAdmin -upgrade or by a CREATE MATERIALIZED VIEW statement.

Columns

Column name	Type	Description
`REPLICATION_NAME`	`TT_CHAR(31) NOT NULL`	Name for a replication scheme
`REPLICATION_OWNER`	`TT_CHAR(31) NOT NULL`	The replication scheme's owner
`REPLICATION_ORIGIN`	`TT_CHAR(1) NOT NULL`	How replication was created '`U`' - for `ttRepAdmin -upgrade` '`C`' - for `CREATE REPLICATION` (or a `ttRepAdmin` command that was translated into `CREATE REPLICATION`)
`REPLICATION_VERSION`	`TT_INTEGER NOT NULL`	The number of `ALTER REPLICATION` commands applied to this replication scheme after its initial creation
`SOURCE_STORE_ID_ALIGN`	`TT_INTEGER NOT NULL`	Internal use, to properly align the `SOURCE_STORE_ID` column
`SOURCE_STORE_ID`	`TT_BIGINT NOT NULL`	If this replication scheme was created by restoring it from a backup: the database ID of the database from which this replication scheme was backed up and restored Otherwise -1 (the invalid database ID)
`CHECKSUM`	`TT_BIGINT`	Indication of whether the replication scheme has been updated

TTREP.REPNETWORK

The REPNETWORK table stores information on interfaces used by the replication agent when two peers communicate. Each row represents a communication path between master and subscriber and describes either the sending or receiving interface used.

Columns

Column name	Type	Description
`REPLICATION_NAME`	`TT_CHAR(31) NOT NULL`	Name of the replication scheme
`REPLICATION_OWNER`	`TT_CHAR(31) NOT NULL`	The owner of the replication scheme
`TT_STORE_ID`	`TT_BIGINT NOT NULL`	Unique, system-generated identifier for a `HOST_NAME/TT_STORE_NAME` pair
`SUBSCRIBER_ID`	`TT_BIGINT NOT NULL`	The identifier for a database that subscribes to at least one replication element owned by `TT_STORE_ID`
`HOST_NAME`	`TT_VARCHAR(200) NOT NULL NOT INLINE`	Name associated with the network interface
`PRIORITY`	`TT_INTEGER NOT NULL`	Integer from 1-99 that denotes the priority of the IP address
`INTERFACE`	`TT_CHAR(1) NOT NULL`	'`S`' if `HOST_NAME` refers to an interface on the sending side '`R`' if `HOST_NAME` refers to an interface on the receiving side

TTREP.REPPEERS

The REPPEERS table displays status information about the stores in a replication scheme. After the initial upgrade, the REPPEERS table contains peer information only about the local database and other databases that it transmits updates to.

Columns

Column name	Type	Description
`REPLICATION_NAME`	`TT_CHAR(31) NOT NULL`	Name for a replication scheme
`REPLICATION_OWNER`	`TT_CHAR(31) NOT NULL`	The replication scheme's owner
`TT_STORE_ID`	`TT_BIGINT NOT NULL`	Unique, system-generated identifier for a `HOST_NAME/TT_STORE_NAME` pair
`SUBSCRIBER_ID`	`TT_BIGINT NOT NULL`	The identifier for a database that subscribes to at least one replication element owned by `TT_STORE_ID` If a valid ID then this record describes the status of `TT_STORE_ID/SUBSCRIBER_ID` as a sender/subscriber pair.
`COMMIT_TIMESTAMP`	`TT_INTEGER` for 32-bit systems `TT_BIGINT` for 64-bit systems	Commit timestamp This field and `COMMIT_SEQNUM` together store the value of the Commit Ticket Number of the refreshed transaction that the subscriber has just committed.
`COMMIT_SEQNUM`	`TT_INTEGER` for 32-bit systems `TT_BIGINT` for 64-bit systems	Commit sequence number This field and `COMMIT_TIMESTAMP` together store the value of the Commit Ticket Number of the refreshed transaction that the subscriber has just committed.
`SENDLSNHIGH`	`TT_INTEGER` for 32-bit systems `TT_BIGINT` for 64-bit systems	The log file number of the highest `TT_STORE_ID` log sequence number sent to and acknowledged by `SUBSCRIBER_ID`
`SENDLSNLOW`	`TT_INTEGER` for 32-bit systems `TT_BIGINT` for 64-bit systems	The log file offset of the highest `TT_STORE_ID` log sequence number sent to and acknowledged by `SUBSCRIBER_ID`
`REPTABLESLSNHIGH`	`TT_INTEGER` for 32-bit systems `TT_BIGINT` for 64-bit systems	For TimesTen internal use
`REPTABLESLSNLOW`	`TT_INTEGER` for 32-bit systems `TT_BIGINT` for 64-bit systems	For TimesTen internal use
`STATE`	`TT_INTEGER`	The state of replication kept by `TT_STORE_ID` with respect to this `SUBSCRIBER_ID` `0` - `START`: Replication is in the active state and all log updates are retained until they have been applied at `SUBSCRIBER_ID`. `1` - `PAUSE`: Replication is not in the active state but all log updates are retained until they have been applied at `SUBSCRIBER_ID`. `2` - `STOP`: Replication is not in the active state and log updates are not retained. `4` - `FAILED`: Replication is not in the active state, log updates are not retained, and the log updates that need to be retained exceed the user defined threshold (`TTREP.REPSTORES.FAIL_THRESHOLD`). When this state has been communicated to `SUBSCRIBER_ID` it is changed to `STOP`.
`TIMESEND`	`TT_INTEGER`	The timestamp (in seconds) for the time of the last known successful transmission from `TT_STORE_ID` to `SUBSCRIBER_ID`
`TIMERECV`	`TT_INTEGER`	The timestamp (in seconds) for the time `TT_STORE_ID` last received a transmission from `SUBSCRIBER_ID`
`PROTOCOL`	`TT_INTEGER`	A number in the range 0 to 5 indicating the protocol level that replication uses for communication between `TT_STORE_ID` and `SUBSCRIBER_ID` A higher number indicates a newer protocol.
`LATENCY`	`BINARY_DOUBLE`	An estimate of the time interval (in seconds) from the commit of a transaction on `TT_STORE_ID` to its receipt of acknowledgement that it has been applied at the subscriber identified by `SUBSCRIBER_ID`
`TPS`	`TT_INTEGER`	An estimate of the number of transactions per second that are committed on `TT_STORE_ID` and successfully received by the subscriber identified by `SUBSCRIBER_ID`
`RECSPERSEC`	`TT_INTEGER`	An estimate of the number of records per second retrieved by the subscriber identified by `SUBSCRIBER_ID` from the database `TT_STORE_ID`
`TRACK_ID`	`TT_TINYINT`	ID of replication track used in user-specified parallel replication
`CTNLISTINDEX`	`TT_INTEGER`	For internal use by the replication agent

TTREP.REPSTORES

The REPSTORES table lists the replication attributes of databases that participate in every TimesTen replication scheme in which the local database participates. Each database is identified by a unique TT_STORE_ID that TimesTen replication assigns to it. A TT_STORE_ID may appear at most once for a given replication scheme, but may appear multiple times in the REPSTORES table. Various replication schemes may define different replication attributes for the same database.

Columns

Column name	Type	Description
`REPLICATION_NAME`	`TT_CHAR(31) NOT NULL`	Name for a replication scheme
`REPLICATION_OWNER`	`TT_CHAR(31) NOT NULL`	The replication scheme's owner
`TT_STORE_ID`	`TT_BIGINT NOT NULL`	Unique, system-generated identifier for a `HOST_NAME/TT_STORE_NAME` pair
`PEER_TIMEOUT`	`TT_INTEGER NOT NULL`	The number of seconds for this database to wait for a subscriber response before trying to reconnect
`FAIL_THRESHOLD`	`TT_INTEGER NOT NULL`	The number of log files whose accumulation makes this database, in this replication scheme, mark subscribers "failed" (See the `STATE` field in `TTREP.REPPEERS`.)
`HEARTBEAT_FACTOR`	`BINARY_DOUBLE`	A multiplier of the current heartbeat frequency

TTREP.REPSUBSCRIPTIONS

The REPSBUBSCRIPTIONS table registers each subscribing database that maintains a secondary copy of a replication element.

Columns

Column name	Type	Description
`REPLICATION_NAME`	`TT_CHAR(31) NOT NULL`	Name for a replication scheme
`REPLICATION_OWNER`	`TT_CHAR(31) NOT NULL`	The replication scheme's owner
`ELEMENT_NAME`	`TT_CHAR(31) NOT NULL`	The replication name for this element, logically distinct from the name of an underlying database object
`SUBSCRIBER_ID`	`TT_BIGINT NOT NULL`	The `TT_STORE_ID` for a subscriber to this element A subscriber may not subscribe more than once to a replication element in a replication scheme.
`RETURN_SERVICE`	`TT_CHAR(1) NOT NULL`	Return service for this subscriber with respect to this replication element '`C`' - `RETURN COMMIT` '`R`' - `RETURN RECEIPT` '`\0`' - no return services '`2`' - `RETURN TWOSAFE`
`RETURN_BY_REQUEST`	`BINARY(1) NOT NULL`	The type of return services for this element 0 - Return services are provided unconditionally. 1 - Return services are provided only by request. This field is ignored if `RETURN_SERVICES = '\0'`.
`PRIVILEGES`	`TT_CHAR(1) NOT NULL`	Privileges for this subscriber with respect to this replication element '`\0`' - no special subscriber privileges

TTREP.REPTABLES

The REPTABLES table contains subscriber-relative information about each of the columns in each table transmitted to a subscriber. This information appears in REPTABLES in the owner (transmitter) database but not in REPTABLES in the subscriber database.

Columns

Column name	Type	Description
`REPLICATION_NAME`	`TT_CHAR(31) NOT NULL`	Name for a replication scheme
`REPLICATION_OWNER`	`TT_CHAR(31) NOT NULL`	The replication scheme's owner
`ELEMENT_NAME`	`TT_CHAR(31) NOT NULL`	The replication name for this element, logically different from the `REF_NAME` of the underlying data base object For example, the `ELEMENT_NAME` for a replicated table may differ from the table name. This name must be unique in a replication scheme.
`SUBSCRIBER_ID`	`TT_BIGINT NOT NULL`	The `TT_STORE_ID` for a subscriber to this element A subscriber may not subscribe more than once to a replication element in a replication scheme.
`COLNUM`	`TT_SMALLINT NOT NULL`	Ordinal number of column in table (starting at 1)
`COLOPTIONS`	`BINARY(1) NOT NULL`	Column specification flags 0x01 - Column is in a primary key. 0x02 - Column value is varying length data type (`VARCHAR[2]`, `NVARCHAR[2]`, `VARBINARY`). 0x04 - Column value can be `NULL`. 0x08 - Column values are unique.
`COLTYPE`	`TT_INTEGER NOT NULL`	Data type of column `1` `TT_CHAR` `2` `TT_DECIMAL` `3` `TT_DECIMAL` `4` `TT_INTEGER` `5` `TT_SMALLINT` `6` `BINARY_FLOAT` `7` `BINARY_FLOAT` `8` `BINARY_DOUBLE` `9` `TT_DATE` `10` `TIME` `11` `TT_TIMESTAMP` `12` `TT_VARCHAR` `13` `DATE` `14` `TIMESTAMP` `15` `NUMBER` `16` `CHAR` `17` `VARCHAR2` `18` `NCHAR` `19` `NVARCHAR2` - `1` `LONGVARCHAR` - `2` `BINARY` - `3` `VARBINARY` - `4` `LONGVARBINARY` - `5` `TT_BIGINT` - `6` `TT_TINYINT` - `7` `BIT` - `8` `WCHAR` - `9` `WVARCHAR` - `10` `WLONGVARCHAR`
`COLLEN`	`TT_INTEGER` `NOT` `NULL` for 32-bit systems `TT_BIGINT` `NOT` `NULL` for 64-bit systems	Length of the column (maximum length for varying-length columns)
`COLPRECISION`	`TT_INTEGER NOT NULL`	Precision of column data This is the number of digits in a fixed-point number, or the number of digits in the mantissa of a floating point number.
`COLSCALE`	`TT_INTEGER NOT NULL`	Scale of column data (non-negative number) A scale of 0 indicates an integer with no digits to the right of a decimal point. For a scale of S, the exact numeric value is the integer value of the significant digits multiplied by: `10 (exp -S)`.
`PTNNUM`	`TT_SMALLINT NOT NULL`	The table partition that contains the column
`PTNCOLOFF`	`TT_INTEGER` `NOT` `NULL` for 32-bit systems `TT_BIGINT` `NOT` `NULL` for 64-bit systems	The offset of the column within the partition
`PTNNULLOFF`	`TT_INTEGER` `NOT` `NULL` for 32-bit systems `TT_BIGINT` `NOT` `NULL` for 64-bit systems	The offset to the null byte within the partition
`REPKEYPOSITION`	`TT_SMALLINT NOT NULL`	The ordinal position of this column in the replication key described by the `REPKEYCOLS`
`TS_EXCEPTION_ACTION`	`TT_CHAR(1) NOT NULL`	The action to take upon detecting a conflict by a timestamp-based detector The action is specified by the `ON EXCEPTION` clause in the `CHECK CONFLICTS` of a `CREATE REPLICATION` statement. They appear in this column as: `\0`' - Undefined action '`N`' - `NO ACTION` '`R`' - `ROLLBACK` (default)
`PNBOFF`	`TT_INTEGER`	For internal use only
`NULLMASK`	`TT_TINYINT`	For internal use only
`COLNAME`	`TT_CHAR(31)`	Column name

TTREP.TTSTORES

The TTSTORES table maps the host name and database name to a unique TT_STORE_ID. The TT_STORE_ID is a foreign key for all other replication schema tables that refer to a database in a replication scheme.

Columns

Column name	Type	Description
`TT_STORE_ID`	`TT_BIGINT NOT NULL`	Unique, system-generated identifier for a `HOST_NAME/TT_STORE_NAME` pair
`HOST_NAME`	`TT_VARCHAR(200) NOT NULL NOT INLINE`	Name of the participating host node
`TT_STORE_NAME`	`TT_VARCHAR(200) NOT NULL NOT INLINE`	The name for this database
`IS_LOCAL_STORE`	`BINARY(1) NOT NULL`	1 if this `TT_STORE_ID` represents the local database 0 otherwise
`MAJOR_RELEASE`	`TT_INTEGER NOT NULL`	The major release part of this database's TimesTen release number 0 indicates the current release.
`MINOR_RELEASE`	`TT_INTEGER NOT NULL`	The minor release part of this store's TimesTen release number
`REP_SCHEMA_VERSION`	`TT_INTEGER NOT NULL`	The version of the replication schema in this database
`REP_PORT_NUMBER`	`TT_INTEGER NOT NULL`	The port number that replication uses to communicate with this database This is 0 if automatically assigned.
`RRPOLICY`	`TT_CHAR(1)`	Subscribers affected by return service failure policy Legal values are: '`S`' - single subscriber '`A`' - all subscribers '`N`' - no policy
`RRTRIGGER`	`TT_INTEGER`	Number of timeouts before the return service failure policy is triggered
`RRRESUME_LATENCY`	`TT_INTEGER`	Resume latency in milliseconds
`RRDURABLE`	`BINARY(1)`	Durable commits on `RETURN RECEIPT` failure Legal values are: 1 - true 0 - false
`RET_LOCAL_ACTION`	`TT_CHAR(1)`	Default commit behavior for `RETURN TWOSAFE` transactions '`C`' - `COMMIT` '`N`' - `NO ACTION`
`RET_WAIT_TIME`	`TT_INTEGER`	The defaulted timeout value for `RETURN TWOSAFE` transactions
`RET_WHEN_STOPPED`	`BINARY(1)`	Return service status If either the replication agent for the database is stopped or if the database is used as master and the replication agent for the database is set to `STOP`, then if the value of the column is a non-zero value, return services for the database are suspended.
`COMPRESSION`	`TT_CHAR(1)`	`Y` if all data from the database is compressed
`MASTER`	`TT_CHAR(1)`	Active or standby database or subscriber database Values are: '`Y`' - active or standby database '`N`' - subscriber database `NULL` - all other cases
`ROLE`	`TT_CHAR(1)`	Role '`A`' - active '`S`' - standby `NULL` - all other cases
`TS`	`TT_BIGINT`	The timestamp at which the specified role change was made
`CONFLICT_REPORT_STOP`	`TT_INTEGER`	The threshold at which conflict reporting is stopped
`CONFLICT_REPORT_RESTART`	`TT_INTEGER`	The rate at which conflict reporting is resumed
`CONFLICT_REPORT_FLUSH_METHOD`	`TT_INTEGER`	Reserved for future use
`TABLECHECK`	`TT_CHAR(1)`	Indication of exact or relaxed replication `E` (exact) - The table structures on the master and subscriber databases must be identical for replication to occur. `R` (relaxed) - Replication can occur between master and subscriber if a relaxed table check has been passed. This means that the number of columns and column data types match for the tables in the master and subscriber databases. `NULL` (default) - This is the value for all other cases.