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) ;
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.
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
"CREATETEMPLATE
file_name
ALTFILECHAR
num_chars
USEALTKEY
(key_specifier
)See "Specifying Alternate Keys".
UNMAPPEDALTFILECREATES {ALTFILEVOL | PRIMARYVOL}
See "Replicating File Create Operations for Alternate Key Files"
HANDLECOLLISIONS | NOHANDLECOLLISIONS
DETECTLOCKS
See "Locking Records".
REPERROR
(error_number
, response
)See "Using REPERROR".
EXCEPTIONSONLY
MAPEXCEPTION
(TARGET
exception_name
, mapping_arguments
)GETNETWORKALTFILENAMES | IGNORENETWORKALTFILENAMES
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".
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.
Table 2-33 Permissible WHERE Operators
Operator | Example |
---|---|
Column names |
PRODUCT_AMT |
Numeric values |
-123, 5500.123 |
Literal strings enclosed in quotes |
"AUTO", "Ca" |
Column tests |
|
Comparison operators |
=, <>, >, <, >=, <= |
Conjunctive operators |
AND, OR |
Grouping parentheses |
Use open and close parentheses for logical grouping of multiple elements. |
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);
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.
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.
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"
Examples
USEALTKEY "TS"
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.
Note:
If you have provided a MAP
for the alternate keys, you will not require this option.
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.
Note:
Use of DETECTLOCKS
could cause an increase in the number of error 73s reported. There can be a lag between when TM/MP tells the application that the transaction has been committed and when DP2 actually releases the locks. Without the DETECTLOCKS
parameter set, Oracle GoldenGate waits for DP2 to release its locks before continuing. With the DETECTLOCKS
parameter active, Oracle GoldenGate returns an error 73 when it is first encountered. You can use REPERROR 73
to address these errors when they occur. Before implementing DETECTLOCKS
review your processing needs to determine the best solution.
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.
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);
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);
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;
Note:
The source and target file names in the preceding MAP
statement (the one the EXCEPTIONSONLY
applies to) cannot include wildcards.
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);
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.
Note:
This feature is not available for Enscribe files. SQLEXEC
queries should not be used to change a value in the primary key column. The primary key value is passed from Extract to Replicat so Replicat can perform further update/delete operations. If Replicat does not know the primary key value, these operations cannot be completed.
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.
Note:
If a SQLEXEC
query fails, the Extract or Replicat process will exit. As such, you must structure your query correctly.
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:
ALLPARAMS
option
Takes one of the following arguments:
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.
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);
Examples
The following example writes to the report and executes a TACLCMD
to rename the file: