Topics:
Parent topic: Using Oracle GoldenGate with IBM DB2 for i
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.
Parent topic: Preparing the System for Oracle GoldenGate
One Extract process can handle up to 30 journals. 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 journals 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.
Parent topic: Preparing the Journals for Data Capture by Extract
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)
Journal State (JRNSTATE): *ACTIVE
Minimize Entry Specific Data (MINENTDTA): *NONE
Fixed Length Data (FIXLENDTA): *USR *SYSSEQ
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(*MAXOPT2) JRNSTATE(*ACTIVE) MINENTDTA(*NONE) FIXLENDTA(*USR *SYSSEQ)
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.
Parent topic: Preparing the Journals for Data Capture by Extract
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
Parent topic: Preparing the Journals for Data Capture by Extract
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 |
Parent topic: Preparing the System for Oracle GoldenGate
The following table attributes must be addressed in an Oracle GoldenGate environment.
Parent topic: Preparing the System for Oracle GoldenGate
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.
Parent topic: Preparing Tables for Processing
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 efficient WHERE
clause.
Parent topic: Assigning Row Identifiers
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.
Parent topic: Assigning Row Identifiers
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.
Parent topic: Preparing Tables for Processing
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
.
emp_src
.salary_src
.emp_targ
.salary_targ
.salary_src
is applied to salary_targ
.Parent topic: Preparing Tables for Processing
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
Parent topic: Preparing Tables for Processing
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.
Parent topic: Enabling Change Capture
To remove the use of a default journal, use the following GGSCI command:
DEFAULTJOURNAL CLEAR
Parent topic: Enabling Change Capture
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.
Parent topic: Preparing Tables for Processing
Before starting Oracle GoldenGate, specify the name of the Oracle GoldenGate for DB2 for i library when running the ggos400install
script. This creates a link to the OGGPRCJRN *SRVPGM
(service program) object that was restored to that library. If the link to the oggprcjrn
service program is deleted you could just re-run the ggos400install
shell script and specify the same library, or use the command "ln -s /qsys.lib/
OGG_library
.lib/oggprcjrn.srvpgm oggprcjrn.srvpgm
". If this link is incorrect or missing, Extract will abend.
Parent topic: Preparing Tables for Processing
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.
Parent topic: Preparing the System for Oracle GoldenGate
This section applies only if you installed Replicat on a Windows or Linux system to operate remotely from the DB2 for i target. In this configuration, Replicat must connect to the target system over a database connection that is specified with ODBC. The following steps show how to install and configure ODBC to connect to the DB2 for i target system.
Parent topic: Preparing the System for Oracle GoldenGate
To configure ODBC, you can use the graphical user interface to run the ODBC configuration utility that is supplied with your Linux distribution, or you can edit the odbc.ini
file with the settings described in these steps. (These steps show the ODBC Administrator tool launched from the ODBCConfig
graphical interface utility for Linux.)
Parent topic: Configuring the ODBC Driver
On Windows, the ODBC Administration tool is in the Administrative Tools folder as Data Sources (ODBC).
Parent topic: Configuring the ODBC Driver