2 Preparing the System for Oracle GoldenGate
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
Parent topic: Using Oracle GoldenGate for DB2 LUW
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.
Parent topic: Preparing the System for Oracle GoldenGate
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
toLOGRETAIN
. -
Set
LOGARCHMETH2
toOFF
.
Alternatively, you can use any other
LOGARCHMETH
options, as long as forward recovery is enabled. For example, the following is valid:-
Set
LOGARCHMETH1
toDISK
. -
Set
LOGARCHMETH2
toTSM
.
-
To determine the log retention parameters:
-
Connect to the database.
db2 connect to
database
userusername
usingpassword
-
Get the database name.
db2 list db directory
-
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:
Parent topic: Configuring the Transaction Logs for Oracle GoldenGate
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 todatabase
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.
Parent topic: Configuring the Transaction Logs for Oracle GoldenGate
Preparing Tables for Processing
The following table attributes must be addressed in an Oracle GoldenGate environment.
- Disabling Triggers and Cascade Constraints
- Ensuring Row Uniqueness for Tables
- Preventing Key Changes
- Enabling Change Capture
- Maintaining Materialized Query Tables
Parent topic: Preparing the System for Oracle GoldenGate
Disabling Triggers and Cascade Constraints
Disable triggers, cascade delete constraints, and cascade update constraints on target Teradata tables. Oracle GoldenGate replicates DML that results from a trigger or 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
.
- A delete is issued for
emp_src
. - It cascades a delete to
salary_src
. - Oracle GoldenGate sends both deletes to the target.
- The parent delete arrives first and is applied to
emp_targ
. - The parent delete cascades a delete to
salary_targ
. - The cascaded delete from
salary_src
is applied tosalary_targ
. - The row cannot be located because it was already deleted in step 5.
Parent topic: Preparing Tables for Processing
Ensuring Row Uniqueness for Tables
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.
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 efficientWHERE
clause. -
If a table does not have an appropriate key, or if you prefer that the existing key(s) are not 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 ReplicatMAP
parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.
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.
Parent topic: Ensuring Row Uniqueness for Tables
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.
Parent topic: Preparing Tables for Processing
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:
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.
Parent topic: Preparing Tables for Processing
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
andMAP
statements. -
Do not include MQTs in the
TABLE
andMAP
statements. -
Wildcards can be used in
TABLE
andMAP
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 ExtractTABLE
statement by name will cause Extract to abend.
Parent topic: Preparing Tables for Processing
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 toDB2READLOG
, 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 onabc123
: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 issuesemget()
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.
-
To connect to DB2 LUW remotely from another system, you must use the following driver packages from IBM:
-
IBM Data Server Runtime Client
-
IBM Data Server Driver Package (DS Driver)
-
IBM Data Server Client
The IBM Data Server Driver for ODBC and CLI (CLI Driver) is not supported for DB2 LUW.
-
Parent topic: Preparing the System for Oracle GoldenGate
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 userDBADM
authority, a user withSYSADM
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
, andDELETE
on the target tables
-
Parent topic: Preparing the System for Oracle GoldenGate
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.
Parent topic: Preparing the System for Oracle GoldenGate
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.
Parent topic: Preparing the System for 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.
Parent topic: Preparing the System for Oracle GoldenGate