Configuring Custom Operations

You can use the custom operations to manipulate Oracle GoldenGate to your specific organization requirements.

You can write C or COBOL routines and call them with Oracle GoldenGate user exits. You can also save frequently used Oracle GoldenGate routines as macros then call the macros from within Extract or Replicat parameter files. You can use OBEY files to access frequently used Oracle GoldenGate parameters.

This topic includes the following:

User Exits

User exits allow you to extend and customize the functionality of Extract and Replicat. At different points during Extract and Replicat processing, you can call COBOL, C or TAL routines to perform an unlimited number of functions. You can also easily add functions to the application and respond to database events almost as soon as they occur without altering production programs. For example, user exits can:

Record Formats for User Exits

User exits expect records to have a specific format. For example, user exits expect:

(offset)(length)(value)(offset)(length)(value)(. . .)

where

Creating User Exits

Create user by performing the following process.

To implement user exits:

  1. Create a user exit shell routine in C, TAL or COBOL. The user shell routine is the communication point between Extract or Replicat and your routines.

    • Cshell routines.Shell routines written in C must be named CUSEREXIT and must accept the EXIT-CALL-TYPE, EXIT-CALL-RESULT, EXIT-PARAMS, and EXIT-REC-BUF parameters. These parameters are supplied by Oracle GoldenGate in the XLIBC include file.

    • COBOLshell routines. Shell routines written in COBOL must specify the ENV COMMON directive, and the PROGRAM-ID of one of the modules must be named COBOLUSEREXIT. The COBOLUSEREXIT program must have a linkage section that contains EXIT-CALL-TYPE, EXIT-CALL-RESULT, EXIT-PARAMS, and EXIT-REC-BUF parameters. These parameters are supplied by Oracle GoldenGate in the XLIBCOB copy library.

    • TALshell routines.Shell routines written in TAL must be named TALUSEREXIT and must accept the EXIT-CALL-TYPE, EXIT-CALL-RESULT, EXIT-PARAMS, and EXIT-REC-BUF parameters. These parameters are supplied by Oracle GoldenGate in the XLIBTAL include file.

    See COBOLUSEREXIT for details about COBOL and C programming language routines.

  2. Include Calling Environment Functions to retrieve information such as record buffers and transaction contexts, if necessary. If the user exit is written in C, you must include the USRDECS file. If the exit is written in COBOL, you must furnish a CONSULT directive to either Extract or Replicat. If the exit is written in TAL, you must source the USRDECT file.

  3. In any language, create routines to respond to each type of event generated by Extract and Replicat.

  4. Compile and bind the shell routine and the routines that respond to individual events, creating the user exit module.

  5. Link the user exit module with Extract or Replicat by running the NLDEXIT macro and creating a custom Extract or Replicat module with a different name.

  6. Include the CUSEREXIT, COBOLUSEREXIT or TALUSEREXIT parameter in your Extract or Replicat parameter file.

  7. Run the custom Extract or Replicat module.

Linking User Exits

Run NLDEXIT to link your user exit to the Extract or Replicat.

TACL >RUN *`$vol.subvol`*.NLDEXIT
[*`options`*] [*`object_type`*]
Options Description

| — | — |

USEROBJ
 The name of the user exit object file. 
NEWOBJ
 The name of the new object file that will contain the exit routines and the Extract or Replicat module. The file must not yet exist, and will be created in the same subvolume as the Extract/Replicat module. 
GGSUBVOL
 The fully defined location of the Oracle GoldenGate environment. 
CATALOG
 The SQL Catalog for the SQLCOMP of the program. This information is not required if your database is Enscribe. 
C++VERSION
 If there were any C++ modules used, the version of the C++ compiler; 2 or 3. 
CEXITWITHCOBOL
 Y or N to indicate whether any COBOL modules were used. 
SHOWCMD
 Displays additional information on the NLDEXIT commands during the session. 
HELP
 Display NLDEXIT Help text.

Example 7-1 Some Additional Information Displayed with NLDEXIT and SHOWCMD

-o $DATA1.GGSSRC.TESTREP
$DATA2.TSPAK.XSKLCON $DATA2.TEST.REPR
$DATA2.TSSOBJ.USRESQL
-nostdfiles
-allow_duplicate_procs
-set runnamed on
-set highpin on
-set highrequesters on
-set saveabend on
-set libname $DATA1.GGSSRC.PRIVLIB
$system.system.crtlmain
-obey $system.system.libcobey
NLD - NATIVE MODE LINKER - T6017D45. . .
(C)1993 Tandem (C)2004 Hewlett-Packard Development Company, L.P.
NLD's command line was:
     \LA.$system.system.nld -stdin
**** INFORMATIONAL MESSAGE **** [20022]:
     The SRL name or archive name specified as 'zcresrl' in a -l, -lib, or
     -import flag was resolved to the SRL named
     '\LA.$SYSTEM.SYS04.zcresrl'.
.
.
. (11 informational messages omitted from this sample)
NLD reported 0 errors.
NLD reported 0 warnings.
NLD reported 12 informational messages.
NLD created the following type of object file:
     \TRILL.$DATA1.GGSSRC.TESTREP (ELF, executable)
NLD Timestamp:  15DEC2010 15:07:30
Elapsed Time:   00:00:06

The following example creates a new native user exit in Extract

Example 7-2 Creating a New Native User Exit

TACL> RUN $*`vol.subvol.`*NLDEXIT
Creates a new Native Extract or Replicat object file linked with a USEREXIT module.
Enter X at any prompt to quit.

Enter type of GGS object to create Extract or Replicat:
GGS Object type: *`extract_name`*
Enter $Vol.Subvol for Extract relinkable *`installation_location`*
Enter location of userexit object: *`your_native_compiled_C_object`*
Enter name for new object file: *`new_native_extract`*

Does your C User Exit contain C++ modules (Y/N): *`Y`*
What version compiler was used for C++ (2/3): *`number`*
Does your C User Exit contain Cobol modules (Y/N)? *`Y`*
New Extract file $*`vol.subvol.`**`extractname`*.*`filename`* created with user exits.

SQL Catalog for SQLCOMP (or N to avoid SQL compile): *`SQL_catalog_subvol`*

Sample User Exits

Two sample user exits are supplied with Oracle GoldenGate: DEMOXCOB (written in COBOL) and DEMOXC (written in C programming language). You can use these exits as skeletons for your own routines.

DEMOXCOB illustrates several applications of user exits. DEMOXCOB responds to Extract events and performs several tasks, including:

DEMOXC provides an example of how to write a user exit that responds to Replicat events. DEMOXC maps records from a source to a target layout and creates a summary transaction record for each delivered transaction.

Using Oracle GoldenGate Macros

By using Oracle GoldenGate macros in parameter files you can easily configure and reuse parameters, commands, and functions. You can use macros for a variety of operations, including:

Oracle GoldenGate macros work with Extract and Replicat parameter files.

Creating a Macro

Create an Oracle GoldenGate macro with the MACRO statement.

MACRO #*`macro_name`*
PARAMS ([*`param1]`* [*`, param2`*] [...])
BEGIN
*`macro_body`*

END;
Argument Description

| — | — |

MACRO #macro_name

Defines an Oracle GoldenGate macro. macro_name must begin with the # character, as in #macro1.

If the # macro character is used elsewhere in the parameter file, such as in a table name, you can change it to something else with the MACROCHAR parameter. See "Changing the Macro Character" for more information. Macro names are not case-sensitive. 

PARAMS ([param1] [, param2] [...])
 Optional. Used to describe parameters to the macro. Each parameter used in the macro must be declared in the PARAMS statement. See “Creating Macro Parameters” for details about this option. 
BEGIN
 Indicates the beginning of the body of the macro. Must be specified before the macro body. 
macro_body

Represents one or more statements to be used as parameter file input. macro_body can include simple parameter statements, such as 

COL1 = COL2

or more complex statements that include parameters, such as

COL1 = #val2

In addition, macro_body may include invocations of other macros. For example: 

#colmap(COL1, #sourcecol)
 
END;
 Ends the macro definition.

Creating Macro Parameters

When you specify the optional PARAMS statement in a macro, the macro processor reads through the macro body looking for instances of the parameter names you defined in the PARAMS statement. For each occurrence of a parameter name, you must specify a corresponding value, which is substituted for the parameter name during invocation.

For example, to convert a proprietary date format, the following macro defines the #year, #month, and #day parameters.

MACRO #make_date
PARAMS (#year, #month, #day)
BEGIN
    @DATE("YYYY-MM-DD", "CC", @IF(#year < 50, 20, 19),
        "YY", #year, "MM", #month, "DD", #day)
END;

Parameter values are substituted within the macro body according to the following rules.

  1. Parameter names must begin with the # macro character, such as #param1. (You can define a different macro character. See “Changing the Macro Character” for details.) When the macro is run, the invocation must include a parameter value for each parameter named in the PARAMS statement. Parameter names are not case-sensitive.

    When the macro processor encounters a parameter with the # prefix that is not in the parameter list, the processor determines whether it is an invocation of another macro. Invocations of other macros also begin with the # character, followed by parentheses enclosing a list of parameter values that are separated by commas.

  2. Besides the leading # character, valid parameter characters are alphanumeric and can include the underscore character (_).

  3. If a parameter name or macro is encountered within quotation marks, it is treated as text and ignored.

Changing the Macro Character

Anything in the parameter file that begins with the # macro character is assumed to be either a macro or macro parameter. This rule does not apply to text within quotation marks; quoted text is ignored.

If the macro character conflicts with a specification in the parameter file, such as table names that include the # character, you specify a different macro character with the MACROCHAR parameter. In the following example, $ is defined as the macro character, rather than #.

MACROCHAR $
MACRO $mymac
PARAMS ($p1)
BEGIN
col = $p1
END;

The MACROCHAR can only be specified once, and must be specified before any macros are defined.

Running the Macro

To run a macro, place the run statement in the parameter file at every place you want the process to occur.

[*`target`* =] *`#macro_name`* ([*`value1`*] [*`, value2`*] [, . . .])
Argument Description

| — | — |

target =

An optional target to which the results of the macro processing are assigned, such as:

DATECOL1 = #make_date(YR1, MO1, DAY1)
 
#macro_name
 The name of the macro, such as #assign_date. 
([value1] [, value2] [, . . .])

The parameter values to be substituted inside the macro, such as #custdate (#year, #month, #day). If the optional PARAMS statement is omitted, the parentheses are still required. See the section on invoking macros without parameters on "Invoking a Macro Without Parameters" for more information.

Valid parameter values include plain text, quoted text, and invocations of other macros. Some examples of valid parameter values are:

my_col_1 "your text here" #mycalc (col2, 100) #custdate (#year, #month, #day) #custdate (#getyyyy (#yy), #month, #day)

Invoking a Macro Without Parameters

If the macro does not specify parameters, the parameter value list is empty, but the parentheses are still required. For example:

#no_params_macro ()

Sample Macros

This section shows you sample macros for implementing multiple uses of a statement and invoking another macro.

Implementing Multiple Uses of a Statement

You can use macros to implement multiple uses of a statement, and eliminate the need for entering one statement several times.

The following example illustrates how mapping can be improved with a macro. In this example, a proprietary date format must be converted and the process is used several times. For such a scenario, you could implement a date format conversion in a macro similar to the following:

MACRO #make_date
PARAMS (#year, #month, #day)
BEGIN
@DATE("YYYY-MM-DD", "CC", @IF(#year < 50, 20, 19),
"YY", #year, "MM", #month, "DD", #day)
END;

To run the macro

  1. Place the run statements at the appropriate location, similar to:
MAP $DATA.PROD.ACCOUNT, TARGET $DATA.BACK.ACCOUNT,
COLMAP (
TARGCOL1 = SOURCECOL1,
DATECOL1 = #make_date(YR1,MO1,DAY1),
DATECOL2 = #make_date(YR2,MO2,DAY2)
);
  1. Upon invocation, the macro expands to:
MAP $DATA.PROD.ACCOUNT, TARGET $DATA.BACK.ACCOUNT,
COLMAP(
TARGCOL1 = SOURCECOL1,
DATECOL1 = @DATE("YYYY-MM-DD", "CC", @IF(YR1 < 50, 20, 19),
"YY", YR1, "MM", MO1, "DD", DAY1)
DATECOL2 = @DATE("YYYY-MM-DD", "CC", @IF(YR2 < 50, 20, 19),
"YY", YR2, "MM", MO2, "DD", DAY2)
);

Consolidating Multiple Commands

In addition, frequently used sets of commands can be specified in a macro, as in this example of the macro #option_defaults.

MACRO #option_defaults
BEGIN
  GETINSERTS
  GETUPDATES
  GETDELETES
  INSERTDELETES
END;

Invoking the macro:

#option_defaults ()
IGNOREUPDATES
MAP $DATA.PROD.TCUSTMER, TARGET $DATA.BACK.TCUSTMER;

expands to:

GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
IGNOREUPDATES
MAP $DATA.PROD.TCUSTMER, TARGET $DATA.BACK.TCUSTMER;

Invoking the macro:

#option_defaults ()
MAP $DATA.PROD.TCUSTORD, TARGET $DATA.BACK.TCUSTORD

expands to:

GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
MAP $DATA.PROD.TCUSTORD, TARGET $DATA.BACK.TCUSTORD;

Macro Libraries

You can create libraries of macros to be included in different parameter files.

To create a macro library:

  1. Create the macros using a text editor, saving them to a file name with the format $DATA.GGSMACR.filename, where filename is the name of the file.

Note: A macro library file can contain multiple macros.

  1. Store your macro library files in $DATA.GGSMACR.

  2. Specify the INCLUDE parameter in your parameter file to include the macro library.

Sample Macro Libraries

These samples show:

  1. The macro library $DATA.GGSMACR.DATELIB that contains #make_date and #assign_date macros for date conversions

  2. The $DATA.GGSMACR.MAINLIB macro library containing the macro with multiple commands

  3. A sample parameter file calling a macro library that shows the include statement, and invocation statements for one of the macros from the library

    The Extract parameter file is using the DATELIB macro library, and the #assign_date macro.

Example 7-3 The $DATA.GGSMACR.DATELIB Macro Library

--
-- Date macro library
--
MACRO #make_date
PARAMS (#year, #month, #day)
BEGIN
@DATE("YYYY-MM-DD", "CC", @IF(#year < 50, 20, 19),
"YY", #year, "MM", #month, "DD", #day)
END;
MACRO #assign_date
PARAMS (#target_col, #year, #month, #day)
BEGIN
#target_col = #make_date (#year, #month, #day)
END;

Example 7-4 The $DATA.GGSMACR.MAINLIB Macro Library

--
-- Main macro library
--
INCLUDE $DATA.GGSMACR.DATELIB
MACRO #option_defaults
BEGIN
GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
END;

Example 7-5 Sample Extract Parameter File

-- Parameter file for EXTRACT EXT1
--
INCLUDE $DATA.GGSMACR.DATELIB
EXTRACT EXT1
...
MAP $DATA.PROD.ACCOUNT, TARGET $DATA.BACK.ACCOUNT,
COLMAP (
TARGCOL1 = SOURCECOL1,
#assign_date(DATECOL1,YR1,MO1,DAY1),
#assign_date(DATECOL2,YR1,MO1,DAY1)
);
...

The parameter file processes the macro as follows:

Suppressing Report File Listing

When including long, standard macro libraries, you may want to suppress listing each macro in the report file. Listing can be turned off and on by placing the LIST and NOLIST commands anywhere within the parameter file or within the included library.

For example, in the following, NOLIST suppresses listing each macro in HUGELIB. Specifying LIST after the INCLUDE statement restores listing to the report file.

NOLIST
INCLUDE $DATA.GGSMACR.HUGELIB
LIST
EXTRACT EXT1
.
.
.

Tracing Parameter Expansion

You can trace macro expansion with the CMDTRACE parameter. When CMDTRACE is enabled, the macro processor displays macro expansion steps in the process’s report file.

The syntax is:

CMDTRACE [ON \| OFF \| DETAIL]
Argument Description

| — | — |

ON
 Enables tracing. 
OFF
 Disables tracing. This is the default setting.

Tracing is enabled before #testmac is called, then disabled after the macro runs, as shown in the following example:

EXTRACT EXT1
MACRO #testmac
BEGIN
COL1 = COL2,
COL3 = COL4
END;
.
.
.
CMDTRACE ON
MAP $DATA.TEST.TEST1, TARGET $DATA.TEST.TEST2,
COLMAP
(
#testmac
);
CMDTRACE OFF
.
.
.

Using OBEY Files

With OBEY files, you can direct Oracle GoldenGate to parameters stored in a different file, then return processing to the current parameter file. OBEY files are useful for frequently used parameter statements, or parameters that are used by multiple parameter files.

OBEY *`filename`*

To use an OBEY file:

  1. Use the NonStop editor to create a file and enter the desired parameters.

  2. Edit the file where you want to place an OBEY parameter.

  3. Enter the OBEY parameter, specifying the name of the file as filename.

OBEY *filename*
For example:

OBEY $DATA03.GGS.FINANCE

Creating High Pin Processes

Use the PCREATE library to intercept the C run-time creation of new processes to create high pin processes.

Note: The PCREATE intercept is only available for native mode on the operating systems.

Replicat

For Replicat PCREATE must be combined with the relinkable PRIVLIB to build a combined library that will include intercepts to create a high pin TACL.

The following example combines the PCREATE intercept object, PCREATEO, with the relinkable (R) native mode (N) PRIVLIB to create a new user library named PRIVLIBX.

eld -ul -o PRIVLIBX PRIVLIBR PCREATEO -set interpose_user_library on
FUP LICENSE PRIVLIBX
eld -change libname $DATA.GGS1000.PRIVLIBX REPLICAT

In the last step the new PRIVLIBX is assigned as Replicat’s library. The library name must be fully qualified as shown in the example.

Extract and GGSCI

Extract and GGSCI can use a combined library, such as created in the above example, or PCREATE can be linked into a user library, such as the PCREATEL in the example below.

eld -ul -o PCREATEL PCREATEO -set interpose_user_library_on

TACL DEFINE

Enter the DEFINE for TACL, =GGS_TACL_PROGRAM, in GLOBALS if it is to be the same for all Extract, Replicat, and GGSCI programs for that Oracle GoldenGate instance. If it is more specific, include it in the Extract or Replicat parameter file. Alternatively it can also be added to TACLLOC or to TACLCSTM.

The following example DEFINE assumes you first FUP DUP $SYSTEM.SYS *nn* .TACL to $SYSTEM.SYS *nn* .TACLHP, turn HighPin ON, then add the DEFINE.

ADD DEFINE =GGS_TACL_PROGRAM, CLASS MAP, FILE $SYSTEM.SYS*`nn`*.TACLHP