ADD EXTRACT
Valid for supported Db2, HP NonStop, MySQL, Oracle, PostgreSQL, SQL Server.
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 theALTER 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 theREGISTER EXTRACT
command before adding the Extract. For details, see theREGISTER 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]
| [, GTIDSET gtidset
| [, 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 theBEGIN
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 existingTRANLOG
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 theREGISTER 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 theADD 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 byNOW
is not as accurate as other methods, such as positioning byLSN
,SCN
, orLRI
.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
orEOL
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
Iftrack_commit_timestamp
isoff
, 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 thetrack_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
Iftrack_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 associatedtransaction 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
0X
nnn
, wherennn
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 letterx
). -
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 activeLRI
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 byEOL
.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 transactionLSN
at which to start capturing data. An alias for this option isEXTLSN
. 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
or0x
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 behi
orlo
. Set the value ashi
for the entry point of the log file.Lo
is the offset in the log file. TheLSN
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 transactionLSN
at which to start capturing data. An alias for this option isEXTLSN
.The specified
LSN
should exist as a validtran_begin_lsn
found in thecdc.lsn_time_mapping
system table, otherwise the Extract will attempt to position after the provided LSN value.ValidLSN
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 theLRI
at which extract can start capturing records from the transaction log. You can use the Db2 utilitydb2logsForRfwd
to obtain theLRI
. This utility providesLRI
ranges present in the Db2 logs.Note that, although Extract might position to a given
LRI
, thatLRI
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 startingLRI
to the Extract report file. -
LOGNUM lognum
-
Valid for MySQL.
This is the log file number.
ADD EXTRACT
will fail if theLOGNUM
value contains zeroes preceding the value. For example,ADD EXTRACT ext1, TRANLOG, LOGNUM 000001, LOGPOS 0
will fail. Instead, setLOGNUM
to1
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 keywordDESC
or the full wordDESCRIPTION
. -
ENCRYPTIONPROFILE
-
Specifies the name of the Oracle GoldenGate encryption profile associated with the specific client.
-
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:
ADD EXTRACT extract_name, TRANLOG, GTIDSET gtidset
-
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 withADD 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 initial-load Extract named
extei
.ADD EXTRACT extn, SOURCEISTABLE
-
The following examples include all the combinations of valid formats of GTID set.
Example 1:ADD EXTRACT exte, TRANLOG, GTIDSET "E11FA47-71CA-11E1-9E33-C80AA9429562:4"
Example 2:ADD EXTRACT exts, TRANLOG, GTIDSET "3E11FA47-71CA-11E1-9E33-C80AA9429562:1-10"
Example 3:ADD EXTRACT extn, TRANLOG, GTIDSET "3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49"
Example 4:ADD 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. -
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