1. Using Oracle GoldenGate Classic Architecture for Non-Oracle Databases
  2. Using Oracle GoldenGate for DB2 for i
  3. Configuring Oracle GoldenGate for DB2 for i

6 Configuring Oracle GoldenGate for DB2 for i

This chapter contains instructions for configuring Oracle GoldenGate to capture source DB2 for i data and apply it to a supported target database.

Topics:

Parent topic: Using Oracle GoldenGate for DB2 for i

What to Expect from this Procedure

These instructions show you how to configure a set of basic Oracle GoldenGate parameter (configuration) files, one for each process that replicates transactional data changes from a DB2 for i source to a DB2 for i target, or to a different database type. Your business requirements probably will require a more complex topology, but this procedure forms a basis for the rest of your configuration steps.

This chapter focuses on the basic parameters that are specific to DB2 for i.

By performing these steps, you can:

  • get the basic configuration files established.

  • build upon them later by adding more parameters as you make decisions about features or requirements that apply to your environment.

  • use copies of them to make additional parameter files faster than starting from scratch.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Getting Started with Oracle GoldenGate

Before proceeding with the configuration process, you should get familiar with the Oracle GoldenGate architecture, the command interface, and the methods for supplying input and instructions to the processes. See Administering Oracle GoldenGate for this information.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Creating the Oracle GoldenGate Instance

Each Oracle GoldenGate installation is rooted in the Manager process. This is the controller process that instantiates the Oracle GoldenGate processes, allocates port numbers, and performs file maintenance. Together, the Manager process and its child processes, and their related programs and files comprise an Oracle GoldenGate instance.

To run Oracle GoldenGate, a Manager process must be running on all systems that will be part of the Oracle GoldenGate environment. To run Manager, you first create a parameter file for it.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Creating a GLOBALS File

The GLOBALS parameter file contains parameters that affect all processes within an Oracle GoldenGate instance.

GGSCHEMA is a mandatory parameter for Oracle GoldenGate 21c (21.3.0) onwards and defines the schema, which Oracle GoldenGate uses on the remote system for necessary Oracle GoldenGate database objects.

The GLOBALS parameter NAMECCSID is specific to DB2 for i and may be required, if the SQL catalog contains object names that are referenced by a different CCSID than the system CCSID. The SQL catalog is created in the system CCSID and does not indicate this difference when queried. Oracle GoldenGate makes queries to the catalog and could retrieve the name incorrectly unless NAMECCSID is used to supply the correct CCSID value. For more information, see Reference for Oracle GoldenGate.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Creating a Data Definitions File

When replicating data from one table to another, an important consideration is whether the column structures (metadata) of the source and target tables are identical. Oracle GoldenGate looks up metadata for the following purposes:

Note:

This is only required when writing trails for Oracle GoldenGate 11.2 or earlier.

  • On the source, to supply complete information about captured operations to the Replicat process.

  • On the target, to determine the structures of the target tables, so that the replicated data is correctly mapped and converted (if needed) by Replicat.

When source and target table definitions are dissimilar, Oracle GoldenGate must perform a conversion from one format to the other. To perform conversions, both sets of definitions must be known to Oracle GoldenGate. Oracle GoldenGate can query the local database to get one set of definitions, but it must rely on a data-definitions file to get definitions from the remote database. The data-definitions file contains information about the metadata of the data that is being replicated.

To create a definitions file, you configure and run the DEFGEN utility and then transfer the definitions file to the target system. This file must be in place on the target system before you start the Oracle GoldenGate processes for the first time.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Enabling SSL

SSL connections can be enabled by setting SSL=1 in the DSN configuration file. To know about how to set up an SSL connection with IBM i Access ODBC Driver, see Make SSL ODBC connections from Linux to Db2 for i and ACS ODBC driver for Linux now supports OpenSSL.

It is recommended to use OpenSSL to setup SSL. After SSL is enabled in the DSN configuration file, JAVA connections that have been established using jt400 libraries will also be using SecureAS400 connections as well.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

User Profiles and Security Privileges

The user who installs Oracle GoldenGate must have read and write privileges on the Oracle GoldenGate installation directory, as these privileges will be required later to perform steps to create sub-folders and run specific programs.

In addition, this user must have the following rights to ensure smooth installation of Oracle GoldenGate on Db2 for i:

  • The*ALLOBJ special authority (required only for installation/upgrade or HEARTBEAT ADD/DELETE commands)

    Note:

    For any new Oracle GoldenGate installation or an upgrade to the latest release version, the user or group profile to be used for the installation, needs to run the DBLOGIN command from GGSCI just once to complete the installation of the new objects for the Oracle Golden Gate installation on the IBM i system.

    Note:

    The objects in the Oracle GoldenGate library (specified with GGSCHEMA), should have their ownership changed to the dedicated user profile for Oracle GoldenGate. If the user does not have the *ALLOBJ authority, then any other user with the *ALLOBJ authority needs to sign-in to the Db2 for i system, and change the ownership of the objects to the user or group profile to be used for Oracle GoldenGate installation.

  • Authority to the RSTOBJ command (typically available with *ALLOBJ)

  • Ability to create a library, if required

Dedicated User Profile Account

It is recommended that the Oracle GoldenGate processes on Db2 for i database be assigned a dedicated user or group profile, and is used by all Oracle GoldenGate processes. This user profile should not be used by any other application(s).

The dedicated user profile should be granted permission only to the objects that the Oracle GoldenGate will be operating on. If there is specific change data that is not to be accessed by Oracle GoldenGate processes, then such change data should not be included in the journals, which are accessed by Oracle GoldenGate and its dedicated user profile. All Oracle GoldenGate processes must have read, write, and delete object privileges within the Oracle GoldenGate installation library, as specified by GGSCHEMA.

Security Privileges on a Db2 for i System

The Extract and Replicat user profiles need to be assigned the following authorities at a minimum:

  • The simplest way to ensure Oracle GoldenGate will be able to operate is to assign *ALLOBJ authority to the Oracle GoldenGate user profile(s), however this is not necessary.

  • The Manager process must have privileges to control all other Oracle GoldenGate processes (DB2 for i *JOBCTL authority).

  • The Oracle GoldenGate user profiles(s) need at least the *USE authority to the*FILE objects in the QSYS2 library which contains the SQL catalog (which by default should be accessible to any user).

  • Assign at least the *USE authority (*OBJOPR, *READ, *EXECUTE) to all the *FILE (table) and *JRNRCV (journal receiver) objects on the system that are accessed by the Extract user profile.

  • Assign the following authorities to the *JRN (journal) objects that are accessed by the Extract user profile, in addition to the *USE authority (*OBJOPR, *READ, *EXECUTE): *OBJEXIST,*OBJREF, and *ADD.

  • Assign the *CHANGE authority to all the *FILE objects on the system that are accessed by the Replicat user profile.

The Oracle GoldenGate user profile that runs the Extract process needs to have the *USE authority on the QSYS/QPMLPMGT service program.

These authorities must be granted through the native DB2 for i interface through a 5250 terminal session or through the DB2 for i Navigator product available from IBM.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Encrypting the Extract and Replicat Passwords

It is strongly recommended that you encrypt the passwords of the user profiles that will be used for the primary and data pump Extracts, and for the Replicat process. The standard Oracle GoldenGate encryption method of AES (Advanced Encryption Standard) is supported by the IBM i platform. To encrypt the password, see Working with Runtime Parameters in Administering Oracle GoldenGate. It also contains information about how to encrypt data within disk storage and across TCP/IP.

Note:

The Oracle GoldenGate credential store is not supported by the iSeries platform.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Configuring Extract for Change Capture from DB2 for i

Perform these steps on the source system to configure the primary Extract and the data pump Extract that support change capture and transport across the network.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Configuring the Primary Extract

These steps configure the primary Extract to capture transaction data from a source DB2 for i and write the data to a local trail for temporary storage.

  1. In GGSCI on the source system, create the Extract parameter file.
    EDIT PARAMS name

    Where: name is the name of the primary Extract.

  2. Enter the Extract parameters in the order shown, starting a new line for each parameter statement.

    Basic parameters for the primary Extract

    EXTRACT finance
SOURCEDB mysource, USERIDALIAS myalias 
ENCRYPTTRAIL AES192
EXTTRAIL /ggs/dirdat/lt
TABLE hr.*;
    Parameter Description 
    EXTRACT group

    group is the name of the Extract group. 

    SOURCEDB database, USERIDALIAS alias

    Specifies the real name of the source DB2 for i database (not an alias), plus the alias of the database login credential of the user that is assigned to Extract. This credential must exist in the Oracle GoldenGate credential store. For more information, see Database User for Oracle GoldenGate Processes. 

    ENCRYPTTRAIL algorithm

    Encrypts the local trail. 

    EXTTRAIL pathname

    Specifies the path name of the local trail to which the primary Extract writes captured data for temporary storage.

    		 
    TABLE schema.object;
TABLE library/file;
TABLE library/file(member);

    Specifies the database object for which to capture data.

    TABLE is a required keyword.

    schema is the schema name or a wildcarded set of schemas.

    object is the table name, or a wildcarded set of tables.

    library is the IBM i library name or a wildcarded set of libraries.

    file is the IBM i physical file name or a wildcarded set of physical files.

    member is the IBM i physical file member name or a wildcarded set of member names.

    When using the IBM i native name format (library/file with optional member) the only valid wildcards are a name with at least one valid character followed by a trailing asterisk (*) or *ALL which matches any name.

    Note:

    The member name is optional, and must be provided if the member names are required to be written in the trail as part of the object name. Without member names all members in a physical file be implicitly merged as a single object in the trail.

    Terminate the parameter statement with a semi-colon.

    To exclude tables from a wildcard specification, use the TABLEEXCLUDE parameter.

  3. Enter any optional Extract parameters that are recommended for your configuration. You can edit this file at any point before starting processing by using the EDIT PARAMS command in GGSCI.
  4. Save and close the file.

Parent topic: Configuring Extract for Change Capture from DB2 for i

Configuring the Data Pump

These steps configure the data pump that reads the local trail and sends the data across the network to a remote trail.

  1. In GGSCI on the source system, create the data-pump parameter file.
    EDIT PARAMS name

    Where: name is the name of the data pump Extract group.

  2. Enter the data-pump parameters in the order shown, starting a new line for each parameter statement. Your input variables will be different. See the following table for description.

    Basic parameters for the data-pump Extract group:

    EXTRACT extpump
SOURCEDB FINANCE USERID ogg, PASSWORD AACAAAAAAAAAAA, BLOWFISH ENCRYPTKEY mykey
RMTHOST fin1, MGRPORT 7809
RMTTRAIL /ggs/dirdat/rt
TABLE hr.*;
    Parameter Description 
    EXTRACT group

    group name is the name of the data pump. 

    SOURCEDB database USERID user, PASSWORD password, BLOWFISH ENCRYPTKEY keyname

    Specifies database connection information.

    • SOURCEDB specifies the default DB 2 for i database.

    • USERID specifies the Extract database user profile.

    • PASSWORD specifies the user's password that was encrypted with the ENCRYPT PASSWORD command. Enter or paste the encrypted password after the PASSWORD keyword.

    • BLOWFISH ENCRYPTKEY keyname specifies the name of the lookup key in the local ENCKEYS file. 

    DECRYPTTRAIL BLOWFISH

    Decrypts the input trail.

    		 
    RMTHOST hostname, MGRPORT portnumber

    • RMTHOST specifies the name or IP address of the target system.

    • MGRPORT specifies the port number where Manager is running on the target. 

    ENCRYPTTRAIL BLOWFISH

    Encrypts the remote trail with Blowfish encryption.

    		 
    RMTTRAIL pathname

    Specifies the path name of the remote trail.

    		 
    TABLE schema.object;
TABLE library/file;
TABLE library/file(member);

    Specifies a table or tables to process.

    Terminate the TABLE statement with a semi-colon.

    To exclude tables from a wildcard specification, use the TABLEEXCLUDE parameter after the TABLE statement.

  3. Enter any optional Extract parameters that are recommended elsewhere in this manual and any others shown in Summary of Extract Parameters in Reference for Oracle GoldenGate.
  4. Save and close the file.

Parent topic: Configuring Extract for Change Capture from DB2 for i

Configuring Replicat for Change Delivery to DB2 for i

These steps configure Replicat to apply data to a DB2 for i target database, operating on a remote Linux system. To configure Replicat for change delivery to a different database type, such as an Oracle database, follow the directions in the Oracle GoldenGate Installation and Configuration guide for that database. There may be additional parameters and requirements for delivery to that database type.

Note:

There does not have to be a database on a Windows or Linux machine to support connection by ODBC by Replicat.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Creating a Checkpoint Table

Replicat maintains its checkpoints in a checkpoint table in the DB2 for i target database. Each checkpoint is written to the checkpoint table, that must be journaled, within the Replicat transaction. Because a checkpoint either succeeds or fails with the transaction, Replicat ensures that a transaction is only applied once, even if there is a failure of the process or the database.

A common method of create the checkpoint table with journaling is as follows:

  1. In GGSCI on the target system, create the Replicat checkpoint file.

    Set the name of the journal that is intended to be used for the checkpoint file. The default will be <GGSCHEMA>/OGGJRN. You can change it by setting the default for the current GGSCI session using the DEFAULTJOURNAL command. The syntax of the DEFAULTJOURNAL command is: 

    DEFAULTJOURNAL library_name/journal_name

    Where: library_name is the name of the library and journal_name is the name of the journal to be used for subsequent operations that may optionally have a journal specified..

  2. Add the checkpoint table.
    ADD CHECKPOINTTABLE library_name.chkptab 

Successfully created checkpoint table kgr.chkptab
  3. Add journaling to the checkpoint table. 
    ADD TRANDATA library_name.CHKPTAB

For more information about creating a checkpoint table, see Administering Oracle GoldenGate.

Parent topic: Configuring Replicat for Change Delivery to DB2 for i

Configuring Replicat

These steps configure the Replicat process in a basic way without any special mapping or conversion of the data.

  1. In GGSCI on the target system, create the Replicat parameter file.
    EDIT PARAMS name

    Where: name is the name of the Replicat group.

  2. Enter the Replicat parameters in the order shown, starting a new line for each parameter statement.
    REPLICAT financer
TARGETDB FINANCIAL USERID ogg, PASSWORD AACAAAAAAAAAAA, BLOWFISH ENCRYPTKEY mykey
DISCARDFILE /users/ogg/disc
MAP hr.*, TARGET hr2.*;
    Parameter Description 
    REPLICAT group

    group is the name of the Replicat group. 

    TARGETDB database USERID user, PASSWORD password, BLOWFISH ENCRYPTKEY keyname

    Specifies database connection information.

    • SOURCEDB specifies the data source name (DSN) of the target DB2 for i database.

    • USERID specifies the Replicat database user profile.

    • PASSWORD specifies the user's password that was encrypted with the ENCRYPT PASSWORD command. Enter or paste the encrypted password after the PASSWORD keyword.

    • BLOWFISH ENCRYPTKEY keyname specifies the name of the lookup key in the local ENCKEYS file. 

    DECRYPTTRAIL BLOWFISH

    Decrypts the input trail.

    		 
    MAP owner.table, TARGET owner.table;
MAP owner.table, TARGET library/file;
MAP library/file, TARGET owner.table;
MAP library/file, TARGET library/file;

    Specifies a relationship between a source and target table or tables. The MAP clause specifies the source objects, and the TARGET clause specifies the target objects to which the source objects are mapped.

    • owner is the schema name.

    • table is the name of a table or a wildcard definition for multiple tables.

    • library is the IBM i library name or a wildcard definition for multiple libraries.

    • file is the IBM i physical file name or a wildcard definition for multiple physical files.

    Note:

    There is an optional physical file member name also allowed with the physical file of the form file(member) and member may also be a wildcard definition for multiple members.

    To exclude tables from a wildcard specification, use the MAPEXCLUDE parameter.

    For more information and for additional options that control data filtering, mapping, and manipulation, see MAP in Reference for Oracle GoldenGate.

  3. Enter any optional Extract parameters that are recommended elsewhere in this manual and any others shown in Summary of Extract Parameters.
  4. Save and close the file.

Parent topic: Configuring Replicat for Change Delivery to DB2 for i

Next Steps in the Deployment

Because of its flexibility, Oracle GoldenGate offers numerous features and options that must be considered before you start any processes. To further configure Oracle GoldenGate to suit your business needs, see the following:

  • For additional configuration guidelines to achieve specific replication topologies, see Administering Oracle GoldenGate. This guide also contains information about:

    • Oracle GoldenGate architecture

    • Oracle GoldenGate commands

    • Oracle GoldenGate initial load methods

    • Using customization features

    • Mapping columns that contain dissimilar data

    • Data filtering and manipulation

  • For syntax options and descriptions of Oracle GoldenGate GGSCI commands and Oracle GoldenGate parameters shown in this guide, see Reference for Oracle GoldenGate.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

When to Start Replicating Transactional Changes

You must start replication when the source and target data is in a synchronized state, where the corresponding rows in the source and target tables contain identical data values. Unless you are starting with brand new source and target databases with no current user activity, you will need to activate change capture and apply processes to handle ongoing transactional changes while an initial load is being applied to the target. This process is known as initial synchronization, or also as instantiation. The initial load captures a point-in-time snapshot of the source data and applies it to the target, while Oracle GoldenGate maintains any changes that are made after that point.

See Instantiating Oracle GoldenGate with an Initial Load in Administering Oracle GoldenGate for instantiation options.

Parent topic: Configuring Oracle GoldenGate for DB2 for i

Starting Extract During Instantiation

When Extract starts for the first time to begin capturing data during the instantiation process, it captures all of the transaction data that it encounters after the specified start point, but none of the data that occurred before that point. To ensure that Extract does not start in the middle of ongoing transactions that would be discarded, set the tables that are to be captured to an inactive state. You can either put the system into a restricted state by using the ALCOBJ command to lock the objects or libraries, or you can force all of the current transactions on those tables to stop at a certain point.

After initialization is complete, remember to unlock any objects that you locked. To do so, log off of the session that locked the objects or use the DLCOBJ command from the OS/400 command line.

Parent topic: When to Start Replicating Transactional Changes

Changing the Position of Extract to a Later Time

You may at some point, over the life of an Extract run, need to set the position of Extract in the data stream manually. To reposition Extract, use the ALTER EXTRACT command in GGSCI. To help you identify any given Extract read position, the INFO EXTRACT command shows the positions for each journal in an Extract configuration, including the journal receiver information. See Reference for Oracle GoldenGate to know more.

Parent topic: When to Start Replicating Transactional Changes

Testing Your Configuration

It is important to test your configuration in a test environment before deploying it live on your production machines. This is especially important in an active-active or high availability configuration, where trusted source data may be touched by the replication processes. Testing enables you to find and resolve any configuration mistakes or data issues without the need to interrupt user activity for re-loads on the target or other troubleshooting activities.

Parent topic: Configuring Oracle GoldenGate for DB2 for i