Use Extract commands to create and manage Extract groups. The Extract process captures either full data records or transactional data changes, depending on configuration parameters, and then sends the data to a target system to be applied to target tables or processed further by another process, such as a load utility.
ADD EXTRACT
Create an extract group.
Syntax: regular extract
ADD EXTRACT group name -- Group name max is 8, do not end with numbers {, SOURCEISTABLE | -- Creates initial load for whole table , TRANLOG [bsds name] | -- Use transaction log as data source. bsds name: for DB2 , VAM | -- Use vendor access module to extract data , EXTFILESOURCE file name | -- Use an extract file as data source (pump). File name needs to be full path , EXTTRAILSOURCE trail name | -- Use a trail file as data source (pump). File name needs to be full path , VAMTRAILSOURCE VAM trail name} -- Use VAM trail file as data source. {, BEGIN {NOW | -- When ADD EXTRACT command is issued. Do not use for pumps. yyyy-mm-dd [hh:mi:[ss[.cccccc]]]} | , EXTSEQNO seqno -- For Oracle , EXTRBA relative byte address | -- For DB2 , LOGNUM log number, LOGPOS byte offset | -- For c-tree , LSN value | -- For SQLServer , EXTRBA relative byte address | , EOF | LSN value | -- DB2 , PAGE data page, ROW row | -- Sybase } [, THREADS n] -- Number of redo threads when extracting data from Oracle RAC. [, PASSIVE] -- Runs in passive mode. Can only be start/stop by an alias extract group on target system. [, PARAMS parameter file] -- Specify full path of parameter file if not default 'dirprm' [, REPORT report file] -- Specify full path of report file if not default 'dirrpt' [, DESC description] -- Some description
Syntax: Alias Extract
ADD EXTRACT group name , RMTHOST {host name | IP address} -- Identifies extract group as an alias extract , {MGRPORT port} | -- Remote manager port (dynamic Collector) {PORT port} -- Static Collector port [, RMTNAME name] -- Specify passive extract name [, DESC description]
Examples
Regular extract
GGSCI 19> add extract e_cust, tranlog, begin now EXTRACT added. GGSCI 20> info extract * EXTRACT E_CUST Initialized 2010-04-15 10:42 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Log Read Checkpoint Oracle Redo Logs 2010-04-15 10:42:05 Seqno 0, RBA 0
Initial load extract group
GGSCI 21> add extract e_load, sourceistable EXTRACT added. GGSCI 23> info extract e_load EXTRACT E_LOAD Initialized 2010-04-15 10:49 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Not Available First Record Record 0 Task SOURCEISTABLE
Delayed start extract.
GGSCI 24> add extract e_delay, tranlog, begin 2010-4-15 02:00 EXTRACT added. GGSCI 26> info extract e_delay EXTRACT E_DELAY Initialized 2010-04-15 11:30 Status STOPPED Checkpoint Lag 09:30:59 (updated 00:00:12 ago) Log Read Checkpoint Oracle Redo Logs 2010-04-15 02:00:00 Seqno 0, RBA 0
Passive extract.
GGSCI 27> add extract e_pass, tranlog, begin now, passive EXTRACT added. GGSCI 28> add extract e_pass_a, rmthost localhost, mgrport 7909 , rmtname e_pass EXTRACT added. GGSCI 29> info extract e_pass EXTRACT E_PASS Initialized 2010-04-15 11:33 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:45 ago) Log Read Checkpoint Oracle Redo Logs 2010-04-15 11:33:20 Seqno 0, RBA 0 GGSCI 33> info extract e_pass_a EXTRACT E_PASS_A Initialized 2010-04-15 11:35 Status STOPPED ALIAS E_PASS LOCALHOST:7909 Forwarding command to E_PASS@LOCALHOST:7909 EXTRACT E_PASS Initialized 2010-04-15 11:33 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:01:58 ago) Log Read Checkpoint Oracle Redo Logs 2010-04-15 11:33:20 Seqno 0, RBA 0
ALTER EXTRACT
Syntax
ALTER EXTRACT group name [, ADD EXTRACT attribute] [, THREAD number] [, ETROLLOVER] -- Rollover to next trail file
Examples
alter extract e_delay, etrollover
CLEANUP EXTRACT
Delete extract run history.
Syntax
CLEANUP EXTRACT[, SAVE n] -- Save most n recent records
Examples
GGSCI 36> cleanup extract e_delay ERROR: Nothing to cleanup for EXTRACT E_DELAY.
DELETE EXTRACT
Delete an extract group. It deletes checkpoint file only. Parameter file and trail file are not deleted.
Syntax
DELETE EXTRACT[!] -- Delete all extract groups associated with a wildcard (*) without prompting.
Examples
GGSCI 37> delete extract e_pass_a Deleted EXTRACT E_PASS_A.
INFO EXTRACT
Show info about an extract group.
Syntax
INFO EXTRACT[, SHOWCH [n]] -- Show checkpoints [, DETAIL] -- Show details [, TASKS -- Show tasks | ALLPROCESSES] -- Show all processes
Examples
info extract e_cust info extract e_cust, detail info extract e_cust, showch info extract *, tasks info extract *, allprocesses
KILL EXTRACT
Kill an extract process if it can *not* be stopped by STOP EXTRACT.
Syntax
KILL EXTRACT group name
Examples
GGSCI 51> kill extract e_cust EXTRACT E_CUST is already stopped. GGSCI 52> kill extract e* EXTRACT E_CUST is already stopped. EXTRACT E_DELAY is already stopped. EXTRACT E_PASS is already stopped.
LAG EXTRACT
Displays true lag between extract and data source.
Syntax
LAG EXTRACT
Examples
lag extract e_cust
SEND EXTRACT
Syntax
Examples
START EXTRACT
Syntax
START EXTRACT group_name
Examples
start extract e_cust
STATS EXTRACT
Get stats about extract process.
Syntax
STATS EXTRACT group name [, statistic] -- e.g. TOTAL, DAILY, HOURLY, LATEST, RESET [, TABLE table] [, TOTALSONLY table spec] [, REPORTFETCH | NOREPORTFETCH] [, REPORTRATE time units] -- e.g. HR, MIN, SEC [, ... ]
Examples
STATS EXTRACT finance, TOTAL, HOURLY, TABLE acct, REPORTRATE MIN, RESET, REPORTFETCH
STOP EXTRACT
Stops an extract process.
Syntax
STOP EXTRACT group name