2 Oracle GoldenGate Parameters

ABORTDUPERRWINDOW

Valid for

Syncfile

Description

Use ABORTDUPERRWINDOW to specify an alternative time interval for Syncfile to restart processes that abend.

To duplicate files, Syncfile starts either a FUP or TACL process to perform the duplication. If one of these processes abends, Syncfile automatically restarts it. However, if the process abends more than three times within one hour, the Syncfile process abends.

Default

1 HOUR

Syntax

ABORTDUPERRWINDOW num_units unit;

num_units

The number of units of time, as in 1 or 10.

unit

The time unit. Specify one of: SECONDS, MINUTES or HOURS.

Example

ABORTDUPERRWINDOW 4 HOURS;

ADD DEFINE

Valid for

GLOBALS

Description

ADD DEFINE provides the ability to specify valid DEFINE statements to help with resolution of default locations, file names, or anything that can be set using a standard DEFINE.

Oracle GoldenGate recommends that you use the defaults, however, if your installation requires that you use other definitions, you can define them in the GLOBALS parameter file using ADD DEFINE. By specifying defines in the GLOBALS parameter file, they are established for all the affected components you install; you don't have to specify them in other parameter files.

Default

ADD DEFINE changes the following defaults:

• The default for the Oracle GoldenGate environment prefix, GGS.

• The default volume and subvolume for AUDCFG, $SYSTEM.GGS.AUDCFG.

• The default number of seconds the Logger process delays before closing the old file when rolling to a new file.

• The default volume and subvolume for parameter files, $VOL.GGSPARM.

• The default volume and subvolume for report files, $VOL.GGSRPT.

  Note:

  Defaults set by ADD DEFINE for =GGS_PARAMS or =GGS_REPORT can be overridden by the appropriate GGSCI ADD or ALTER command.

• The default TCPIP process name, $ZTC0

  Note:

  Defaults set by the ADD DEFINE for =TCPIP^PROCESS^NAME can be overridden by using the TCPIPPROCESSNAME parameter for Manager or Extract or using the RMTHOST TCPIPPROCESSNAME option.

When you create a new volume, subvolume, or file name, you need to specify the new location and file name in the appropriate places, such as when you execute a GGSCI ADD or ALTER command, or in any parameter file where you specified a location or file name.

Syntax

ADD DEFINE {define}, [class_node_vol_spec]

define

The define must be one of the following:

=GGS_AUDCFG

Changes the default file name of the AUDCFG file.

=GGS_DB_SUBVOL

Defines the subvolume location of the Oracle GoldenGate database.

=GGS_LOGFILE_CLOSE_DELAY

Changes the delay before Logger closes the old file when rolling to a new one. The default is 120 seconds.

=GGS_PARAMS

Changes the default subvolume used to store parameter files.

=GGS_PREFIX

Lets you define a different Oracle GoldenGate prefix for process names and subvolumes.

=GGS_REPORT

Changes the default subvolume used to store the report file.

=TCPIP^PROCESS^NAME

Changes the default TCPIP process name.

class_node_vol_spec

If you are defining a new volume and subvolume or a file name enter class_node_vol_spec as follows:

• To specify a file name, enter:

  ADD DEFINE =GGS_type, CLASS MAP, 
  FILE \NODE.$volume.subvolume.file_name
  

• To specify a new default volume and subvolume, enter:

  ADD DEFINE =GGS_type, CLASS DEFAULTS, 
  VOLUME \NODE.$volume.subvolume
  

• To specify a new prefix, enter:

  ADD DEFINE =GGS_PREFIX, CLASS MAP, FILE $prefix
  

• To specify a delay time for closing the log file, enter:

  ADD DEFINE =GGS_LOGFILE_CLOSE_DELAY, CLASS MAP, FILE $xseconds
  

  Where FILE is up to five digits for seconds and x can be any character a to z.

Examples

Example 1   

This example changes the default subvolume used to store parameter files to $GGSPROD.

ADD DEFINE =GGS_PARAMS, CLASS DEFAULTS, VOLUME \PROD.$DATA3.GGSPROD

Example 2   

This example changes the default subvolume used to store the report files.

ADD DEFINE =GGS_REPORT, CLASS DEFAULTS, VOLUME \PROD.$DATA3.NEWRPT

Example 3   

This example changes the location of the audit configuration file.

ADD DEFINE =GGS_AUDCFG, CLASS MAP, FILE \NODE.$DATA1.GGS.AUDCFG

Example 4   

This example identifies the subvolume where the default database is installed. This is not recommended because moving the database can cause problems when migrating to a new release of Oracle GoldenGate. If Extract, Replicat, or Syncfile are moved to a different subvolume it may be necessary to use the defines since they must know where to find checkpoint files.

ADD DEFINE =GGS_DB_SUBVOL, CLASS DEFAULTS, VOLUME, \PROD.$DATA6.GGS

Example 5   

This example sets a delay of 240 seconds for Logger to wait before closing the old file when rolling to a new file.

ADD DEFINE =GGS_LOGFILE_CLOSE_DELAY, CLASS MAP, FILE $a240

Example 6   

This example changes the default $ZTC0 TCPIP process name to $ZTC3.

ADD DEFINE =TCPIP^PROCESS^NAME, FILE $ZTC3

ALLOCFILES

Valid for

Extract, Replicat

Description

Use ALLOCFILES to control the incremental number of memory structures allocated once the initial memory allocation specified by the NUMFILES parameter is reached (see "NUMFILES"). Together, these parameters control how process memory is allocated for storing information about the source and target tables being processed.

The default values should be sufficient for both NUMFILES and ALLOCFILES, because memory is allocated by the process as needed, system resources permitting.

ALLOCFILES must occur before any TABLE or MAP entries to have any effect.

Default

500

Syntax

ALLOCFILES num_structures

num_structures

The additional number of memory structures to be allocated. Do not set ALLOCFILES to an arbitrarily high number, or memory will be consumed unnecessarily. The memory structures of Oracle GoldenGate support up to two million tables.

Example

ALLOCFILES 1000

ALTFILERESOLVE | NOALTFILERESOLVE

Valid for

Extract

Description

Use ALTFILERESOLVE to resolve wildcards during the startup of Extract. As the wildcards are resolved, audited alternate key files are placed on the "exclude list" so no data will be sent for them. This avoids duplicate key errors that can otherwise occur when alternate key file I/O is encountered before primary file I/O. A message is produced for each wildcard entry as it is processed.

Unstructured files, SQL/MP tables, SQL/MX tables, and NSK standard subvolumes are ignored during the scan.

After startup, any file create or alter operation triggers Extract to evaluate the file for audited alternate key files to add to the exclude list.

To avoid the initial scan and its startup overhead, set NOALTFILERESOLVE. When this is set, alternate key files will be identified when the first primary file I/O is encountered.

Default

ALTFILERESOLVE

Syntax

ALTFILERESOLVE | NOALTFILERESOLVE

Example

The following is an example of the message produced during a wildcard scan at startup.

2010-02-16 08:10:58.161307 Scanning $*.A12GEN* for Alternate Key Files
2010-02-16 08:10:58.230067 Files scanned 16
2010-02-16 08:10:58.230788 Scanning $*.A12SQL* for Alternate Key Files
2010-02-16 08:10:58.289293 Files scanned 72

ALTINPUT

Valid for

Extract

Description

Use ALTINPUT for direct file extraction. With ACI files, multiple files can be in use at one time. For example, processing can continue on Monday's file after midnight, while Tuesday's file is opened for new data. To handle a multiple file situation, run more than one Extract process for the file sequence. Use the ALTINPUT RANGE option to distribute the files across the processes so that Extract never processes two files in sequence.

You can also use ALTINPUT to specify the access mode of the file open, and to move Extract to the next sequential file if an application has a file open that it is not updating.

To set up Extract for direct file extraction, specify the FILETYPE option in the GGSCI commands ADD or ALTER EXTRACT. When FILETYPE is specified, Extract uses a series of application files as the source of database changes rather than TMF audit trails or log trails. ALTINPUT sets up processing rules for the source files.

Syntax

ALTINPUT [RANGE (x OF y)]
[, SHARED | EXCLUSIVE | PROTECTED]
[, OPENTIMEOUT minutes]
[, TEMPLATE template]
[, USENEXTMODIFIED]
[, NOWAITNEXTMODIFIED]
[, FASTREADS]
[, WAITNEXTRBA num_bytes]
[, JTSOFFSET byte_offset]
[, TANDEMTSOFFSET byte_offset]
[, ONEFILE]

RANGE (x OF y)

Use RANGE to process multiple files within a sequence simultaneously. For information on specifying the RANGE option see "Specifying the ALTINPUT RANGE Option"

SHARED | EXCLUSIVE | PROTECTED

Specifies the access mode of the OPEN for the input file. Defaults to SHARED, in read-only mode.

OPENTIMEOUT minutes

Determines when it is safe to assume that there are no more updates to the input file.

• If OPENTIMEOUT is not specified, Extract assumes there are no more updates five seconds after the last one is received.

• OPENTIMEOUT minutes instructs Extract to wait for the specified number of minutes before assuming there are no more updates.

An application can have an open file that is not being updated, such as when a report program has opened a file in other than read-only mode.

TEMPLATE template

Specifies a file name template when FILETYPE is ENTRY that can be a wildcard.

The following example specifies files on any volume starting with $DATA, on the MYDAT subvolume, beginning with FL and ending in exactly three more characters.

TEMPLATE $DATA*.MYDAT.FL???

In GGSCI, the ADD EXTRACT or ADD REPLICAT command could specify that the first file to process would be $DATA5.MYDAT.FLABC.

NOWAITNEXTMODIFIED

By default, Extract waits until the next file in the sequence is modified before processing it. Use NOWAITNEXTMODIFIED to move to the next file regardless of when it was modified.

FASTREADS

Causes Extract to perform bulk reads of the source file set, boosting read performance.

WAITNEXTRA num_bytes

Causes Extract to wait until the next file in the sequence has accumulated a specified number of bytes.

JTSOFFSET byte_offset

Specifies the byte offset of a 64-bit Julian timestamp field within each record when specifying FILETYPE ENTRY. Various Oracle GoldenGate processes use the Julian timestamp to help determine replication lag. Use a timestamp field that reflects when the record was inserted into the database.

TANDEMTSOFFSET byte_offset

Specifies the byte offset of a 48-bit timestamp field within each record when specifying FILETYPE ENTRY. Various Oracle GoldenGate processes use the Julian timestamp to help determine replication lag. Use a timestamp field that reflects when the record was inserted into the database.

ONEFILE

Specify ONEFILE if FILETYPE ENTRY is used and the file set to process consists of a single file.

Specifying the ALTINPUT RANGE Option

ALTINPUT RANGE allows processing of multiple ACI BASE24 files within a sequence simultaneously. Since two TLF or PTLF files can be active at the same time, using ALTINPUT RANGE enables one Extract to process even Julian date files and the other Extract to process odd Julian date files. Even and odd Julian dates can be determined by computing a Julian date from the last six digits of each file name.

The "even" Extract process retrieves data from files where the remainder of the Julian date divided by 2 is zero (range 1 of 2). The "odd" Extract retrieves data from files where the remainder of the Julian date divided by 2 is 1 (range 2 of 2).

For example, an "odd" Extract instance processes files named PO990101, PO990103, PO990105 (Julian dates 2451181, 2451183, and 2451185, respectively). An "even" Extract instance processes files PO990102, PO990104, PO990106 (Julian dates 2451180, 2451182, and 2451184, respectively). This enables extraction for files PO990101 and PO990102 at the same time.

ASSUMETARGETDEFS

Valid for

Replicat

Description

Use ASSUMETARGETDEFS when the source and target files have the same record or columns structure (for example, in hot site replication). This parameter is useful when source and target data definitions match and a definitions file is not available for the source database.

Use SOURCEDEFS to use the source data definitions.

Syntax

ASSUMETARGETDEFS

AUDITING

Valid for

GLOBALS

Description

Use AUDITING to automatically set other system parameters to TMF, non-TMF, or both. For example, setting AUDITING to NONTMF automatically sets the Replicat NOAUDITREPS parameter.

Default

ALL

Syntax

AUDITING {TMF | NONTMF | ALL}

AUDITREPS | NOAUDITREPS

Valid for

Replicat

Description

Use AUDITREPS to determine if Replicat transactions are framed within TMF transactions. It is highly recommended that you use AUDITREPS to ensure the integrity of the target database.

Use NOAUDITREPS when the target files are not protected by TMF. If any target tables or files are audited, AUDITREPS is required.

Default

AUDITREPS

Syntax

AUDITREPS | NOAUDITREPS

AUDITRETRYDELAY

Valid for

Extract

Description

Use AUDITRETRYDELAY to control the amount of time Extract waits at the end of TMF audit trails before attempting to read more data. Setting AUDITRETRYDELAY to a higher value can save system resources, but can also result in longer lag time when replicating data.

Default

AUDITRETRYDELAY 1

Syntax

AUDITRETRYDELAY seconds | AUDITRETRYDELAYCSECS centiseconds

seconds

The number of seconds to wait before reading more data.

centiseconds

The number of centiseconds to wait before reading more data.

AUDSERVCACHEBLOCKS

Valid for

Extract

Description

Use AUDSERVCACHEBLOCKS to determine the amount of cache space reserved for SQL table definitions. This parameter affects the amount of memory reserved by Audserv for SQL table definitions. Cache blocks are useful for processing SQL update statements quickly. The default is sufficient for most installations.

Default

300

Syntax

AUDSERVCACHEBLOCKS cache

cache

The amount of cache space. The maximum recommended value is 1000. At a minimum, allocate one cache block per frequently accessed table partition.

AUDSERVCPU

Valid for

Extract

Description

Use AUDSERVCPU to start the audit reading process on a different CPU from Extract. For example, AUDSERVCPU 1 starts Audserv for the master audit trail and any auxiliary trails in CPU 1. AUDSERVCPUS 3, 5, 6 starts Audserv for the master trail in CPU 3, for AUX01 in CPU 5, and for AUX02 in CPU 6.

Using AUDSERVCPU results in the constant gathering of audit while Extract performs its own processing. This technique can reduce batch run times by up to 20%.

When TMF uses auxiliary TMF audit trails, AUDSERVCPU can specify different processors for each Audserv process. If you specify fewer CPU than the number of master and auxiliary TMF audit trails, the last CPU specified is used as the default.

When you specify IGNOREAUXTRAILS, you should still specify AUDSERVCPU with the CPU for the ignored auxiliary trail as a placeholder for any subsequently included auxiliary trails.

Default

The last CPU specified

Syntax

AUDSERVCPU[S] cpu_num [, cpu_num...]

cpu_num

The CPU identifier.

AUDSERVPARAM

Valid for

Extract

Description

Use AUDSERVPARAM to pass parameters that are specific to the Audserv process.

Guardian must be version D46 or G06 and later for AUDSERVPARAM.

Default

ABENDONSECURITYCHECK, IGNORENONDATACHANGES, IGNOREALTFILES, SQLCATCLOSEDELAY 60

Syntax

AUDSERVPARAM 
[ABENDONSECURITYCHECK | NOABENDONSECURITYCHECK]
[ARLIBERROR error_number, response]
[ARERRORREPORTINTERVAL seconds]
[GETNONDATACHANGES | IGNORENONDATACHANGES]
[GETALTFILES | IGNOREALTFILES]
[EXCLUDEFILECODES (file_code [, ...])
[SQLCATCLOSEDELAY seconds]

ABENDONSECURITYCHECK | NOABENDONSECURITYCHECK

With ABENDONSECURITYCHECK Audserv will log a message and then abend when the security check omits a file. NOABENDONSECURITYCHECK triggers Audserv to log a message, but not abend. The default is ABENDONSECURITYCHECK.

ARLIBERROR error_number, response

Specifies the action to take when the error number is triggered. Valid response are:

IGNORE

Continue processing and do not issue a message.

WARN

Issue a warning message and continue processing.

ABEND

Issue an error message and end processing.

ARERRORREPORTINTERVAL

Specifies the number of seconds to wait before the reissue of a warning message.

GETNONDATACHANGES | IGNORENONDATACHANGES

Filters records for SQL partition moves and splits. The default is IGNORENONDATACHANGES. NOGETNONDATACHANGES is a synonym for IGNORENONDATACHANGES.

When using NOGETNONDATACHANGES, you cannot extract any data changes produced by the RDF subsystem.

GETALTFILES | IGNOREALTFILES

By default Audserv excludes Enscribe alternate keys. This means, when a file is added to Audserv's "include" list, it will search for any alternate keys and automatically exclude them.

If you are using wildcards in your files to denote alternate keys, you may wish to override this feature. There are two ways to override the default programming. If you wish to include alternate keys for specific files, you may specify the following syntax in your parameter file:

FILE $VOL.SUBVOL.PRIMARY;
FILE $VOL.SUBVOL.ALTFILE0;
FILE $VOL.SUBVOL.ALTFILE1;

where ALTFILE0 and ALTFILE1 represent alternate keys in the PRIMARY file.

If you wish to replicate all alternate keys, you can use the GETALTFILES option in your Extract parameter file as follows:

AUDSERVPARAM GETALTFILES

The default setting for this option is IGNOREGETALTFILES.

EXCLUDEFILECODES (file_code)

The numeric file code of a type of file whose audit is to be excluded.

SQL/MX and SQL/MP catalog files (file codes 563, 564, 565, 572, and 585) are automatically excluded and do not need to be listed.

SQLCATCLOSEDELAY seconds

Sets the time delay after which the SQL/MP catalog tables that have not been accessed are closed. The value must be between 10 and 3600 seconds.

The default is to close the catalog tables when they have not been accessed for 60 seconds.

AUDSERVPREFIX

Valid for

Extract

Description

Use AUDSERVPREFIX to tell Extract to assign its Audserv process a sequence of names with the same prefix. The names are processed in the same order as MAT, AUX01, AUX02, and so on.

Syntax

AUDSERVPREFIX prefix

prefix

A 3-character prefix, as in $GAX.

Example

Specifying:

AUDSERVPREFIX $GAX 

would assign:

MAT = $GAX00
AUX01 = $GAX01
AUX02 = $GAX02

If you use AUDSERVPROCESS, you can not use AUDSERVPREFIX, and vice versa.

AUDSERVPROCESS

Valid for

Extract

Description

Use AUDSERVPROCESS to control the names assigned to Audserv processes. The names are processed in the same order as MAT, AUX01, AUX02, and so on.

Syntax

AUDSERVPROCESS name

name

Assign a $5-character name

Example

Specifying:

AUDSERVPROCESS $GGMAT, $GGX01, $GGX02 

results in the following assignments:

MAT - $GGMAT
AUX01 - $GGX01
AUX02 - $GGX02

If you use AUDSERVPROCESS, you can not use AUDSERVPREFIX, and vice versa.

AUDSERVPROGRAM

Valid for

Extract

Description

Use AUDSERVPROGRAM to run both Native mode and TNS mode Audserv programs. AUDSERVPROGRAM overrides the default program file name Audserv with the program name of your choice.

Syntax

AUDSERVPROGRAM program_name

Example

The following example starts Audserv using the program EXTRACTN and with the name AUDSERVN.

GGSCI> ADD EXT FINANCE, BEGIN NOW, PROCESS, PROGRAM EXTRACTN, DESCRIPTION "NATIVE TMF EXTRACT"
GGSCI> ADD EXTTRAIL $DATA.GGSDAT.ET, EXTRACT FINANCE
EXTRACT FINANCE
AUDSERVPROGRAM $DATA.GGS.AUDSERVN
EXTTRAIL $DATA.GGSDAT.ET
FILE $PROD.ACCOUNT.*;

AUTORESTART

Valid for

Manager

Description

Use the AUTORESTART parameter to start one or more Extract and Replicat processes automatically after they fail. AUTORESTART provides fault tolerance when something temporary interferes with a process, such as intermittent network outages or programs that interrupt access to transaction logs.

You can use multiple AUTORESTART statements in the same parameter file.

Default

Do not auto-restart

Syntax

Syntax AUTORESTART process_type group_name
[, RETRIES max_retries]
[, WAITMINUTES wait_minutes]
[, RESETMINUTES reset_minutes]

process_type

Specify one of the following:

• EXTRACT or EXT

• REPLICAT or REP

• ER (Extract and Replicat)

• LOGGER

• SYNCFILE or SYNC

• COORD (Coordinator)

group name

A group name or wildcard specification for multiple groups. When wildcards are used, Oracle GoldenGate starts all groups of the specified process_type on the local system that satisfy the wildcard.

RETRIES max_retries

The maximum number of times that Manager should try to restart a process before aborting retry efforts.

If RETRIES is not set, MAXABENDRESTARTS is used. If neither is set, the default number of tries is 2.

WAITMINUTES wait_minutes

The amount of time to pause between discovering that a process has terminated abnormally and restarting the process. Use this option to delay restarting until a necessary resource becomes available or some other event occurs. The default delay is 1 minute.

RESETMINUTES reset_minutes

The window of time a restarted process must run without abending for the retries count to be reset to the maximum. If the process abends within this time, the maximum retries value is decremented. When it reaches zero, no more restarts are attempted.

For example, RETRIES is set to 2 and RESETMINUTES is 15. If process A is restarted and runs without abending for 15 minutes RETRIES will be reset to 2. If instead, process A abends in less than 15 minutes, RETRIES becomes 1. If it is restarted and abends again within 15 minutes, no more retries will be attempted.

If RETSETMINUTES is not set for AUTORESTART, RESTARTINERVAL is used. If neither option is set, the default is 20 minutes.

Example

In the following example, Manager tries to start all Extract processes three times after failure within a one hour time period, and waits five minutes before each attempt.

AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

AUTOSTART

Valid for

Manager

Description

The AUTOSTART parameter specifies the processes to be automatically started by Manager. When Manager starts up it scans the list and attempts to start any listed process that is not already running. To activate changes to the list, stop and restart the Manager process.

Syntax

AUTOSTART [group_type] {group_name | process_name}
      [, ALLPROCESSES]

group_type

The type of group to be started. This is an optional entry, but if used must be one of the following.

• EXTRACT or EXT

• REPLICAT or REP

• ER (Extract and Replicat)

• LOGGER

• SYNCFILE or SYNC

• COORD (Coordinator)

group_name

The group name. Required entry for group types other than Logger. Wildcards can be used for part or all of the name.

Note: TASK groups that match the wildcard will not be started. To start a TASK group either specify a group without a wildcard or use the ALLPROCESSES option.

process_name

The Logger process name in the format $xxnnn. Required entry for the LOGGER group_type. Wildcards can be used for all or part of the name.

ALLPROCESSES

Specifies that TASK groups should be included in wildcarded groups to be started.

Examples

Example 1   

The following will start all Replicat processes that begin with R20.

AUTOSTART REPLICAT R20*

Example 2   

The following will start all Extracts.

AUTOSTART EXT *

Example 3   

The following will start the logger named $ABC01.

AUTOSTART LOGGER $ABC01

Example 4   

The following will start all groups that begin with R20 except TASK groups.

AUTOSTART R20*

Example 5   

The following will start all groups, including TASK groups, that begin with R20.

AUTOSTART R20*, ALLPROCESSES

BACKUPCPU

Valid for

Manager

Description

Use BACKUPCPU to specify a CPU that is running the backup Manager process.

Syntax

BACKUPCPU cpu_number

cpu_number

The identifier for the CPU that is running the backup Manager process.

BEGIN

Valid for

Extract, Replicat

Description

Use BEGIN to specify a date and time at which to start capturing data. Any record with a timestamp greater than or equal to the time specified by BEGIN that satisfies other criteria is extracted.

SPECIALRUN uses BEGIN to determine its start date and time.

Syntax

BEGIN date time[:seconds][.centiseconds]

date

The date in yyyy-mm-dd format.

time

The time in hh:mm format.

seconds

You can optionally specify seconds with the time option, as in hh:mm:ss.

centiseconds

You can optionally specify centiseconds seconds with the time and seconds options, as in hh:mm:ss.cccccc.

Examples

Example 1   

BEGIN 2010-08-12 08:00 specifies a timestamp for August 12, 2010 at 8:00 AM.

Example 2   

BEGIN 2010-08-12 08:00:30.000030 specifies a timestamp for August 12, 2010 at 8:00:30.000030 AM.

BULKIOLOAD | NOBULKIOLOAD

Valid for

Replicat

Description

Use BULKIOLOAD to enable bulk I/O, in 28K blocks, whenever writing unstructured data to structured or unstructured files. Typically, this occurs when replicating FUP LOAD or FUP DUP operations, and allows Replicat to process those types of operations many times faster than with conventional I/O.

BULKIOLOAD applies to all subsequent MAP entries. Use NOBULKIOLOAD to turn off BULKIOLOAD for subsequent MAP statements.

Syntax

BULKIOLOAD | NOBULKIOLOAD

CHECKINTERVAL

Valid for

Syncfile

Description

Use CHECKINTERVAL to change the interval between the time a scheduled event occurs and the time that Syncfile duplicates an associated file. Once every minute, by default, Syncfile determines which of the scheduled events have occurred. When the event takes place, Syncfile duplicates the associated files.

Default

1 minute

Syntax

CHECKINTERVAL num_units unit;

num_units

The number of units of time.

unit

The time unit type. Specify one of: SECONDS, MINUTES or HOURS.

Example

CHECKINTERVAL 10 SECONDS;

CHECKMINUTES

Valid for

Manager

Description

Use CHECKMINUTES to determine how often Manager performs maintenance activities. If audit trails roll over frequently and the trails are actively managed, decreasing the frequency of maintenance activities can significantly affect performance.

Default

10

Syntax

CHECKMINUTES minutes

minutes

The frequency, in minutes, for performing maintenance.

Example

The following example specifies maintenance activities are performed every 20 minutes.

CHECKMINUTES 20

CHECKPARAMS

Valid for

Extract, Replicat

Description

Use CHECKPARAMS to verify parameter file contents before processing data. The program performs parameter checking, then quits before processing data.

Default

No parameter check

Syntax

CHECKPARAMS

CHECKPOINTSECS

Valid for

Extract and Replicat

Description

Use the CHECKPOINTSECS parameter to control how often Extract and Replicat make their routine checkpoints.

• Decreasing the value causes more frequent checkpoints. This reduces the amount of data that must be reprocessed if the process fails, but it could cause performance degradation because data is written to disk more frequently.

• Increasing the value causes less frequent checkpoints. This might improve performance, but it increases the amount of data that must be reprocessed if the process fails. When using less frequent Extract checkpoints, make certain that the transaction logs remain available in case the data has to be reprocessed.

  Note:

  In addition to its routine checkpoints, Replicat also makes a checkpoint when it commits a transaction.

This parameter is only valid when using log-based extraction. Avoid changing CHECKPOINTSECS unless directed to do so by Oracle GoldenGate Technical Support.

Default

10

Syntax

CHECKPOINTSECS seconds

seconds

The number of seconds to wait before issuing a checkpoint.

Example

CHECKPOINTSECS 20

CHECKUNIQUEKEY | NOCHECKUIQUEKEY

Valid for

Replicat

Description

Use CHECKUNIQUEKEY specify that the target file should be checked to ensure that the key does not already exist before inserting a record.

If a unique alternate key condition is violated while attempting to insert a record to an entry-sequenced Enscribe file, an "empty record" results in the target file.

Use NOCHECKUNIQUEKEY to reset CHECKUNIQUEKEY behavior for subsequent entries. CHECKUNIQUEKEY parameter affects only files with unique alternate keys. CHECKUNIQUEKEY applies only to MAP statements that follow it in the parameter file.

Default

NOCHECKUNIQUEKEY

Syntax

CHECKUNIQUEKEY | NOCHECKUNIQUEKEY

CLEANUPSAVECOUNT

Valid for

Manager

Description

Use CLEANUPSAVECOUNT to tell Manager how many old run history records to save for Extract and Replicat groups. Every evening after midnight, Manager cleans up old history records.

Default

10

Syntax

CLEANUPSAVECOUNT max_history_count

max_history_count

The number of history records to save. You can set the number of history records to a number between 5 and 50.

COBOLUSEREXIT

Valid for

Extract, Replicat

Description

Use COBOLUSEREXIT to call a custom COBOL routine at different points during processing.

If COBOLUSEREXIT is specified in the parameter file, but a user exit is not bound to the Extract or Replicat object, the process will abend.

Syntax

COBOLUSEREXIT

COLMATCH

Valid for

Extract, Replicat

Description

Use COLMATCH to map columns when source and target tables are different. The COLMATCH parameter enables mapping between databases with similarly structured tables but different names. COLMATCH specifies rules for default column mapping that apply to all columns that match the specified name.

COLMATCH is required when the source and target columns are different.You can also use the COLMAP option of the Replicat MAP parameter.

Syntax

COLMATCH 
{NAMES target_column = source_column |
PREFIX prefix | SUFFIX suffix | RESET}

NAMES target_column = source_column

Matches a target column to a source column.

• target_column is the name of the target column.

• = is the assignment operator.

• source_column is the name of the source column.

PREFIX prefix

Specifies a prefix to ignore.

SUFFIX suffix

Specifies a suffix to ignore.

RESET

Turns off any COLMATCH rules previously specified.

Global rules and table names

It may be that a source and target database are identical except for slightly different names, as shown in the following table.

CUST_CODE
CUST_NAME
CUST_ADDR
PHONE

CUSTOMER_CODE
CUSTOMER_NAME
CUSTOMER_ADDRESS
PHONE

CUST_CODE
CUST_NAME
ORDER_ID
ORDER_AMT

CUSTOMER_CODE
CUSTOMER_NAME
ORDER_ID
ORDER_AMT

To map the source database columns to the target, you could specify each individual column mapping, but an easier method is to specify global rules using COLMATCH as follows:

COLMATCH NAMES CUSTOMER_CODE = CUST_CODE
COLMATCH NAMES CUSTOMER_NAME = CUST_NAME
COLMATCH NAMES CUSTOMER_ADDRESS = CUST_ADDR;

Specifying matches this way enables all columns in ACCT to be mapped to ACCOUNT, and all columns in ORD to map to ORDER. When performing default mapping, Extract checks for any matches according to global rules (this enables mapping of CUST_CODE, CUST_NAME and CUST_ADDR). In addition, exact column name matches are checked as always (enabling mapping of ORDER_ID, ORDER_AMT and PHONE).

Global rules and suffixes

Another frequently encountered situation is:

CUST_CODE
CUST_NAME
ORDER_ID
ORDER_AMT

CUST_CODE_K 
CUST_NAME_K 
ORDER_ID
ORDER_AMT

In this case, a global rule can specify that the _K suffix appended to columns in the target table be ignored, as in: COLMATCH SUFFIX _K.

This also resolves the opposite situation:

CUST_CODE_K
CUST_NAME_K
ORDER_ID
ORDER_AMT

CUST_CODE
CUST_NAME
ORDER_ID
ORDER_AMT

The same principle can be applied to column prefixes: COLMATCH PREFIX P_

P_CUST_CODE 
P_CUST_NAME
ORDER_ID 
ORDER_AMT

CUST_CODE 
CUST_NAME 
ORDER_ID 
ORDER_AMT

Global rules and map entries

Global rules can be turned off for subsequent map entries with COLMATCH RESET.

COMMENT

Valid for

All

Description

Use COMMENT to insert comments within a parameter file. Anything on the same line after COMMENT is ignored during processing. Two hyphens (--) also denote a comment.

A comment can be entered anywhere within the parameter file. Comments continuing to the next line must be preceded by another double hyphen or COMMENT keyword.

If any columns in the tables being synchronized contain the word "comment," there may be conflicts with the COMMENT parameter, so double hyphens are the recommended option.

Syntax

COMMENT comment_text} | {-- comment_text}

Example

Both of the following are valid comments. The second uses the recommended syntax.

COMMENT Oracle GoldenGate NSK SQL Extract parameter file
-- Oracle GoldenGate NSK SQL Extract parameter file

COMPRESSDELETES | NOCOMPRESSDELETES

Valid for

Extract

Description

Use COMPRESSDELETES and NOCOMPRESSDELETES to control the way columns are written to the trail record for delete operations.

COMPRESSDELETES will extract only the primary key fields or columns for deleted operations. NOCOMPRESSDELETES, the default, sends all columns to the trail. By sending only the primary key, Oracle GoldenGate has all of the data required to delete the target record, while restricting the amount of data that must be processed. This creates a net performance gain.

COMPRESSDELETES and NOCOMPRESSDELETES can be used globally for all TABLE statements in the parameter file, or they can be used as on-off switches for individual TABLE statements.

Default

NOCOMPRESSDELETES

Syntax

COMPRESSDELETES

CONVERTALLFLOATSTOIEEE | NOCONVERTALLFLOATSTOIEEE

Valid for

Extract, Replicat

Description

Use CONVERTALLFLOATSTOIEEE to convert the Tandem (TDM) float data type numbers used by Oracle GoldenGate for HP NonStop to the Institute of Electrical and Electronics Engineers (IEEE) format used by Oracle GoldenGate for Windows and UNIX. CONVERTALLFLOATSTOIEEE converts all the float data type numbers in a file or table if the following conditions are met:

• There is no column mapping for the table or file

• Any SQL/MP table is not a view

• Definitions are provided for the Enscribe files

CONVERTALLFLOATSTOIEEE converts 64 bit float data types (SQL/MP type float or Enscribe type double.) It does not convert 32 bit float data types (SQL/MP Real or Enscribe type float) because these are converted by Oracle GoldenGate for Windows and UNIX.

Use CONVERTALLFLOATSTOIEEE and NOCONVERTALLFLOATSTOIEEE as toggles in the parameter file to turn the conversion on for some of the files or tables and off for others. A CONVERTALLFLOATSTOIEEE parameter will be in affect until a NOCONVERTALLFLOATSTOIEE parameter is encountered and vice versa.

Default

NOCONVERTALLFLOATSTOIEE

Syntax

CONVERTALLFLOATSTOIEE

Example

EXTRACT EXTORD
RMTHOST host01, MGRPORT 12345
CONVERTALLFLOATSTOIEEE 
TABLE $DATA01.SALES.ORDERS;
NOCONVERTALLFLOATSTOIEEE 
TABLE $DATA03.SALES.CUSTOMER;

CONVERTALLFLOATSTOTDM | NOCONVERTALLFLOATSTOTDM

Valid for

Extract, Replicat

Description

Use CONVERTALLFLOATSTOTDM to convert the Institute of Electrical and Electronics Engineers (IEEE) format used for float data type numbers from Oracle GoldenGate Windows and UNIX to the Tandem (TDM) format used by Oracle GoldenGate for HP NonStop. This will convert all the float data type numbers in a file or table if the following conditions are met:

• There is no column mapping for the file or table

• Any SQL/MP table is not a view

• Definitions are provided for the Enscribe files

CONVERTALLFLOATSTOTDM converts both 32 and 64 bit float data types because these are not converted by Oracle GoldenGate for Windows and UNIX.

Use CONVERTALLFLOATSTOTDM and NOCONVERTALLFLOATSTOTDM as toggles around the tables in the parameter file to turn the float data type conversion on for some of the tables and off for others. The NOCONVERTALLFLOATSTOTDM parameter will be in affect until the CONVERTALLFLOATSTOTDM parameter is encountered and vice versa.

Default

NOCONVERTALLFLOATSTOTDM

Syntax

CONVERTALLFLOATSTOTDM

Example

REPLICAT REPORD
CONVERTALLFLOATSTOTDM 
TABLE $DATA03.SALES.ORDERS,
  TARGET $DATA03.SALES.ORDERS;
NOCONVERTALLFLOATSTOTDM 
TABLE $DATA03.SALES.ORDERS,
  TARGET $DATA03.SALES.CUSTOMER;

COORDINATOR

Valid for

Coordinator, Replicat

COORDINATOR for Coordinator

Use COORDINATOR in the Coordinator parameter file to identify the name of the group.

Syntax

COORDINATOR group_name

group_name

The name of the group.

Example

COORDINATOR ACCTCO

COORDINATOR for Replicat

Use COORDINATOR in the Replicat parameter file to identify the name of the process that coordinates transactions distributed across multiple nodes in the network.

Syntax

COORDINATOR process_name
[MAXRETRIES number]
[DELAYSECS seconds | DELAYCSECS centiseconds]

process_name

The process name of the Coordinator group with which the Replicat will communicate to track distributed network transactions. The format for the default name is \node.$GGCnn with the node designator required.

MAXRETRIES number

The number of times Replicat will try to start the process before allowing the process to end abnormally. The default is 5.

DELAYSECS seconds | DELAYCSECS centiseconds

Sets the number of seconds or centiseconds that Replicat waits between tries. The default is 20 seconds.

Example

The following Replicat parameter specifies Coordinator group $GGC00 on the NY system.

REPLICAT REPNET
COORDINATOR \NY.$GGC00

CPU

Valid for

Logger

Description

Use CPU to specify primary and backup CPUs for the current Logger process. For example: CPU 9, 3 directs Logger to switch from the primary (CPU 9) to the backup (CPU 3) in the event of system problems.

The process on the primary CPU pings the process on the backup CPU every minute. If the backup CPU becomes unavailable, the primary process detects it and recreates it.

Both primary and backup are required

Syntax

CPU primary, backup

primary

The primary CPU identifier.

backup

The backup CPU identifier.

CUSEREXIT

Valid for

Extract, Replicat

Description

Use CUSEREXIT to call custom C routines at different points during processing. If your user exit is written in COBOL, see "COBOLUSEREXIT" for information on the COBOLUSEREXIT parameter.

If CUSEREXIT is specified in the parameter file, but a user exit is not bound to the Extract or Replicat object, the process will abend.

Syntax

CUSEREXIT

DEBUGONSTACKCHECK

Valid for

Logger

Description

Use DEBUGONSTACKCHECK to call DEBUG whenever an application's process stack is close to overflowing. Oracle GoldenGate recommends using this parameter only when instructed to do so by Oracle GoldenGate support.

Default

Omit DEBUGONSTACKCHECK

Syntax

DEBUGONSTACKCHECK

DECRYPTTRAIL

Valid for

Extract, Replicat

Description

Use DECRYPTTRAIL to decrypt Oracle GoldenGate trails that were encrypted by an upstream Extract process (for information on ENCRYPTTRAIL see "ENCRYPTTRAIL | NOENCRYPTTRAIL"). Specify DECRYPTTRAIL only when the ENCRYPTTRAIL parameter is specified in the upstream Extract process.

Syntax

Syntax
DECRYPTTRAIL

DICTIONARY

Valid for

Extract, Replicat

Description

Use DICTIONARY to establish an Enscribe DDL dictionary to use for evaluating WHERE, FILTER, and COLMAP clauses for FILE entries. For example, DICTIONARY $DATA5.PRODDICT specifies a physical subvolume.

Each DICTIONARY entry closes any previously open dictionary. This means only one is active at any given time while processing startup parameters. For this reason, you must specify your DICTIONARY parameter before the FILE/MAP entry that uses it.

Syntax

DICTIONARY dictionary_subvol

dictionary_subvol

A physical subvolume or an existing define name of class catalog.

DISCARDFILE

Valid for

Extract, Replicat

Description

Use DISCARDFILE to create a file containing records discarded by the process. Records can be discarded for a number of reasons, such as attempting to update a missing record. Each entry in the discard file provides the field names, field values and operation attempted, along with associated transaction information.

If DISCARDFILE is not present in the Replicat parameter file, Replicat will create a default discard file. See Administering Oracle GoldenGate for HP NonStop (Guardian) for details on the naming and characteristics of the default discard file.

Syntax

DISCARDFILE file_name
[, APPEND | PURGE] | ROLLOVER]
[, EXTENTS (primary, secondary, maximum) | MEGABYTES megabytes]
[, OWNER (group_number, user_number)]
[, SECURE "rwep"]

file_name

A physical file name or an existing define name of class map.

APPEND | PURGE | ROLLOVER

• APPEND - Appends records to an existing file. This is the default if no value is entered.

• PURGE - Purges an existing file and creates a new one

• ROLLOVER - Renames the discard files by appending a sequence number to the file name, according to these rules:

  • If the file name is 7 characters or less, 1 digit is appended.

  • If the file name is 8 characters, the file will not rollover and a warning is given that the name is too long. This is true when the rollover is requested from GGSCI as well as from the DISCARDFILE parameter.

EXTENTS (primary, secondary, maximum)

Sets the extent sizes and maximum extents for the file. The default setting is (4, 4, 100).

MEGABYTES megabytes

Sets the maximum size of the file in megabytes. The maximum size is 2 gigabytes.

OWNER (group_number, user_number)

Defines ownership of the discard file.

SECURE "rwep"

Secures the file using standard Guardian security for read, write, execute and purge operations.

Example

DISCARDFILE =DISCARD_FILE, OWNER 100,1, SECURE "NNNN", PURGE

DISCARDROLLOVER

Valid for

Extract, Replicat

Description

Use DISCARDROLLOVER to specify when a discard file is aged and a new one is created. Old files are renamed in the format of group_name(n), where group_name is the name of the Extract or Replicat group and (n) is a number that gets incremented by one each time a new file is created, for example: $DATA.GGSDISC.DISCARD0, $DATA.GGSDISC.DISCARD1, $DATA.GGSDISC.DISCARD2, and so forth.

Either the AT or ON option can be entered, or both options can be used together. The following rules apply to the possible variations in entries:

• Entering AT with a time but without a day creates a new discard file at the specified time every day.

• Entering AT without a time generates an error.

• Entering ON with a day but without a time creates a new discard file at midnight on the specified day.

• Entering ON without a day will generate an error.

• If no option is entered, the discard file will roll over at midnight every night.

To have more than one rollover, enter multiple AT, ON, or AT ON options. Up to 30 will be used.

A discard file will not roll over if:

• The discard file is empty

• The discard file was created by default rather than declared using the DISCARDFILE parameter

Default

Disabled. No rules specified.

Syntax

DISCARDROLLOVER {
AT hh:mm | 
ON day_of_week | 
AT hh_mm ON day_of_week
}

AT hh:mm

The time of day to age the file based on a 24-hour clock.

Valid values:

• hh is an hour of the day from 1 through 23.

• mm is minutes from 00 through 59.

ON day_of_week

The day of the week to age the file.

Valid values are SUNDAY through SATURDAY. They are not case-sensitive.

Examples

Below are examples of the use of DISARDROLLOVER.

Example 1   

This example closes the existing discard file and creates a new one at 5:30 a.m. each day.

DISCARDROLLOVER AT 05:30

Example 2   

This example rolls the discard file over every Friday at midnight.

DISCARDROLLOVER ON friday

Example 3   

This example rolls the discard file over at 5:30 a.m. every Friday.

DISCARDROLLOVER AT 05:30 ON FRIDAY

Example 4   

This example will roll over the discard file at 5:30 a.m. every Wednesday and at 5:30 am every Friday.

DISCARDROLLOVER AT 05:30 ON WEDNESDAY, AT 05:30 ON FRIDAY

DISKTHRESHOLD | NODISKTHRESHOLD

Valid for

Manager

Description

Use DISKTHRESHOLD to generate an event message when the percentage of all audit left on disk falls below the specified percentage. Use NODISKTHRESHOLD to eliminate reporting disk related thresholds.

Because audit is always being created, old audit eventually has to be recycled. If an Extract process needs audit that is about to be recycled, processing integrity is in danger.

To report audit thresholds, specify a percentage level for DISKTHRESHOLD in the Manager parameter file. When Extract program processing falls to the specified percentage, Manager sends a threshold message to EMS or to another specified location at regular intervals.

For example: You have ten audit trail files numbered 10-19, and you specify DISKTHRESHOLD 25 to generate an event message when audit files to process reaches 25% of the entire audit trail. When Extract's checkpoint is positioned in audit file 10 or 11, Manager generates a threshold message, because less than 25% of audit available for writing new data before the current position is recycled.

Alternatively, you can use the THRESHOLD or NOTHRESHOLD parameters to report on disk or tape audit thresholds. For information on these parameters see "THRESHOLD | NOTHRESHOLD".

Default

DISKTHRESHOLD 20%

Syntax

DISKTHRESHOLD percent_left | NODISKTHRESHOLD

percent_left

The percentage level at which to generate an event message.

DISPLAYFILEREFRESHES

Valid for

Extract, Replicat

Description

Use DISPLAYFILEREFRESHES to display a message when attributes are refreshed for a file. DISPLAYFILEREFRESHES is a global parameter that is set once for the parameter file. It triggers generation of a refresh message when file attributes for each file included in the processing are refreshed.

Default

File refresh messages are suppressed.

Syntax

DISPLAYFILEREFRESHES

Example

When DISPLAYFILEREFRESHES is used, each file refresh displays a message similar to:

File Attributes Refreshed for
\NODE1.$VOL1.SUBVOL.FILE1 at 2013-05-13 09:13:36

DISPLAYTRAILSWITCH | NODISPLAYTRAILSWITCH

Valid for

Extract

Description

Use DISPLAYTRAILSWITCH or NODISPLAYTRAILSWITCH to print or suppress printing of messages to the report file when Extract switches trails.

Default

DISPLAYTRAILSWITCH

Syntax

DISPLAYTRAILSWITCH | NODISPLAYTRAILSWITCH

DOWNCRITICAL

Valid for

Manager

Description

Use DOWNCRITICAL to include a process that has terminated normally in the report generated by DOWNREPORT. When events are sent to the event log, they are reported as critical if either a process terminates abnormally or the DOWNCRITICAL parameter is specified. Processes that terminate abnormally are automatically reported by the DOWNREPORT parameter.

See the DOWNREPORT parameter "DOWNREPORT" for information on setting the frequency for sending information to the event log.

Default

Manager does not report a normally terminated process.

Syntax

DOWNCRITICAL

DOWNREPORT

Valid for

Manager

Description

Use DOWNREPORTMINUTES or DOWNREPORTHOURS to report Extract and Replicat groups that are not running every n minutes or hours.

Events are generated any time Extract and Replicat processes are started, stopped, or interrupted. The Manager reports when a process is terminated.

Default

One hour

Syntax

DOWNREPORTMINUTES minutes | DOWNREPORTHOURS hours

minutes

The reporting interval, in minutes.

hours

The reporting interval, in hours.

DUP

Valid for

Syncfile

Description

Use DUP to specify the source and target file sets to duplicate and the name of the event to execute. Optionally, you can exclude individual files and/or types of files from duplication, duplicate based on whether records have changed, and execute FUP or TACL commands.

Follows the EVENT parameter. At least one DUP entry is required.

Syntax

DUP source_file_set, TARGET target_file_set 
{, EVENT event_name}
[, NAME identifier]
[, EXCLUDE exclude_file], 
[, INCLUDEFILECODE (include_codes)
[, EXCLUDEFILECODE (exclude_codes)],
[, CHANGED | ALWAYS]
[, FUPOPTIONS "fup_options"]
[, GETAUDITED | IGNOREAUDITED]
[, TACLCMD tacl_command];

source_file_set

Required. Identifies the source file set. You can use standard wildcards.

TARGET target_file_set

Required. Identifies the target file set. You can use standard wildcards.

EVENT event_name

Required. Specifies an event that has been defined by the EVENT parameter. You can specify multiple events as:

EVENT event_name, EVENT event_name,...

NAME identifier

Optional logical name to be assigned to the DUP. Enclose in quotes if the name contains spaces. If NAME is not specified, the logical name will default to ID_num where num is the ordinal number of the DUP item within the parameter file. The logical name is displayed when Syncfile starts processing a DUP.

EXCLUDE exclude_file

Specify EXCLUDE when you want to exclude certain files in the file set from duplication.

INCLUDEFILECODE (include_codes)

A comma delimited list to specify the file codes to be excluded from duplication. Wildcards are not accepted. The list must be enclosed in parenthesis, for example:

INCLUDEFILECODE (32767, 65000)

If an INCLUDEFILECODE list is specified, any file code that is not in the list will be excluded.

EXCLUDEFILECODE (exclude_codes)

A comma delimited list to specify the file codes to be included in the duplication. Wildcards are not accepted. The list must be enclosed in parenthesis, for example:

EXCLUDEFILECODE (0, 100, 700, 800)

If a file code is on the EXCLUDEFILECODE list it will be excluded, even if it is on the INCLUDEFILECODE list.

CHANGED | ALWAYS

• CHANGED is the default. Duplicates files only if the source file has changed more recently than the target file.

• ALWAYS duplicates source files whether or not source records have been modified since the corresponding target file records.

FUPOPTIONS "fup_options"

Enables options to be appended to the FUP DUP command. By default, Syncfile executes:

FUP DUP source, target, PURGE, SAVEALL

You can replace the PURGE and SAVEALL options with others. For example: specifying: FUPOPTIONS "SOURCEDATE, NEW"; results in the command:

FUP DUP source, target, SOURCEDATE, NEW.

GETAUDITED | IGNOREAUDITED

• GETAUDITED duplicates files that have the audit flag set to on.

• IGNOREAUDITED duplicates files that have the audit flag set to off. IGNOREAUDITED is the default.

TACLCMD tacl_command

See "Specifying TACL commands".

Specifying TACL commands

The TACLCMD tacl_command option executes a user-supplied TACL macro to perform the duplication. The macro can execute virtually any set of Guardian functions. For example, you can write a TACL macro to make a copy of the file to duplicate to a temporary location; edit the temporary file and change occurrences of the string "$DATA3" to "$D16"; age the previous file on the secondary system to a backup location; and FTP the temporary file to the backup.

When specifying TACLCMD, enclose the entire command to execute in quotes as shown below:

TACLCMD "RUN $DATA1.TACLMAC.DUPMAC source target"

As part of this command, you can specify the source and target file names as the source and target arguments. For example, the following command causes Syncfile to invoke TACL with $D16.TEST.CFG1 as the source and \BKUP.$DATA6.TEST.CFG1 as the target:

RUN $DATA1.TACLMAC.DUPMAC $D16.TEST.CFG1 \BKUP.$DATA6.TEST.CFG1

Enter <source> and <target> without substituting any file names to trigger Syncfile to take the source and target arguments from the DUP statement. For example, the following command will duplicate $DATA1.GGSPARM.* to $DATA5.GGSPARM.*.

DUP $DATA1.GGSPARM.*, TARGET $DATA5.*.*, 
TACLCMD "RUN $DATA1.GGSPARM.TACL5 <SOURCE> <TARGET>", 
ALWAYS, EVENT DAILY 1330; 

For more information on this see the Administering Oracle GoldenGate for HP NonStop (Guardian).

DUPONLYAFTEREVENT

Valid for

Syncfile

Description

Use DUPONLYAFTEREVENT to change the time events execute.

As part of the EVENT definition, you can specify that events should execute at a certain time each day (for example, 01:00). By default, Syncfile executes an event if the current time is greater than time specified, and the event has not yet been performed that day. Therefore, if Syncfile is started at 11:00, an event specified at 01:00 executes when Syncfile starts and then not again until 01:00 the following day.

In some cases you may want the event to execute later than the intended time. Specifying DUPONLYAFTEREVENT executes the event after the specified time is passed, ensuring that DUPs happen close to the intended time.

DUPONLYAFTEREVENT follows the EVENT parameter.

Syntax

DUPONLYAFTEREVENT;

DUPPROCESS

Valid for

Syncfile

Description

Use DUPPROCESS to specify the process name used by the TACL or FUP process used to duplicate files. This is generally for debugging purposes.

You must specify either the FUPOPTIONS or TACLCMD options for the DUP parameter.

Syntax

DUPPROCESS process_name;

process_name

The process name used to duplicate files.

DYNAMICPARTITIONS

Valid for

Extract

Description

Use DYNAMICPARTITIONS if your environment dynamically partitions tables; this ensures all data is captured.

By default, Extract scans tables on startup to determine the base table, primary partition, and any secondary partitions containing data, so it can begin its captures. However, if your system dynamically creates another partition, Extract does not know it exists unless the process stops, starts, and conducts another scan.

Using the DYNAMICPARTITIONS parameter allows Extract to recognize additional secondary partitions without having to stop and restart. This means that any data stored in the newly-created partition will be captured as part of Extract's regular processing.

For example, Extract is set up to look at $DATA2.SQL.ACCOUNT, and includes DYNAMICPARTITIONS in its parameter file. $DATA2.SQL.ACCOUNT is a table with a single secondary partition, $DATA4.SQL.ACCOUNT. When Extract starts, it sees both $DATA2.SQL.ACCCOUNT and $DATA4.SQL.ACCOUNT, and records are captured from both. As processing continues, a new partition called $DATA6.SQL.ACCOUNT is created. Because the DYNAMICPARTITIONS parameter is specified, data stored in $DATA6.SQL.ACCOUNT is captured as well.

Default

Not activated

Syntax

DYNAMICPARTITIONS 

DYNAMICPORTLIST

Valid for

Manager

Description

Use DYNAMICPORTLIST to specify the ports that Manager can dynamically allocate to Collector and Replicat processes and to GGSCI sessions. You can specify ports individually or a range of ports.

When specifying individual ports, delimit each port with a comma. To specify a range of ports, use a dash (-) to separate the first and last port in the range. You can combine a range of ports and individual ports in the same statement.

Syntax

DYNAMICPORTLIST {port | port-port} [, ...]

port

A port (or ports) that can be dynamically allocated. Port entries are limited to 256.

Example

DYNAMICPORTLIST 7820 - 7830, 7833

DYNAMICPORTREASSIGNDELAY

Valid for

Manager

Description

Use DYNAMICPORTREASSIGNDELAY to specify the time to wait before a port can be reused.

Default

3

Syntax

DYNAMICPORTREASSIGNDELAY time

time

The number of seconds to delay before reusing a port.

EMBEDDEDMACROS | NOEMBEDDEDMACROS

Valid for

Extract, Replicat

Description

Use EMBEDDEDMACROS to control whether a macro can be expanded in a quoted string. Use NOEMBEDDEDMACROS to make text inside a quoted string invisible.

Default

NOEMBEDDEDMACROS

Syntax

EMBEDDEDMACROS | NOEMBEDDEDMACROS

EMSLOG

Valid for

GLOBALS

Description

Use EMSLOG to direct EMS messages to a Collector other than the default ($0).

The Extract, Replicat, and Manager programs check for the NonStop define name =EMS_COLLECTOR. As part of process initialization these programs take the value set for EMSLOG and use it to override any pre-existing value that was set for =EMS_COLLECTOR.

When Manager creates a process, it sets the NonStop define values for =EMSCOLLECTOR and =EMS_COLLECTOR to the value specified for EMSLOG (or the default of $0).

Default

$0

Syntax

EMSLOG {collector | NONE}

collector

Specify either the Collector name, or NONE when there is no Collector.

ENCRYPTTRAIL | NOENCRYPTTRAIL

Valid for

Extract

Description

Use ENCRYPTTRAIL to encrypt data records in subsequent Oracle GoldenGate trails until a NOENCRYPTTRAIL is encountered. All records going into an Oracle GoldenGate trail are encrypted both across any data links and within the trail. This applies to EXTFILE, EXTTRAIL, RMTFILE and RMTTRAIL entries. ENCRYPTTRAIL is not recommended for RMTBATCH.

Parameter files for downstream Extract or Replicat processes must specify a DECRYPTTRAIL to read the files.

Default

NOENCRYPTTRAIL

Syntax

ENCRYPTTRAIL | NOENCRYPTTRAIL

END

Valid for

Extract, Replicat

Description

Use END to specify the point at which the process stops processing in the TMF audit or Oracle GoldenGate trails. If END is omitted, processing continues until you stop it manually with GGSCI.

With END, processing terminates when an audit record is encountered with a timestamp equal to or greater than the time specified. You can specify the day, the time of day, including seconds and centiseconds as in: END 2010-08-12 17:00:00.

END is used to determine when SPECIALRUN processing will terminate.

Syntax

END {date [time] | RUNTIME}

date time

Causes Extract or Replicat to terminate when it encounters a record with a timestamp equal to, or greater than, the time specified.

Valid values:

• date is a date in the format of yyyy-mm-dd.

• time is the time in the format of hh:mi[:ss[.cccccc]] based on a 24-hour clock.

RUNTIME

Causes Extract or Replicat to terminate when it reaches process startup time. One advantage of using RUNTIME is that you do not have to alter the parameter file to change dates and times from run to run.

ENTRYSEQUPDATES

Valid for

Replicat

Description

Use ENTRYSEQUPDATES to enable entry-sequenced records to be replicated with exactly the same key as the record on the source system. Because standard Guardian functions do not permit control of entry-sequenced record keys, omitting ENTRYSEQUPDATES results in the following limitations:

• During an initial load (or similar situation), duplicate record conditions may not be detected; therefore, multiple instances of the same record can occur in the target file.

• When updates occur on the source database, there is no guarantee that the corresponding key on the target database is the same. Therefore, to guarantee updates are applied properly, a unique alternate key must be specified.

When ENTRYSEQUPDATES is specified, Replicat manipulates file contents directly through unstructured file access. This type of access imposes the following restrictions on the target file:

• The file is open for PROTECTED access (no other processes, including other Replicat processes, can update the file).

• The target file cannot be TMF audited.

• No more than one source file can be associated with the target file (to guarantee that the keys are unique).

• A trade-off exists between flushing file contents at any given time and performance.

ENTRYSEQUPDATES causes Replicat to manipulate file blocks directly. This ensures that each record is inserted into the target file in exactly the same place as in the source file (for other file types, this occurs automatically). To maximize performance, Replicat attempts to build these blocks in memory, keeping a private cache, and assumes subsequent records will be inserted in close proximity. This maximizes performance by minimizing the amount of messages with the disk process.

You must specify ENTRYSEQUPDATES in the parameter file before any MAP statements for entry-sequenced files.

Syntax

ENTRYSEQUPDATES {EXACTKEY | NOEXACTKEY}
[, FLUSHALWAYS | NOFLUSHALWAYS]
[, HIDEGAPS | NOHIDEGAPS]
[, EXCLUSIVEOPEN | PROTECTEDOPEN]

EXACTKEY | NOEXACTKEY

Subsequent maps should always have exact key replication specified. Specify NOEXACTKEY to cancel exact key replication for subsequent entries.

FLUSHALWAYS | NOFLUSHALWAYS

Each time a block is updated, it is flushed to disk. This makes the record immediately visible to the application. The default is NOFLUSHALWAYS.

HIDEGAPS | NOHIDEGAPS

• HIDEGAPS — The default. If records for a particular block arrive out of order, hide records in the block from view until all prior records in the block arrive. This guarantees that records with a length of zero are never returned. Use HIDEGAPS when replicating non-TMF entry-sequenced files.

• NOHIDEGAPS — Make visible all records in a block, regardless of whether prior records have arrived. The trade-off is that missing records will appear to have a length of zero when reading the file.

EXCLUSIVEOPEN

Opens the target file exclusively. This is recommended by Oracle GoldenGate when opening structured files for unstructured write access, as is done when ENTRYSEQUPDATES is specified.

PROTECTEDOPEN

Allows users access to read-only files while Oracle GoldenGate is writing to the file.

Example

The following example causes exact replication of the key, optimized for the highest throughput.

ENTRYSEQUPDATES, EXACTKEY, NOFLUSHALWAYS

EOFDELAY | EOFDELAYCSECS

Valid for

Extract, Replicat

Description

Use EOFDELAY or EOFDELAYCSECS to specify the number of seconds or centiseconds to delay before looking for more data. Increase the time interval to increase the lag time between updates on the source and target systems, especially when the source system is experiencing a small amount of activity.

This parameter only applies when Extract or Replicat is reading an Oracle GoldenGate trail.

Default

1 second

Syntax

EOFDELAY seconds | EOFDELAYCSECS centiseconds

seconds

The number of seconds to delay.

centiseconds

The number of centiseconds to delay.

ERREPLYTIMEOUT

Valid for

GLOBALS

Description

Use ERREPLYTIMEOUT to set the timeout, in seconds, when GGSCI communicates with Oracle GoldenGate components.

Default

30

Syntax

ERREPLYTIMEOUT seconds

seconds

Specify a timeout value in seconds.

ERROR59ROLLOVER

Valid for

Extract

Description

Use ERROR59ROLLOVER during direct file extraction to tell Extract to skip ahead to the next file in a sequence upon encountering a damaged block (Guardian Error 59).

Certain applications leave file blocks in a damaged state to signal that the end of data within the file has been reached. All data prior to the first damaged block is considered valid. Extract, when processing a sequence of files directly, will correctly process all data up until the damaged block. By default, if Extract subsequently reads a damaged block, it will abend.

Syntax

ERROR59ROLLOVER

ETNEWFORMAT | ETOLDFORMAT

Valid for

Extract

Description

Use ETNEWFORMAT to cause Extract to generate trails in formats that are compatible with Replicat version 7.0 or later.

Use ETOLDFORMAT to cause Extract to generate trails in formats that are compatible with versions of Replicat prior to 7.0. You must use this parameter before any other parameter when running pre-7.0 versions of Oracle GoldenGate for NonStop, and pre-7.2 versions of Oracle GoldenGate for Windows and UNIX.

Default

ETNEWFORMAT

Syntax

ETNEWFORMAT | ETOLDFORMAT

EVENT

Valid for

Syncfile

Description

Use EVENT to define an event and its schedule. Associate the event with a file set via the EVENT keyword in DUP parameter argument. For example, you might define an EVENT named DAILY that executes a duplication at 03:00 daily:

EVENT DAILY, 
EVERY DAY AT 03:00;
.
.
.
DUP $DATA1.GGSCFG.Z*, TARGET \BKUP.$DAT2.*.*,
ALWAYS
EVENT DAILY;

For this example, the DUP arguments includes EVENT DAILY. At 03:00, every day, data set $DATA1.GGSCFG.Z* is duplicated to data set \BKUP.$DAT2.*.*.

You can include multiple EVENT parameters. You can schedule for time of day, day of week, or date. You can also exclude specific dates or days of the week.

EVENT precedes all other parameters. At least one EVENT entry is required.

Syntax

EVENT event_name, every_options [, exclude_options];

event_name

Any name to identify the EVENT. The name is used by subsequent DUP entries to link file set duplication to an appropriate schedule.

every_options

Can be one of the following:

EVERY DAY AT time

Enter the time as 0100, 0200...1400, 1500, etc.

EVERY date AT time

Specify the month and day of the month, as in: August 3. Month is spelled out. Enter the time as 01:00, 02:00...14:00, 15:00, etc.

EVERY time_interval

Enter an interval, such as 2 HOURS or 10 MINUTES.

exclude_options

Can be one or both of the following:

EXCLUDE day_of_week

Spelled out, such as SUNDAY or SATURDAY.

EXCLUDE date

Specify the month and day of the month, as in: August 3. Month is spelled out.

Example

For this example, two EVENTS are defined, then called by subsequent DUP parameters.

EVENT DAILY,
EVERY DAY AT 1:00;
EVENT FREQUENT,
EVERY 2 HOURS;

DUP $DATA1.GGSCFG.Z*, TARGET \BKUP.$DATA2.*.*,
ALWAYS,
EVENT DAILY;

DUP $DATA1.GGSPARM.*, TARGET \BKUP.$DATA2.*.*,
EVENT FREQUENT;

EXCLUDEFILE

Valid for

Logger

Description

Use EXCLUDEFILE to exclude specific files from extraction to the current log. You can implicitly and explicitly exclude files that have been included by the FILE parameter. For example, if the Logger parameter file includes FILE and EXCLUDEFILE entries similar to:

FILE $DATA4.*.*
EXCLUDEFILE $DATA4.DAT.TRANSFL

The EXCLUDEFILE parameter excludes the specified file set, even though the preceding FILE statement implicitly included it with a wildcard argument.

If used, EXCLUDEFILE must follow the FILE parameter. If a file is included by the FILE parameter without filters, then excluded by EXCLUDEFILE according to one or more filters, the file is excluded when the filter criteria are met.

You can exclude files by:

• Specifying file sets to exclude.

• Filtering file sets that are opened by a specified process or program.

• Filtering file sets that are associated with an opening program's user ID.

Syntax

EXCLUDEFILE file_set 
[, PROCESS process_set] 
[, PROGRAM program_set]
[, USER user_set]

file_set

The name of the file set to exclude.

PROCESS process_set

Excludes data when the opener is the process or set of processes specified (process set can be a single process or a wildcard, for example $APP*).

PROGRAM program_set

Excludes data when the opener is the program or set of programs specified (program set can be a single program or a wildcard: $DATA1.PROGS.*).

USER user_set

Excludes data when the creator access ID of the opener is the user specified (user set can be a single user or wildcard: FINANCE.JOE or SUPER.*).

Examples

Example 1   

EXCLUDEFILE $DATA4.DAT.TRANSFL

Example 2   

EXCLUDEFILE $D15.DAT.*

EXCLUDEGGSTRANSRECS | INCLUDEGGSTRANSRECS

Valid for

Extract

Description

Use INCLUDEGGSTRANSRECS to create network transaction tracking records. You need these records if you replicate distributed network transactions using Readers and a Coordinator. These processes depend on tracking records in the trail.

The default EXCLUDEGGSTRANSRECS suppresses the creation of network transaction tracking records.

Default

EXCLUDEGGSTRANSRECS

Syntax

INCLUDEGGSTRANSRECS

EXCLUDESUFFIXCHAR

Valid for

Manager

Description

Use EXCLUDESUFFIXCHAR to specify characters that are not to be used as the first character of the suffix when generating process names. This can be useful in avoiding conflicts with process naming conventions already in use.

The process name is made up of a two-character prefix, which can be set using ADD DEFINE for GGS_PREFIX (see "ADD DEFINE"), and a three character suffix. The suffix can have one or two alphanumeric characters and will end with a sequential number. The process name $GGL00, for example, uses the prefix GG. The first character of the suffix is L, indicating a Logger process, and the zeroes indicate it is the first process generated for $GGL.

Characters that are used to name Oracle GoldenGate processes are excluded by default. These include C (Coordinator), E (Extract), L (Logger), R (Replicat), and S (Server/Collector). The characters, MG, used in naming Manager processes, are also excluded by default.

Default

CELRS and MG

Syntax

EXCLUDESUFFIXCHAR characters

characters

The characters to be excluded. These should be entered as a string of characters without commas. The characters can optionally be enclosed in single or double quotation marks (e.g. "WTZ" or 'BP').

Examples

Example 1   

The following examples all exclude the characters T and X from the first position of the suffix for generated process names.

EXCLUDESUFFIXCHAR TX
EXCLUDESUFFIXCHAR 'TX'
EXCLUDESUFFIXCHAR "TX"

Example 2   

To reset to the defaults, enter an empty set of characters as shown below.

EXCLUDESUFFIXCHAR ""

EXPANDDDL

Valid for

Extract, Replicat

Description

Use EXPANDDDL to format Enscribe data. Enscribe DDL definitions frequently contain occurs, or array items. For example, a definition might contain:

05 GROUP1 OCCURS 3 TIMES.
   10 FIELD1 PIC X(5) OCCURS 20 TIMES.

To reference items within arrayed structures in a WHERE, FILTER, or COLMAP clause, you must identify the occurrence. The default syntax for doing so is: field_name-occurrence. For example, to retrieve the second occurrence of FIELD1 in the third group, the syntax would be GROUP1-3.FIELD1-2

The EXPANDDDL parameter changes this array notation. For example:

• EXPANDDDL USEBRACKETS specifies field as GROUP1[3].FIELD1[2]

• EXPANDDDL USETWOUNDERSCORES, ZEROFILL ARRAYWIDTH specifies field as GROUP1__3.FIELD1__02, when the maximum array width in GROUP1 is less than 10 (requiring at most one digit) and the maximum array width of FIELD1 is 20 (requiring two digits).

EXPANDDDL also determines how field occurrences are output when FORMATASCII or FORMATSQL are specified.

Syntax

EXPANDDDL format 
[, ZEROFILL width | ARRAYWIDTH]
[, INCLUDEREDEFS | OMITREDEFS]

format

Can be one of the following:

• USEDASH — Reference array items by –n, where n is the occurrence number.

• USEBRACKETS — Reference array items by [n], where n is the occurrence number.

• USEUNDERSCORE — Reference array items by _n, where n is the occurrence number.

• USETWOUNDERSCORES — Reference array items by __n, where n is the occurrence number.

ZEROFILL width | ARRAYWIDTH

Directs Extract to reference occurrences of each field adjusting for a maximum width.

INCLUDEREDEFS | OMITREDEFS

INCLUDEREDEFS includes redefined fields.

OMITREDEFS is the default. It excludes redefined fields, which has the following consequences:

• Data is only output to columns that do not redefine another field.

• When Extract specifies FORMATASCII or FORMATSQL, Extract does not output redefined fields.

EXTFILE

Valid for

Extract, Replicat

Description

Use EXTFILE when Extract writes to an Oracle GoldenGate trail that contains a single file. EXTFILE defines the name, dimensions, and security of a file in the Oracle GoldenGate trail. The parameter file must include at least one EXTFILE or EXTTRAIL entry. EXTFILE must precede the names of files and tables containing data you want to extracted into the file.

All FILE and TABLE entries after the current entry but before the next EXTFILE, EXTTRAIL, RMTFILE, or RMTTRAIL parameter result in output to the current trail.

The trail must contain record headers or an error is returned at run-time.

Syntax

EXTFILE file_name 
[, APPEND | PURGE]
[, EXTENTS (primary, secondary, maximum)]
[, MAXFILES num_files]
[, MEGABYTES number]
[, OWNER (group_number, user_number)]
[, SECURE "rwep"]

file_name

A physical file name or an existing define name of class map.

APPEND | PURGE

• Specify APPEND to append to the file.

• Specify PURGE to purge the file.

EXTENTS (primary, secondary, maximum)

Sets up the extent sizes and maximum extents for the file.

MAXFILES num_files

Valid for Extract. Forces a sequence of files to be created, rather than a single file. MAXFILES permits up to num_files to be created as needed. Aged files are appended with a six-digit sequence number. When using MAXFILES, MEGABYTES should also be specified in order to explicitly set the maximum size of each file in the sequence.

MEGABYTES number

Sets up the maximum size of the file, or sequence of files if you specified MAXFILES. The maximum size of each file is 2 gigabytes.

OWNER (group_number, user_number)

Gives ownership of the file to a different owner. Securing a file is useful when you extract logical sets of records to different files (for example, accounting data to one and sales data to another), and only want the sales group to have access to sales information.

SECURE "rwep"

Secures the file using standard Guardian security.

Example

The following example illustrates how the EXTFILE parameters work in conjunction with the file and table parameters. This example extracts data owned by the EAST region into one file and data owned by WEST into another.

EXTFILE $DATA1.EXTRACT.EAST, OWNER (100,255), 
SECURE "GGGG", PURGE
        TABLE $DATA5.SALES.ORDERS WHERE (REGION = "EAST");
        TABLE $DATA5.ACCTING.RECEIPTS WHERE (REG = "E");
EXTFILE $DATA1.EXTRACT.WEST, OWNER (200,255), 
SECURE "GGGG", PURGE
        TABLE $DATA5.SALES.ORDERS WHERE (REGION = "WEST");
        TABLE $DATA5.ACCTING.RECEIPTS WHERE (REG = "W");

EXTRACT

Valid for

Extract

Description

Use EXTRACT to associate with the Extract group defined by the GGSCI ADD EXTRACT command. The association with the group ensures that each extracted record is processed exactly once.

One of EXTRACT, SPECIALRUN or SOURCEISFILE entry is required.

Syntax

EXTRACT group_name 

group_name

The name of the group. Extract group_name can contain no more than 7 characters. It must be the first parameter in the Extract parameter file.

EXTTRAIL

Valid for

Extract, Replicat

Description

Use EXTTRAIL to establish the current Oracle GoldenGate trail to which data will be output. All FILE and TABLE entries after the current entry but before the next parameter (EXTFILE, EXTTRAIL, RMTBATCH, RMTFILE, RMTTRAIL) result in output to the current trail. Unlike EXTFILE, EXTTRAIL parameters are set up externally to the parameter file using GGSCI.

The trail entered must correspond with an Oracle GoldenGate trail created with GGSCI. In addition, the Extract group specified in the parameter file must match the entry linked with EXTTRAIL. For more information about adding Oracle GoldenGate trails, refer to "ADD EXTTRAIL".

Syntax

EXTTRAIL file_prefix 

file_prefix

A file name with two characters in the file portion. file_prefix can also be a define with the same characteristics.

Example

An Extract group, FINANCE, has two associated trails. This configuration sends ACCOUNTS records to the XX trail and ORDERS to the YY trail. The parameter file includes the following commands:

EXTRACT FINANCE
...
EXTTRAIL $DATA1.EXTDAT.XX
FILE $DATA2.FINANCE.ACCOUNTS;
EXTTRAIL $DATA2.EXTDAT.YY
FILE $DATA3.FINANCE.ORDERS;

FASTIO

Valid for

Extract

Description

Use FASTIO to output records in large blocks of up to 28K bytes, resulting in high performance gains. Use FASTIO in high volume scenarios. It is less important when data extract rates fall below several hundred megabytes per hour.

Syntax

FASTIO

FASTPOSITION | NOFASTPOSITION

Valid for

Extract

Description

Use FASTPOSITION to instruct the Audserv program to perform a binary search of the TMF audit trail at startup, before the initial checkpoint is established. This significantly reduces the startup time and CPU overhead associated with starting the process for the first time. This parameter is particularly useful for systems that have implemented auxiliary TMF audit trails.

FASTPOSITION does not apply to Oracle GoldenGate trails, SOURCEISFILE, or direct file reads.

Default

FASTPOSITION

Syntax

FASTPOSITION

FASTREADS | NOFASTREADS

Valid for

Coordinator, Extract, Replicat

Description

Use FASTREADS to change the number of bytes that Extract reads when processing Oracle GoldenGate trails. Extract reads up to 4096 bytes at a time by default. FASTREADS enables larger reads of up to 28K bytes. When data volumes are significant, FASTREADS can result in greater throughput and lower overhead.

Default

NOFASTREADS

Syntax

FASTREADS

FETCHCOMPS | FETCHLASTIMAGE

Valid for

Extract

Description

Use FETCHCOMPS or FETCHLASTIMAGE to extract full update images. When audit compression is used for a SQL table or Enscribe file, update operations to the table or file are recorded in a compressed format. Therefore, only part of the image is available for updates. (Full delete and insert images are always available). For some applications, this is acceptable. For example, when delivering the operation to another file, usually you need only the portion of the record that changed.

When either FETCHCOMPS or FETCHLASTIMAGE is specified, Extract attempts to retrieve the full record images of any compressed records from the original database using a SQL SELECT or Guardian READ statement.

You can fetch compressed images for selected files or tables. FETCHCOMPS and FETCHLASTIMAGE apply only to FILE or TABLE entries listed below the FETCHCOMPS or FETCHLASTIMAGE specification. Specify NOFETCHCOMPS or NOFETCHLASTIMAGE to turn off fetching for subsequent entries.

FETCHCOMP is more useful for audit applications, and FETCHLASTIMAGE is more appropriate for hot site applications. Several other items to note when using these parameters:

• FETCHLASTIMAGE outputs the latest image of the record found on disk. Changes that occurred during the current transaction are ignored.

• FETCHCOMPS uses data from the latest image to fill in missing values in the compressed record. This approximates but is not always exactly the same as the actual transaction values (if subsequent updates occurred).

• If the original record has been deleted from the database, Extract discards the record from the compressed record with a warning message.

• The record retrieved may be a newer version than the record processed from the audit trail, so intermediate update information will be lost.

• Fetching each update from the database can result in significant performance penalties because each record must be fetched with random access into the database (Extract processes other updates sequentially).

Syntax

FETCHCOMPS | FETCHLASTIMAGE | NOFETCHCOMPS | NOFETCHLASTIMAGE

FILE

Valid for

Logger

Description

Use FILE to specify file sets and record types to the current log. You can also use the EXCLUDEFILE parameter to subsequently exclude files that have been included by FILE.

You can include files in the current log by:

• Specifying file sets

• Specifying filtering values

• Optionally, you can specify a variety of attributes to enhance file processing.

Syntax

FILE file_set [, attribute...][, filter];

file_set

The name of the file, or file set to be processed.

attribute

For details about the attributes you can specify as FILE options see "Using parameters as FILE options".

filter

Can be one or more of the following:

• PROCESS process_file_set

• PROGRAM program_file_set

• USER user_id

If more than one filter is specified, all of the specified criteria must be met. See "Including with filters" for details about using filters.

Specifying file sets

FILE can explicitly name a file set, as in $DATA3.DAT.TRANS, or specify wildcard arguments as in $DATA4.*.*.

When multiple FILE statements are included in a parameter file, only the first match is logged. The following is an example:

FILE $DATA4.GGSDAT.FIN*
FILE $DATA4.GGSDAT.*

In this case, the file set $DATA4.GGSDAT.FIN* is logged only once, to the corresponding log, even if a subsequent FILE (such as $DATA4.GGSDAT.*) implicitly includes it in a wildcard argument.

The primary partition of the file must be satisfied by a FILE entry for extraction to take place. Also, excluding secondary partitions has no effect.

If you wish to retrieve a file set from a remote node, Logger must be configured and running on that node locally.

Using parameters as FILE options

You can use other parameters as options with FILE; this lets you apply a parameter to a single file or file set, rather than all the files in your Logger group. Parameters that can be set as an option in FILE include:

• COMPRESSUPDATES | NOCOMPRESSUPDATES (default)

• GETAUDITED | IGNOREAUDITED (default)

• GETBEFOREUPDATES | IGNOREBEFOREUPDATES (default)

• GETBULKIO | IGNOREBULKIO (default)

• GETUNSTRUCTURED | IGNOREUNSTRUCTURED (default)

• RENAMEDELAY delay_seconds

• SUSPENDED | ACTIVE (default)

Including with filters

A file can also be included based on filters. If you specify more than one filter, all of the filter criteria must be met to include the file for logging. For example, the following only includes the specified file set when both the process and user ID filter criteria are met.

FILE $DATA3.APPL.TL*, PROCESS $APP*, USER SUPER.*

If a file is included by FILE without filters, then excluded according to one or more filters, the file is excluded when the filter criteria are met.

Syntax

FILE file_set 
[, PROCESS process_file_set] 
[, PROGRAM program_file_set] 
[, USER user_id]
;

PROCESS process_file_set

Includes files sets that are opened by the specified process.

PROGRAM program_file_set

Includes file sets that are opened by the specified program.

USER user_id

Includes file sets when the creator ID of the opening process or program is associated with the specified user ID.

Specifying attributes

You can optionally specify attributes for:

• Compressing updates

• Capturing or omitting record types

• Supporting file renaming

• Suspending and resuming logging

To specify multiple attributes, include each attribute in a separate FILE statement, similar to:

COMPRESSUPDATES
FILE $DATA1.DAT.PAYMENT, NOCOMPRESSUPDATES
FILE $DATA1.DAT.ACCOUNT, GETBULKIO
FILE $DATA1.DAT.TRANS, RENAMEDELAY 10

This example does the following:

• Compresses updates for subsequent FILE statements.

• Turns compression off for the PAYMENT file.

• Logs bulk I/O for the ACCOUNT file.

• Supports file renaming by delaying the actual rename of the TRANS file until the name is changed in the log.

See the following subsections for details about using the FILE parameter attributes.

Compressing updates

You can compress and decompress update records using the COMPRESSUPDATES and NOCOMPRESSUPDATES options.

By default, update records are not compressed. The default can result in lower throughput, especially across Wide Area Networks, due to the additional traffic load. For example, consider an application that updates a 1000-byte customer record with a key 20 bytes long. If typically only the balance field within the customer record is changed, and that field is 10 bytes long, only 38 bytes rather than 1000 need to be transmitted across the network to execute a replicated update (20 bytes for the key, 10 bytes for the balance field, and 8 bytes to indicate the position within the record of the changed bytes and the key).

If you wish to use a field in KEYCOLS which is not part of the source file's primary key, you cannot use COMPRESSUPDATES.

Syntax

FILE file_set, {COMPRESSUPDATES | NOCOMPRESSUPDATES}

NOCOMPRESSUPDATES

No compression (the default).

COMPRESSUPDATES

COMPRESSUPDATES directs Logger to compress update record images by comparing before and after-images. Compressed images include the key of the changed record and only the changed bytes within the record. Note that if you wish to use a field in KEYCOLS which is not part of the source file's primary key, you cannot use COMPRESSUPDATES.

Capturing or omitting record types

You can specify that Logger include or omit the following record types:

• TMF audited records

• Before updates

• Bulk I/O updates

• Unstructured file changes

• Omit inserts, updates, or deletes.

Syntax

FILE file_set 
[, GETAUDITED | IGNOREAUDITED]
[, GETBEFOREUPDATES | IGNOREBEFOREUPDATES]
[, GETBULKIO | IGNOREBULKIO]
[, GETUNSTRUCTURED | IGNOREUNSTRUCTURED]
[, OMITINSERTS | OMITUPDATES | OMITDELETES] ;

GETAUDITED | IGNOREAUDITED

Retrieves or omits data from files that are TMF audited.

Note:

Carefully consider possible outcomes before using GETAUDITED. Logger cannot capture TMF abort operations. When GETAUDITED is used, Logger will capture operations that it will not be able to back out if TMF aborts them.

GETBEFOREUPDATES | IGNOREBEFOREUPDATES

Retrieves or omits the before-images of records.

GETBEFOREUPDATES retrieves images of records before they are changed in addition to capturing the after record image. For example, if an account balance was $100 before a transaction, and after it was $1000, both records will be written to the log trail. This information can be useful for data warehousing, archival, and other applications. When records are deleted, before-images are always logged. The default is to not extract before-images.

GETBULKIO | IGNOREBULKIO

Retrieves or omits bulk I/O updates. IGNOREBULKIO (omit bulk I/O updates) is the default. Bulk I/O occurs when Guardian SETMODE 141 is invoked by a program, such as during FUP LOAD or FUP DUP.

GETUNSTRUCTURED | IGNOREUNSTRUCTURED

Retrieves or omits unstructured file changes. IGNOREUNSTRUCTURED (omit unstructured file changes) is the default.

OMITINSERTS | OMITUPDATES | OMITDELETES

Excludes the specified record operation from being captured. You can combine these options.

Supporting file renaming

If your application renames database files while they are still open and being updated, the RENAMEDELAY option ensures that the new file name changes in the log trail. For RENAMEDELAY, specify a delay interval to give the system time to detect and record the new name.

Syntax

FILE file_set, RENAMEDELAY delay_seconds

delay_seconds

Represents the delay interval, as in RENAMEDELAY 10. The interval should range from 5-15 seconds, depending on overall system resource usage and hardware capacity. In general, more powerful systems require less delay. Use this feature with caution, since the process invoking the rename will be delayed before being allowed to continue.

Suspending and resuming logging

You can temporarily suspend, then resume logging for a specified file set by using the SUSPENDED and ACTIVE options.

Syntax

FILE file_set, {SUSPENDED | ACTIVE} ;

SUSPENDED

Temporarily suspends logging for the particular file set.

ACTIVE

Resumes logging for the file set.

FILE | TABLE

Valid for

Extract

Description

Use FILE or TABLE to specify the files or tables for which to capture data. You can specify a file name, or a wildcard arguments such as $DATA3.*.*. If you are retrieving records from remote locations, you must fully qualify the file name with its node as well as the volume, subvolume, and file. (For simplicity, references to FILE in this section also refer to TABLE unless explicitly stated otherwise.)

For Enscribe, unless you specify otherwise, records from every partition of the specified file are retrieved.

You can invoke FILE or TABLE more than once in a parameter file, and you can invoke the same FILE or TABLE argument more than once. This is useful, for example, to split records into different trails according to column values, to put inserts, updates and deletes into separate files, and to segment data for other reasons.

Syntax

FILE file_name
[, ALTNAME alternate_file_name]
[, AUTOTRUNCATE]
[, COLMAP (column_map_specification) | NOCOLMAP]
[, COMPRESSDELETES]
[, DEF source_ddl_definition]
[, EXITPARAM "exitparam_string"]
[, FILTER (expression)]
[, KEYCOLS (key_column_specification)]
[, PARTITIONS partition_specification]
[, RANGE (range_specification)]
[, SQLEXEC (sqlexec_clause)]
[, SQLNAME table_alias]
[, STARTKEY key_specification, ENDKEY key_specification]
[, TARGET target_file_name]
[, TARGETDEF target_ddl_definition]
[, TARGETNAME target_file_name]
[, USETARGETDEFLENGTH]
[, TOKENS (token_specification)]
[, WHERE (where_condition)]
;

file_name

A physical file name or an existing define name of CLASS MAP or a wildcard file name. The file can be a SQL table, SQL view or Enscribe file

ALTNAME

See "Handling missing files".

AUTOTRUNCATE

See "Purging records for Initial Load".

COMPRESSDELETES

See "Compressing Records".

COLMAP SQLNAME TARGETDEF TARGETNAME USETARGETDEFLENGTH

See "Mapping Data".

DEF FILTER PARTITIONS STARTKEY, ENDKEY RANGE WHERE

See "Selecting Records".

EXITPARAMS

See "Passing literal strings to user exits".

SQLNAME

See "Specifying a table alias".

SQLEXEC

See "Performing a query".

TOKENS

See "Using tokens".

Compressing Records

Use COMPRESSDELETES to replicate only the primary keys for deleted records. Without this parameter, all columns are replicated. By sending only the primary key, Oracle GoldenGate has all of the data required to delete the target record, while restricting the amount of data that must be processed.

Syntax

FILE file_name, COMPRESSDELETES

Selecting Records

You can select records by:

• Selecting or excluding records using FILTER.

• Selecting based on a conditional statement using WHERE.

• Selecting a subset of records using RANGE.

• Selecting a specific data partition using PARTITIONS.

• Selecting Enscribe records based on a STARTKEY and ENDKEY.

  Note:

  Using the RANGE option of FILE or MAP provides different capabilities than using the @RANGE function within a FILTER. And both of these are different than the RANGE option of ALTINPUT.

Selecting or Excluding Records Using FILTER

In the FILTER expression, records are selected according to a filter clause. Options specify the record types to include or omit when applying the filter. You can combine the filter clause with one or more options, but the filter_clause must always be included.

If you are selecting from an Enscribe file using FILTER, you must also specify the DEF option.

Syntax

FILE file_name,
FILTER (filter_clause 
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE])
[, DEF source_ddl_definition]
;

ON INSERT | ON UPDATE | ON DELETE

Include in the filter expression to specifically limit the filter clause to be executed on an insert, update or delete. You can specify more than one option. For example, ON UPDATE, ON DELETE executes on updates and deletes, but not inserts.

IGNORE INSERT | IGNORE UPDATE | IGNORE INSERT

Ignores the specified operation. You can specify more than one IGNORE option.

DEF source_ddl_definition

Has meaning only for Enscribe files. Use a DDL definition or record within the open dictionary. This definition describes the record that is extracted from the TMF audit trails. You cannot specify more than one definition for any FILE statement.

Selecting Based on a Conditional Statement

With the WHERE option, you can select information based on a conditional statement. If you are selecting from an Enscribe file using WHERE, you must also specify the DEF option.

Syntax

FILE file_name, WHERE (where_condition) 
[, DEF source_ddl_definition];

where_condition

Selects a subset of records from a source file or table, based on a condition, such as WHERE (branch = "NY"). For a list of valid operators, see Table 2-32.

DEF source_ddl_definition

Has meaning only for Enscribe files. Use a DDL definition or record within the open dictionary. This definition describes the record that is extracted from the TMF audit trails. You cannot specify more than one definition for any FILE statement.

PRODUCT_AMT

-123, 5500.123

 "AUTO", "Ca"

 =, <>, >, <, >=, <=

AND, OR

Selecting a Subset of Records Using RANGE

Use the RANGE clause to select a subset of the records in the source file or table. Unlike WHERE, RANGE does not require knowledge of the table or file structure.

Syntax

FILE file_name, RANGE (x [, x, ...] OF y);

(x [, x,...] OF y)

Selects a subset of records from a source file or table, based on a condition, such as RANGE (3 of 5) or RANGE (1, 3 of 5).

Duplicate unique index errors are possible when using the RANGE option for a file with a unique alternate key. For example, you delete the primary key for a record with primary key A and alternate key B. Then you insert with a different primary key but the same alternate key (e.g. primary key C, alternate key B). RANGE separates records based on the primary key, so transaction A and C can be sent to different Replicats and encounter a duplicate unique index on B when the insert is picked up first.

To avoid this, use the FILTER (@RANGE) function explained on "RANGE"and supply the columns that make up the unique alternate key as illustrated in the following example.

Example

In this example table TAB1 has three columns. The first two, COL1 and COL2, form the primary key and the third is the unique alternate index named U_INDX_COL. The range for the trail files is set up to override the primary key with the unique index column as shown below.

EXTTRAIL AA
TABLE TAB1, FILTER (@RANGE (1, 2, U_INDX_COL));
EXTTRAIL BB
TABLE TAB1, FILTER (@RANGE (2, 2, U_INDX_COL));

Selecting a Specific Data Partition

Use the PARTITIONS option to specify which partitions of the file or table to write to the current Oracle GoldenGate trail. Particular partitions can be output to specific files in the trail, or skipped altogether.

Use PARTITIONS only when you have specified SOURCEISFILE and either FASTUNLOAD or FASTUNLOADSHARED. Otherwise, PARTITIONS has no effect.

Syntax

FILE file_name, PARTITIONS (volume_specification);

volume_specification

A volume name, a volume number, or a range of volume numbers. Volume numbers begin with zero, and the last volume number can be specified as L. You can specify multiple volumes, delimited by commas, as in the following examples:

TABLE XYZ, PARTITIONS 
(\LA.$DATA1, $DATA3, \XYZ.$SYSTEM);
TABLE ABC, PARTITIONS 
(0, 2 - 5, 10 - L);

Selecting Enscribe Records Based on Key

STARTKEY and ENDKEY can be used to limit the range of the keys that will be selected if your parameter settings meet the following requirements:

• The FILE statement must specify an Enscribe file as the source.

• SOURCEISFILE must apply.

• Either SOURCEDEFS or both DICTIONARY and DEF must be present.

• If SOURCEDEFS is used, the access cannot be by ALTKEY.

• The PARTITIONS option cannot be used.

• The FASTUNLOAD option cannot be used.

The columns used in the key specification must make up the high order portion of a system key, primary key, or alternate key defined for the Enscribe file. A SOURCEDEFS or DICTIONARY must be present to define the column name and value.

Both STARTKEY and ENDKEY are required and both are evaluated when deciding whether to select the record. Any existing FILTER or WHERE clauses are processed for records selected based on key range.

STARTKEY and ENDKEY can be defined for different FILE statements in the same parameter file.

Syntax

FILE file_name
[, STARTKEY key_specification, ENDKEY key_specification]

Example

FILE $NY.ACCT.MASTER, STARTKEY (DIV="A1", ACCTNO=00000),
                      ENDKEY (DIV="Z9", ACCTNO=49999),
                      DEF $NY.DDLDEF.ACTDEF;

Mapping Data

Oracle GoldenGate has the following data mapping capability for the FILE or TABLE parameters:

• Mapping columns.

• Retrieving data layout during Replicat processing.

• Invoking a user exit.

Mapping Columns

Using a COLMAP clause, you can extract fields or columns from one record and map them to a differently structured record. This is useful, for example, when delivering data from an Enscribe file to an SQL table with similar, but not identical, fields. COLMAP selects, translates, and moves the fields you want into the new structure. When associated records are output, they are identified by the target file rather than the source to reflect the new structure of the record.

• When mapping from an Enscribe file, include either the DEF or the TARGETDEF option. Use DEF when capturing and mapping from the trail. If the target is an Enscribe file, you must associate a DDL definition with the target so that mapping instructions can be interpreted with TARGETDEF.

• When SQL tables are identified as the target, the layout of the target record after mapping is known since the SQL table structure is retrieved from the SQL catalog.

Additionally, you can match the source record length to the target record length. See "Matching Source and Target Record Lengths".

COLMAP requires the TARGET option. A DDL definition for an Enscribe target is required only once in the parameter file, and only when using a COLMAP clause.

Syntax

FILE file_name, 
COLMAP (column_map_specification) | NOCOLMAP
TARGET target_file_name 
[, DEF source_ddl_definition | TARGETDEF target_ddl_definition]
;

column_map_specification

The column mapping expression, as in:

(target_column = source_expression)

Explicitly defines a source-target column map where target_column is the name of the target column and source_expression can be any of the following:

• Numeric constant, such as 123

• String constant enclosed within quotes, such as "ABCD"

• The name of a source column, such as ORD_DATE

• An expression using an Oracle GoldenGate column-conversion function, such as @STREXT (COL1, 1, 3)

NOCOLMAP

Allows the user to specify a DEF for filtering purposes, but prevents the column-mapping command from completing. Example:

MAP \PROD.$DATA06.CER1ATLF.TL*, 
TARGET \GGS2.$DATA10.GGSLOGS.*, 
DEF TLF, 
NOCOLMAP,
WHERE (TLF.HEAD.REC-TYP <> "00");

TARGET target_file_name

Names the target file. Must be an existing Enscribe file or SQL table, and can be an active define name.

DEF source_ddl_definition

Has meaning only for Enscribe files. Use a DDL definition or record within the open dictionary. This definition describes the record that is extracted from the TMF audit trails. You cannot specify more than one definition for any FILE statement.

TARGETDEF target_ddl_definition

Use TARGETDEF when invoking column mapping to an Enscribe file structure and the Enscribe file has not yet been specified in the parameter file, or did not have a definition associated with it.

If you assigned a definition to the target file earlier in the parameter file, you can omit TARGETDEF.

Matching Source and Target Record Lengths

Use the USETARGETDEFLENGTH option to adjust the source record length to the length of the target record. Precede the COLMAP statement with the USETARGETDEFLENGTH option.

Syntax

USETARGETDEFLENGTH
USETARGETDEFLENGTH
COLMAP (USEDEFAULTS,
CRD-TYP = @STRSUB (CRD-TYP, "VD", "PV"),
FIID = @STRSUB (FIID, "WA", "SFNB"),
FIID = @STRSUB (FIID, "ID", "BAID"));

Retrieving Data Layout during Replicat Processing.

The table name stored in the remote trail can be different for each record. Using the TARGETNAME option, you can specify a different file name in the header for each record retrieved from the file. Replicat uses the new file name to resolve the file or table layout.

If the specified file exists at the target node, Replicat can retrieve the layout from a local catalog or dictionary, which can save a significant amount of time.

Syntax

FILE file_name, TARGETNAME target_file_name;

target_file_name

The name of the target file for which Replicat retrieves the layout.

Purging records for Initial Load

When extracting data for an initial load, you can use the AUTOTRUNCATE option to send a PURGEDATA record to the trail as the first record. The PURGEDATA purges the target file before Replicat applies any data, so that the target file is loaded from a clean state.

Use AUTOTRUNCATE with extreme caution, since it causes all existing data to be purged from the target file.

• Do not use AUTOTRUNCATE if you are performing multiple direct loads to the same target file, such as when using a range function to distribute the processing load.

• AUTOTRUNCATE is not suited to a bi-directional configuration. PURGEDATA is a DDL statement that is automatically committed and not linked to any transaction, making loop detection difficult. Without effective loop detection, AUTOTRUNCATE could cause not only target, but also original source files, to be purged of data. If you use AUTOTRUNCATE in a bi-directional configuration, you should use IGNOREPURGEDATAS in your online Extract groups.

Handling missing files

Use the ALTNAME option to specify an alternate name for a file that may no longer exist. Many applications use daily transaction files; purging the previous file and creating a new transaction file each day. A problem can occur when Extract expects to process data from a purged or renamed file.

Using ALTNAME, you can specify a generic definition for these types of files. This requires that a generic file exist that accurately describes the structure of each of the files in a group. The generic file does not require any data.

This option applies only when the source is an Oracle GoldenGate or Logger trail.

Syntax

FILE file_name, ALTNAME alternate_file_name 
[, DEF source_ddl_definition];

alternate_file_name

The alternate file name.

DEF source_ddl_definition

Has meaning only for Enscribe files. Use a DDL definition or record within the open dictionary. This definition describes the record that is extracted from the TMF audit trails. You cannot specify more than one definition for any FILE statement.

Example

FILE $DATA1.DAT.TR*, ALTNAME $DATA1.TEMPLATE.TRANS, DEF TRANS-DEF;

Passing literal strings to user exits

Use EXITPARAM "exitparam_string" to pass a literal string to user exit routines whenever a record from FILE is encountered.

The string must be enclosed in double quotes and an ampersand used if it continues to additional lines. It is unlimited in size, but you must use the new function GET_EXIT_PARAM_VALUE to access values over the default of 256 bytes.

Syntax

FILE file_name, EXITPARAM "exitparam_string";

Specifying a table alias

When you specify the FORMATASCII, FORMATSQL, or FORMATXML parameters, you can use SQLNAME to substitute a string for the table name in the output. To preserve lowercase attributes of the string, enclose the string in quotes.

Syntax

FILE file_name, SQLNAME table_alias;

Performing a query

Use SQLEXEC to perform a SQL query when processing a record for a SQL/MP table. SQLEXEC enables Oracle GoldenGate to communicate directly with the database to perform any query that SQL supports. The database function can be part of the synchronization process, such as retrieving values for column conversion, or it can be independent of extracting or replicating data.

By using SQLEXEC within multiple FILE or MAP statements, you can create different rules for different tables; these rules can be as simple or as complex as needed. A query that is executed can accept input parameters from source or target rows and pass output parameters.

In the following example, SQLEXEC runs a select statement, extracting the timestamp from the target table, then filters out records as needed.

FILE $DATA1.SQLDAT.ORDERS, 
SQLEXEC (ID check, 
QUERY " SELECT TIMESTAMP FROM $DATA1.SQLDAT.ORDERS "
" WHERE PKCOL =?P1 ", PARAMS (P1 = PKCOL), ERROR REPORT);

A SQLEXEC statement expects legal SQL syntax for the database being affected. Refer to the SQL for NonStop reference guide for permissible SQL syntax.

Syntax

FILE file_name, SQLEXEC (
ID logical_name, 
QUERY "sql_query", 
{PARAMS param_spec | NOPARAMS}
[, AFTERFILTER | BEFOREFILTER]
[, DBOP]
[, EXEC frequency]
[, MAXVARCHARLEN bytes]
[, PARAMBUFSIZE num_bytes]
[, TRACE option]
[, ALLPARAMS option]
[, ERROR action]
[, ...]
)

ID logical_name

Defines a logical name for the query. A logical name is required in order to extract values from the query results. ID logical_name references the column values returned by the query.

QUERY "sql_query"

Specifies the SQL query syntax to execute against the database. The query must be valid, standard query statement for the database against which it is being executed. It can either return results with a SELECT statement or update the database with an INSERT, UPDATE, or DELETE statement.

For any query that produces output with a SELECT statement, only the first row returned by the SELECT is processed. Do not specify an "INTO..." clause for any SELECT statements.

Enclose the query within quotes. For a multi-line query, use quotes on each line. To ensure success, place a space after each begin quote and each end quote, or at least before the end quote.

For example, in the following, there are spaces before the words select and where and after the words ggs_notify and ?p1."

SQLEXEC (
ID ggs, ON UPDATES, ON INSERTS,
QUERY " select notified from $DATA1.SQLDAT.NOTIFY "
" where account_no = ?p1 ",
PARAMS (p1 = account_no)
)

PARAMS param_spec | NOPARAMS

Defines whether the query accepts parameters. One of these options must be used.

AFTERFILTER | BEFOREFILTER

Specifies when to execute the query in relation to a FILTER clause. AFTERFILTER executes after the filter and enables you to skip the overhead of executing the SQL unless the filter is successful. This is the default. BEFOREFILTER executes before the filter and enables you to use the results of the procedure or query in the filter.

DBOP

Commits INSERT, UPDATE, DELETE, and SELECT statements executed within the query. Otherwise, they could potentially be rolled back. Oracle GoldenGate issues the commit within the same transaction boundaries as the source transaction. Use caution: any changes that are committed by the procedure can result in overwriting existing data.

EXEC frequency

Controls the frequency with which a query executes and how long the results are considered valid, if extracting output parameters. Takes one of the following arguments:

MAP

Executes the query once for each source-target table map for which it is specified. MAP renders the results invalid for any subsequent maps that have the same source table. For example, if a source table is being synchronized with more than one target table, the results would only be valid for the first source-target map. MAP is the default.

ONCE

Executes the query once during the course of an Oracle GoldenGate run, upon the first invocation of the associated FILE or MAP statement. The results remain valid for as long as the process remains running.

TRANSACTION

Executes the query once per source transaction. The results remain valid for all operations of the transaction.

SOURCEROW

Executes the query once per source row operation. Use this option when you are synchronizing a source table with more than one target table, so that the results of the procedure or query are invoked for each source-target mapping.

MAXVARCHARLEN bytes

Specifies the maximum length allocated for any output parameter in a query. Beyond this maximum, output values are truncated. The default is 255 bytes without an explicit MAXVARCHARLEN clause.

PARAMBUFSIZE num_bytes

Specifies the maximum size of the memory buffer that stores parameter information, including both input and output parameters. Oracle GoldenGate issues a warning whenever the memory allocated for parameters is within 500 bytes of the maximum. The default is 10,000 bytes without an explicit PARAMBUFSIZE clause.

TRACE option

Takes one of the following arguments:

TRACE ALL

Writes the input and output parameters of each invocation of a query to the report file.

TRACE ERROR

Writes parameters to the report file only if an error occurs.

ALLPARAMS option

Takes one of the following arguments:

ALLPARAMS REQUIRED

Indicates that all parameters must be present for the queries to execute.

ALLPARAMS OPTIONAL

Allows the query to execute without all parameters being present.

ERROR action

Requires one of the following arguments:

ERROR IGNORE

Database error is ignored and processing continues.

ERROR REPORT

Database error is written to a report.

ERROR RAISE

Database error is handled just as a table replication error.

ERROR FINAL

Database error is handled as a table replication error, but does not process any additional queries.

ERROR FATAL

Database processing abends.

Using tokens

Use TOKENS to define a user token and associate it with data. Tokens enable you to extract and store data within the user token area of a trail record header. Token data can be retrieved and used in many ways to customize the delivery of Oracle GoldenGate data. For example, you can use token data in column maps or macros.

To use the defined token data in target tables, use the @TOKEN column-conversion function in the COLMAP clause of a Replicat MAP statement. The @TOKEN function maps the name of a token to a target column.

Syntax

FILE file_name, 
TOKENS (token_name = token_data [, ...])
;

token_name

A name of your choice for the token. It can be any number of alphanumeric characters and is not case-sensitive.

token_data

A character string of up to 2000 bytes. The data can be either a constant that is enclosed within double quotes or the result of an Oracle GoldenGate column-conversion function.

Example

The following creates tokens named TK-OSUSER, TK-GROUP, and TK-HOST and maps them to token data obtained with the @GETENV function.

TABLE $DATA.MASTER.ACCOUNT, TOKENS (
TK-OSUSER = @GETENV ("GGENVIRONMENT", "OSUSERNAME"),
TK-GROUP = @GETENV ("GGENVIRONMENT", "GROUPNAME")
TK-HOST =  @GETENV ("GGENVIRONMENT", "HOSTNAME"));

FILEAGEDAYS

Valid for

Extract, Replicat

Description

Use FILEAGEDAYS to specify the number of days a file can be inactive before the Extract or Replicat process ages it off of its file list.

Only files added with a wildcard match are eligible to be removed. Files cannot be aged off if they are designated in FILE and MAP statements that have filters, SQLEXEC statements, or column mappings that use column functions.

Audserv will inherit this parameter from the Extract.

Default

Files are not aged off the list.

Syntax

FILEAGEDAYS days

days

The number of days a file can be inactive before the process will remove it from the file list. Entries can range from 1 to 365 days.

Examples

Example 1   

The following MAP example includes the @DATE function in the column mapping so the TL* files are not eligible for aging off the file list.

MAP $DATA.SOURCE.TL*, TARGET $DATA4.TARGET.*
COLMAP (USEDEFAULTS,
       DATETIMECOL = @DATE("YYYY-MM-DD:HH:MI:SS", "YYYY-MM-DD:HH:MI:SS",
       @DATENOW())
       );

Example 2   

The following MAP example does not include a function, so the TL* files are eligible to be aged off the file list.

MAP $DATA.SOURCE.TL*, TARGET $DATA4.TARGET.*
COLMAP (USEDEFAULTS,
       DATETIMECOL = "2010-02-14:01:02:03"
       );

FILEEXCLUDE

Valid for

Extract

Description

Use FILEEXCLUDE to exclude files or file sets from a wildcard list. Specify the FILEEXCLUDE parameter after the EXTFILE or EXTTRAIL entry to which the rule applies. This parameter is an alias for TABLEEXCLUDE.

Syntax

FILEEXCLUDE file_identifier

file_identifier

The file set name or a wildcard argument.

Example

The following example includes file names that begin with A, except for files in subvolume BADSUB, files that begin with AB and the file $DATA1.BAD.A123. Data is output into EXTFILE $DATA1.EXTDAT.EXTFILE1. Data from any file beginning with A would be included in EXTFILE $DATA1.EXTDAT.EXTFILE2.

EXTFILE $DATA1.EXTDAT.EXTFILE1
FILE $*.*.A;
FILEEXCLUDE $*.BADSUB.*
FILEEXCLUDE $*.*.AB*
FILEEXCLUDE $DATA1.BAD.A123
EXTFILE $DATA1.EXTDAT.EXTFILE2
FILE $*.*.A;

FILEOPWARNING

Valid for

Replicat

Description

Use FILEOPWARNING to control Replicat's behavior when it attempts to purge non-existent files. FILEOPWARNING instructs Extract to ABEND, ignore the attempted purge, or issue a warning.

Default

ABEND

Syntax

FILEOPWARNING {ABEND | IGNORE | WARN}

ABEND

Stops processing

IGNORE

Ignores the purge attempt and continues processing.

WARN

Issues a warning to the report file.

FILERESOLVE

Valid for

Extract

Description

Use FILERESOLVE to alter the rules for processing wildcard file and table entries. By default, wildcard FILE entries are processed each time the wildcard rule is satisfied. If incorrect syntax is entered in the FILE parameter, this can lead to ABEND conditions after startup parameters have been processed.

Replicat also processes file lists with wildcards, but it does so dynamically and the default value of FILERESOLVE DYNAMIC can only be altered for Extract.

Default

DYNAMIC

Syntax

FILERESOLVE {DYNAMIC | IMMEDIATE | BOTH}

DYNAMIC

Wildcard FILE entries are processed each time the wildcard rule is satisfied.

IMMEDIATE

Existing files or tables that satisfy the wildcard definition are processed at startup.

BOTH

Files existing at startup are processed at that time, and files created after startup are processed when encountered.

Examples

Example 1   

Consider the following scenario. $DATA1.DAT.FILE1 exists when Extract starts, and $DATA1.DAT.FILE2 is created after startup.The following example results only in extraction from FILE1. In addition, the list of files to which the wildcard applies is immediately available in the report.

FILERESOLVE IMMEDIATE
FILE $DATA1.DAT.*

In this case, the file list for $DATA1.DAT.* is resolved immediately, and future additions to this list are ignored.

Example 2   

The following example results in the extraction from both TABLE1 and TABLE2. However, the FILE parameter is not resolved until a record for TABLE1 or TABLE2 is encountered. Therefore, the MAP statement is not checked for validity until that point.

FILERESOLVE DYNAMIC
TABLE $DAT1.DAT.*, TARGET $DATA2.DAT.TEMPLATE,
COLMAP (COL1 = COL2);

Example 3   

In the following example, if TABLE1 exists at runtime, but TABLE2 does not, the MAP for TABLE1 is checked for syntax immediately, and TABLE2 will still be picked up.

FILERESOLVE BOTH
TABLE $DATA1.DAT.*, TARGET $DATA2.DAT.TEMPLATE,
COLMAP (COL1 = COL2);

FILLSHORTRECS | NOFILLSHORTRECS

Valid for

Extract, Replicat

Description

Use FILLSHORTRECS to tell the Extract to fill the end of data records to fit their maximum configured length with spaces, zeros or SQL default column values. Use this to generate records in the trails, that are a constant length or contain the same number of columns.

FILLSHORTRECS can also be used for Replicat MAP statements that have a COLMAP statement to trigger mapping.

This functionality can be toggled on and off around FILE or MAP statements by using NOFILLSHORTRECS.

Default

NOFILLSHORTRECS

Syntax

FILLSHORTRECS | NOFILLSHORTRECS

FILTERVIEW | NOFILTERVIEW

Valid for

Extract

Description

Use FILTERVIEW to process SQL views by entering a view as the object in a FILE or TABLE entry. When processing views, Extract is actually processing the underlying table in the TMF audit trail. Extract internally maps the table to the view structure.

By default, when processing updates for views, Extract outputs a view update only if one of the underlying columns in the view changes. As a result, updates to the underlying table are not always output. Use NOFILTERVIEW to ensure that a record is output whenever the underlying table is updated regardless of whether or not any of the view's columns changed. FILTERVIEW ensures that records are output only when one of the columns changed.

Default

FILTERVIEW

Syntax

FILTERVIEW | NOFILTERVIEW

FLUSHCHECKPOINT | NOFLUSHCHECKPOINT

Valid for

Replicat

Description

Use FLUSHCHECKPOINT to control whether Enscribe files are flushed when Replicat records a checkpoint. Specify FLUSHCHECKPOINT to guarantee that when a checkpoint is recorded all data replicated before the checkpoint is stored on disk. If FLUSHCHECKPOINT is off, data may remain in file buffers.

Although FLUSHCHECKPOINT is safer, it can result in significant performance impact to Replicat processes.

Default

NOFLUSHCHECKPOINT

Syntax

FLUSHCHECKPOINT | NOFLUSHCHECKPOINT

FLUSHSECS | FLUSHCSECS

Valid for

Extract

Description

Use these parameters to specify the number of seconds (FLUSHSECS) or centiseconds (FLUSHCSECS) for Extract to buffer records before flushing to the current log trail.

Default

2 seconds

Syntax

FLUSHSECS seconds | FLUSHCSECS centiseconds

seconds

The maximum number of seconds to buffer records before flushing.

centiseconds

The maximum number of centiseconds to buffer records before flushing.

FORCESTOPDELAY

Valid for

Logger

Description

Use FORCESTOPDELAY with the STOPDELAYCSECS keyword to instruct BASELIB to delay stopping a process for specified time to give Logger time to read messages in its $RECEIVE queue. The delay interval is specified with the STOPDELAYCSECS parameter.

If the process being stopped has messages queued on Logger's $RECEIVE queue and that process is stopped before Logger reads them, the message system will remove those messages from the queue and discard them. If this happens, changes that were done to the database will never get logged to the log trail.

FORCESTOPDELAY applies to a STOP, ABEND, or other operating system instruction that stops a process. It also applies to stops issued from an application, an operator issuing a stop command, or to a process issuing a STOP or ABEND.

If the process that is stopping another process is not bound with BASELIB then this parameter cannot delay the stop.

Default

No delay

Syntax

FORCESTOPDELAY STOPDELAYCSECS centiseconds

centiseconds

The delay interval in centiseconds.

FORCEUSESYSKEY | NOFORCEUSESYSKEY

Valid for

Replicat

Description

Use FORCEUSESYSKEY to force Replicat to use the mapped SYSKEY when executing update and delete operations on entry-sequenced SQL tables. For relative SQL tables, the default is to include the syskey unless other KEYCOLS have been specified. When using FORCEUSESYSKEY, include SYSKEY in a COLMAP statement to ensure that the proper row is updated or deleted in the target table. NOFORCEUSESYSKEY results in SYSKEY being omitted from the WHERE clause for entry-sequenced SQL updates and deletes (an alternative path is assumed).

For inserts on relative Enscribe files, FORCEUSESYSKEY forces the SYSKEY to be -1 (insert at end of file). If FORCEUSESYSKEY is used on the target file, the source file SYSKEY cannot be used for update and delete operations since there is no assurance the two keys will match. In this case a unique alternate key or index should be used for updates and deletes.

This parameter has no effect on entry-sequenced or key-sequenced Enscribe files, or on standard key-sequenced or cluster key SQL tables.

Do not use when you are using the MAP KEYCOLS option.

Default

NOFORCEUSESYSKEY

Syntax

FORCEUSESYSKEY | NOFORCEUSESYSKEY

Examples

Example 1   

In this example the SYSKEY from the source table is stored in the SAVE_SYSKEY column when the record is an insert.

GETINSERTS
IGNOREUPDATES
IGNOREDELETES
MAP $DATA1.DAT.SOURCE, TARGET $DATA2.DAT.TARGET,
COLMAP (AMOUNT = AMT, 
SAVE_SYSKEY = SYSKEY);

Example 2   

Then the saved SYSKEY from the source identifies the target row when the record is an update or delete.

FORCEUSESYSKEY
IGNOREINSERTS
GETUPDATES
GETDELETES
MAP $DATA1.DAT.SOURCE, TARGET $DATA2.DAT.TARGET,
COLMAP (SYSKEY = SAVE_SYSKEY, 
AMOUNT = AMT);
NOFORCEUSESYSKEY

FORMATASCII | NOFORMATASCII

Valid for

Extract

Description

Use FORMATASCII to format subsequent output in external ASCII format. FORMATASCII applies to all FILE or TABLE specifications that follow the FORMATASCII entry, and can be turned off with the NOFORMATASCII parameter.

Using FORMATASCII, you can format output compatible with the majority of popular database load utilities and other tools.

You can specify the parameter with or without options. If you don't include options, records are output as follows:

• An operation type character, I, D, U, V (insert, delete, update, compressed update).

• A before or after-image indicator, B or A.

• The file or table name.

• If the NAMES option (the default) is selected: field name, field value, field name, field value...

• If the NONAMES option is selected: field value, field value... (NAMES format is always used for compressed records).

• Between each of the above items, a field delimiter (which defaults to tab).

• A new line character (line feed).

Before the beginning of the first record output for each transaction, a begin transaction record will appear. This record includes the following information:

• The begin transaction indicator, B.

• The timestamp at which the record committed.

• The sequence number of the audit trail in which the commit was found.

• The relative byte address (RBA) of the commit record within the audit trail.

• Delimiters following each of the above fields, followed by a new line character.

After the last record in each transaction is output, a commit record will appear. This record contains C, the delimiter and a new line character.

Every record in a transaction, and no other records, are contained between the begin and commit indicators. Each combination of commit timestamp and RBA is unique and increases as records are output.

You can customize the output format with options described in the syntax.

Do not use this format if the data is to be processed by Replicat. Replicat expects the default format.

Syntax

NOFORMATACSII | FORMATASCII [, option, ...]

option is one of the following.

BCP

Formats the output for compatibility with SQL Server's BCP (Bulk Copy Program) high-speed load utility.

Options that can be used with BCP are:

BCPRECORDLIMITER

The option changes the line delimiter from only line feed ('\n') to line feed and carriage return ('\r\n'). Use when sending data to Windows or UNIX SQL in bulk copy format. NonStop will not receive data in this format.

TIMESTAMP {0 | 3 | 6}

Valid when sending data to Windows or UNIX SQL in bulk copy format. The numeric options set the fractional part of the datetime data type:

• 0 — truncate the fractional portion of the timestamp

• 3 — include three digits in the fractional portion

• 6 — include six digits in the fractional portion

For example, the datetime 2013-12-11 18:26:06:35.123456 would be output as 2013-12-11 18:26:06:35 with the 0 option, 2013-12-11 18:26:06:35.123 with the 3 option, and 2013-12-11 18:26:06:35.123456 with 6.

The default format for the datetime data type is 0, truncate the fractional portion.

Note:

FORMATLOCAL must be used with datetime fractional options 3 or 6 for proper processing by the Collector on a Windows or Linux/UNIX system.

COLHDRS

Outputs the table's column names before the data. COLHDRS takes effect only when extracting directly from the table (rather than the TMF audit trails).

{DATE | TIME | TS}

Specifies one of the following:

• DATE — date (year to day),

• TIME — time (year to second) before record data,

• TS — transaction timestamp (year to fraction).

DELIMITER delimiter

An alternative field delimiter (the default is tab). Use the word TAB to delimit with tabs, otherwise use a single character enclosed in single quotes (for example, '/').

EXTRACOLS number_of_columns

Includes placeholders for additional columns at the end of each record. Use this when a target table has more columns than the source.

FILE

Includes just the file name portion of the file or table (default is the fully qualified file name).

NAMES | NONAMES

Includes or excludes column names from the output. For compressed records, column names are included unless you also specify PLACEHOLDERS.

NOHDRFIELDS header_option

Suppresses output of transaction information, the operation type character, the before or after-image indicator, and the file or table name.

You can customize header information by including a header_option as follows:

• IND includes the before or after indicator

• OP includes the operation type character

• WHOLEFILE includes the fully qualified file name

• FILE includes the file name portion of the file or table (as specified above)

NOQUOTE

Excludes quotation marks from character-type data. The default is to use quotation marks.

NOSYSKEY

Omits the record SYSKEY (relative or entry key) from the output, if one exists.

NOTRANSTMTS

Excludes transaction information.

NULLISSPACE

Outputs NULL fields as empty fields. The default is to output null fields as the word NULL.

PLACEHOLDERS

Outputs a placeholder for missing fields or columns. For example, if the second and fourth columns are missing in a four column table, the data would appear similar to:

'ABC',,123,,

SQLLOADER

Generates a file compatible with the Oracle SQL*Loader high-speed data load utility. SQLLOADER produces a fixed-length, ASCII-formatted file. Use this option only when one table's data is written to the Oracle GoldenGate trail (usually in the initial-load, SOURCEISFILE case).

Example

The following is a sample table and description. CUSTNAME is the primary key.

$DATA1.TEST.CUSTOMER:
CUSTNAME   CHAR(10)
LOCATION   CHAR(10)
BALANCE    INTEGER

The transaction on this table is:

BEGIN WORK;
INSERT INTO CUSTOMER VALUES ("Eric", "San Fran", 550);
UPDATE CUSTOMER SET BALANCE = 100 WHERE CUSTNAME = "Eric";
COMMIT WORK;

Entering FORMATASCII produces:

B,2010-02-17:14:09:46.421335,8,1873474,
I,A,\GGS.$DATA1.TEST.CUSTOMER,CUSTNAME,'Eric',LOCATION, 'San Fran',BALANCE,550,
V,A,\GGS.$DATA1.TEST.CUSTOMER,CUSTNAME,'Eric',BALANCE,100, C,

Entering FORMATASCII, NONAMES, DELIMITER '|', FILE produces:

B|2010-02-17:14:09:46.421335|8|1873474|
I|A|CUSTOMER|'Eric'|'San Fran'|550|
V|A|CUSTOMER|CUSTNAME|'Eric'|BALANCE|100|
C|

The last record returns column names because the record is a compressed update.

Entering FORMATASCII, NOHDRFIELDS, FILE, OP, TS, NONAMES, NOQUOTE produces:

I,CUSTOMER,2010-02-17:14:09:46.421335,Eric,San Fran,550,
V,CUSTOMER,2010-02-17:14:09:46.421335,Eric,,100,

The absence of the second field in the update record is indicated by two consecutive commas. Ordering of header fields is predetermined and is not affected by the ordering of options.

The ampersand (&) in the parameter entry continues the parameter to the next line in the parameter file.

FORMATLOCAL

Valid for

Extract

Description

Use FORMATLOCAL to determine whether formatting extracted data occurs on the source or the target. When data is output on NonStop, formatting is always local.

When formatting remotely, database definitions need to be exported from NonStop to the remote system. Retrieving this information is automatic when formatting is local, eliminating the requirement to keep remote definitions synchronized with the most current definitions.

Default

Format data remotely (offers far better performance)

Syntax

FORMATLOCAL

FORMATSQL | NOFORMATSQL

Valid for

Extract

Description

Use FORMATSQL to format subsequent output records in external SQL DML format. This is the format SQL needed to create the operation, that is, an INSERT, UPDATE or DELETE statement. You can apply this format to both SQL and Enscribe records.

FORMATSQL applies to all Oracle GoldenGate files specified below the FORMATSQL entry. Turn off FORMATSQL with the NOFORMATSQL parameter.

The output contains the following transaction-related information before the first record output for each transaction:

• the begin transaction indicator, B

• the timestamp at which the record committed

• the sequence number of the audit trail in which the commit was found

• the relative byte address (RBA) of the commit record within the audit trail

• commas following each of the above fields

• a new line character

After the last record in each transaction is output, a commit record will appear. This record contains C, the delimiter and a new line character.

Every record in a transaction, and no other records, are contained between the begin and commit indicators. Each combination of commit timestamp and RBA is unique and increases as records are output.

Do not use this format if the data will be processed by Replicat. Replicat expects the default format.

Syntax

NOFORMATSQL | FORMATSQL {ORACLE | NONAMES | FILE}

ORACLE

Records are formatted for compatibility with Oracle databases. Primarily this means that date and time fields are converted to a format suitable to SQL*Plus (for example, TO_DATE('2010-05-01','YYYY-MM-DD').

NONAMES

Omits column names when all columns are present in insert records to conserve space.

FILE

Outputs only the file portion of the NonStop file name in the SQL statement.

FORMATXML

Valid for

Extract

Description

Use the FORMATXML parameter to output data in XML format, instead of the default Oracle GoldenGate canonical format. A FORMATXML statement affects all extract files or trails that are defined after it.

Default

None

Syntax

FORMATXML [option] [, ...]

option is one of the following.

INLINEPROPERTIES | NOINLINEPROPERTIES

Controls whether properties are included within the XML tag or written separately. INLINEPROPERTIES is the default.

ONERECPERTRANS

Causes Extract to create one FORMATXML transaction per record if the SOURCEISFILE parameter is present. The default is to create one FORMATXML transaction per file.

TRANS | NOTRANS

Controls whether transaction boundaries and commit timestamps should be included in the XML output. TRANS is the default.

Example

FORMATXML NOINLINEPROPERTIES, NOTRANS

FUNCTIONSTACKSIZE

Valid for

Extract, Replicat

Description

Use FUNCTIONSTACKSIZE to control the size of the memory stack that is used for processing Oracle GoldenGate functions. You should not need to use this parameter unless Oracle GoldenGate returns a message indicating that the size of the stack should be increased.

The memory stack holds arguments supplied to and from an Oracle GoldenGate function. When a very large number of functions or arguments are used, the size of the stack may need to be increased.

FUNCTIONSTACKSIZE is a global parameter. It affects all clauses in a parameter file.

The default without FUNCTIONSTACKSIZE is 200 arguments, which optimizes the performance of Oracle GoldenGate and its usage of system memory. Increasing this parameter can adversely affect the performance and use of system memory.

FUNCTIONSTACKSIZE must appear in the parameter file before any parameter clauses are listed.

Default

200

Syntax

FUNCTIONSTACKSIZE stack_size

stack_size

A value between 50 and 5000 that denotes the number of arguments to allow in a parameter clause.

Example

FUNCTIONSTACKSIZE 300

GETALTKEYS | IGNOREALTKEYS

Valid for

Extract

Description

Use GETALTKEYS or IGNOREALTKEYS to tell Extract to produce or not produce file create records for alternate key files after it outputs the file create record for the primary file.

• GETALTKEYS causes Extract to create primary and alternate key files.

• IGNOREALTKEYS causes Extract to create primary key files.

Default

GETALTKEYS

Syntax

GETALTKEYS | IGNOREALTKEYS

GETAPPLOPS | IGNOREAPPLOPS

Valid for

Extract

Description

Use GETAPPLOPS or IGNOREAPPLOPS to include or exclude records produced by any program except Replicat in a particular Extract file. GETAPPLOPS remains in effect until IGNOREAPPLOPS is entered. Once entered, IGNOREAPPLOPS is in effect until GETAPPLOPS is entered.

IGNOREAPPLOPS provides a method for isolating database operations performed by Replicat against audited files from other work.

See "GETREPLICATES | IGNOREREPLICATES" for information on how to use GETREPLICATES and IGNOREREPLICATS to include or exclude records created by Replicat.

Default

GETAPPLOPS

Syntax

GETAPPLOPS | IGNOREAPPLOPS

GETAUXTRAILS | IGNOREAUXTRAILS

Valid for

Extract

Description

Use IGNOREAUXTRAILS to tell Extract to bypass TMF auxiliary TMF audit trails when capturing database changes. If relevant database changes are recorded in the master audit trail, this can have significant performance advantages.

IGNOREAUXTRAILS can specify particular trails to ignore, rather than all the trails.

Default

GETAUXTRAILS

Syntax

IGNOREAUXTRAILS aux_trail_num

aux_trail_num

A value between 0 and 15.

Example

The first example ignores all auxiliary TMF audit trails, while the second example ignores auxiliary trails AUX02 and AUX03.

IGNOREAUXTRAILS
IGNOREAUXTRAILS 2, 3

GETCOMPS | IGNORECOMPS

Valid for

Extract

Description

Use GETCOMPS or IGNORECOMPS to include or exclude compressed update records for the specified EXTFILE. GETCOMPS remains in effect until IGNORECOMPS is entered. Once entered, IGNORECOMPS remains in effect until GETCOMPS is entered.

Default

GETCOMPS

Syntax

GETCOMPS | IGNORECOMPS

Example

This example includes compressed update records in EXTRACT1 and EXTRACT3, but excludes compressed update records in EXTRACT2.

GETCOMPS
EXTFILE $DATA1.EXTDAT.EXTRACT1
        TABLE $DATA2.FINANCE.ACCOUNTS;
IGNORECOMPS
EXTFILE $DATA1.EXTDAT.EXTRACT2
        TABLE $DATA2.FINANCE.ACCOUNTS;
GETCOMPS
EXTFILE $DATA1.EXTDAT.EXTRACT3
        TABLE $DATA2.FINANCE.ACCOUNTS;

GETCREATES | IGNORECREATES

Valid for

Extract, Replicat

Description

Use GETCREATES or IGNORECREATES to include or exclude file create records from the specified EXTFILE. GETCREATES remains in effect until IGNORECREATES is entered. Once entered, IGNORECREATES remains in effect until GETCREATES is entered.

Default

IGNORECREATES

Syntax

GETCREATES | IGNORECREATES

Example

This example separates inserts, updates and creates into three files, EXTRACT1, EXTRACT2, and EXTRACT3.

IGNOREUPDATES
IGNORECREATES
GETINSERTS
EXTFILE $DATA1.EXTDAT.EXTRACT1
TABLE $DATA2.FINANCE.ACCOUNTS;
IGNOREINSERTS
GETUPDATES
IGNORECREATES
EXTFILE $DATA1.EXTDAT.EXTRACT2
TABLE $DATA2.FINANCE.ACCOUNTS;
IGNOREINSERTS
IGNOREUPDATES
GETCREATES
EXTFILE $DATA1.EXTDAT.EXTRACT3
TABLE $DATA2.FINANCE.ACCOUNTS;

GETDEFAULTS

Valid for

Extract, Replicat

Description

Use GETDEFAULTS to reset Extract parameters to their original default settings. This is useful if you have changed multiple Extract parameters, and now wish to reverse those changes.

Using GETDEFAULTS sets the following parameters to ON:

GETINSERTS
GETUPDATES
GETDELETES
GETUPDATEAFTERS
GETCOMPS
GETPURGEDATAS
GETALTKEYS

Using GETDEFAULTS sets the following parameters to OFF:

GETUPDATEBEFORES
GETNETCHANGES
GETPURGES
GETCREATES
GETALTERS
GETRENAMES

Syntax

GETDEFAULTS

GETDELETES | IGNOREDELETES

Valid for

Extract, Replicat

Description

Use GETDELETES or IGNOREDELETES to include or exclude delete records from the specified data source. GETDELETES is in effect until IGNOREDELETES is entered. Once entered, IGNOREDELETES is in effect until GETDELETES is entered.

Default

GETDELETES

Syntax

GETDELETES | IGNOREDELETES

Example

This example separates inserts, updates and deletes into three files.

IGNOREUPDATES
IGNOREDELETES
GETINSERTS
EXTFILE $DATA1.EXTDAT.EXTRACT1
TABLE $DATA2.FINANCE.ACCOUNTS;
IGNOREINSERTS
GETUPDATES
IGNOREDELETES
EXTFILE $DATA1.EXTDAT.EXTRACT2
TABLE $DATA2.FINANCE.ACCOUNTS;
IGNOREINSERTS
IGNOREUPDATES
GETDELETES
EXTFILE $DATA1.EXTDAT.EXTRACT3
TABLE $DATA2.FINANCE.ACCOUNTS;

GETENV

Valid for

Extract, Replicat

Description

Use GETENV to retrieve environment variables that were set with the SETENV parameter. The retrieved results can be used as input parameters for stored procedures or macros. The results are printed to screen and the Extract report file. Use one GETENV statement per variable to be retrieved. See ""GETENV"" for more information on the GETENV variables.

Default

None

Syntax

GETENV (environment_variable)

environment_variable

The name of the environment variable

Example

GETENV TRAIL1

GETFILEOPS | IGNOREFILEOPS

Valid for

Extract, Replicat

Description

Use GETFILEOPS or IGNOREFILEOPS to include or exclude file-level operations in the current EXTFILE. IGNOREFILEOPS remains in effect until GETFILEOPS is entered. Once entered, GETFILEOPS remains in effect until IGNOREFILEOPS is entered.

File-level operations that are included or excluded are CREATE, ALTER, PURGE, PURGEDATA, RENAME and some SETMODE, CONTROL and file label change operations. You must specify GETFILEOPS to extract bulk I/O records. When extracting from the TMF audit trail, only CREATE and PURGE records are available. For CREATE operations on audited files, Extract needs the file on disk to extract the record.

To implement GETFILEOPS or IGNOREFILEOPS, specify the desired parameter on one line, followed by the operation to include or exclude as shown in the syntax.

Using GETFILEOPS turns on the following values:

GETPURGES
GETPURGEDATAS
GETRENAMES
GETALTERS
GETCREATES
GETCHANGELABELS
GETSETMODES
GETUNSTRUCTOPS
GETCLOSES

Default

IGNOREFILEOPS

Syntax

GETFILEOPS operation | IGNOREFILEOPS operation

GETINSERTS | IGNOREINSERTS

Valid for

Extract, Replicat

Description

Use GETINSERTS or IGNOREINSERTS to include or exclude insert records in the current data source. GETINSERTS remains in effect until IGNOREINSERTS is entered. Once entered, IGNOREINSERTS remains in effect until GETINSERTS is entered.

Default

GETINSERTS

Syntax

GETINSERTS | IGNOREINSERTS

GETMARKERS | IGNOREMARKERS

Valid for

Extract

Description

Use GETMARKERS or IGNOREMARKERS to include or exclude marker records from the specified EXTFILE.

Marker records are special audit records created by users with the GGSCI command ADD MARKER. You can use marker records to identify application-specific critical points in Extract and Replicat processing.

The most common use of a marker is to identify a point at which all data has been replicated from a source to a target database. To do this, an operator brings down application activity against the source database, then adds a marker. After seeing a message that the marker was processed by Replicat, you can safely assume all records from the source database have been processed. At this point, for example, a hot site switch to the backup database could safely occur.

Default

GETMARKERS

Syntax

GETMARKERS | IGNOREMARKERS

GETNETCHANGES | IGNORENETCHANGES

Valid for

Extract

Description

Use GETNETCHANGES to retrieve fields that were changed in a particular update record, plus the primary key. Use IGNORENETCHANGES to output all fields, changed or not.

GETNETCHANGES remains in effect until IGNORENETCHANGES is entered. Once entered, IGNORENETCHANGES remains in effect until GETNETCHANGES is entered.

GETNETCHANGES guarantees that only changed records are retrieved. Compressed update records do not guarantee this will happen, because some update records do not actually change fields and will not be returned (for example, UPDATE TABX SET PRICE = 10 WHERE PRICE = 10). This is common in applications that use, for example, a generic update of each column to accomplish all update tasks, regardless of what is changed.

GETNETCHANGES retrieves and compares both before and after-images. This can result in a system resource usage increase.

Default

IGNORENETCHANGES

Syntax

GETNETCHANGES | IGNORENETCHANGES

GETNETWORKALTFILENAMES | IGNORENETWORKALTFILENAMES

Valid for

Replicat

Description

GETNETWORKALTFILENAMES lets the file system qualify the alternate key file names with the local node name. Use IGNORENETWORKALTFILENAMES to tell the file system not to qualify the alternate key file names with the local node name. You can add this parameter as part of a MAP statement or toggle it around MAP statements.

Default

GETNETWORKALTFILENAMES

Syntax

GETNETWORKALTFILENAMES | IGNORENETWORKALTFILENAMES

GETNEWCOLUMNS | IGNORENEWCOLUMNS

Valid for

Extract, Replicat

Description

Use GETNEWCOLUMNS to retrieve records from the TMF audit trails that describe new SQL column changes. GETNEWCOLUMNS enables two different activities:

• Updates table definitions in Extract and downstream in Replicat, without bringing either process down first (NonStop replication only). This lets you replicate columns added to a table after Oracle GoldenGate startup.

• Downstream in Replicat (NonStop only), replicates the statement that created the column.

Apply GETNEWCOLUMNS to files or Oracle GoldenGate trails individually. This means that you can withhold new column records from specific Extract files. To ensure that the column exists in the target database before replicating into it, it is recommended that GETNEWCOLUMNS apply to all files in the Oracle GoldenGate trail.

Default

GETNEWCOLUMNS

Syntax

GETNEWCOLUMNS | IGNORENEWCOLUMNS

GETPARTONLYPURGEDATAS | IGNOREPARTONLYPURGEDATAS

Valid for

Extract, Replicat

Description

Use GETPARTONLYPURGEDATAS or IGNOREPARTONLYPURGEDATAS to include or exclude PARTONLY PURGEDATA operations. From the point it is entered in the parameter file, the parameter remains in effect until its opposite is encountered. If GETPARTONLYPURGEDATAS is entered, for example, it remains in effect until an IGNOREPARTONLYPURGEDATAS.