Valid for

Replicat

Description

Use MAP to deliver records from a source to the target. Normally, the source is an Oracle GoldenGate trail containing records that were processed by Extract. The MAP parameter is similar to Extract's FILE and TABLE parameters in its mapping capabilities and functionality for executing user exits and stored procedures.

At least one MAP statement is required.

You can invoke MAP more than once in a parameter file, and you can invoke the same MAP argument more than once. This is useful, for example, to split records into different trails to be replicated to different targets.

Syntax

MAP source_file_name, TARGET target_file_name
[, EXCEPTIONSONLY]
[, DEF source_ddl_definition]
[, DICTIONARY source_ddl_dictionary]
[, EXITPARAM "exitparam_string"]
[, WHERE (where_condition)]
[, FILTER (expression)]
[, RANGE (range_specification)]
[, COLMAP ([USEDEFAULTS], column_map_specification)]
[, COMPENSCRIBEMAPS | NOCOMPENSCRIBEMAPS]
[, INSERTALLRECORDS]
[, MAPID]
[, PARTIALCOLSOK | NOPARTIALCOLSOK]
[, SQLNAME]
[, USESOURCERECLENGTH]
[, TARGETDEF target_ddl_definition]
[, TARGETDICT target_ddl_dictionary]
[, KEYCOLS (column_list)]
[, USEALTKEY (key_specifier)]
[, UNMAPPEDALTFILECREATES {ALTFILEVOL | PRIMARYVOL}]
[, CREATETEMPLATE file_name]
[, ALTFILECHAR num_chars]
[, GETNETWORKALTFILENAMES | IGNORENETWORKALTFILENAMES]
[, HANDLECOLLISIONS | NOHANDLECOLLISIONS]
[, DETECTLOCKS]
[, REPERROR (error_number, response)]
[, PARTMAP (source_partition_spec, target_partition_spec)]
[, MAPEXCEPTION (TARGET exception_name, mapping_arguments)]
[, SHOWSYNTAX]
[, SQLEXEC (sqlexec_clause)] 
[, EVENTACTIONS (action_options)
[, TRACETLFTOKENS]
[, ALTNAME alternate_file_name]
[, B24MULTINETREMOTEFLAG]
[, CREATEUSINGTARGETUSERID (group_number,user_number)]
;

source_file_name

The origin of the record to deliver. Most often, this is the name of the file, table or SQL view from which the record was originally extracted. If Extract performed column mapping on the associated records, however, a different file identifier is attached to reflect the new column structure.

source_file_name can also be an existing define name of CLASS MAP, or a wildcard specification.

source_file_name can also be an OpenSys two or three part ANSI name. If the name contains any mixed case or special characters, then the section of this must be quoted.

MAP "MX3cat".SCHEMA."Has$Dollar", TARGET $DATA01.XOUT.TCUSTMER;
MAP "Schema Space".TABLEA, TARGET $DATA01.XOUT.TABLEA;

TARGET target_file_name

The name of the target file or table.

DEF source_ddl_definition

DICTIONARY source_ddl_dictionary

FILTER (expression)

RANGE (range_specification)

WHERE (where_condition)

See "Selecting Records".

COLMAP ([USEDEFAULTS], column_map_specification)

COMPENSCRIBEMAPS | NOCOMPENSCRIBEMAPS

INSERTALLRECORDS

MAPID

PARTIALCOLSOK | NOPARTIALCOLSOK

SQLNAME

TARGETDEF target_ddl_definition

TARGETDICT target_ddl_dictionary

USESOURCERECLENGTH

See "Mapping Data".

KEYCOLS (column_list)

See "Defining Primary Key Columns".

EXITPARAM "exitparam_string"

See "Passing Literal Strings to User Exits".

CREATETEMPLATE file_name

ALTFILECHAR num_chars

See "Creating a Target Enscribe File".

USEALTKEY (key_specifier)

See "Specifying Alternate Keys".

UNMAPPEDALTFILECREATES {ALTFILEVOL | PRIMARYVOL}

See "Replicating File Create Operations for Alternate Key Files"

HANDLECOLLISIONS | NOHANDLECOLLISIONS

See "Turning Error Handling On and Off"

DETECTLOCKS

See "Locking Records".

REPERROR (error_number, response)

See "Using REPERROR".

EXCEPTIONSONLY

MAPEXCEPTION (TARGET exception_name, mapping_arguments)

See "Creating an Exceptions Statement".

GETNETWORKALTFILENAMES | IGNORENETWORKALTFILENAMES

See "Qualifying Alternate Key File Names".

PARTMAP (source_partition_spec, target_partition_spec)

Use PARTMAP to specify alternative mapping of partitions during file creation operations. For details see the PARTMAP parameter on "PARTMAP"

SHOWSYNTAX

See "Displaying a SQL Statement".

SQLEXEC (sqlexec_clause)

See "Performing a Query".

EVENTACTIONS (action_options)

See "Triggering Actions".

[TRACETLFTOKENS]

Use TRACETLFTOKENS to show and debug token parsing when combined with the column function @GETTLFTOKEN().

Use TRACETLFTOKENS only when validating and testing the detokenizing of TLF/PTLF records using @GETTLFTOKEN().

ALTNAME

See "Handling missing files".

B24MULTINETREMOTEFLAG

Use B24MULTINETREMOTEFLAG to set the remote flag in the TLF and PTLF multi-network token BK. Replicat either updates the token if found or add the token if not found. If no tokens are found, then add both the BK token and the Header token. Using this parameter removes the requirement of using the D24 User Exit for this usage.

CREATEUSINGTARGETUSERID (group_number, user_number)

See "Creating a File"

Selecting Records

You can select records by:

• Selecting or excluding records using FILTER.

• Selecting based on a conditional statement using WHERE.

• Selecting a subset or records using RANGE.

  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

Use FILTER expressions to select or exclude data based on a numeric value. You can use a filter expression with conditional operators (such as @IF), column-conversion functions, or both. When using a FILTER expression, you can apply the filter clause to only certain record types, or specify one or more record types to omit.

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

Syntax

MAP source_file_name, TARGET target_file_name, 
FILTER (filter_clause 
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
[, RAISEERROR error_number]
)
[, DEF source_ddl_definition]
[, DICTIONARY source_ddl_dictionary]
;

filter_clause

Selects records from a source MAP based on an expression.

ON INSERT | ON UPDATE| ON DELETE

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

IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE

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

RAISEERROR error_number

Raises a user-defined error number if the filter fails. Can be used as input to the REPERROR parameter to invoke error handling. Make certain that the value for error_number is outside the range of error numbers that is used by the database or Oracle GoldenGate. For example: RAISEERROR 21000

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 audit trails. You cannot specify more than one definition for any FILE statement.

DICTIONARY source_ddl_dictionary

Points to the location of the source DDL dictionary. DICTIONARY establishes an Enscribe DDL dictionary to use for evaluating WHERE, FILTER, and COLMAP clauses. For example, DICTIONARY $DATA5.PRODDICT specifies a physical subvolume.

Each DICTIONARY entry closes any previously open dictionary, so only one dictionary is active at any given time while processing the startup parameters.

Example

The following example inserts the state CA if the column string matches "CA".

MAP $PROD1.CUST.BRANCH, TARGET $DATA01.C9701.BRANCH, 
DEF BRANCH-REC,                                      
FILTER (@IF(@STREQ(STATE, "CA"), 1, 0), ON INSERT);

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 and DICTIONARY keywords.

Syntax

MAP source_file_name, TARGET target_file_name, 
WHERE (where_condition)
[, DEF source_ddl_definition]
[, DICTIONARY source_ddl_dictionary]
;

where_condition

Selects a subset of records from a source MAP, based on a condition, such as WHERE (BRANCH = "NY"). For a list of valid operators, see Table 2-33.

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 audit trails. You cannot specify more than one definition for any FILE statement.

DICTIONARY source_ddl_dictionary

Points to the location of the source DDL dictionary. DICTIONARY establishes an Enscribe DDL dictionary to use for evaluating WHERE, FILTER, and COLMAP clauses. For example, DICTIONARY $DATA5.PRODDICT specifies a physical subvolume.

Each DICTIONARY entry closes any previously open dictionary, so only one dictionary is active at any given time while processing the startup parameters.

PRODUCT_AMT

-123, 5500.123

 "AUTO", "Ca"

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

AND, OR

Selecting a Subset of Records

Use the RANGE clause to select a subset of the records from Replicat's source. Unlike WHERE, RANGE does not require knowledge of the source file's structure.

Syntax

MAP source_file_name, TARGET target_file_name,
RANGE (x [, x, ...] of y);

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

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

Mapping Data

Oracle GoldenGate has the following data mapping capability for the MAP parameters:

• Mapping columns.

• Matching source and target record lengths.

If no explicit column mapping is specified with COLMAP, Replicat determines the column map using the following rules:

• Columns with the same name are mapped to each other if the data types of the source and target are compatible.

• If the target definition has column names corresponding to special transaction values those values are mapped to the target columns.

• Column names are changed to uppercase for name comparison.

• Global rules set up with COLMATCH parameters enable different column names to be mapped by default.

• Target columns that do not correspond to any source column take default values determined by the database.

The default mapping is displayed in the report file just after the corresponding MAP entry.

Mapping Columns

Using a COLMAP clause, you can retrieve fields or columns from one record and map them to a differently structured record. This is useful, for example, when replicating 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 and DICTIONARY keywords, otherwise the source and target structures are assumed to be identical. DEF and DICTIONARY are required only once in the parameter file, and only when using a COLMAP clause.

  When Enscribe files without corresponding DEF parameters are encountered, source and target structures are assumed to be identical.

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

Syntax

MAP source_file_name, TARGET target_file_name,
COLMAP ([USEDEFAULTS], column_map_specification)
[, option];

column_map_specification

The column mapping expression, as in

(target_column = source_expression)

Explicitly defines a source-target column map.

target_column

The name of the target column.

source_expression

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)

Example:

COLMAP (USEDEFAULTS, 
targcol1 = srccol1,  
targcol2 = srccol2,  
targcol3 = srccol3) 

TARGET target_file_name

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

option

A DDL definition obtained by the following:

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 audit trails. You cannot specify more than one definition for any MAP statement.

NOCOLMAP

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

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

DICTIONARY source_ddl_dictionary

Points to the location of the source DDL dictionary. DICTIONARY establishes an Enscribe DDL dictionary to use for evaluating WHERE, FILTER, and COLMAP clauses. For example, DICTIONARY $DATA5.PRODDICT specifies a physical subvolume.

Each DICTIONARY entry closes any previously open dictionary, so only one dictionary is active at any given time while processing the startup parameters.

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.

TARGETDICT target_ddl_dictionary

Points to the target DDL dictionary. Use in conjunction with TARGETDEF when the target definitions are in a DDL dictionary different from DEF. Follows the TARGETDEF targetdef entry, similar to:

MAP $vol.subvol.source, DEF sourcedef, TARGET $vol.subvol.target, 
TARGETDEF targetdef, TARGETDICT $vol.subvol,
COLMAP (USEDEFAULTS
targcol1 = srccol1, 
targcol2 = srccol2, 
targcol3 = srccol3);

USEDEFAULTS

Causes Oracle GoldenGate to automatically map source and target columns that have the same name. USEDEFAULTS eliminates the need to explicitly map every source column unless the target column has a different name. This is the default unless an explicit column mapping is used.

Matching Source and Target Record Lengths

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

Syntax

USESOURCERECLENGTH

Example

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

Compressing Enscribe Records

Use COMPENSCRIBEMAPS to compress an update record after column mapping on an Enscribe target. Compressing ensures that updates to an Enscribe file after column mapping can only update the fields that were changed.

Default

COMPENSCRIBEMAPS

Syntax

MAP source_file_name, TARGET target_file_name 
{, COMPENSCRIBEMAPS | NOCOMPENSCRIBEMAPS};

When Replicat is compressing an update record and the size of the column exceeds the amount of available data, a compressed fragment is produced for the available partial data. Use NOPARTIALCOLSOK to not compress an update fragment.

Default

PARTIALCOLSOK (Compress partial column values)

Syntax

MAP source_file_name, TARGET target_file_name, 
DEF sourcedef, TARGETDEF targetdef, NOPARTIALCOLSOK;

Inserting All Records

Use the INSERTALLRECORDS option to apply all record types as inserts for the current map. This will create a record of all operations made to the target record instead of maintaining only the current version. This can be useful when complete transaction history is needed. See "INSERTALLRECORDS | NOINSERTALLRECORDS" for details on INSERTALLRECORDS.

Syntax

MAP source_file_name, TARGET target_file_name,
INSERTALLRECORDS;

Defining Primary Key Columns

Use the KEYCOLS option to define one or more columns of the table as a primary key for use by Oracle GoldenGate. Oracle GoldenGate uses the key to locate rows for updates and deletes and to prevent duplicate inserts.

KEYCOLS also applies to view definitions when a view is used as the file parameter. KEYCOLS has the following dependencies:

• You must use key columns when the primary key cannot be determined, or when a SYSKEY does not exist and either FORMATSQL or FORMATASCII are specified in the parameter file.

• If the same file name is specified in multiple MAP entries, only the first KEYCOLS entry takes effect.

Syntax

MAP source_file_name, TARGET target_file_name, 
KEYCOLS (column [, column] [, ...]);

(column)

Defines a column to be used as a substitute primary key. To specify multiple columns, create a comma-delimited list as in:

KEYCOLS (COL1, COL2);

Example

Consider a relative SQL table R1 with the columns SYSKEY, CUSTOMER, BALANCE and a unique index on the CUSTOMER column. For the following transaction, you will get different results depending on whether you have specified KEYCOLS or FORMATSQL.

UPDATE R1 SET BALANCE = 20 WHERE CUSTOMER = "SMITH"; 

If your FILE parameter statement is simply: FILE R1; and FORMATSQL is included in the parameter file, but no KEYCOLS are specified, the output is similar to:

UPDATE R1 SET BALANCE = 20 WHERE SYSKEY = 1334519;

If instead the FILE entry is: FILE R1, KEYCOLS (CUSTOMER); the output is:

UPDATE R1 SET BALANCE = 20, SYSKEY = 1334519 WHERE CUSTOMER = "SMITH";

Passing Literal Strings to User Exits

Use EXITPARAM to pass a literal string to user exit routines whenever a record from MAP 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

MAP source_file_name, TARGET target_file_name,
EXITPARAM "exitparam_string"

Creating a Target Enscribe File

To deliver to an Enscribe file that does not yet exist, a file is created according to rules in a file template. Using the template file name, a file is created with the same structure, alternate keys and partitions.

The new file substitutes the target file name in the map for the template name (file name only, not subvolume), and uses the partition name, alternate key volumes and subvolumes of the template. The altkeys file names are derived from the new file name: by default, by appending 0, 1, 2 to the file name. If ALTFILECHAR is specified, a 0, 1, 2 is inserted at the indicated character.

Valid values for ALTFILECHAR are 2-8 for the modifiable positions of the name: $VOL.SUBVOL.XX2345678.

CREATETEMPLATE is invoked upon an insert if the file does not exist. It is not invoked on file create operations, so when CREATETEMPLATE is used, file creates should not be captured. The CREATETEMPLATE option is valid for MAP statements that use wildcards or fully qualified file names. It applies only to Enscribe files.

If a file is renamed or removed after OPENTIMEOUT, the next operation on the missing file will trigger creation of a new file if the CREATETEMPLATE parameter is specified. Replicat will recognize that the create time of the target file changed and refresh the file attributes.

Syntax

MAP source_file_name, TARGET target_file_name,
CREATETEMPLATE file_name, ALTFILECHAR num_chars

Example

MAP $DATA2.DAT.TL*,
TARGET $DATA5.DAT.*,
CREATETEMPLATE $DATA3.GGSMASK.TLYYMMDD, 
ALTFILECHAR 2;

Specifying Alternate Keys

Use USEALTKEY when replicating updates to Enscribe entry-sequenced or relative files, since Replicat cannot guarantee that the keys in the source and target will match. If target records can be uniquely identified using an alternate key, USEALTKEY enables Replicat to update the correct record.

Do not use this option when the unique alternate key is updated.

To ensure USEALTKEY works correctly when replicating data, turn off update compression for any entry-sequenced files that depend on this method. Turn off compression using FUP for TMF-audited files and using the Logger configuration for non-TMF files.

Syntax

MAP source_file_name, TARGET target_file_name,
USEALTKEY "key_specifier"

"key_specifier"

The unique key identifier.

Examples

Example 1   

USEALTKEY "TS"

Example 2   

The following parameter file example uses an alternate key for updates to an Enscribe relative file. The first set of statements insert the record ignoring the SYSKEY. The second set uses the alternate key "TM" to locate records for updates and deletes.

IGNOREUPDATES
IGNOREDELETES
GETINSERTS
MAP \NY.$DATA1.PRDDAT.FILEA, 
TARGET \LA.$DATA3.BKDAT.FILEA,
DEF FILEA-REC, TARGETDEF FILEA-REC,
COLMAP (USEDEFAULTS, SYSKEY = -2);
GETUPDATES
GETDELETES
IGNOREINSERTS
MAP \NY.$DATA1.PRDDAT.FILEA, 
TARGET \LA.$DATA3.BKDAT.FILEA,
USEALTKEY "TM";

Replicating File Create Operations for Alternate Key Files

Use UNMAPPEDALTFILECREATES when you want to replicate file create operations for alternate key files, but have not included a MAP statement for the alternate key file. You can create your alternate key file one of two ways: by creating it from the alternate key file on the source system, or by creating it from the location of the primary file's volume.

Syntax

MAP source_file_name, TARGET target_file_name,
UNMAPPEDALTFILECREATES {ALTFILEVOL | PRIMARYVOL}

ALTFILEVOL

Creates the alternate key file based on the location of the source system's alternate keys file. If this is not possible, the process abends with the -2 mapping error.

PRIMARYVOL

Attempts to create the alternate key file based on the location of the source system's alternate key file. If this is not possible, it creates an alternate key file based on the location of the primary file's volume.

Example

The following example will create an alternate key file based on the location of the file's primary volume if the source system alternate key file cannot be located.

MAP $DATA02.TSSOUT.ALTXX*, TARGET $DATA4.TSSIN.*, UNMAPPEDALTFILECREATES PRIMARYVOL;

Turning Error Handling On and Off

HANDLECOLLISIONS can be set to ignore duplicate and missing record errors for the MAP statement. NOHANDLECOLLISIONS will turn this off.

Syntax

MAP source_file_name, TARGET target_file_name,
HANDLECOLLISIONS;

Example

The following example will turn HANDLECOLLISIONS on for all of the target files included in the ORD* wildcard and off for CUSTOMERS.

MAP $DATA2.GGSSOU.ORD*, TARGET $DATA4.GGSTAR.*,
HANDLECOLLISIONS;
MAP $DATA2.GGSOUT.CUSTOMERS, target $DATA4.GGSIN.*,
NOHANDLECOLLISIONS;

Locking Records

For Enscribe files, DETECTLOCKS causes Replicat to issue a SETMODE 4 to reject a lock request with an error 73. For SQL tables, it causes Replicat to issue the "CONTROL TABLE RETURN IF LOCKED" statement.

Setting a REPERROR clause allows Replicat to retry a locked record a specified number of times.

Syntax

MAP source_file_name, TARGET target_file_name, DETECTLOCKS

Using REPERROR

Use REPERROR to specify an error and a response that together control how Replicat responds to the error when executing the MAP statement. You can use REPERROR at the MAP level to override and supplement global error handling rules set with the REPERROR parameter (see "REPERROR"). Multiple REPERROR statements can be applied to the same MAP statement to enable automatic, comprehensive management of errors and interruption-free replication processing.

Syntax

MAP source_file_name, TARGET target_file_name, 
REPERROR (error_number, response) [, REPERROR (error_number, response)] [, ...];

Refer to the REPERROR parameter on "REPERROR" for details on the error_number and response specifications. Note that RESET is not a valid option for REPERROR used under MAP.

Examples

The following examples show different ways that REPERROR can be used in a MAP statement in conjunction with a global REPERROR statement.

Example 1   

In the following example, when error_1 occurs for the first MAP statement, the action is response_2, not response_1, because an override was specified. However, if an error_1 occurs for the second MAP statement, the response is response_1, the global response. The response for error_2 is response_3, which is MAP-specific.

REPLICAT group_name
REPERROR (error_1, response_1)
MAP source_1, TARGET target_1, REPERROR (error_1, response_2);
MAP source_2, TARGET target_2, REPERROR (error_2, response_3);

Example 2   

In the following example, when replicating from src1 to src2, all errors and actions (1-3) apply, because all REPERROR statements address different errors (there are no MAP-specific overrides).

REPLICAT group_name
REPERROR (error_1, response_1)
MAP source_1, TARGET target_1, REPERROR (error_2, response_2),
REPERROR (error_3, response_3);

Example 3   

In the following example, if error1 occurs for the first MAP statement, the action is response2. For the second one it is response1 (the global response), and for the third one it is response4 (because of the second REPERROR statement). A global REPERROR statement applies to all MAP statements that follow it in the parameter file until another REPERROR statement starts new rules.

REPLICAT group_name
REPERROR (error_1, response_1)
MAP source_1, TARGET target_1, REPERROR (error_1, response_2);
MAP source_2, TARGET target_2, REPERROR (error_2, response_3);
REPERROR (error_1, response_4)
MAP source_2, TARGET target_2, REPERROR (error_3, response_3);

Creating an Exceptions Statement

Errors that have REPERROR set to EXCEPTION can be captured to an exception file using either EXCEPTIONSONLY or MAPEXCEPTION.

Using EXCEPTIONSONLY

EXCEPTIONSONLY specifies that the current map is executed only when an error occurred for the last record processed in the preceding map. You must set REPERROR to EXCEPTION for the error that occurred, and the exception map must specify the same source table as the map in error. The exception map must follow the map in error in the parameter file.

Syntax

MAP source_file_name, TARGET target_file_name,
EXCEPTIONSONLY;

Using MAPEXCEPTION

MAPEXCEPTION specifies a target file for exceptions in processing the source and target files of the MAP. The source and target file names can include wildcards and all exceptions that apply to the file set are written to the exception_file_name. Any valid mapping arguments, such as COLMAP or KEYCOL, can be included.

Syntax

MAP source_file_name, TARGET target_file_name,
MAPEXCEPTION (TARGET exception_file_name, 
mapping_arguments);

exception_file_name

Identifies the target file for errors that have been identified as EXCEPTION with REPERROR.

mapping_arguments

Any valid mapping argument that can be used with the MAP statement.

Example

This example uses wildcarded source and target file names. Exceptions that occur when processing any file on $DATA02.ACCT with a name beginning with TRX will be captured to $DATA08.ACCT.OLDTRX using the designated mapping.

MAP $DATA04.ACCT.TRX*, TARGET $DATA05.ACCT.*,
MAPEXCEPTION (TARGET $DATA08.ACCT.OLDTRX,
   COLMAP (USEDEFAULTS,
   ACCT-NO = ACCT-NO,
   OPTYPE = @GETENV ("LASTERR", "OPTYPE"),
   DBERR = @GETENV ("LASTERR", "DBERRNUM"),
   DBERRMSG = @GETENV ("LASTERR", "DBERRMSG")
   )
);

Qualifying Alternate Key File Names

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. This parameter can also be toggled around MAP statements.

Default

GETNETWORKALTFILENAMES

Syntax

MAP source_file_name, TARGET target_file_name, 
[GETNETWORKFILENAMES | IGNORENETWORKALTFILENAMES];

Displaying a SQL Statement

Use SHOWSYNTAX to display a SQL statement before it is executed. The default is to display SQL statement text in the report file.

Syntax

MAP source_file_name, TARGET target_file_name, SHOWSYNTAX;

Example

REPSQLLOG $DATA.SQLLOG.JSR01
MAP $DATA.SOURCE.ACCOUNT, TARGET $DATA.TARGET.ACCOUNT,
SHOWSYNTAX,
MAPID "My Mapid",
COLMAP (USEDEFAULTS);

The MAPID is displayed along with the SQL statement. Use the MAPID option to help in complicated mapping situations that require conditional mapping using a WHERE clause. If MAPID is not specified, a default ID of the form "MAP" is built with a sequential number indicating the COLMAP for the file. The MAPID text can be up to 32 bytes long. If it contains spaces, it must be enclosed in either single or double quotes.

An optional Replicat parameter REPSQLLOG redirects the output to a separate log file and keeps it out of the Replicat report file. See "REPSQLLOG".

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.

MAP $DATA1.SQLDAT.ORDERS, TARGET $DATA1.MASTER.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

MAP source_file_name, TARGET target_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]
[, option] [, ...]
)

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.

Creating a File

Use CREATEUSINGTARGETUSERID to have Replicat create a file using this ID instead of either the process owner of Replicat or the original source.

Syntax

CREATEUSINGTARGETUSERID (group_number, user_number)

Example

CREATEUSINGTARGETUSERID (100, 20);

Triggering Actions

EVENTACTIONS can be used to trigger an event based on a file or table receiving a DML record. The event actions will take place after mapping and either before or after the record is processed depending on the action.

Syntax

MAP source_file, TARGET target_file
EVENTACTIONS ([VERBOSE]
  [, IGNORE | DISCARD]
  [, TACLCMD ("CMD_file_details") | TACLCMD (CMD $1, VAR $1 = value)]
  [, EMS WARN | INFO]
  [, CHECKPOINT {BEFORE | AFTER | BOTH}]
  [, REPORT]
  [, STOP]
  [, SUSPEND])

VERBOSE

Writes details to the report for each event as it is processed

IGNORE

Skips the record. IGNORE and DISCARD are incompatible, so only one of these options can be used.

DISCARD

Discards the record. IGNORE and DISCARD are incompatible, so only one of these options can be used.

TACLCMD

Executes a user-defined system command. Valid TACLCMD file commands are PURGE, PURGEDATA, RUN, RENAME, OBEY, and FUP. Non-file values can be obtained by SQLEXEC, @GETENV(), or Column Data.

EMS

Writes either INFO or WARN messages to EMS.

CHECKPOINT

Checkpoints its position BEFORE the record is processed, AFTER the record is processed or BOTH.

REPORT

Writes the current statistics to the report file.

STOP

Stops the process.

SUSPEND

Suspends the process until it is resumed by a command from GGSCI.

MAP $DATA.SLS.PLR, TARGET $DATA3.SLS.PLA
  EVENTACTIONS (VERBOSE, TACLCMD "RENAME $DATA.SLS.PLR, $DATA.SLS.T4M")

The following example sets VAR options using parameters from SQLEXEC:

EXTRACT EXMPAB
SETENV FETCHTBL=$DATA02.TOJ06n.ENVENTAB
MAP$data02.TOJOUT.EMARKER, TARGET $data03.TOJOUT.EMARAKER, 
  COLMAP (COL1 = COL1, SYSKEY=SYSKEY);SQLEXEC (ID getprog,
  QUERY "SELECT * FROM ?FETCHTBL"
  WHERE "table_name =?T1",
PARAMS(T1=@GETEMV("GGHEADER","TABLENAME")), ERROR REORT),
EVENTACTIONS (VERBOSE,EMS WARN,REPORT,CHECKPOINT BEFORE,
  TACLCMD("RUN $1/CPU $3,PRI $4, NAME $5/-f$2",
    var $1=GETPROG.program_name,
    var $2=GETPROG.file_name,
    var $3=GETPROG.cpu,
    var $4=GETPROG.pri,
    var $5=GETPROG.process),
  DISCARD);