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 IBM for i journal at a specific journal sequence number.

  • To position any given Informix logical log at a specific LSN.

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

Syntax

ALTER EXTRACT group_name
[, ADD_EXTRACT_attribute]
[, TRANLOG LRI_number]
[, UPGRADE INTEGRATED TRANLOG]
[, DOWNGRADE INTEGRATED TRANLOG [THREADS number]]
[, THREAD number]
[, LSN value]
[, SCN value]
[, ETROLLOVER]

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

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

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

ADD_EXTRACT_attribute

You can change any of the attributes specified with the ADD 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 finance, BEGIN 2011-01-01
ALTER EXTRACT finance, ETROLLOVER
ALTER EXTRACT finance, 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)
TRANLOG LRI_number

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

UPGRADE INTEGRATED TRANLOG

Upgrades the Extract group from classic capture to integrated capture. To support the upgrade, the transaction log that contains the start of the oldest open transaction must be available on the source or downstream mining system. For instructions on making the transition from classic to integrated capture, see the full procedure in Administering Oracle GoldenGate for Windows and UNIX.

DOWNGRADE INTEGRATED TRANLOG [THREADS number]

Downgrades the Extract group from integrated capture to classic capture. When downgrading on a RAC system, the THREADS option must be used to specify the number of RAC threads. On a non-RAC system, you can optionally specify THREADS 1 to cause the downgraded classic Extract to run in threaded mode with one thread, which is similar to doing an ADD EXTRACT with THREADS 1 on a non-RAC system.

See Administering Oracle GoldenGate for Windows and UNIX for the full procedure for performing the transition from integrated to classic capture.

To support the downgrade, the transaction log that contains the start of the oldest open transaction must be available on the source or downstream mining system. For information about integrated capture, see Installing and Configuring Oracle GoldenGate for Oracle Database.

THREAD number

Valid for classic capture mode. In an Oracle RAC configuration, alters Extract only for the specified redo thread. Only one thread number can be specified.

LSN value

Valid for Informix. Repositions Extract to by the specified LSN to begin from the EOF in the logical log. This option is valid classic capture mode.

SCN value

Valid for Oracle. Repositions Extract to the transaction in the redo log that has the specified Oracle system change number (SCN). This option is valid both for integrated capture mode and classic capture mode.

ETROLLOVER

Causes Extract to increment to the next file in the trail sequence when restarting. For example, if the current file is ET000002, the current file will be ET000003 when Extract restarts. A trail can be incremented from 000001 through 999999, and then the sequence numbering starts over at 000000.

CPU number

Valid for SQL/MX. Specifies the number of the CPU to be used for the process. Valid values are numbers 1 - 15 and -1 is default, which is assigned 1 higher than the last Manager started.

PRI number

Valid for SQL/MX. Specifies the Extract process priority. Valid values are numbers are 1 - 199 and -1 is the default, and is the same as the manager process priority.

HOMETERM device_name

Valid for SQL/MX. Specifies the name of the device to be used and must be a terminal or process. It can be entered in either Guardian $ or OSS /G/xxxxx form. The default is $zhome or the current session HOMETERM when $zhome is not defined.

PROCESSNAME process_name

Valid for SQL/MX. Specifies the name of the process as alphanumeric string up to five characters and can be entered in either Guardian $ or OSS /G/xxxxx form. The default is a system generated process name.

BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]}
[JOURNAL journal_library/journal_name
[JRNRCV receiver_library/ receiver_name]] |
, EOF [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.

Examples

Example 1   

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

ALTER EXTRACT finance, BEGIN 2011-01-01
Example 2   

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

ALTER EXTRACT finance, EXTSEQNO 26, EXTRBA 338
Example 3   

The following alters Extract in an Oracle RAC environment, and applies the new begin point only for redo thread 4.

ALTER EXTRACT accounts, THREAD 4, BEGIN 2011-01-01
Example 4   

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

ALTER EXTRACT sales, LSN 3454:875:445
Example 5   

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

ALTER EXTRACT finance, ETROLLOVER
Example 6   

The following alters Extract to upgrade to integrated capture.

ALTER EXTRACT finance, UPGRADE INTEGRATED TRANLOG
Example 7   

The following alters Extract to downgrade to classic capture in a RAC environment.

ALTER EXTRACT finance, DOWNGRADE INTEGRATED TRANLOG THREADS 3
Example 8   

The following alters Extract in an Oracle environment to start processing data from source database SCN 778899.

ALTER EXTRACT finance, SCN 778899
Example 9   

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

ALTER EXTRACT finance, SEQNO 1234  JOURNAL accts/acctsjrn
Example 10   

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

ALTER EXTRACT finance, SEQNO 1234 JOURNAL accts/acctsjrn JRNRCV accts/jrnrcv0005
Example 11   

The following alters an Extract on a SQL/MX NonStop platform.

ALTER EXTRACT exttcp, CPU 1, PRI 150, HOMETERM /G/zhome,  PROCESSNAME $ose01
Example 12   

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

ALTER EXTRACT extcust, TRANLOG LRI 8066.322711