STATS EXTRACT

Use STATS EXTRACT to display statistics for one or more Extract groups. The output includes DML and DDL operations that are included in the Oracle GoldenGate configuration.

To get the most accurate number of operations per second that are being processed, do the following.

  1. Issue the STATS EXTRACT command with the RESET option.

  2. Issue the STATS EXTRACT REPORTRATE command. The LATEST STATISTICS field shows the operations per second.

Note:

The actual number of DML operations executed on a DB2 database might not match the number of extracted DML operations reported by Oracle GoldenGate. DB2 does not log update statements if they do not physically change a row, so Oracle GoldenGate cannot detect them or include them in statistics.

Note:

To get accurate statistics on a Teradata source system where Oracle GoldenGate is configured in maximum protection mode, issue STATS EXTRACT to the VAM-sort Extract, not the primary Extract. The primary Extract may contain statistics for uncommitted transactions that could be rolled back; whereas the VAM-sort Extract reports statistics only for committed transactions.

Syntax

STATS EXTRACT group_name
[, statistic] 
[, DDLSONLY]
[, TABLE [container. | catalog.]schema.table]
[, TOTALSONLY [container. | catalog.]schema.table]
[, REPORTCDR]
[, REPORTCHARCONV]
[, REPORTFETCH | NOREPORTFETCH]
[, REPORTRATE time_units]
group_name

The name of an Extract group or a wildcard (*) to specify multiple groups. For example, T* returns statistics for all Extract groups whose names start with T.

statistic

The statistic to be displayed. More than one statistic can be specified by separating each with a comma, for example STATS EXTRACT finance, TOTAL, DAILY.

TOTAL

Displays totals since process startup.

DAILY

Displays totals since the start of the current day.

HOURLY

Displays totals since the start of the current hour.

LATEST

Displays totals since the last RESET command.

RESET

Resets the counters in the LATEST statistical field.

DDLSONLY

Displays the statistics for DDL statements including number of DDL statements in a readable format.

TABLE [container. | catalog.]schema.table_name

Displays statistics only for the specified table or a group of tables specified with a wildcard (*). The table name or wildcard specification must be fully qualified with the two-part or three-part name, for example hr.emp or *.*.*.

TOTALSONLY [container. | catalog.]schema.table_name

Summarizes the statistics for the specified table or a group of tables specified with a wildcard (*). The table name or wildcard specification must be fully qualified with the two-part or three-part name, for example hr.emp or *.*.*.

REPORTCDR

Shows statistics for Conflict Detection and Resolution. Statistics include:

  • Total CDR conflicts

  • CDR resolutions succeeded

  • CDR resolutions failed

  • CDR INSERTROWEXISTS conflicts

  • CDR UPDATEROWEXISTS conflicts

  • CDR DELROWEXISTS conflicts

  • CDR DELROWMISSING conflicts

REPORTCHARCONV

Use only when TABLE parameters have a TARGET clause and character-set conversion is performed. The following statistics are added to the STATS output:

Total column character set conversion failure: the number of validation or conversion failures in the current Extract run.

Total column data truncation: the number of times that column data was truncated in the current Extract run as the result of character set conversion

REPORTFETCH | NOREPORTFETCH

Controls whether or not statistics about fetch operations are included in the output. The default is NOREPORTFETCH. The output of REPORTFETCH is as follows:

  • row fetch attempts: The number of times Extract attempted to fetch a column value from the database when it could not obtain the value from the transaction log.

  • fetch failed: The number of row fetch attempts that failed.

  • row fetch by key: Valid for Oracle. The number of row fetch attempts that were made by using the primary key. The default is to fetch by row ID.

REPORTRATE time_units

Displays statistics in terms of processing rate rather than absolute values.

HR
MIN
SEC

Example

The following example displays total and hourly statistics per minute for a specific table, and it also resets the latest statistics and outputs fetch statistics.

STATS EXTRACT exte, TOTAL, HOURLY, TABLE hr.emp, REPORTRATE MIN, RESET, REPORTFETCH
STATS EXTRACT exte, LATEST, REPORTFETCH