16 Customizing Oracle GoldenGate Processing

SQLEXEC extends the functionality of both Oracle GoldenGate and the database by allowing Oracle GoldenGate to use the native SQL of the database to execute custom processing instructions.

• Stored procedures and queries can be used to select or insert data into the database, to aggregate data, to denormalize or normalize data, or to perform any other function that requires database operations as input. Oracle GoldenGate supports stored procedures that accept input and those that produce output.

• Database commands can be issued to perform database functions required to facilitate Oracle GoldenGate processing, such as disabling triggers on target tables and then enabling them again.

The SQLEXEC parameter can be used as follows:

• as a clause of a TABLE or MAP statement

• as a standalone parameter at the root level of the Extract or Replicat parameter file.

When used within a TABLE or MAP statement, SQLEXEC can pass and accept parameters. It can be used for procedures and queries, but not for database commands.

SQLEXEC (SPNAME sp_name,
[ID logical_name,]
{PARAMS param_spec | NOPARAMS})

SPNAME

sp_name

ID logical_name

PARAMS param_spec |
NOPARAMS

SQLEXEC (ID logical_name, QUERY ' query ',
{PARAMS param_spec | NOPARAMS})

ID logical_name

QUERY ' sql_query '

SQLEXEC 'SELECT "col1" from "schema"."table"'

PARAMS param_spec |
NOPARAMS

select col1 from db1:tab1
select col2 from db2:schema2.tab2
select col3 from tab3
select col3 from schema4.tab4

When used as a standalone parameter statement in the Extract or Replicat parameter file, SQLEXEC can execute a stored procedure, query, or database command. As such, it need not be tied to any specific table and can be used to perform general SQL operations. For example, if the Oracle GoldenGate database user account is configured to time-out when idle, you could use SQLEXEC to execute a query at a defined interval, so that Oracle GoldenGate does not appear idle. As another example, you could use SQLEXEC to issue an essential database command, such as to disable target triggers. A standalone SQLEXEC statement cannot accept input parameters or return output parameters.

SQLEXEC 'call procedure_name()'

SQLEXEC 'sql_query'

SQLEXEC 'database_command'

'call
procedure_name ()'

SQLEXEC 'call prc_job_count ()'

'sql_query'

SQLEXEC 'SELECT "col1" from "schema"."table"'

'database_command'

SQLEXEC provides options to control processing behavior, memory usage, and error handling. For more information, see Reference for Oracle GoldenGate.

Oracle GoldenGate provides options for passing input and output values to and from a procedure or query that is executed with SQLEXEC within a TABLE or MAP statement.

There are two types of error conditions to consider when implementing SQLEXEC:

• The column map requires a column that is missing from the source database operation. This can occur for an update operation if the database only logs the values of columns that changed, rather than all of the column values. By default, when a required column is missing, or when an Oracle GoldenGate column-conversion function results in a "column missing" condition, the stored procedure does not execute. Subsequent attempts to extract an output parameter from the stored procedure results in a "column missing condition" in the COLMAP or FILTER clause.

• The database generates an error.

Observe the following SQLEXEC guidelines:

• Up to 20 stored procedures or queries can be executed per TABLE or MAP entry. They execute in the order listed in the parameter statement.

• A database login by the Oracle GoldenGate user must precede the SQLEXEC clause. Use the SOURCEDB and/or USERID or USERIDALIAS parameter in the Extract parameter file or the TARGETDB and/or USERID or USERIDALIAS parameter in the Replicat parameter file, as needed for the database type and configured authentication method.

• The SQL is executed by the Oracle GoldenGate user. This user must have the privilege to execute stored procedures and call RDBM-supplied procedures.

• Database operations within a stored procedure or query are committed in same context as the original transaction.

• Do not use SQLEXEC to update the value of a primary key column. If SQLEXEC is used to update the value of a key column, then the Replicat process will not be able to perform a subsequent update or delete operation, because the original key value will be unavailable. If a key value must be changed, you can map the original key value to another column and then specify that column with the KEYCOLS option of the TABLE or MAP parameter.

• For DB2, Oracle GoldenGate uses the ODBC SQLExecDirect function to execute a SQL statement dynamically. This means that the connected database server must be able to prepare the statement dynamically. ODBC prepares the SQL statement every time it is executed (at the requested interval). Typically, this does not present a problem to Oracle GoldenGate users. See the IBM DB2 documentation for more information.

• Do not use SQLEXEC for objects being processing by a data-pump Extract in pass-through mode.

• All object names in a SQLEXEC statement must be fully qualified with their two-part or three-part names, as appropriate for the database.

• All objects that are affected by a SQLEXEC stored procedure or query must exist with the correct structures prior to the execution of the SQL. Consequently, DDL on these objects that affects structure (such as CREATE or ALTER) must happen before the SQLEXEC executes.

• All objects affected by a standalone SQLEXEC statement must exist before the Oracle GoldenGate processes start. Because of this, DDL support must be disabled for those objects; otherwise, DDL operations could change the structure or delete the object before the SQLEXEC procedure or query executes on it.

To define an Oracle GoldenGate macro, use the MACRO parameter in the parameter file. MACRO defines any input parameters that are needed and it defines the work that the macro performs.

MACRO #macro_name
PARAMS (#p1, #p2 [, ...])
BEGIN
macro_body
END;

MACRO

#macro_name

PARAMS (#p1, #p2)

BEGIN

macro_body

END;

MACRO #macro1  
PARAMS ( #o, #t, #k )  
BEGIN  
MAP #o.#t, TARGET #o.#t, KEYCOLS (#k), COLMAP (USEDEFAULTS); 
END; 

MACRO #option_defaults
BEGIN
GETINSERTS
GETUPDATES
GETDELETES
INSERTDELETES
END;

This section shows you how to call a macro. (To define a macro, see Defining a Macro).

To call a macro, use the following syntax where you want the macro to run within the parameter file.

[target =] macro_name (val[, ...])

[target =] macro_name (val | {val, val, ...}[, ...])

DATECOL1 = #make_date (YR1, MO1, DAY1)

#make_date (YR1, MO1, DAY1)

To call other macros from a macro, create a macro definition similar to the following. In this example, the #make_date macro is nested within the #assign_date macro, and it is called when #assign_date runs.

The nested macro must define all, or a subset of, the same parameters that are defined in the base macro. In other words, the input values when the base macro is called must resolve to the parameters in both macros.

The following defines #assign_date:

MACRO #assign_date
PARAMS (#target_col, #year, #month, #day)
BEGIN
#target_col = #make_date (#year, #month, #day)
END;

The following defines #make_date. This macro creates a date format that includes a four-digit year, after first determining whether the two-digit input date should be prefixed with a century value of 19 or 20. Notice that the PARAMS statement of #make_date contains a subset of the parameters in the #assign_date macro.

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;

The following syntax calls #assign_date:

#assign_date (COL1, YEAR, MONTH, DAY)

The macro expands to the following given the preceding input values and the embedded #make_date macro:

COL1 = @DATE ('YYYY-MM-DD', 'CC', @IF (YEAR < 50, 20, 19),'YY', YEAR, 'MM', MONTH, 'DD', DAY)

You can create a macro library that contains one or more macros. By using a macro library, you can define a macro once and then use it within many parameter files.

-- datelib 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;

INCLUDE /ggs/dirprm/datelib.mac
REPLICAT rep
ASSUMETARGETDEFS
USERIDALIAS ogg
MAP fin.acct_tab, TARGET fin.account;

NOLIST
INCLUDE /ggs/dirprm/hugelib.mac
LIST
INCLUDE /ggs/dirprm/mdatelib.mac
REPLICAT REP

You can trace macro expansion with the CMDTRACE parameter. With CMDTRACE enabled, macro expansion steps are shown in the Extract or Replicat report file.

CMDTRACE [ON | OFF | DETAIL]

REPLICAT REP
MACRO #testmac
BEGIN
COL1 = COL2,
COL3 = COL4,
END;
...
CMDTRACE ON
MAP test.table1, TARGET test.table2,
COLMAP (#testmac);
CMDTRACE OFF

You can employ user exits as an alternative to, or in conjunction with, the column-conversion functions that are available within Oracle GoldenGate. User exits can be a better alternative to the built-in functions because a user exit processes data only once (when the data is extracted) rather than twice (once when the data is extracted and once to perform the transformation).

The following are some ways in which you can implement user exits:

• Perform arithmetic operations, date conversions, or table lookups while mapping from one table to another.

• Implement record archival functions offline.

• Respond to unusual database events in custom ways, for example by sending an e-mail message or a page based on an output value.

• Accumulate totals and gather statistics.

• Manipulate a record.

• Repair invalid data.

• Calculate the net difference in a record before and after an update.

• Accept or reject records for extraction or replication based on complex criteria.

• Normalize a database during conversion.

The basis for most user exit processing is the EXIT_CALL_PROCESS_RECORD function. For Extract, this function is called just before a record buffer is output to the trail. For Replicat, it is called just before a record is applied to the target. If source-target mapping is specified in the parameter file, the EXIT_CALL_PROCESS_RECORD event takes place after the mapping is performed.

When EXIT_CALL_PROCESS_RECORD is called, the record buffer and other record information are available to it through callback routines. The user exit can map, transform, clean, or perform any other operation with the data record. When it is finished, the user exit can return a status indicating whether the record should be processed or ignored by Extract or Replicat.

The following instructions help you to create user exits on Windows and UNIX systems. For more information about the parameters and functions that are described in these instructions, see Reference for Oracle GoldenGate for Windows and UNIX.

CUSEREXIT eruserexit.so MyUserExit

CUSEREXIT eruserexit.dll MyUserExit

To maintain data integrity, a user exit needs to understand the character set of the character-type data that it exchanges with an Oracle GoldenGate process. Oracle GoldenGate user exit logic provides globalization support for:

• character-based database metadata, such as the names of catalogs, schemas, tables, and columns

• the values of character-type columns, such as CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB, as well as string-based numbers, date-time, and intervals.

Properly converting between character sets allows column data to be compared, manipulated, converted, and mapped properly from one type of database and character set to another. Most of this processing is performed when the EXIT_CALL_PROCESS_RECORD call type is called and the record buffer and other record information is made available through callback routines.

The user exit has its own session character set. This is defined by the GET_SESSION_CHARSET and SET_SESSION_CHARSET callback functions. The caller process provides conversion between character sets if the character set of the user exit is different from the hosting context of the process.

To enable this support in user exits, there is the GET_DATABASE_METADATA callback function code. This function enables the user exit to get database metadata, such as the locale and the character set of the character-type data that it exchanges with the process that calls it (Extract, data pump, Replicat). It also returns how the database treats the case-sensitivity of object names, how it treats quoted and unquoted names, and how it stores object names.

For more information about these components, see Reference for Oracle GoldenGate for Windows and UNIX.

The object name that is passed by the user exit API is the exact name that is encoded in the user-exit session character set, and exactly the same name that is retrieved from the database. If the user exit compares the object name with a literal string, the user exit must retrieve the database locale and then normalize the string so that it is compared with the object name in the same encoding.

Oracle GoldenGate provides the following macros that can be called by the user exit to check the metadata of database object names. For example, a macro can be used to check whether a quoted table name is case-sensitive and whether it is stored as mixed-case in the database server. These macros are defined in the usrdecs.h file.

The input parameter column_value_mode describes the character format of the data that is being processed and is used in several of the function codes. The following table describes the meaning of the EXIT_FN_RAW_FORMAT, EXIT_FN_CHAR_FORMAT, and EXIT_FN_CNVTED_SESS_FORMAT format codes, per data type.

The usrdecs.h file is versioned to allow backward compatibility with existing user exits when enhancements or upgrades, such as new functions or structural changes, are added to a new Oracle GoldenGate release. The version of the usrdecs.h file is printed in the report file at the startup of Replicat or Extract.

To use new user exit functionality, you must recompile your routines to include the new usrdecs file. Routines that do not use new features do not need to be recompiled.

Oracle GoldenGate installs the following sample user exit files into the UserExitExamples directory of the Oracle GoldenGate installation directory:

• exitdemo.c shows how to initialize the user exit, issue callbacks at given exit points, and modify data. It also demonstrates how to retrieve the fully qualified table name or a specific metadata part, such as the name of the catalog or container, or the schema, or just the unqualified table name. In addition, this demo shows how to process DDL data. The demo is not specific to any database type.

• exitdemo_utf16.c shows how to use UTF16-encoded data (both metadata and column data) in the callback structures for information exchanged between the user exit and the caller process.

• exitdemo_more_recs.c shows an example of how to use the same input record multiple times to generate several target records.

• exitdemo_lob.c shows an example of how to get read access to LOB data.

• exitdemo_pk_befores.c shows how to access the before and after image portions of a primary key update record, as well as the before images of regular updates (non-key updates). It also shows how to get target row values with SQLEXEC in the Replicat parameter file as a means for conflict detection. The resulting fetched values from the target are mapped as the target record when it enters the user exit.

Each directory contains the *.c files as well as makefiles and a readme.txt file.

These examples highlight some use cases for the event marker system. For syntax details and additional usage instructions, see Reference for Oracle GoldenGate.