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.

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]
[, TABLE [container. | catalog.]schema.table]
[, TOTALSONLY [container. | catalog.]schema.table]
[, 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.

Valid values:

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.

TABLE [container. | catalog.]schema.table

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

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 *.*.*.

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. See "STATOPTIONS" for defaults that control fetching and options for altering fetch behavior. 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.

Valid values:

HR
MIN
SEC

Example

Example 1   

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 finance, TOTAL, HOURLY, TABLE hr.acct,
REPORTRATE MIN, RESET, REPORTFETCH
Example 2   

The following is sample output using the LATEST and REPORTFETCH options

STATS EXTRACT ext, LATEST, REPORTFETCH
Sending STATS request to EXTRACT GGSEXT...
Start of Statistics at 2011-01-08 11:45:05.
DDL replication statistics (for all trails):
*** Total statistics since extract started     ***
    Operations                                   3.00
    Mapped operations                            3.00
    Unmapped operations                          0.00
    Default operations                           0.00
    Excluded operations                          0.00
Output to ./dirdat/aa:
Extracting from HR.EMPLOYEES to HR.EMPLOYEES:
*** Latest statistics since 2011-01-08 11:36:55 ***
    Total inserts                              176.00
    Total updates                                0.00
    Total deletes                               40.00
    Total discards                               0.00
    Total operations                           216.00
Extracting from HR.DEPARTMENTS to HR.DEPARTMENTS:
*** Latest statistics since 2011-01-08 11:36:55 ***
No database operations have been performed.
End of Statistics.