FETCHOPTIONS

Valid For

Extract

Description

Use the FETCHOPTIONS parameter to control certain aspects of the way that Oracle GoldenGate fetches data in the following circumstances:

When the transaction record does not contain enough information for Extract to reconstruct an update operation.
When Oracle GoldenGate must fetch a column value as the result of a MISSINGCOLS clause of a TABLE statement.

FETCHOPTIONS is table-specific. One FETCHOPTIONS statement applies for all subsequent TABLE statements until a different FETCHOPTIONS statement is encountered.

Default fetch properties are adequate for most installations.

Default

Ignore missing rows and continue processing

Syntax

FETCHOPTIONS
[, FETCHPKUPDATECOLS]
[, MISSINGCOLS]
[, INCONSISTENTROW action]
[, MAXFETCHSTATEMENTS number]
[, MISSINGROW action]
[, NOFETCH]
[, SUPPRESSDUPLICATES]
[, USEKEY | NOUSEKEY]
[, USELATESTVERSION | NOUSELATESTVERSION]
[, USESNAPSHOT | NOUSESNAPSHOT]
[, USEROWID | NOUSEROWID]

FETCHPKUPDATECOLS

Fetches all unavailable columns when a primary key is updated. This option is off by default. When off, column fetching is performed according to other FETCHOPTIONS options that are enabled.

When on, it only takes effect during an update to a primary key column. The results are the same as using (*) in the TABLE statement. LOB columns are included in the fetch.

Use this parameter when using HANDLECOLLISIONS. When Replicat detects a missing update, all of the columns will be available to turn the update into an insert.

MISSINGCOLS

Fetches any missing columns from update and delete operations, including LOB columns. This option is only valid for Oracle Database. It can negatively impact the database and the Extract performance due to additional queries to fetch the data. Especially if there are large LOB values that need to be fetched and written to the trail.

Setting this parameter is the same as setting the following parameters:

MISSINGCOLS(*) in the TABLE statement
NOCOMPRESSDELETES FETCHMISSINGCOLS
GETUPDATEBEFORES
NOCOMPRESSUPDATES
LOGALLSUPCOLS

However, setting FETCHOPTIONS MISSINGCOLS conflicts with the following parameters:

FETCHOPTIONS NOFETCH
FETCHOPTIONS FETCHPKUPDATECOLS
COMPRESSDELETES
COMPRESSUPDATES
GETUPDATEBEFORES
LOGALLSUPCOLS

INCONSISTENTROW action

Indicates that column data was successfully fetched by row ID, but the key did not match. Either the row ID was recycled or a primary key update occurred after this operation (and prior to the fetch).

action can be one of the following:

ALLOW: Allow the condition and continue processing.
IGNORE: Ignore the condition and continue processing. This is the default.
REPORT: Report the condition and contents of the row to the discard file, but continue processing the partial row.
DISCARD: Discard the data and do not process the partial row.
ABEND: Discard the data and quit processing.

MAXFETCHSTATEMENTS number

Controls the maximum allowable number of prepared queries that can be used by Extract to fetch row data from a source database. The fetched data is used when not enough information is available to construct a logical SQL statement from a transaction log record. Queries are prepared and cached as needed. When the value set with MAXFETCHSTATEMENTS is reached, the oldest query is replaced by the newest one. The value of this parameter controls the number of open cursors maintained by Extract for fetch queries only. Additional cursors may be used by Extract for other purposes, such as those required for stored procedures. This parameter is only valid for Oracle databases.The default is 100 statements. Make certain that the database can support the number of cursors specified, plus cursors used by other applications and processes.

MISSINGROW action

Provides a response when Oracle GoldenGate cannot locate a row to be fetched, causing only part of the row (the changed values) to be available for processing. Typically a row cannot be located because it was deleted between the time the change record was created and when the fetch was triggered, or because the row image required was older than the undo retention specification.

action can be one of the following:

ALLOW: Allow the condition and continue processing. This is the default.
IGNORE: Ignore the condition and continue processing.
REPORT: Report the condition and contents of the row to the discard file, but continue processing the partial row.
DISCARD: Discard the data and do not process the partial row.
ABEND: Discard the data and quit processing.

NOFETCH

Prevents Extract from fetching the column from the database, this option is off by default. Extract writes the record to the trail, but inserts a token indicating that the column is missing.

SUPPRESSDUPLICATES

Valid for Oracle. Avoids target tablespaces becoming overly large when updates are made on LOB columns. By default, SUPPRESSDUPLICATES is set to off. For example, after replication a source tablespace of 232MB becomes a target tablespace of 7.52GB.

USEKEY | NOUSEKEY

Determines whether or not Oracle GoldenGate uses the primary key to locate the row to be fetched.

If both USEKEY and USEROWID are specified, ROWID takes priority for faster access to the record. USEROWID is the default.

USELATESTVERSION | NOUSELATESTVERSION

Valid for Oracle. Use with USESNAPSHOT. The default, USELATESTVERSION, directs Extract to fetch data from the source table if it cannot fetch from the undo tablespace. NOUSELATESTVERSION directs Extract to ignore the condition if the snapshot fetch fails, and continue processing.

To provide an alternate action if a snapshot fetch does not succeed, use the MISSINGROW option.

USESNAPSHOT | NOUSESNAPSHOT

Valid for Oracle. The default, USESNAPSHOT, causes Extract to use the Oracle Flashback mechanism to fetch the correct snapshot of data that is needed to reconstruct certain operations that cannot be fully captured from the redo record. NOUSESNAPSHOT causes Extract to fetch the needed data from the source table instead of the flashback logs.

USEROWID | NOUSEROWID

Valid for Oracle. Determines whether or not Oracle GoldenGate uses the row ID to locate the row to be fetched.

If both USEKEY and USEROWID are specified, ROWID takes priority for faster access to the record. USEROWID is the default.

Examples

Example 1

The following directs Extract to fetch data by using Flashback Query and to ignore the condition and continue processing the record if the fetch fails.

FETCHOPTIONS USESNAPSHOT, NOUSELATESTVERSION

Example 2

MAXFETCHSTATEMENTS 150

Example 3

The following directs Extract to fetch data by using Flashback Query and causes Extract to abend if the data is not available.

FETCHOPTIONS USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW ABEND