Introduction
Oracle GoldenGate (OGG) Macros provide functionality for sharing parameters or other run-time configuration settings across multiple components and externalizing complex configuration settings in order to streamline parameter file contents. In this article we shall discuss how to create a Macro Library and access specific Macros via the OGG Group parameter file.
Main Article
A Macro Library is a collection of OGG Macros used to externalize OGG parameters shared across multiple Groups. The library can be a single file containing multiple Macro definitions, or multiple files. Best practice is to create a directory “dirmac” as part of the OGG installation environment to hold the library files. Another best practice is to use the suffix “.mac” on all library files. This way Macro Library files can be recognized without having to open and read one.
Using these best practice tips, the following Macro Library would be stored inside of the file “$OGG_BASE/dirmac/macrolib.mac”.
MACRO #dbconnect
BEGIN
userid gguser, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
END;
MACRO #bpsettings
BEGIN
-- The following are "best practice" runtime options which may be
-- used for workload accounting and load balancing purposes.
-- STATOPTIONS RESETREPORTSTATS ensures that process
-- statistics counters will be reset whenever a new report file is
-- created.
STATOPTIONS RESETREPORTSTATS
-- Generate a report every day at 1 minute after midnight.
-- This report will contain the number of operations, by operation
-- type, performed on each table.
REPORT AT 00:01
-- Close the current report file and create a new one daily at 1
-- minute after midnight. Eleven report files are maintained on disk
-- in the dirrpt directory/folder for each GoldenGate group. The
-- current report file names are <group name>.rpt. The older reports
-- are <group name>0.rpt through <group name>9.rpt, with the
-- older report files having larger numbers.
REPORTROLLOVER AT 00:01
-- REPORTCOUNT denotes that every 60 seconds the Replicat report file
-- in the dirrpt directory/folder will have a line added to it that reports the
-- total number of records processed since startup, along with the rated
-- number of records processed per second since startup, and the change
-- in rate, or "delta" since the last report.
-- In a production environment, this setting would typically be 1 hour.
REPORTCOUNT EVERY 60 SECONDS, RATE
-- End of "best practices" section
END;
MACRO #funcsmap
PARAMS (#src_table, #target_table)
BEGIN
-- Map the source table provided in the variable #src_table to the target
-- table listed in the variable #target_table. There are extra columns in the
-- target we need to populate, so get the data from either the environment
-- variable, or the user token data sent over from Extract
MAP #src_table, TARGET #target_table,
colmap (usedefaults,
orders_trans_ts = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
trans_rec_loc8tr = @STRCAT (@GETENV ("RECORD", "FILESEQNO"),
@GETENV ("RECORD", "FILERBA")),
extract_lag_ms = @TOKEN ("TKN-EXTLAG-MSEC"),
replicat_lag_ms = @GETENV ("LAG", "MSEC"),
src_db_name = @TOKEN ("TKN-SRC-DBNAME"),
src_db_version = @TOKEN ("TKN-SRC-DBVERSION"),
src_txn_csn = @TOKEN ("TKN-TXN-CSN")
);
END;
Macros are identified by the keyword “Macro” followed by the macro name “#<name>”. The Macro body is contained within the “BEGIN” and “END” statements. The three Macros provided in this example library file are #dbconnect, #bpsettings, and #funcsmap.
- The #dbconnect Macro provides a centralized location for storing database connection information. The database login password is Blowfish encrypted using the “DEFAULT” GoldenGate encryption key.
- #bpsettings activates best practices settings for generating hourly and daily activity counts.
- #funcsmap accepts input parameters and uses them to build a Replicat map statement.
Using the Macro Library
Consider the following Replicat parameter file:
nolist include ./dirmac/macrolib.mac list replicat rfuncb #dbconnect () #bpsettings () sourcedefs ./dirdef/mydefs.defs discardfile ./dirrpt/rfuncb.dsc, purge #funcsmap (amer.orders, euro.funcs_test)
The include statement loads the Macro Library file ./dirmac/macrolib.mac when the Replicat RFUNCB is started. The parameter nolist tells the Replicat process to not write any of the following text into its report file; while list restores normal output listing.
If list is not included in the parameter file after using nolist, no information will be written to the Group report file.
As the Replicat process reads its parameter file during the startup process, text substitution will occur for each Macro specified. So, the text “#dbconnect ()” will be replaced with the text from the Macro #dbconnect in the Macro Library file. The same will occur for “#bpsettings ()” and “#funcsmap (amer.orders, euro.funcs_test)”.
You may have noticed that the call to the Macro #funcsmap is a bit different from the other two in that it has two arguments. Because the Macro builds the Replicat MAP statement, it requires source and target table definitions. The PARAMS option is used to define input arguments for the Macro. The following applies when PARAMS is specified:
- Parameter names are not case-sensitive.
- Do not use quotes, or else the parameter name will be treated as text and ignored.
- A parameters clause is optional.
- The maximum size of a PARAMS statement is 9999 bytes and can contain no more than 99 parameters.
- Every parameter used in a macro must be declared in the PARAMS statement, and when the macro is invoked, the invocation must include a value for each parameter.
Summary
Oracle GoldenGate (OGG) Macros can be an important trick in your toolkit for sharing parameters or other run-time configuration settings across multiple components and externalizing complex configuration settings in order to streamline parameter file contents.
No comments:
Post a Comment