1. Command Line Interface Reference
  2. Admin Client Command Line Interface Commands
  3. ADD EXTRACT

ADD EXTRACT

Valid for supported Db2, HP NonStop, MySQL, Oracle, PostgreSQL, SQL Server, and Sybase ASE databases.

Use ADD EXTRACT to create a source database capture process, known as an Extract. The Extract can be created as a change data Extract, which captures current transactions from the database log or through other means depending on the database vendor, or it can be created as an initial load Extract, which will capture the records that exist in the database tables.

It is recommended to create only one change data or one initial load Extract per source database, however in rare situations, it may improve capture throughput by using multiple Extracts per database.

Oracle GoldenGate can support a large number of concurrent Extract and Replicat processes per deployment, depending on the resources available with the operating system. However, it is recommended to monitor system resources as more Extract or Replicat processes get added, and to keep the total number of processes per deployment to 300 or less.

Note:

  • This command cannot exceed 500 bytes in size for all keywords and input, including any text that you enter for the DESC option.

  • For Db2 for i, this command establishes a global start point for all journals and is a required first step. After issuing the ADD EXTRACT command, you can then optionally position any given journal at a specific journal sequence number by using the ALTER EXTRACT command with an appropriate journal option.

  • For Oracle and PostgreSQL databases, establish a connection to the source database using DBLOGIN USERIDALIAS and then issue the REGISTER EXTRACT command before adding the Extract. For details, see the REGISTER EXTRACT command.

  • For SQL Server, establish a connection to the source database using DBLOGIN USERIDALIAS, prior to adding the Extract. This is necessary for the Administration Service to bind the credential information with the Extract process.

Admin Client Syntax

ADD EXTRACT extract-name
|             { SOURCEISTABLE |
|              [ INTEGRATED ] TRANLOG 
|               {, BEGIN     [ NOW | yyyy-mm-ddThh:mm.ssZ | LSN value | EOL | EOF | EXTRBA archive-offset-number |  SCN  scn } 
|               [, DESC        description    ]
|               [, 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]
extract_name

The name of an Extract. It can contain up to eight characters, see Choosing Names for Processes and Files.

SOURCEISTABLE

Creates an Extract task that extracts entire records from the specified tables for an initial load using Oracle GoldenGate. When using SOURCEISTABLE, do not specify any service options.

Task parameters must be specified in the parameter file, see Add Initial Load Extract Using the Admin Client in Oracle GoldenGate Microservices Documentation.

TRANLOG [bsds_name]

Use this option for all databases. TRANLOG requires the BEGIN option.

(Db2 on z/OS) You can use the bsds_name option for Db2 on a z/OS system to specify the Bootstrap Data Set file name of the transaction log, though it is not required and is not used. You do not need to change existing TRANLOG parameters.

(Oracle) Extract reads the Oracle redo logs directly. See INTEGRATED TRANLOG for an alternate configuration.

INTEGRATED TRANLOG

Valid for Oracle.

Adds Extract in integrated capture mode. In this mode, Extract integrates with the database logmining server, which passes logical change records (LCRs) directly to Extract. Before using INTEGRATED TRANLOG, use the REGISTER EXTRACT command.

BEGIN {NOW | yyyy-mm-ddthh:mm.ssZ}

Specifies a timestamp in the data source at which to begin processing.

  • NOW

    NOW specifies the time at which the ADD EXTRACT command is issued and when the Extract is started, it will attempt to position to a transaction boundary at or after that specific time. Positioning an Extract by NOW is not as accurate as other methods, such as positioning by LSN, SCN, or LRI.

    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-ddT[ hh:mi:[ss[.cccccc]]]Z

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

    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-29T02:15:54Z

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

EXTRBA archive-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) where the next record will be written. 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.

EOF

Valid for MySQL, Db2 z/OS, Db2 LUW, SQL Server, PostgreSQL, Oracle GoldenGate for DAA.

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. Positioning to an LSN is precise.

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:
ADD EXTRACT extn TRANLOG, LSN 0xDEE40E4F27A3245400
Example 2:
ADD EXTRACT extn TRANLOG, LSN 22216433159121980904448

The following example shows the LSN value for SQL Server:

ADD EXTRACT extn TRANLOG, LSN 0X00000d7e:0000036b:0001
LRI value

Valid for Db2 LUW. Specifies a start position in the transaction logs when Extract starts.

You can use the LRI option for Db2 LUW systems to specify the LRI at which extract can start capturing records from the transaction log. You can use the Db2 utility db2logsForRfwd to obtain the LRI. This utility provides LRI ranges present in the Db2 logs.

Note that, although Extract might position to a given LRI, that LRI might not necessarily be the first one that Extract will process. There are numerous record types in the log files that Extract ignores, such as Db2 internal log records. Extract will report the actual starting LRI to the Extract report file.

LOGNUM lognum

Valid for MySQL.

This is the log file number. ADD EXTRACT will fail if the LOGNUM value contains zeroes preceding the value. For example, ADD EXTRACT ext1, TRANLOG, LOGNUM 000001, LOGPOS 0 will fail. Instead, set LOGNUM to 1 for this example to succeed.

LOGPOS logpos

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.

SEQNO sequence_number

Valid for Db2 for i. Starts capture at, or just after, a system sequence number, which is a decimal number up to 20 digits in length.

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 in integrated mode, the SCN value must be greater than the SCN at which the Extract was registered with the database. For more information, see REGISTER EXTRACT.

PARAMS file_name

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

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.

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 Oracle GoldenGate encryption profile associated with the specific client.

CRITICAL

Indicates if the process is critical for the deployment.

PROFILE

Name of the auto start profile.

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.

SOCKSPROXY {host_name | IP_address}[:port] [PROXYCSALIAS credential_store_alias [PROXYCSDOMAIN credential_store_domain]

Use for an alias Extract. Specifies the DNS host name or IP address of the proxy server. You can use either one to define the host though you must use the IP address if your DNS server is unreachable. If you are using an IP address, use either an IPv6 or IPv4 mapped address, depending on the stack of the destination system. You must specify the PROXYCSALIAS. In addition, you can specify the port to use, and the credential store domain.

Examples

The following example creates an Extract group named exte that captures database changes from the transaction logs. Extraction starts with records generated at the time when the Extract group was created and started with ADD EXTRACT. 

ADD EXTRACT extn, TRANLOG, BEGIN NOW
In the following example, from the Admin Client, an Extract group name extw is created to get the database changes from the transaction logs beginning from the specified time.
ADD EXTRACT extn, TRANLOG, BEGIN 2020-08-02T06:05:30.000Z

The following creates an integrated Extract group.

ADD EXTRACT extn, INTEGRATED TRANLOG, BEGIN NOW

The following creates an initial-load Extract named extei. 

ADD EXTRACT extn, SOURCEISTABLE

The following examples create and position Extract at a specific Oracle system change number (SCN) in the redo log.

ADD EXTRACT extn TRANLOG SCN 123456
ADD EXTRACT extn INTEGRATED TRANLOG SCN 123456

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

ADD EXTRACT extn, TRANLOG LRI 8066.322711
The following example creates an Extract with the autostart option using Admin Client.
ADD EXTRACT extn, TRANLOG , BEGIN NOW , AUTOSTART yes