ALTER EXTRACT

Use ALTER EXTRACT for the following purposes:

  • To change the attributes of an Extract group created with the ADD EXTRACT command.

  • To increment a trail to the next file in the sequence.

  • To upgrade to an integrated capture configuration.

  • To downgrade from an integrated capture configuration.

  • To position any given Db2 for i journal at a specific journal sequence number.

Before using this command, stop the Extract with the STOP EXTRACT group_name command.

Syntax

ALTER EXTRACT group-name
         [, BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]} |
|            [, EXTRBA archive-offset-number]
|            [, ADD_EXTRACT_attribute  | SCN value]
|            [, LSN
|            [, EOL 
|            [, DESC
|            [ CRITICAL    [ YES | NO ]     ]
|            [ ENCRYPTIONPROFILE     encryption-profile-name]
|            [ PROFILE
|                [ AUTOSTART   [ YES | NO ]
|                  [ DELAY     delay-number   ] ]
|                  [ AUTORESTART [ YES | NO ]
|                       [ RETRIES          retries-number ]
|                       [ WAITSECONDS      wait-number    ]
|                       [ RESETSECONDS     reset-number   ]
|                       [ DISABLEONFAILURE [ YES | NO ] ] ] ]
|             [ LOGNUM lognum]
|             [ LOGPOS logpos

The following ALTER EXTRACT options are supported for Db2 for i to position Extract for a given journal: 

ALTER EXTRACT {BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]Z]}
[JOURNAL journal_library/journal_name [JRNRCV receiver_library/
  receiver_name]] |
, EOL [JOURNAL journal_library/journal_name
  [JRNRCV receiver_library/receiver_name]] |
, SEQNO sequence_number [JOURNAL journal_library/journal_name 
  [JRNRCV receiver_library/receiver_name]]}
BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}
[JOURNAL journal_library/journal_name
[JRNRCV receiver_library/ receiver_name]] |
, EOL [JOURNAL journal_library/journal_name
[JRNRCV receiver_library/receiver_name]] |
, SEQNO sequence_number [JOURNAL journal_library/journal_name
[JRNRCV receiver_library/receiver_name]]

These IBM for i options allow journal-specific Extract positioning after the global start point is issued with ADD EXTRACT. A specific journal position set with ALTER EXTRACT does not affect any global position that was previously set with ADD EXTRACT or ALTER EXTRACT; however a global position set with ALTER EXTRACT overrides any specific journal positions that were previously set in the same Extract configuration.

Note:

SEQNO, when used with a journal in ALTER EXTRACT, is the journal sequence number that is relative to that specific journal, not the system sequence number that is global across journals.
group-name

The name of the Extract group that is to be altered.

{BEGIN {NOW | Tyyyy-mm-dd[ hh:mi:[ss[.cccccc]]]Z}
NOW

For all databases except Db2 LUW, NOW specifies the time at which the ALTER EXTRACT command is issued.

NOW specifies the time at which the ALTER EXTRACT command is issued.

For Db2 LUW, only commit and end transaction records contain timestamps, so the Extract starting position can only be calculated relative to those timestamps. This is a limitation of the API that is used by Oracle GoldenGate. It must be noted that positioning by timestamp is not accurate and can also take a long time. It is recommended to use LRI or EOL options wherever possible.

YYYY-MM-DDThh:mm:ssZ

A date and time (timestamp) in the given form. For example, 2017-07-14T14:54:45Z.

yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]

A date and time (timestamp) in the given form. For an Oracle Extract in integrated mode, the timestamp value must be greater than the timestamp at which the Extract was registered with the database.

Positioning by timestamp in a SQL Server transaction log is affected by the following characteristics of SQL Server:

  • The timestamps recorded in the SQL Server transaction log use a 3.3333 microsecond (ms) granularity. This level of granularity may not allow positioning by time between two transactions, if the transactions began in the same 3.3333 ms time interval.

  • Timestamps are not recorded in every SQL Server log record, but only in the records that begin and commit the transaction, as well as some others that do not contain data.

  • SQL Server timestamps are not from the system clock, but instead are from an internal clock that is specific to the individual processors in use. This clock updates several times a second, but between updates it could get out of sync with the system clock. This further reduces the precision of positioning by time.

  • Timestamps recorded for log backup files may not precisely correspond to times recorded inside the backup (however this imprecision is less than a second).

Positioning to an LSN is precise.

Positioning by timestamp in PostgreSQL includes the following scenarios:

  • Scenario 1

    If track_commit_timestamp is off, the following output will be displayed when the Extract process starts irrespective of what positioning method is used:
    2020-04-29 02:15:54 INFO OGG-01517 Position of first record processed LSN: 0/2222C20, Jan 1, 1970 12:00:00 PM.

  • Scenario 2

    If the track_commit_timestamp is enabled before Extract is registered then the correct timestamp will be displayed once the records are pushed in the source database as mentioned in the following example: 
    2020-04-29 02:19:07 INFO OGG-01515 Positioning to begin time Apr 29,2020 2:18:38 AM.

  • Scenario 3

    If track_commit_timestamp is enabled after the Extract is registered, then there may be chances that the older records are available in the log for which the commit timestamp is not built up with the associated transaction ID. In that case, if Extract does not get the timestamp then it will fallback using the default timestamp mentioned in scenario 1. The output will be similar to the following:
    020-04-29 01:55:07 INFO OGG-01517 Position of first record processed LSN: 0/221D028, Jan 1, 1970 12:00:00 PM.

  • Past timestamp cannot be specified if the replication slot has moved away.

START

Valid for PostgreSQL.

Adds an Extract without mentioning BEGIN NOW or LSN. Extract will start from the replication slot restart position automatically.

ADD_EXTRACT_attribute

You can change any of the attributes specified with the ALTER EXTRACT command, except for the following:

  • Altering an Extract specified with the EXTTRAILSOURCE option.

  • Altering the number of RAC threads specified with the THREADS option.

For these exceptions, delete the Extract group and then add it again.

If using the BEGIN option, do not combine other options in the statement. Issue separate statements, for example: 

ALTER EXTRACT exte, BEGIN 2019-01-01
ALTER EXTRACT exte, ETROLLOVER
ALTER EXTRACT exte, SCN 789000

If using the SCN or BEGIN option for Integrated Extract, it requires a DBLOGIN, and the SCN or timestamp value specified cannot be below the outbound server's first SCN or timestamp. To find the outbound server's first SCN, issue the following command: 

INFO EXTRACT group_name, SHOWCH DETAIL

The first SCN value is listed as shown in the following example:

Integrated Extract outbound server first scn: 0.665884 (665884)
EXTRBA offset_number

Valid for Db2 z/OS.

Specifies the relative byte address within a transaction log at which to begin capturing data.

The required format is 0Xnnn, where nnn is a 1 to 20 digit hexadecimal number (the first character is the digit zero, and the second character can be upper or lower case letter x).

EOL

Valid for Db2 for i, Db2 LUW, MySQL, PostgreSQL, and SQL Server.

Configures processing to start at the end of the log files (or journals) that the next record will be written to. Any active transactions will not be captured.

For Db2 LUW, it configures processing to start at the active LRI value in the log files. The active LRI is the position at the end of the log files that the next record will be written to. Any active transactions will not be captured.

For PostgreSQL, DBLOGIN is required for position by EOL.

For MySQL, it finds the position corresponding to the end of the file and starts reading transactions from there. The EOL position is not exact, if data is continuously written to the binary log.

TRANLOG LRI LRI_number

(Db2 LUW) You can use this option for Db2 LUW systems to specify the LRI record value for the checkpoint transaction log.

For PostgreSQL, DBLOGIN is required for position by EOL.

REPORT file_name

Specifies the full path name of an Extract report file in a location other than the default of dirrpt within the Oracle GoldenGate directory.

SCN value

Valid for Oracle.

Starts Extract at the transaction in the redo log that has the specified Oracle system change number (SCN). For Extract, the SCN value must be greater than the SCN at which the Extract was registered with the database. For SCN or BEGIN option, DBLOGIN is required, and the SCN or timestamp value specified cannot be below the outbound server's first SCN or timestamp.

LSN value
Valid for Db2 z/OS, PostgreSQL, and SQL Server.
Specifies the transaction LSN at which to start capturing data. An alias for this option is EXTLSN.

For Db2 z/OS, LSN value can be either a series of hex digits or a series of decimal digits. If it is in hex format, it must be prefixed with either 0X or 0x followed by 1 to 20 hex digits: 0-9, a-g, or A-G. If it is in decimal format, there is no prefix and there are 1-25 decimal digits 0-9.

For PostgreSQL, LSN value can be hi or lo. Set the value as hi for the entry point of the log file. Lo is the offset in the log file. The LSN position should lie between the replication slot restart position and write ahead log current location. If the position specified itself exists between the mentioned range then Extract will throw an error.

For SQL Server, LSN specifies the transaction LSN at which to start capturing data. An alias for this option is EXTLSN.

The specified LSN should exist as a valid tran_begin_lsn found in the cdc.lsn_time_mapping system table, otherwise the Extract will attempt to position after the provided LSN value.

Valid LSN specifications for SQL Server consists of the following:

  • Colon separated hex string (8:8:4) padded with leading zeroes and 0X prefix, as in 0X00000d7e:0000036b:0001

  • Colon separated hex string with 0X prefix and without leading zeroes, as in 0Xd7e:36b:1

You can find the minimum LSN available by querying the following:

SELECT min([tran_begin_lsn]) FROM [cdc].[lsn_time_mapping] with (nolock) where tran_id <> 0x00

The following examples show the use of LSN value for Db2 z/OS:

Example 1:
ALTER EXTRACT extn TRANLOG, LSN 0xDEE40E4F27A3245400
Example 2:
ALTER EXTRACT extn TRANLOG, LSN 22216433159121980904448

The following example shows the LSN value for SQL Server:

ALTER EXTRACT extn TRANLOG, LSN 0X00000d7e:0000036b:0001
LOGNUM

Valid for MySQL.

This is the log file number. For example, if the required log file name is test.000034, the LOGNUM value is 34. Extract will search for this log file.

Note:

In Microservices Architecture, ALTER EXTRACT will fail if the LOGNUM value contains zeroes preceding the value. For example, ALTER EXTRACT ext1, TRANLOG, LOGNUM 000001, LOGPOS 0 will fail. Instead, set LOGNUM to 1 for this example to succeed.
LOGPOS

Valid for MySQL.

This is an event offset value within the log file that identifies a specific transaction record. Event offset values are stored in the header section of a log record. To position at the beginning of a binlog file, set the LOGPOS as 0.

ETROLLOVER

Use for manual recovery situations that require repositioning and regenerating trail files for a primary Extract and when upgrading Oracle GoldenGate from a previous version.

Causes Extract to create a new incarnation of the trail file and increments to the next file in the trail sequence when restarting, requiring readers such as pump or Replicat, to be manually repositioned to the new trail sequence number.

With ETROLLOVER, Extract captures all records after the specified SCN with which Extract is starting irrespective of whether it was already captured and written to trail or not. Without ETROLLOVER, Extract will skip the capture of any records that are already captured and written to trail.

From 19c onwards, during Distribution Service processing:

  • If the primary Extract on the source deployment is upgraded with target trail file ETROLLOVER, then theDistribution Service automatically detects the source trail file ETROLLOVER and starts reading from the next input trail file. This is the same command as data pump ALTER EXTRACT groupname EXTSEQNO seqno+1 EXTRBA 0.

  • After upgrading the Distribution Service, it automatically performs the ETROLLOVER for the output trail file upon restarting and writes the next trail file properly. This is the same as data pump ALTER EXTRACT groupname ETROLLOVER.

DESC 'description'

Specifies a description of the group, such as 'Extracts account_tab on Serv1'. Enclose the description within single quotes. You may use the abbreviated keyword DESC or the full word DESCRIPTION.

ENCRYPTIONPROFILE
Specifies the name of the encryption profile for the Extract. This name is case sensitive so you must use the exact name that you entered with ADD EXTRACT.
GTIDSET gtidset

Valid for MySQL.

Specifies the initial positioning of of Extract by using the position type positiob ing the GTID set option for GTID-based capture for MySQL. The supported MySQL sources for GTID set are MySQL Server 8.0, MySQL Server 5.7, MySQL Database Service (MDS). The maximum supported GTID set size is 64 KB.

Syntax:

ALTER EXTRACT extract_name, TRANLOG, GTIDSET gtidset

The following examples include all the combinations of valid formats of GTID set.

Example 1:
ALTER EXTRACT exte, TRANLOG, GTIDSET "E11FA47-71CA-11E1-9E33-C80AA9429562:4"
Example 2:
ALTER EXTRACT exts, TRANLOG, GTIDSET "3E11FA47-71CA-11E1-9E33-C80AA9429562:1-10"
Example 3:
ALTER EXTRACT extn, TRANLOG, GTIDSET "3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49"
Example 4:
ALTER EXTRACT extw, TRANLOG, GTIDSET "2174B383-5441-11E8-B90A-C80AA9429562:1-3,24DA167-0C0C-11E8-8442-00059A3C7B00:1-19"

Note:

As shown in example 4, when GTID set contains multiple uuids, to form a REST API request, this GTID set is broken into multiple GTID sets using comma separator.
CRITICAL

Indicates if the process is critical for the deployment.

PROFILE
Name of the auto start profile. This name is case sensitive so you must use the exact name that you entered with ALTER EXTRACT.
AUTOSTART
Specifies whether the managed process has to be started automatically when the Administration Service starts. The default value is YES.
RETRIES

The maximum number of tries for restarting the task before canceling retry efforts. This is optional.

WAITSECONDS

The duration (in seconds) in which the retries are counted.

RESETSECONDS
Resets the duration in which the retries are counted.
DISABLEONFAILURE

If set to TRUE, then the task is disabled when the number of retries is exhausted.

INFO EXTRACT group-name, SHOWCH DETAIL

The first SCN value is listed as shown in the following example: 

Integrated Extract outbound server first scn: 0.665884 (665884)

Examples

The following alters Extract to start processing data from January 1, 2019.

ALTER EXTRACT exte, BEGIN 2019-01-01

The following alters Extract to start processing at a specific location in the trail.

ALTER EXTRACT exte, EXTSEQNO 26, EXTRBA 338

The following alters Extract in a SQL Server environment to start at a specific LSN.

ALTER EXTRACT exte, LSN 0Xd7e:36b:1

The following alters Extract to increment to the next file in the trail sequence.

ALTER EXTRACT exte, ETROLLOVER

The following alters Extract to upgrade to integrated capture.

ALTER EXTRACT exte, UPGRADE INTEGRATED TRANLOG

The following command uses ETROLLOVER to prevent filtering of duplicate records with backward SCN 778899. 

ALTER EXTRACT exte, SCN 778899
ALTER EXTRACT exte ETROLLOVER
START EXTRACT exte
If you do not use ETROLLOVER, then if you alter Extract to the previous SCN value, it will not process duplicate records. The command to alter Extract to filter duplicate records is as follows:
ALTER EXTRACT exte, SCN 778899
START EXTRACT exte

The following shows ALTER EXTRACT for an IBM for i journal start point. 

ALTER EXTRACT exte, SEQNO 1234  JOURNAL accts/acctsjrn

The following shows ALTER EXTRACT for an IBM for i journal and receiver start point. 

ALTER EXTRACT exte, SEQNO 1234 JOURNAL accts/acctsjrn JRNRCV accts/jrnrcv0005

The following example alters an Extract on a Db2 LUW system.

ALTER EXTRACT exte, TRANLOG LRI 8066.322711
The following example shows the ALTER EXTRACT options used with PostgreSQL: 
ALTER EXTRACT exte, EOL
ALTER EXTRACT POSTEXT, LSN 0/156784
ALTER EXTRACT POSTEXT, BEGIN 2022-02-18T14:50:43.4230Z