This chapter contains guidelines for preparing the database and the system to support Oracle GoldenGate.
This chapter contains the following sections:
The following table attributes must be addressed in an Oracle GoldenGate environment.
Disabling Triggers and Cascade Constraints
Disable triggers, cascade delete constraints, and cascade update constraints on the target tables, or alter them to ignore changes made by the Oracle GoldenGate database user. Oracle GoldenGate replicates DML that results from a trigger or cascade constraint. If the same trigger or constraint gets activated on the target table, it becomes redundant because of the replicated version, and the database returns an error. Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ.
A delete is issued for emp_src.
It cascades a delete to salary_src.
Oracle GoldenGate sends both deletes to the target.
The parent delete arrives first and is applied to emp_targ.
The parent delete cascades a delete to salary_targ.
The cascaded delete from salary_src is applied to salary_targ.
The row cannot be located because it was already deleted in step 5.
Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.
Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:
Primary key
First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration.
Note:
If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficientWHERE clause.KEYCOLS to Specify a Custom KeyIf a table does not have one of the preceding types of row identifiers, or if you prefer those identifiers not to be used, you can define a substitute key if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. For more information, see the Reference for Oracle GoldenGate for Windows and UNIX.
ROWID ColumnsAny attempt to insert into a target table that includes a column with a data type of ROWID GENERATED ALWAYS (the default) will fail with the following ODBC error:
ODBC error: SQLSTATE 428C9 native database error -798. {DB2 FOR OS/390}{ODBC DRIVER}{DSN08015} DSNT408I SQLCODE = -798, ERROR: YOU CANNOT INSERT A VALUE INTO A COLUMN THAT IS DEFINED WITH THE OPTION GENERATED ALWAYS. COLUMN NAME ROWIDCOL.
You can do one of the following to prepare tables with ROWID columns to be processed by Oracle GoldenGate:
Ensure that any ROWID columns in target tables are defined as GENERATED BY DEFAULT.
If it is not possible to change the table definition, you can work around it with the following procedure.
To Work Around ROWID GENERATE ALWAYS:
For the source table, create an Extract TABLE statement, and use a COLSEXCEPT clause in that statement that excludes the ROWID column. For example:
TABLE tab1, COLSEXCEPT (rowidcol);
The COLSEXCEPT clause excludes the ROWID column from being captured and replicated to the target table.
For the target table, ensure that Replicat does not attempt to use the ROWID column as the key. This can be done in one of the following ways:
Specify a primary key in the target table definition.
If a key cannot be created, create a Replicat MAP parameter for the table, and use a KEYCOLS clause in that statement that contains any unique columns except for the ROWID column. Replicat will use those columns as a key. For example:
MAP tab1, TARGET tab1, KEYCOLS (num, ckey);
For more information about KEYCOLS, see Section 3.1.2, "Assigning Row Identifiers".
The following guidelines assume that an appropriate ODBC driver is installed and configured.
Section 3.2.1, "Setting Initialization Parameters"
Section 3.2.2, "Specifying the Path to the Initialization File"
Section 3.2.3, "Ensuring ODBC Connection Compatibility"
Section 3.2.4, "Specifying the Number of Connection Threads"
The following DB2 for z/OS initialization parameters apply to Oracle GoldenGate and must be set correctly before starting Oracle GoldenGate processes.
MVSDEFAULTSSID: set to the DB2 subsystem.
LOCATION: set to the DB2 location name as stored in the DB2 Boot Strap Dataset.
MVSATTACHTYPE: set to RRSAF (Recoverable Resource Manager Services Attachment Facility) or CAF (Call Attachment Facility). IBM recommends using RRSAF.
MULTICONTEXT: set to 1 if using RRSAF.
PLANNAME: set to the DB2 plan. The default plan name is DSNACLI.
Do not use the CURRENTAPPENSCH initialization parameter (keyword).
Note:
When using theCAF attachment type, you must use the Oracle GoldenGate DBOPTIONS parameter with the NOCATALOGCONNECT option in the parameter file of any Extract or Replicat process that connects to DB2. This parameter disables the usual attempt by Oracle GoldenGate to obtain a second thread for the DB2 catalog. Otherwise, you will receive error messages, such as: ODBC operation failed: Couldn't connect to data source for catalog queries.Specify the ODBC initialization file by setting the DSNAOINI environment variable in the z/OS UNIX profile, as in the following example:
export DSNAOINI="/etc/odbc810.ini"
To ensure that you configure the DB2 ODBC initialization file correctly, follow the guidelines in the DB2 UDB for z/OS ODBC Guide and Reference manual. One important consideration is the coding of the open and close square brackets (the [ character and the ] character). The square bracket characters are "variant" characters that are encoded differently in different coded character set identifiers (CCSID), but must be of the IBM-1047 CCSID in the ODBC initialization file. DB2 ODBC does not recognize brackets of any other CCSID. Note the following:
The first (or open) bracket must use the hexadecimal characters X'AD' (0xAD).
The second (or close) bracket must use the hexadecimal characters X'BD' (0xBD).
To set the correct code for square brackets, use any of the following methods.
Use the hex command in OEDIT and change the hex code for each character appropriately.
Use the iconv utility to convert the ODBC initialization file. For example, to convert from CCSID IBM-037 to IBM-1047, use the following command:
iconv -f IBM-037 -t IBM-1047 ODBC.ini > ODBC-1047.ini
mv ODBC-1047.ini ODBC.ini
Change your terminal emulator or terminal configuration to use CCSID IBM-1047 when you create or alter the file.
Every Oracle GoldenGate process makes a database connection. Depending on the number of processes that you will be using and the number of other DB2 connections that you expect, you might need to adjust the following DB2 system parameters on the DSNTIPE DB2 Thread Management Panel:
MAX USERS (macro DSN6SYSP CTHREAD)
MAX TSO CONNECT (macro DSN6SYSP IDFORE)
MAX BATCH CONNECT (macro DSN6SYSP IDBACK)
If using RRSAF, allow:
Two DB2 threads per process for each of the following:
Extract
Replicat
The GGSCI command DBLOGIN (logs into the database)
DEFGEN utility (generates data definitions for column mapping)
One extra DB2 thread for Extract for IFI calls.
One extra DB2 thread for each SQLEXEC parameter statement that will be issued by each Extract and Replicat process. For more information about SQLEXEC, see the Reference for Oracle GoldenGate for Windows and UNIX.
If using CAF, there can be only one thread per Oracle GoldenGate process.
Grant Oracle GoldenGate USS access to the SDSNLOAD system load library and to the DSNHDECP load module. You can include the libraries in one of the following places:
The z/OS system search order.
The USS profile of the Oracle GoldenGate user. Use a UNIX command similar to the following, where DSN810 is the user-assigned data set prefix from the DB2 installation.
export STEPLIB='DSN810.SDSNEXIT:DSN810.SDSNLOAD'
The preceding command will cause USS to allocate the equivalent of a STEPLIB DD statement whenever it executes a shell command or Oracle GoldenGate process. If using APF, all libraries in the STEPLIB concatenation must be APF-authorized.
By default, USS sets the job name and owner of all Oracle GoldenGate processes to that of the user who started them. You can change the job name or user by setting the _BPX_JOBNAME and _BPX_USERID environment variables, or you can create z/OS jobs or started-task procedures for the Oracle GoldenGate processes. To use the environment variable _BPX_JOBNAME, at a minimum you should have read access to the RACF FACILITY class and BPX.JOBNAME name. For more details, see Table 1-1, "Operating System Privileges" and the IBM z/OS System Services Planning document.
The user who starts the Manager process is typically the user by which other Oracle GoldenGate processes run. Oracle GoldenGate work appears as forked child processes of WLM subsystem type OMVS. Assign the Oracle GoldenGate processes their Workload Manager (WLM) velocity goals based on the following guidelines.
Assign the Extract process that reads the transaction logs a medium velocity goal, one that is below the velocity of the main DB2 address spaces, but above the velocity of most online transactions, TSO/E sessions, and z/OS batch work. The higher the velocity goal, the more processor power that Extract will receive, and the less lag that it will experience.
You can assign an initial-load Extract process a velocity goal, or you can treat it as a typical DB2 batch job. For more information about the initial-load processes, see Administering Oracle GoldenGate for Windows and UNIX.
You might need to assign the Replicat process a higher velocity goal. Although Replicat is a typical DB2 batch application, it might require more processing power to prevent backlogs and latency.
You probably will run Oracle GoldenGate utilities, such as DEFGEN and LOGDUMP, only occasionally, so you can let them perform like the other UNIX terminal-oriented work.
If executing stored procedures with the SQLEXEC command, make certain that they do not become a bottleneck for Oracle GoldenGate. Their priority should be close to that of the calling Extract or Replicat process. WLM executes them with that priority, but the z/OS system executes them under the priority of a stored procedure as defined by the DB2 and z/OS system programmers.
If you run Oracle GoldenGate under the TSO/E OMVS command, the Oracle GoldenGate processes are subject to the system and WLM limits of the TSO/E user account, rather than those of the UNIX kernel. Very long TSO/E response times (up to 20 seconds), often with little service consumption, can be recorded for an OMVS user because of the way that OMVS polls for terminal input. This can affect those WLM goals that are based on response time.
You can use multiple WLM service classes for the Oracle GoldenGate processes. The following is an example of how to maintain relative priorities for Oracle GoldenGate and other work, from highest priority to the lowest:
z/OS system processes, including the UNIX kernel and IRLM.
DB2 for z/OS address spaces for the primary Extract group.
Primary Extract group configured for online or batch change synchronization, and any DB2 stored procedures that it calls.
z/OS transaction managers, such as CICS and IMS.
Collector (Server) for local Extract data pump, if used.
Local Extract data pump (reading from trail), if used.
Collector for remote trails (files received from a remote site). Such files include the QSAM file created with the Extract RMTBATCH parameter on a NonStop system.
Online Replicat groups and any DB2 stored procedures that they call.
Manager process (required only for startup of Oracle GoldenGate processes and trail cleanup).
GGSCI and other user UNIX and TSO/E terminal work.
Initial-load Extract and any DB2 stored procedures that it calls.
Initial-load Replicat and any DB2 stored procedures that it calls.
Other z/OS batch work.
The following topics provide information about monitoring Oracle GoldenGate with z/OS system facilities.
Section 3.6.1, "Viewing Oracle GoldenGate Messages"
Section 3.6.2, "Identifying Oracle GoldenGate Processes"
Section 3.6.3, "Interpreting Statistics for Update Operations"
Section 3.6.3, "Interpreting Statistics for Update Operations"
If the system log process (syslog daemon syslogd ) is running, USS routes Oracle GoldenGate messages to their configured destination by means of UNIX message priority. For more information about configuring syslogd, see the z/OS IP configuration documents and the UNIX System Services Planning document.
If syslogd is not running, Oracle GoldenGate writes its command output, status information, and error messages to the system console. You can redirect console messages to the Oracle GoldenGate USS session and to the Oracle GoldenGate report files by using the following UNIX command:
export _BPXK_JOBLOG=STDERR
The system management facility (SMF) typically creates a separate accounting record for each UNIX process, including Oracle GoldenGate processes. However, if a user invokes the UNIX shell by using the OMVS command with the default SHAREAS option, or if a user sets the environment variable _BPX_SHAREAS to YES , it could cause two or more processes to run in the same address space. SMF provides process identification only for the first process, but resource consumption is accumulated for all processes that are running. For Oracle GoldenGate, this means that the work probably will be recorded under the Manager process, which is named mgr.
If the DB2 accounting trace is also active to the SMF destination, DB2 will create an SMF accounting record for each of the following Oracle GoldenGate processes:
Extract
Replicat
Manager, if performing maintenance on Oracle GoldenGate tables. Examples of Oracle GoldenGate tables are the marker table and the Replicat checkpoint table.
GGSCI sessions that issue the Oracle GoldenGate DBLOGIN command to log into the database.
The actual number of DML operations that are executed on the DB2 database might not match the number of extracted DML operations that are reported by Oracle GoldenGate. DB2 does not log update statements if they do not physically change a row, so Oracle GoldenGate cannot detect them or include them in statistics.
Oracle GoldenGate provides globalization support. The following are things to take into consideration when using this support.
Replicating From a Source that Contains Both ASCII and EBCDIC
Specifying Multi-Byte Characters in Object Names
When replicating to or from a DB2 source system to a target that has a different character set, some consideration must be given to the encoding of the character data on the DB2 source if it contains a mix of ASCII and EBCDIC data. Character set conversion by any given Replicat requires source data to be in a single character set.
The source character set is specified in the trail header. Thus, the Oracle GoldenGate trail can contain either ASCII or EBCDIC data, but not both. Unicode tables are processed without any special configuration and are exempt from the one-character set requirement.
With respect to a source that contains both character encoding types, you have the following options:
You can use one Extract for all of your tables, and have it write the character data to the trail as either ASCII or as EBCDIC.
You can use different Extracts: one Extract to write the ASCII character data to a trail, and another Extract to write the EBCDIC character data to a different trail. You then associate each trail with its own data pump process and Replicat process, so that the two data streams are processed separately.
To output the correct character set in either of those scenarios, use the TRAILCHARSETASCII and TRAILCHARSETEBCDIC parameters. The default is TRAILCHARSETEBCDIC. Without these parameters, ASCII and EBCDIC data are written to the trail as-is. When using these parameters, note the following:
If used on a single-byte DB2 subsystem, these parameters cause Extract to convert all of the character data to either the ASCII or EBCDIC single-byte CCSID of the subsystem to which Extract is connected, depending on which parameter is used (except for Unicode, which is processed as-is).
If used on a multi-byte DB2 subsystem, these parameters cause Extract to capture only ASCII or EBCDIC tables (and Unicode). Character data is written in either the ASCII or EBCDIC mixed CCSID (depending on the parameter used) of the DB2 z/OS subsystem to which Extract is connected.
If the name of a schema, table, column, or stored procedure in a parameter file contains a multi-byte character, the name must be double-quoted. For more information about specifying object names, see Administering Oracle GoldenGate for Windows and UNIX.