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:

• What to Expect from this Procedure
  
• Getting Started with Oracle GoldenGate
  
• Creating the Oracle GoldenGate Instance
  
• Creating a GLOBALS File
  
• Creating a Data Definitions File
  
• Encrypting the Extract and Replicat Passwords
  
• Configuring Extract for Change Capture from DB2 for i
  
• Configuring Replicat for Change Delivery to DB2 for i
  
• Next Steps in the Deployment
  
• When to Start Replicating Transactional Changes
  
• Testing Your Configuration
  

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.

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.

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.

Creating a GLOBALS File

The GLOBALS parameter file contains parameters that affect all processes within an Oracle GoldenGate instance. 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.

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:

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

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. Oracle GoldenGate must use Blowfish encryption on the DB2 for i platform. 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.

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.

• Configuring the Primary Extract
  
• Configuring the Data Pump
  

Configuring the Primary Extract

These steps configure the primary Extract to capture transaction data from a source DB2 LUW 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;	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. The question mark (?) wildcard is not supported for this database. Note that only the asterisk (*) wildcard is supported for DB2 LUW. Terminate the parameter statement with a semi-colon. To exclude tables from a wildcard specification, use the TABLEEXCLUDE parameter. For more information and for additional options that control data filtering, mapping, and manipulation, see TABLE | MAP in Reference for Oracle GoldenGate.

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.

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 owner.table;	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 owner.table 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.

Configuring Replicat for Change Delivery to DB2 for i

These steps configure Replicat to apply data to a DB2 for i target database, operating either on the target system or on a remote Windows or 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.

• Creating a Checkpoint Table
  
• Configuring Replicat
  

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.

   DEFAULTJOURNAL library_name/journal_name
   

   Where: library_name is the name of the library and journal_name is the name of the default journal.

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.

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
   ASSUMETARGETDEFS
   -- Instead of ASSUMETARGETDEFS, use SOURCEDEFS if replicating from
   -- DB2 LUW to a different database type, or from a DB2 DB2 LUW source
   -- that is not identical in definitions to a target DB2 LUW database.
   -- SOURCEDEFS /users/ogg/dirdef/defsfile
   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 LUW 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.
   SOURCEDEFS pathname | ASSUMETARGETDEFS	Specifies how to interpret data definitions. Use SOURCEDEFS if the source and target tables have different definitions, such as when replicating data between dissimilar IBM databases or from an IBM database to an Oracle database. For pathname, specify the source data-definitions file that you created with the DEFGEN utility. Use ASSUMETARGETDEFS if the source and target tables are all DB2 LUW and have the same definitions.
   MAP owner.table, TARGET owner.table;	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 or library name. table is the name of a table or a wildcard definition for multiple tables. Terminate the MAP statement with a semi-colon. 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.

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.

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.

• Starting Extract During Instantiation
  
• Changing the Position of Extract to a Later Time
  

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.

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.

Note:

Because the journals can have a transaction split among them, if a given journal is independently repositioned far into the past, the resulting latency from reprocessing the entries may cause the already-read journals to stall until the reading of the latent journal catches up.

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.