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 only for the databases that are listed here:

DB2 for i
DB2 LUW
DB2 z/OS
Oracle
SQL Server
PostgreSQL

For other supported databases, this functionality may exist already or must be configured through the database interface. See the Oracle GoldenGate installation guide for your database 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

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.

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 table enabled with TRANDATA.

Optionally, 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 
Logging of supplemental log data is enabled for table dbo.test2 in filegroup 
myFileGroup 
Logging of supplemental log data is enabled for table dbo.test3 in filegroup 
myFileGroup 
Logging of supplemental log data is enabled for table dbo.test4 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 
Logging of supplemental log data is enabled for table dbo.test2 in filegroup 
yourFileGroup 
Logging of supplemental log data is enabled for table dbo.test3 in filegroup 
yourFileGroup 
Logging of supplemental log data is enabled for table dbo.test4 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 
Logging of supplemental log data is enabled for table dbo.test2 in filegroup 
PRIMARY 
Logging of supplemental log data is enabled for table dbo.test3 in filegroup 
PRIMARY 
Logging of supplemental log data is enabled for table dbo.test4 in filegroup 
PRIMARY

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

PostgreSQL

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

Admin Client Syntax

ADD TRANDATA container.owner.table]
[, NOSCHEDULINGCOLS | ALLCOLS] 
[, ALLOWNOVALIDATEKEYS]
[, PREPARECSN  {WAIT | LOCK | NOWAIT | NONE}]

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]
[, KEYCOLSONLY] 
[, NOVALIDATE]
[, PREPARECSN  {WAIT | LOCK | NOWAIT | NONE}]

[container.]owner.table

Valid for DB2 LUW, DB2 z/OS, Oracle, 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[JOURNALlibrary/journal] |

library/file[JOURNALlibrary/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

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

NOSCHEDULINGCOLS | ALLCOLS

Valid for Oracle. ALLCOLS is valid for PostgreSQL also.

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

NOSCHEDULINGCOLS

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. For more information about integrated Replicat.

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.

(PostgreSQL) Sets the REPLICA IDENTITY to FULL.

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.

KEYCOLSONLY

Valid for PostgreSQL.

If the table contains an explicit primary key defined, the REPLICA IDENTITY of the table is set to default and the before images of only the primary key columns are logged, wherever applicable.

If the table does not contain an explicit primary key defined, then the REPLICA IDENTITY of the table is set to FULL, logging the before images of all the columns of the table.

NOKEY

Valid for all databases supported by ADD TRANDATA.

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 all databases supported by ADD TRANDATA.

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

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

ADD TRANDATA finance.acct

The following example enables the unconditional supplemental logging of all of the key and non-key columns for the table named acct.

ADD TRANDATA finance.acct ALLCOLS

The following Oracle Database example causes the primary key to be logged plus the non-key columns name and address.

ADD TRANDATA finance.acct, 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.

ADD TRANDATA finance.acct, NOKEY, COLS (name, pid)

The following example adds logging though does not prepare the table for instantiation.

ADD TRANDATA acct PREPARECSN NONE

The following example suppress additional table information processing.

ADD TRANDATA acct.emp* NOVALIDATE