2 Preparing the System for Oracle GoldenGate

This chapter describes how to prepare the environment to run Oracle GoldenGate on DB2 LUW.

Topics:

• Configuring the Transaction Logs for Oracle GoldenGate
  
• Preparing Tables for Processing
  
• Database Configuration for DB2 LUW
  
• Database User for Oracle GoldenGate Processes for DB2 LUW
  
• Setting the Session Character Set
  
• Preparing for Initial Extraction
  
• Specifying the DB2 LUW Database in Parameter Files
  

Configuring the Transaction Logs for Oracle GoldenGate

To capture DML operations, Oracle GoldenGatereads the DB2 LUW online logs by default. However, it reads the archived logs if an online log is not available. To ensure the continuity and integrity of Oracle GoldenGateprocessing, configure the logs as follows.

• Retaining the Transaction Logs
  
• Specifying the Archive Path
  

Retaining the Transaction Logs

Configure the database to retain the transaction logs for roll forward recovery by enabling one of the following parameter sets, depending on the database version.

• DB2 LUW 9.5 and later:

  Set the LOGARCHMETH parameters as follows:

  • Set LOGARCHMETH1 to LOGRETAIN.

  • Set LOGARCHMETH2 to OFF.

  Alternatively, you can use any other LOGARCHMETH options, as long as forward recovery is enabled. For example, the following is valid:

  • Set LOGARCHMETH1 to DISK.

  • Set LOGARCHMETH2 to TSM.

To determine the log retention parameters:

1. Connect to the database.

   db2 connect to database user username using password 
   

2. Get the database name.

   db2 list db directory 
   

3. Get the database configuration for the database.

   db2 get db cfg for database 
   

   The fields to view are:

   Log retain for recovery status = RECOVERY 
   User exit for logging status = YES 
   

To set the log retention parameters:

1. Issue one of the following commands.

   To enable USEREXIT:

   db2 update db cfg for database using USEREXIT ON 
   

   If not using USEREXIT, use this command:

   db2 update db cfg for database using LOGRETAIN RECOVERY 
   

   To set LOGARCHMETH:

   db2 update db cfg for database using LOGARCHMETH1 LOGRETAIN 
   db2 update db cfg for database using LOGARCHMETH2 OFF 
   

2. Make a full backup of the database by issuing the following command.

   db2 backup db database to device
   

3. Place the backup in a directory to which DB2 LUW has access rights. If you get the following message, contact your systems administrator:

   SQL2061N An attempt to access media "device" is denied. 
   

Specifying the Archive Path

Set the DB2 LUW OVERFLOWLOGPATH parameter to the archive log directory. The node attaches automatically to the path variable that you specify.

db2 connect to database
db2 update db cfg using overflowlogpath "path" 

Exclude the node itself from the path. For example, if the full path to the archive log directory is /sdb2logarch/oltpods1/archive/OLTPODS1/NODE0000, then the OVERFLOWLOGPATH value should be specified as /sdb2logarch/oltpods1/archive/OLTPODS1.

Preparing Tables for Processing

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

• Triggers and Cascade Constraints Considerations
  
• Assigning Row Identifiers
  
• Preventing Key Changes
  
• Enabling Change Capture
  
• Maintaining Materialized Query Tables
  

Triggers and Cascade Constraints Considerations

Triggers

Disable triggers on the target tables, or alter them to ignore changes made by the Oracle GoldenGate database user. Oracle GoldenGate replicates DML that results from a trigger. If the same trigger gets activated on the target table, then it becomes redundant because of the replicated version, and the database returns an error.

Cascade Constraints Considerations

Cascading updates and deletes captured by Oracle GoldenGate are not logged in binary log, so they are not captured. This is valid for both MySQL and MariaDB. For example, when you run the delete statement in the parent table with a parent child relationship between tables, the cascading deletes (if there are any) happens for child table, but they are not logged in binary log. Only the delete or update record for the parent table is logged in the binary log and captured by Oracle GoldenGate.

See https://mariadb.com/kb/en/replication-and-foreign-keys/ and https://dev.mysql.com/doc/refman/8.0/en/innodb-and-mysql-replication.html for details.

To properly handle replication of cascading operations, it is recommended to disable cascade deletes and updates on the source and code your application to explicitly delete or update the child records prior to modifying the parent record. Alternatively, you must ensure that the target parent table has the same cascade constraints configured as the source parent table, but this could lead to an out-of-sync condition between source and target, especially in cases of bi-directional replication.

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

Do not add columns to a key after Oracle GoldenGate starts extracting data from the table. This rule applies to a primary key, a unique key, a KEYCOLS key, or an all-column key. DB2 LUW does not supply a before image for columns that are added to a table. If any columns in a key are updated on the source, Oracle GoldenGate needs a before image to compare with the current values in the target table when it replicates the update.

Enabling Change Capture

Configure DB2 to log data changes in the expanded format that is supplied by the DATA CAPTURE CHANGES feature of the CREATE TABLE and ALTER TABLE commands. This format provides Oracle GoldenGate with the entire before and after images of rows that are changed by update statements. You can use GGSCI to issue the ALTER TABLE command as follows.

To Enable Change Capture from GGSCI:

1. From the Oracle GoldenGate directory, run GGSCI.
2. Log on to DB2 from GGSCI as a user that has ALTER TABLE privileges. Specify the data source name with SOURCEDB and specify the user login with USERID and PASSWORD.

   DBLOGIN SOURCEDB dsn, USERID user[, PASSWORD password] 
   

3. Issue the following command. where owner.table is the fully qualified name of the table. You can use a wildcard to specify multiple table names. Only the asterisk (*) wildcard is supported for DB2 LUW.

   ADD TRANDATA owner.table
   

   ADD TRANDATA issues the following command, which includes logging the before image of LONGVAR columns:

   ALTER TABLE name DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS; 
   

Example 2-1 To Exclude LONGVAR Logging:

To omit the INCLUDE LONGVAR COLUMNS clause from the ALTER TABLE command, use ADD TRANDATA with the EXCLUDELONG option.

ADD TRANDATA owner.table, EXCLUDELONG 

Note:

If LONGVAR columns are excluded from logging, the Oracle GoldenGate features that require before images, such as the GETUPDATEBEFORES, NOCOMPRESSUPDATES, and NOCOMPRESSDELETES parameters, might return errors if tables contain those columns. For a workaround, see the REQUIRELONGDATACAPTURECHANGES | NOREQUIRELONGDATACAPTURECHANGES options of the TRANLOGOPTIONS parameter.

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.

Database Configuration for DB2 LUW

• The Oracle GoldenGate Extract process calls the DB2READLOG function in the Administrative API to read the transaction log files of a DB2 LUW source database. In addition to DB2READLOG, Extract uses a small number of other API routines to check the source database configuration on startup.

• The Oracle GoldenGate Replicat process uses the DB2 CLI interface on a DB2 LUW target database. For instructions on installing this interface, see the DB2 documentation.

• The database can reside on a different server from the one where Oracle GoldenGate is installed, so long as the database is defined locally. For example, the following enables you to use database mydb locally with data that is on abc123:

  catalog tcpip node abc123 remote abc123.us.mycompany.com server 00000
  catalog db mydb as abc123 at node abc123 AUTHENTICATION server

• The DB2 Universal Database has an internal trace facility called db2trc, which acquires Interprocess Communication resources (IPC) (both semaphore and shared memory). Even though a DB2 trace is not turned on, it may issue semget() calls to the operating system. These calls fail since no IPC resources are acquired so you must issue the following command on the DB2 client:

  db2trc alloc 

• For best performance for DB2 clients with a local database, Oracle recommends that you create a local node catalog instead of TCP/IP when connecting Oracle GoldenGate to a database that resides on the same machine. This is because local node uses IPC, which is much faster than a TCP/IP node that uses a socket API to access the local database.

Database User for Oracle GoldenGate Processes for DB2 LUW

• Create a database user that is dedicated to Oracle GoldenGate. It can be the same user for all of the Oracle GoldenGate processes that must connect to a database:

  • Extract (source database)

  • Replicat (target database)

  • DEFGEN (source or target database)

• To preserve the security of your data, and to monitor Oracle GoldenGate processing accurately, do not permit other users, applications, or processes to log on as, or operate as, the Oracle GoldenGate database user. It is recommended that you store the login credentials in an Oracle GoldenGate credential store. The credential store makes use of local secure storage for the login names and passwords, and permits you to specify only an alias in the Oracle GoldenGate parameter files.

• Assign system administrator (SYSADM) or database administrator (DBADM) authority to the database user under which Extract runs. To give the Extract user DBADM authority, a user with SYSADM authority can issue the following grant statement.

  GRANT DBADM ON DATABASE TO USER user
  

  This authority can also be granted from the User and Group Objects folder in the DB2 Control Center. The database tab for the user that is assigned to an Oracle GoldenGate process should have the Database Administrative Authority box checked.

  Note:

  If the Extract user does not have the required authority, Extract will log the following errors and stop.

  [SC=-1224:SQL1224N A database agent could not be started to 
  service a request, or was terminated as a result of a database 
  system shutdown or a force command. 
  SQL STATE 55032: The CONNECT statement is invalid, because the 
  database manager was stopped after this application was started]
  

• Grant at least the following privileges to the database user under which Replicat runs:

  • Local CONNECT to the target database

  • SELECT on the system catalog views

  • SELECT, INSERT, UPDATE, and DELETE on the target tables

Setting the Session Character Set

To support the conversion of character sets between the source and target databases, make certain that the session character set is the same as the database character set. You can set the session character set with the DB2CODEPAGE environment variable.

Preparing for Initial Extraction

During the initialization of the Oracle GoldenGate environment, you will be doing an initial data synchronization and starting the Oracle GoldenGate processes for the first time. In conjunction with those procedures, you will be creating process groups. To create an Extract group, an initial start position must be established in the transaction log. This initial read position is on a transaction boundary that is based on one of the following:

• End of the transaction file

• A specific LRI value

The start point is specified with the BEGIN option of the ADD EXTRACT command.

When the Extract process starts for the first time, it captures all the transaction data that it encounters after the specified start point, but none of the data that occurred before that point. This can cause partial transactions to be captured if open transactions span the start point.

To ensure initial transactional consistency:

To avoid the capture of partial transactions, initialize the Extract process at a point in time when the database is in a paused state. DB2 LUW provides a QUIESCE command for such a purpose. This is the only way to ensure transactional consistency.

Note:

After the Extract is past the initialization, subsequent restarts of the Extract do not extract partial transactions, because the process uses recovery checkpoints to mark its last read position.

To view open transactions:

IBM provides a utility called db2pd for monitoring DB2 databases and instances. You can use it to view information about open transactions and to determine if any of them span the start point. However, because DB2 LUW log records lack timestamps, it might not be possible to make an accurate assessment. If possible, quiesce the database prior to initialization of Oracle GoldenGate.

For more information on initializing the Oracle GoldenGate environment, see Instantiating Oracle GoldenGate with an Initial Load in Administering Oracle GoldenGate.

Specifying the DB2 LUW Database in Parameter Files

For an Oracle GoldenGate process to connect to the correct DB2 LUW database, you must specify the name (not an alias) of the DB2 LUW database with the following parameters:

• Specify the DB2 source database with the Extract parameter SOURCEDB.

• Specify the DB2 target database name with the Replicat parameter TARGETDB.

For more information about these parameters, see the Reference for Oracle GoldenGate for Windows and UNIX.