IBMQREP_APPLYMON table (native Oracle targets)

The Q Apply program periodically inserts rows in the IBMQREP_APPLYMON table to record performance statistics, one row for each receive queue.

Server: Oracle database

Default schema: ASN

Unique index: MONITOR_TIME ASC, RECVQ ASC

Important: Do not alter this table using SQL. Altering this table inappropriately can cause unexpected results and loss of data.

The value that you specify for MONITOR_INTERVAL in the IBMQREP_APPLYPARMS table determines how often the Q Apply program inserts rows into this control table. The MONITOR_LIMIT value determines how long rows remain in the table before they are eligible for pruning.

Table 1 provides a description of the columns in the IBMQREP_APPLYMON table.

Table 1. Columns in the IBMQREP_APPLYMON table for native Oracle targets
Column name Description
MONITOR_TIME Data type: TIMESTAMP; Nullable: No

The timestamp in the local time of the Q Apply server when the row was inserted into the IBMQREP_APPLYMON table.
RECVQ Data type: VARCHAR2(48); Nullable: No

The name of the receive queue that this row of Q Apply performance statistics pertains to.
QSTART_TIME Data type: TIMESTAMP; Nullable: No

The timestamp in the local time of the Q Apply server when the receive queue was started.
CURRENT_MEMORY Data type: NUMBER(10); Nullable: No

The amount of memory in bytes that the Q Apply browser thread used for reading transactions from this queue.
QDEPTH Data type: NUMBER(10); Nullable: No

The queue depth (number of messages on the queue). The Q_PERCENT_FULL column expresses the fullness of the queue as a percentage.
END2END_LATENCY Data type: NUMBER(10); Nullable: No

The average elapsed milliseconds between the time that transactions were committed to the source table and the time that they were committed to the target.
QLATENCY Data type: NUMBER(10); Nullable: No

The average elapsed milliseconds between the time that the Q Capture program put messages on the send queue and the time that the Q Apply program got them from the receive queue.
APPLY_LATENCY Data type: NUMBER(10); Nullable: No

The average elapsed milliseconds between the time that the Q Apply program read transactions from the receive queue and the time that they were committed to the target.
TRANS_APPLIED Data type: NUMBER(10); Nullable: No

The total number of transactions from this receive queue that the Q Apply committed to the target.
ROWS_APPLIED Data type: NUMBER(10); Nullable: No

The total number of insert, update, and delete operations from this receive queue that the Q Apply program applied to the target.
TRANS_SERIALIZED Data type: NUMBER(10); Nullable: No

The total number of transactions that conflicted with another transaction (either because of a row conflict or a referential integrity conflict). In these cases, the Q Apply program suspends parallel processing and applies the row changes within the transaction in the order they were committed at the source.
RI_DEPENDENCIES Data type: NUMBER(10); Nullable: No

The total number of referential integrity conflicts that were detected, forcing transactions to be serialized.
RI_RETRIES Data type: NUMBER(10); Nullable: No

The number of times that the Q Apply program had to re-apply row changes because of referential integrity conflicts when the transactions that they were part of were executed in parallel.
DEADLOCK_RETRIES Data type: NUMBER(10); Nullable: No

The number of times that the Q Apply program re-applied row changes because of lock timeouts and deadlocks.
ROWS_NOT_APPLIED Data type: NUMBER(10); Nullable: No

The number of rows that could not be applied, and were entered in the IBMQREP_EXCEPTIONS table.
MONSTER_TRANS Data type: NUMBER(10); Nullable: No

The number of transactions that exceeded the MEMORY_LIMIT for the receive queue set in the IBMQREP_RECVQUEUES table.
MEM_FULL_TIME Data type: NUMBER(10); Nullable: No

The number of seconds that the Q Apply program could not build transactions from this receive queue because its agents were using all available memory to apply transactions.
APPLY_SLEEP_TIME Data type: NUMBER(10); Nullable: No

The number of milliseconds that Q Apply agents for this receive queue were idle while waiting for work.
SPILLED_ROWS Data type: NUMBER(10); Nullable: No

The number of rows that the Q Apply program sent to temporary spill queues while targets were being loaded or while Q subscriptions were placed into a spill state by the spillsub parameter of the MODIFY or asnqacmd command.
SPILLEDROWSAPPLIED Data type: NUMBER(10); Nullable: No

The number of spilled rows that were applied to the target.
OLDEST_TRANS Data type: TIMESTAMP; Nullable: No

A timestamp that is based on the local time at the Q Apply server that helps determine how far Q Apply has caught up with respect to the source. At each monitor interval OLDEST_TRANS represents:

  • If Q Apply is processing transactions, the source commit time for which all transactions to that point have been applied to the target. (Other more recent transactions might also have been applied. Because the Q Apply program processes transactions in parallel, the commit times of these more recent transactions do not refer to a point at which all previous transactions have been applied.)
  • The latest heartbeat time, if no transactions are being processed and the heartbeat message arrived after the oldest applied transaction.
  • The value 1900-01-01-00.00.00.000000 if the Q Apply program has not seen any messages (transaction or heartbeat).
OLDEST_INFLT_TRANS Data type: TIMESTAMP; Nullable: Yes

At each monitor interval OLDEST_INFLT_TRANS represents:

  • If Q Apply is processing transactions, the source commit time of the oldest currently in-flight transaction. An in-flight transaction has not been fully applied and committed at the target.
  • The value NULL, if Q Apply is not processing transactions.
  • The value NULL, if Q Apply has not seen any transaction messages so far.

OLDEST_INFLT_TRANS does not consider heartbeat messages. It only considers transaction messages. This value also does not reflect how far Q Apply has caught up with respect to the source because the source commit time belongs to a transaction that has not yet been fully processed or committed.

Timestamps for this column are always in the local time of the Q Apply server.
OK_ERROR_CODE_ERRORS Data type: VARCHAR2(128); Nullable: No

The number of row changes that caused an SQL error that is defined as acceptable in the OK_ERROR_CODE field of the IBMQREP_TARGETS table. The Q Apply program ignores these errors.
HEARTBEAT_LATENCY Data type: NUMBER(10); Nullable: No

The average elapsed milliseconds between the time that heartbeat messages were sent by the Q Capture program and the time that they were received by the Q Apply program.
KEY_DEPENDENCIES Data type: NUMBER(10); Nullable: No

The total number of replication key constraints that were detected, forcing transactions to be serialized.
UNIQ_DEPENDENCIES Data type: NUMBER(10); Nullable: No

The total number of unique index constraints that were detected, forcing transactions to be serialized.
UNIQ_RETRIES Data type: NUMBER(10); Nullable: No

The number of times that the Q Apply program tried to re-apply rows that were not applied in parallel because of unique index constraints.
JOB_DEPENDENCIES Data type: NUMBER(10); Nullable: Yes

The number of transactions that are delayed because of correlation ID dependencies.
CAPTURE_LATENCY Data type: NUMBER(10); Nullable: Yes

The average elapsed milliseconds between the time transactions were committed to the source table and the time Q Capture puts the last message for the transactions on the send queue.
OLDEST_COMMIT_LSN Data type: RAW(16); Nullable: Yes

The commit log sequence number (LSN) from the source recovery log that corresponds to the oldest transaction that was applied. All transactions with lower LSNs were applied. Some more recent transactions might also be applied. If no source transactions are committed yet, the value of this column is all zeroes, for example: x'00000000000000000000'.

You can use the OLDEST_COMMIT_LSN value on z/OS® or on Linux®, UNIX, and Windows V9.8 and later as the value for the maxcmtseq parameter when you need to restart the Q Capture program from a known point in the recovery log.
ROWS_PROCESSED Data type: NUMBER(10); Nullable: Yes

The number of rows that were read from receive queues and applied but not yet committed to the target.
Q_PERCENT_FULL Data type: NUMBER(5); Nullable: Yes, with default

The fullness of the receive queue expressed as a percentage, where the MAXDEPTH attribute of the queue is 100 percent. The QUEUE_DEPTH column expresses the fullness as a number of messages. Default: NULL
OLDEST_COMMIT_SEQ Data type: RAW(16); Nullable: Yes

An internal log marker that represents the last transaction that was applied by the Q Apply program before which all previous transactions have also been applied. The value is a formatted timestamp with nanosecond precision that is encoded as two integers, seconds, and nanoseconds into a 10-byte sequence.

On Linux, UNIX, and Windows V9.7 and earlier, you can use the OLDEST_COMMIT_SEQ value as the value for the maxcmtseq parameter when you need to restart the Q Capture program from a known point in the recovery log.
MQ_BYTES Data type: NUMBER(10); Nullable: Yes

The number of bytes of data read from all receive queues during the monitor interval, including message data and the message header.
MQGET_TIME Data type: INTEGER; Nullable: Yes

The number of milliseconds that the Q Apply browser thread spent making MQGET calls. The value includes MQGET calls that time out when receive queues are empty. The total does not include MQGET calls from the pruning thread.
NUM_MQGETS Data type: INTEGER; Nullable: Yes

The number of times that the Q Apply browser thread made MQGET calls to retrieve messages from receive queues.
NUM_MQMSGS Data type: INTEGER; Nullable: Yes

The total number of MQ messages that were received by the Q Apply program, including data messages, heartbeat messages, and control messages. You can determine the number of timeouts during the monitor interval by using the following formula:

NUM_MQGETS value - NUM_MQMSGS value = number of timeouts
NUM_DBMS_COMMITS Data type: INTEGER; Nullable: Yes

The number of times that Q Apply browser or agent threads committed transactions to the target. Commits by spill agent threads are not included in the total.
OLDEST_COMMIT_TIME Data type: TIMESTAMP; Nullable: Yes

The source commit time for which all transactions to that point were applied to the target. This value helps determine what data might have been lost after a disaster. This value is related to OLDEST_COMMIT_LSN and the two columns are updated together. The timestamp is based on the local time at the Q Apply server.

Other more recent transactions might also have been applied. Because the Q Apply program processes transactions in parallel, the commit times of these more recent transactions do not refer to a point at which all previous transactions were applied.

If Q Apply did not see any transaction messages or heartbeat messages during the monitor interval, the value is null.