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
  
• Configuring the ODBC Driver
  

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

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)

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

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.

• Assigning Row Identifiers
  
• Preventing Key Changes
  
• Disabling Constraints on the Target
  
• Enabling Change Capture
  
• Maintaining Materialized Query Tables
  
• Specifying the Oracle GoldenGate Library
  

Assigning Row Identifiers

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.

• How Oracle GoldenGate Determines the Kind of Row Identifier to Use
  
• Using KEYCOLS to Specify a Custom Key
  

How Oracle GoldenGate Determines the Kind of Row Identifier to Use

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.

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.

   Note:

   Only BLOWFISH encryption is supported for DB2 for i systems.

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.

Specifying the Oracle GoldenGate Library

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.

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.

Configuring the ODBC Driver

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.

Configuring ODBC on Linux

Configuring ODBC on Windows

• Configuring ODBC on Linux
  
• Configuring ODBC on Windows
  

Configuring ODBC on Linux

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

1. Download and install the 32-bit or 64-bit iSeries Access ODBC driver on the remote Linux system according to the vendor documentation. The iSeries ODBC driver is supplied as a free component of iSeries Access.
2. Issue one of the following commands, depending on the driver that you want to use.

   32-bit driver:

   rpm -ivh iSeriesAccess-7.1.0-1.0.i386.rpm
   

   64-bit driver:

   rpm -ivh iSeriesAccess-7.1.0-1.0.x86_64.rpm
   

3. You can create a user DSN (a connection that is available only to the user that created it) or a system DSN (a connection that is available to all users on the system). To create a user DSN, log on to the system as the user that you will be using for the Replicat process.
4. Run the ODBC configuration utility.
5. On the initial page of the ODBC configuration tool, select the User DSN tab to create a user DSN or the System DSN tab to create a system DSN. (These steps create a user DSN; creating a system DSN is similar.)
6. On the tab you selected, click Add.
7. Select the appropriate iSeries Access ODBC driver, click OK. If the correct driver is not shown in the Select the DRIVER list, click the Add button and then complete the fields.

   Steps to complete the Driver Properties fields when the driver is not found:

   • Set Name to the name of the driver.

   • Set Driver to the path where the driver is installed.

   • Set Setup to the libcwbodbci.so file that is in the driver installation directory.

   • Leave the other settings to their defaults.

   • Click the check mark above the Name field to save your settings.

8. In the Name field of the Data Source Properties dialog, supply a one-word name for the data source. In the System field, enter the fully qualified name of the target DB2 for i system, for example: sysname.company.com. Leave the UserID and Password fields blank, to allow Replicat to supply credentials when it connects to the database. Leave the remaining fields set to their defaults, and then click the check mark above the Name field to save your settings.
9. You are returned to the ODBC Data Source Administrator dialog. Click OK to exit the ODBC configuration utility.
10. To support GRAPHIC, VARGRAPHIC and DBCLOB types, edit the .odbc.ini file and add the following line.

    GRAPHIC = 1
    

    Note:

    If you created a user Data Source Name, this file is located in the home directory of the user that created it. If you created a system DSN, this file is in /etc/odbc.ini or /usr/local/etc/odbc.ini.

11. From the Oracle GoldenGate directory on the target, run GGSCI and issue the DBLOGIN command to log into the target database.

    DBLOGIN SOURCEDB database, USERID db_user [, PASSWORD pw [encryption options]]
    

    Where:

    • SOURCEDB database specifies the new Data Source Name.

    • USERID db_user, PASSWORD pw are the Replicat database user profile and password.

    • encryption options is optional password encryption.

    Note:

    Only BLOWFISH encryption is supported for DB2 for i systems.

Configuring ODBC on Windows

On Windows, the ODBC Administration tool is in the Administrative Tools folder as Data Sources (ODBC).

1. Download and install the 32-bit or 64-bit iSeries Access ODBC driver from the DB2 for iSeries Access package on the remote Windows system according to the vendor documentation. The iSeries ODBC driver is supplied as a free component of iSeries Access.
2. You can create a user DSN (a connection that is available only to the user that created it) or a system DSN (a connection that is available to all users on the system). To create a user DSN, log on to the system as the user that you will be using for the Replicat process.
3. From the Windows Control Panel, select Administrative Tools, then Data Sources (ODBC).
4. On the first page of the ODBC configuration tool, select the User DSN tab to create a user DSN or the System DSN tab to create a system DSN. (These steps create a user DSN; creating a system DSN is similar.)
5. On the tab that you selected, click Add.
6. Select the appropriate iSeries Access ODBC Driver from the list of drivers, and then click Finish.
7. On the General tab of the DB2 for i Access for Windows ODBC Setup dialog, provide a name (without any spaces) in the Data Source Name field, add an optional description in the Description field, and then select the system name from the System selection list.
8. On the Server tab, set Naming Convention to SQL Naming Convention (*SQL). Leave the other fields set to their defaults.
9. On the Data Types tab, select the Report as Supported check box under Double Byte Character Set (DBCS) graphic data types.
10. On the Conversions tab, clear the Convert binary data (CCSID 65535) to text check box.
11. Click Apply, then OK. You are returned to the ODBC Data Source Administrator dialog.
12. Confirm that the new Data Source Name appears under User Data Sources.
13. Click OK to exit the ODBC configuration utility.
14. From the Oracle GoldenGate directory on the target, run GGSCI and issue the DBLOGIN command to log into the target database. See Reference for Oracle GoldenGate for detailed syntax.

    DBLOGIN SOURCEDB database, USERID db_user [, PASSWORD pw [encryption_options]]
    

    Where:

    • SOURCEDB database specifies the new data source name.

    • USERID db_user, PASSWORD pw are the Replicat database user profile and password.

    • encryption_options is optional password encryption.

      Note:

      Only BLOWFISH encryption is supported for DB2 for i systems.