ADD TRANDATA

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.

ADD TRANDATA is valid for the databases that are listed here:

  • Db2 for i

  • Db2 LUW

  • Db2 z/OS

  • Oracle

  • PostgreSQL

  • SQL Server

  • Sybase

For other supported databases, this functionality may exist already or must be configured through the database interface.

See the following tables to know about the use of wildcards for different databases when using ADD TRANDATA:

Database Wildcard Use
Db2 LUW, Db2 z/OS Wildcard can be used with table names only. Not schema names.
Oracle Wildcard can be used with schema and table names. Not PDB name.
PostgreSQL and SQL Server Wildcard can be used with schema and table names

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

Oracle GoldenGate 23ai (23.6) for Oracle Database 23ai and higher support replication for JSON Relational Duality Views and JSON Collection Tables. The ADD TRANDATA command enables supplemental logging for JSON Relational Duality Views and JSON Collection Tables. However, ADD TRANDATA does not impact JSON Duality View base tables and will not add tranactional data for base tables. For details, see Prerequisites for Setting Up Replication for JSON DV and JSON Collection Tables in Oracle GoldenGate Microservices Documentation.

Note that using the command similar to the following, will only apply to tables and NOT to JSON Relational Duality Views and JSON Collection Tables:
ADD TRANDATA HR.*
To capture from JSON Duality Views and JSON Collection Tables, you need to run the ADD TRANDATA command for each JSON Duality View and JSON Collection Table that needs to be replicated. This ensures that you are aware of the JSON Duality Views and JSON Collection Tables that are being set up for replication to avoid system overload.

Note:

Wildcards are not supported when enabling logical replication for JSON Duality Views using ADD TRANDATA. However, JSON Collection Tables support wildcards just like regular tables, therefore, the TABLE/MAP statement also allow use of wildcards.

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.

Note:

It is possible to use ADD TRANDATA for Oracle when DDL support is enabled, but only if you can stop DML on all tables before DDL is performed on them or, if that is not possible, you can guarantee that no users or applications will issue DDL that adds new tables whose names satisfy an object specification in a TABLE or MAP statement. There must be no possibility that users or applications will issue DDL that changes the key definitions of any tables that are already in the Oracle GoldenGate configuration.

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.

Syntax

ADD TRANDATA container.owner.table]
[, FILEGROUP filegroup-name]
[, NOSCHEDULINGCOLS | ALLCOLS] 
[, ALLOWNONVALIDATEKEYS]
[, PARTIALJSON]
[, 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.

If you run the command from cdb$root, make sure that you also set the following user privilege on the database side:
ALTER USER userID SET CONTAINER_DATA = ALL  CONTAINER = CURRENT;
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 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 the command line considers the default FILEGROUP of the database while adding TRANDATA 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.

PARTIALJSON

Valid for Oracle.

Fetches partial JSON updates at the table level. If enabled, the redo will include partial JSON records.

Also see, TRANLOGOPTIONS FETCHPARTIALJSON parameter.

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