1.70 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. 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 Databases

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 Database

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 Database

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 Installing and Configuring Oracle GoldenGate for Oracle Database 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. See Summary of Wildcard Exclusion Parametersfor more information.

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.

For more information, see ADD SCHEMATRANDATA.

Oracle strongly encourages 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. See Installing and Configuring Oracle GoldenGate for Oracle Database for more information about configuring logging to support Oracle GoldenGate.

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 Database

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.

Sybase Database

ADD TRANDATA marks a Sybase table for replication by executing the Sybase sp_setreptable and sp_setrepcol system procedures. ADD TRANDATA options employ database features to control how the database propagates LOB data for the specified table. See the ADD TRANDATA options list.

Syntax

ADD TRANDATA {[container.]owner.table | schema.table [JOURNAL 
library/journal] | 
   library/file [JOURNAL library/journal]} 
[, NOSCHEDULINGCOLS | ALLCOLS] 
[, COLS (columns)] 
[, INCLUDELONG | EXCLUDELONG] 
[, LOBSNEVER | LOBSALWAYS | LOBSIFCHANGED | LOBSALWAYSNOINDEX] 
[, NOKEY] 
[, PREPARECSN  {WAIT | LOCK | NOWAIT | NONE}]

[container.]owner.table

Valid for DB2 LUW, DB2 for z/OS, Oracle, SQL Server, and Sybase.

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.

NOSCHEDULINGCOLS | ALLCOLS

Valid for Oracle

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 key(s) and foreign key(s) 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, see Installing and Configuring Oracle GoldenGate for Oracle Database.

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.

COLS (columns)

Valid for all databases supported by ADD TRANDATA.

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. Examples of this functionality are the GETUPDATEBEFORES, NOCOMPRESSUPDATES, and NOCOMPRESSDELETES parameters. To support this functionality, changes to LONGVAR columns in the transaction logs must include both the before and after images of the column value.

LOBSNEVER | LOBSALWAYS | LOBSIFCHANGED | LOBSALWAYSNOINDEX

Valid for Sybase.

Controls how the database propagates LOB data for the specified table.

Note:

The ADD TRANDATA command will overwrite the LOB setting that is currently set for the table. To change the setting afterwards, you must use the sp_setrepcol script.

LOBSNEVER

Prevents LOB data from being propagated. Note this exception: If the LOB column is inserted with a NULL value, or if it is skipped in an INSERT operation, then Extract will write that column to the trail with NULL data.

LOBSALWAYS

Does two things: it uses sp_setrepcol to set LOB replication to ALWAYS_REPLICATE (always replicate LOB data whether or not it has changed in a transaction), and it marks the table to use an index on replication (by means of the USE_INDEX option of sp_setreptable). Because a LOB is marked for replication in a single transaction, this can take a long time, and USE_INDEX reduces that time by creating a global nonclustered index for every LOB. A shared-table lock is held while the global nonclustered index is created.

LOBSIFCHANGED

Replicates LOB data only if it was changed during a transaction. This reduces replication overhead but does not protect against inconsistencies that could occur on the target outside the replication environment. This is the default.

LOBSALWAYSNOINDEX

Sets LOB replication to ALWAYS_REPLICATE (always replicate LOB data whether or not it has changed in a transaction). This adds overhead, but protects against inconsistencies that could occur on the target outside the replication environment. LOBSALWAYSNOINDEX does not mark the table to use an index on replication. The benefit is that no lock is held while ADD TRANDATA is being executed. LOBSALWAYSNOINDEX is the default for Sybase databases earlier than version 15.

Note:

When using the ALWAYS_REPLICATE option, if a LOB column contains a NULL value, and then another column in the table gets updated (but not the LOB), that LOB will not be captured even though ALWAYS_REPLICATE is enabled.

You can check the LOB settings of a table with the INFO TRANDATA command, after ADD TRANDATA has been used for that table. It shows the LOB settings for all of the LOB columns. You can use the Sybase system procedures to change the LOB settings for any given column as needed.

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.

PREPARECSN {WAIT | LOCK | NOWAIT | NONE}

Valid for Oracle for both DML and DDL. Automatically prepares the tables at the source so the Oracle Datapump Export dump file will includes Instantiation CSNs. Replicat uses the per table instantiation CSN set by the Oracle Datapump (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

Example 1   

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; or a Sybase table to be marked for replication.

ADD TRANDATA finance.acct

Example 2   

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

ADD TRANDATA acct ALLCOLS

Example 3   

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

ADD TRANDATA finance.acct, COLS (name, address)

Example 4   

The following Oracle 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)

Example 5   

The following Sybase example marks the acct table for replication and specifies to log LOB data only if it was changed during a transaction.

ADD TRANDATA finance.acct, LOBSIFCHANGED

Example 6   

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

ADD TRANDATA acct PREPARECSN NONE