Use ADD TRANDATA to enable Oracle GoldenGate to acquire the transaction information that it needs from the transaction records.

Before using this command, use the DBLOGIN command to establish a database connection.

For other supported databases, this functionality may exist already or must be configured through the database interface. See Installing Oracle GoldenGate for any special requirements that apply to making transaction information available.

Db2 for i

Use ADD TRANDATA to start the journaling of data. 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.

Db2 LUW

Use ADD TRANDATA to enable DATA CAPTURE CHANGES on specified tables. By default, ADD TRANDATA issues the following command to the database:

ALTER TABLE name DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;

You can exclude the LONGVAR clause by using ADD TRANDATA with the EXCLUDELONG option.

Db2 z/OS

Use ADD TRANDATA to enable DATA CAPTURE CHANGES on specified tables. By default, ADD TRANDATA issues the following command to the database:

ALTER TABLE name DATA CAPTURE CHANGES;

Oracle Database

From the 21c release onward, this command would also enable a table for auto capture for Oracle database.

By default, ADD TRANDATA for Oracle enables the unconditional logging of the primary key and the conditional supplemental logging of all unique key(s) and foreign key(s) of the specified table, see Ensuring Row Uniqueness in Source and Target Tables for more information about how Oracle GoldenGate handles supplemental logging for Oracle Databases.

If possible, use the ADD SCHEMATRANDATA command rather than the ADD TRANDATA command. The ADD SCHEMATRANDATA command ensures replication continuity should DML occur on an object for which DDL has just been performed. You can exclude objects from the schema specification by using the exclusion parameters.

To use the Oracle GoldenGate DDL replication feature, you must use the ADD SCHEMATRANDATA command to log the required supplemental data.

When using ADD SCHEMATRANDATA, you can use ADD TRANDATA with the COLS option to log any non-key columns, such as those needed for FILTER statements and KEYCOLS clauses in the TABLE and MAP parameters.

Oracle recommends putting the source database into forced logging mode and enabling minimal supplemental logging at the database level when using Oracle GoldenGate. This adds row chaining information, if any exists, to the redo log for update operations

Take the following into account when using ADD TRANDATA for an Oracle database:

• If any of the logging details change after Oracle GoldenGate starts extracting data, you must stop and then start the Extract process that is reading from the affected table before any data is changed.

• When creating a supplemental log group with ADD TRANDATA, Oracle GoldenGate appends the object ID to a prefix of GGS_, for example GGS_18342.

PostgreSQL

Using ADD TRANDATA command, the REPLICA IDENTITY setting of the table is altered. The REPLICA IDENTITY setting controls before images of columns of a table that should be logged to the transaction log for UPDATE and DELETE operations.

SQL Server

Use ADD TRANDATA to provide the extended logging information that Oracle GoldenGate needs to reconstruct SQL operations. The SQL Server transaction log does not provide enough information by default.

By enabling TRANDATA, Oracle GoldenGate enables the SQL Server Change Data Capture feature for the database and creates a Change Data Capture table for each instance enabled with TRANDATA.

GGSCI Syntax

ADD TRANDATA {[container.]owner.table | schema.table [JOURNAL 
library/journal] | 
   library/file [JOURNAL library/journal]} 
[, FILEGROUP filegroup-name]
[, NOSCHEDULINGCOLS | ALLCOLS] 
[, COLS (columns)] 
[, INCLUDELONG | EXCLUDELONG] 
[, NOKEY] 
[, NOVALIDATE]
[, PREPARECSN  {WAIT | LOCK | NOWAIT | NONE}]

[container.]owner.table

Valid for Db2 LUW, Db2 z/OS, Oracle, PostgreSQL, and SQL Server.

The two-part or three-part name specification. Use a two-part name of owner.table for all supported databases except an Oracle multitenant container database. Use a three-part name of container.owner.table for an Oracle multitenant container database. A wildcard can be used for any component. Used with a wildcard, ADD TRANDATA filters out names that match the names of system objects. To use ADD TRANDATA for objects that are not system objects but have names that match those of system objects in a wildcard pattern, issue ADD TRANDATA for those objects without using a wildcard.

schema.table [JOURNAL library/journal] |

library/file [JOURNAL library/journal]

Valid for Db2 for i.

Specifies the SQL schema and name of a table or the native library and file name. If a default journal is set with the DEFAULTJOURNAL command, you can omit the JOURNAL option; otherwise it is required.

FILEGROUP filegroup-name

Valid for SQL Server.

(Optional) You can designate the filegroup in which the SQL Server Change Data Capture staging tables will be placed, by using the FILEGROUP option with an existing filegroup name.

ADD TRANDATA owner.table FILEGROUP cdctables

You can use the FILEGROUP option in the GLOBALS file also if you need to use the same FILEGROUP for each table when enabling TRANDATA.

The following example shows setting the FILEGROUP myFileGroup in GLOBALS file:

FILEGROUP myFileGroup

The GGSCI command is:

ADD TRANDATA dbo.*

The output is:

Logging of supplemental log data is enabled for table dbo.test1 in filegroup 
myFileGroup

In this case, ADD TRANDATA command uses the myFileGroup for all the tables.

If you also use the FILEGROUP parameter with ADD TRANDATA, the command overrides the filegroup name defined in the GLOBALS file.

For example, if you set FILEGROUP myFileGroup in the GLOBALS file and then execute the following ADD TRANDATA command:

ADD TRANDATA dbo.* FILEGROUP yourFileGroup

Then the output is:

Logging of supplemental log data is enabled for table dbo.test1 in filegroup 
yourFileGroup 

In this case, ADD TRANDATA uses the yourFileGroup instead of myFileGroup for all the tables.

If you don't specify the FILEGROUP either in GLOBALS or with ADD TRANDATA, then GGSCI considers the default FILEGROUP of the database while adding TRANADATA of the table. For example, if you run ADD TRANDATA dbo.*, the output is:

Logging of supplemental log data is enabled for table dbo.test1 in filegroup 
PRIMARY

In this case, the default FILEGROUP is Primary. If you run the INFO TRANDATA command, the FILEGROUP name shows as PRIMARY.

NOSCHEDULINGCOLS | ALLCOLS

Valid for Oracle and PostgreSQL.

From Oracle GoldenGate 21c onward, NOSCHEDULINGCOLS is deprecated for Oracle database 21c and higher if the running database supports auto capture capabilities.

These options satisfy the logging requirements of an integrated Replicat that will be processing the tables that you are specifying with ADD TRANDATA.

NOSCHEDULINGCOLS

Valid for Oracle only.

Disables the logging of scheduling columns. By default, ADD TRANDATA enables the unconditional logging of the primary key and the conditional supplemental logging of all unique keys and foreign keys of the specified table. Unconditional logging forces the primary key values to the log whether or not the key was changed in the current operation. Conditional logging logs all of the column values of a foreign or unique key if at least one of them was changed in the current operation. The primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies.

ALLCOLS

Enables the unconditional supplemental logging of all of the key and non-key columns of the table. This option enables the logging of the keys required to compute dependencies, plus all other columns for use in filtering, conflict resolution, or other purposes.

For PostgreSQL, ALLCOLS sets REPLICA IDENTITY for the table to FULL. ALLCOLS is specified as part of the ADD TRANDATA command, to enable logging of all the columns for UPDATE and DELETE operations, even if those columns have not been modified.

For tables without a Primary Key or Unique Index, the ALLCOLS option is redundant. Here's the syntax:

ADD TRANDATA table_name ALLCOLS

COLS (columns)

Valid for Oracle.

Use the COLS option to log specific non-key columns. Can be used to log columns specified in a KEYCOLS clause and to log columns that will be needed for filtering or manipulation purposes, which might be more efficient than fetching those values with a FETCHCOLS clause in a TABLE statement. Separate multiple columns with commas, for example NAME, ID, DOB.

INCLUDELONG | EXCLUDELONG

Valid for Db2 LUW.

Controls whether or not the ALTER TABLE issued by ADD TRANDATA includes the INCLUDE LONGVAR COLUMNS attribute. INCLUDELONG is the default. When ADD TRANDATA is issued with this option, Oracle GoldenGate issues the following statement:

ALTER TABLE name DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;

When EXCLUDELONG is used, the following is the command:

ALTER TABLE name DATA CAPTURE CHANGES;

When EXCLUDELONG is used, Oracle GoldenGate does not support functionality that requires before images of tables that include LONGVAR columns. For example, the GETUPDATEBEFORES parameter. To support this functionality, changes to LONGVAR columns in the transaction logs must include both the before and after images of the column value.

NOKEY

Valid for Db2 for i, Db2 LUW, Db2 z/OS, Oracle.

From Oracle GoldenGate 21c onward, NOKEY is deprecated for Oracle database 21c and higher if the running database supports auto capture capabilities.

Suppresses the supplemental logging of primary key columns. If using NOKEY, use the COLS option to log alternate columns that can serve as keys, and designate those columns as substitute keys by using the KEYCOLS option of the TABLE or MAP parameter.

NOVALIDATE

Valid for Db2 for i, Db2 LUW, Db2 z/OS, Oracle.

Suppresses additional information about the table being handled being processed by ADD TRANDATA. By default, this option is enabled. The additional information processing creates a lapse time on command response so this option can be used to increase response time.

ALLOWNONVALIDATEDKEYS

Valid for Db2 for i, Db2 LUW, Db2 z/OS, Oracle.

It includes NON VALIDATED and NOT VALID primary keys in the supplemental logging. These keys override the normal key selection criteria that is used by Oracle GoldenGate. If the GLOBALS parameter ALLOWNONVALIDATEDKEYS is being used, ADD SCHEMATRANDATA runs with ALLOWNONVALIDATEDKEYS whether or not it is specified. By default, NON VALIDATED and NOT VALID primary keys are not logged.

PREPARECSN {WAIT | LOCK | NOWAIT | NONE}

Valid for Oracle for both DML and DDL. Automatically prepares the tables at the source so the Oracle data pump Export dump file will includes Instantiation CSNs. Replicat uses the per table instantiation CSN set by the Oracle data pump (on import) to filter out trail records. On the target, the data pump import populates the system tables and views with instantiation SCNs using the DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter to enable table-level instantiation filtering.

WAIT

Wait for any in-flight transactions and prepare table instantiation.

LOCK

Put a lock on the table (to prepare for table instantiation).

NOWAIT

Default behavior, preparing for instantiation is done immediately.

NONE

No instantiation preparation occurs.

Examples

The following example causes one of the following: the primary key to be logged for an Oracle table; supplemental data to be logged for a SQL Server or DB2 table. This would also enable the table for auto capture.

ADD TRANDATA hr.employees

The following example enables the unconditional supplemental logging of all of the key and non-key columns for the table named acct. This would also enable the table for auto capture.

ADD TRANDATA hr.employees ALLCOLS

The following Oracle Database example causes the primary key to be logged plus the non-key columns name and address. This would also enable the table for auto capture.

ADD TRANDATA hr.employees, COLS (name, address)

The following Oracle Database example prevents the primary key from being logged, but logs the non-key columns name and pid instead. This would also enable the table for auto capture.

ADD TRANDATA hr.employees, NOKEY, COLS (name, pid)

The following example adds logging although it does not prepare the table for instantiation. This would also enable the table for auto capture.

ADD TRANDATA hr.employees PREPARECSN NONE

The following example suppresses additional table information processing. This would also enable the table for auto capture.

ADD TRANDATA hr.employees.*name NOVALIDATE