5 Preparing the System for Oracle GoldenGate

This chapter contains guidelines for preparing the DB2 for i system to support Oracle GoldenGate.

Topics:

• Preparing the Journals for Data Capture by Extract
  
• Specifying Object Names
  
• Preparing Tables for Processing
  
• Adjusting the System Clock
  

Preparing the Journals for Data Capture by Extract

All tables for which you want data to be captured must be journaled, either explicitly or by default by means of a QSQJRN journal in the same library. To preserve data integrity, data journal entries are sent to the Extract process in time order as they appear on the system. This section provides guidelines for configuring the journals to support capture by the Extract process.

• Allocating Journals to an Extract Group
  
• Setting Journal Parameters
  
• Deleting Old Journal Receivers
  

Allocating Journals to an Extract Group

One Extract process can process a single journal. If using more journals than that, use additional Extract processes to handle the extra journals. You can also use additional Extract processes to improve capture performance if necessary.

Note:

To ensure transaction integrity, all objects that correspond to any given transaction must be read by the same Extract group. For more information about using multiple Extract processes, see Tuning the Performance of Oracle GoldenGate in Administering Oracle GoldenGate.

Setting Journal Parameters

To support the capture of data by the Extract process, the following are the minimal journaling parameter settings that are required.

• Manage Receivers (MNGRCV): *SYSTEM

• Delete Receivers (DLTRCV): *NO

• Receiver Size Option (RCVSIZOPT): *MAXOPT2 (*MAXOPT3 recommended to avoid the necessity to perform an ALTER EXTRACT with the ETROLLOVER option when the journal sequence numbers run out if *MAXOPT2 is used.)

• Journal State (JRNSTATE): *ACTIVE

• Minimize Entry Specific Data (MINENTDTA): *NONE

• Fixed Length Data (FIXLENDTA): *USR

In the following example, the command to set these attributes for a journal JRN1 in library LIB1 would be:

CHGJRN JRN(LIB1/JRN1) MNGRCV(*SYSTEM) DLTRCV(*NO) RCVSIZOPT(*MAXOPT3) JRNSTATE(*ACTIVE) MINENTDTA(*NONE) FIXLENDTA(*USR)

Note:

To check the attributes of a journal, use the command WRKJRNA JRN(LIB1/JRN1) DETAIL(*CURATR).

When the journaling is set to the recommended parameter settings, you are assured that the entries in the journals contain all of the information necessary for Oracle GoldenGate processing to occur. These settings also ensure that the system does not delete the journal receivers automatically, but retains them in case Extract needs to process older data.

Deleting Old Journal Receivers

Although the DLTRCV parameter is set to NO in the recommended configuration for Extract (see Setting Journal Parameters), you can delete old journal receivers manually once Extract is finished capturing from them.

If using another application that is using the journals that Oracle GoldenGate will be reading, consideration must be given regarding any automatic journal receiver cleanup that may be in place. Oracle GoldenGate must be able to read the journal receivers before they are detached or removed.

To Delete Journal Receivers

1. Run GGSCI.
2. In GGSCI, issue the following command to view the journal positions in which Extract has current processing points, along with their journal receivers.

   INFO EXTRACT group
   

3. Use the following DB2 for i command to delete any journal receivers that were generated prior to the ones that are shown in the INFO EXTRACT command.

   DLTJRNRCV JRNRCV(library/journal_receiver)
   

   Where:

   library and journal_receiver are the actual names of the library and journal receiver to be deleted. See the DB2 for i Information Center for more information about this command.

Specifying Object Names

Oracle GoldenGate commands and parameters support input in the form of SQL names, native names in the format of library_name/file_name(member_name), or a mix of the two. If a native file system name does not include the member name, all members are implicitly selected by the Oracle GoldenGate process. For a SQL name only the first member is used.

To support case sensitivity of double quoted object names, specify those names within double quotes in the Oracle GoldenGate parameter files. This is true of both SQL and native file system names.

When specifying a native table name in a MAP statement on a platform other than DB2 for i, the name must be enclosed within double quotes so that Oracle GoldenGate correctly interprets it as a separator character.

For consistency of terminology in other administrative and reference Oracle GoldenGate documentation, the SQL terms "schema" and "table" are used to reference the containers for the DB2 for i data, as shown here.

Table 5-1 Native-SQL object name relationships

Native	SQL	Notes
Library (maximum length 10)	Schema (maximum length 128)	The operating system creates a corresponding native name for a SQL-created schema.
File (maximum length 10)	Table (maximum length 128)	The operating system creates a corresponding native name for a SQL-created table.
Member	Not Applicable	Contains the actual data. Only the first member of a FILE object can be accessed through SQL. To access data in other members the native system name must be used.

Preparing Tables for Processing

The following table attributes must be addressed in an Oracle GoldenGate environment.

• Ensuring Row Uniqueness for Tables
  
• Preventing Key Changes
  
• Disabling Constraints on the Target
  
• Enabling Change Capture
  
• Maintaining Materialized Query Tables
  

Ensuring Row Uniqueness for Tables

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:

1. Primary key

2. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.

3. 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 efficient WHERE clause.

4. If a table does not have an appropriate key, or if you prefer that the existing key(s) are not 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. See TABLE | MAP in Reference for Oracle GoldenGate.

• Using KEYCOLS to Specify a Custom Key
  

Using KEYCOLS to Specify a Custom Key

If 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.

Preventing Key Changes

If you must add columns to the key that Extract is using as the row identifier for a table (primary key, unique key, KEYCOLS key, or all-column key) after Oracle GoldenGate has started processing journal data, follow these steps to make the change.

1. Stop Extract.

   STOP EXTRACT group
   

2. Issue the following command until it returns EOF, indicating that it has processed all of the existing journal data.

   INFO EXTRACT group
   

3. Make the change to the key.
4. Start Extract.

   START EXTRACT group

Disabling Constraints on the Target

Triggers and cascade constraints must be disabled on the target tables or configured to ignore changes made by Replicat. Constraints must be disabled because Oracle GoldenGate replicates DML that results from a trigger or a 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.

1. A delete is issued for emp_src.
2. It cascades a delete to salary_src.
3. Oracle GoldenGate sends both deletes to the target.
4. The parent delete arrives first and is applied to emp_targ.
5. The parent delete cascades a delete to salary_targ.
6. The cascaded delete from salary_src is applied to salary_targ.
7. The row cannot be located because it was already deleted in step 5.

Enabling Change Capture

To capture changes to a table in a journal, you can run the STRJRNPF command on the OS/400 command line or run the ADD TRANDATA command from GGSCI. The ADD TRANDATA command calls STRJRNPF and is the recommended method to start journaling for tables, because it ensures that the required journal image attribute of Record Images (IMAGES): *BOTH is set on the STRJRNPF command.

To Run ADD TRANDATA

1. Run GGSCI on the source system.
2. Issue the DBLOGIN command.

   DBLOGIN SOURCEDB database USERID user, PASSWORD password [encryption_options]

   Where: SOURCEDB specifies the default DB 2 for i database, USERID specifies the Extract user profile, and PASSWORD specifies that profile's password.

3. Issue the ADD TRANDATA command.

   ADD TRANDATA table_specification

   Where: table_specification is one of the following:

   • schema.table [JOURNAL library/journal]

   • library/file [JOURNAL  library/journal] (See Specifying a Default Journal)

• Specifying a Default Journal
  
• Removing a Default Journal Specification
  

Specifying a Default Journal

To specify a default journal for multiple tables or files in the ADD TRANDATA command, instead of specifying the JOURNAL keyword, use the following GGSCI command before issuing ADD TRANDATA.

DEFAULTJOURNAL library/journal 

Any ADD TRANDATA command used without a journal assumes the journal from DEFAULTJOURNAL.

To display the current setting of DEFAULTJOURNAL, you can issue the command with no arguments.

Removing a Default Journal Specification

To remove the use of a default journal, use the following GGSCI command:

DEFAULTJOURNAL CLEAR

Maintaining Materialized Query Tables

To maintain parity between source and target materialized query tables (MQT), you replicate the base tables, but not the MQTs. The target database maintains the MQTs based on the changes that Replicat applies to the base tables.

The following are the rules for configuring these tables:

• Include the base tables in your TABLE and MAP statements.

• Do not include MQTs in the TABLE and MAP statements.

• Wildcards can be used in TABLE and MAP statements, even though they might resolve MQT names along with regular table names. Oracle GoldenGate automatically excludes MQTs from wildcarded table lists. However, any MQT that is explicitly listed in an Extract TABLE statement by name will cause Extract to abend.

Adjusting the System Clock

It is recommended that you set the system clock to UTC (Universal Time Coordinate) time and use the timezone offset in the DB2 for i system values to represent the correct local time. If this setup is done correctly, local daylight savings time adjustments can occur automatically with no disruption to replication.