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

1. Transdata International
2. Transdata Corp

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

TransData 20EWM563 VAR 2 Element +/-1 mA Output Transducer 1000 Fullscale Calibrating VARS Fast Response Time with Calibration Certificate and Free Overnight Shipping $1,095.00 $ 1,095. 00 FREE Shipping. TransData provides BPO to the North America’s most prestigious Title, Appraisal, and Flood Determination companies. In doing so, the company recruits, and retains the industry’s best and brightest talent that enables TransData the opportunity to deliver successfully, in time and within scope. Bosch - MPC5xx EDC7 (TRUCK/TRACTOR) terminal adapter (base board F34DM003 required, NOT included in K34NTDIMAL04). New Trasdata is a unique tool supporting all microprocessors utilized by any kind of vehicles in the Automotive field, for reading and programming operations.

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:

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:

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 Parameters' for 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.

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.

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

[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 multitentant container database. Use a three-part name of container.owner.table for an Oracle multitentant 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.

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:

When EXCLUDELONG is used, the following is the command:

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.

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.

Example 2

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

Example 3

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

Example 4

The following Oracle example prevents the primary key from being logged, but logs the non-key columns name and pid instead.

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.

Example 6

The following example enables the any in-flight transactions to be finished and acquires a lock on the interested table object, acct.

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:

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:

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 Parameters' for 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.

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.

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

[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 multitentant container database. Use a three-part name of container.owner.table for an Oracle multitentant 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.

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:

When EXCLUDELONG is used, the following is the command:

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.

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.

Example 2

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

Example 3

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

Example 4

The following Oracle example prevents the primary key from being logged, but logs the non-key columns name and pid instead.

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.

Transdata International

Example 6

Transdata Corp

The following example enables the any in-flight transactions to be finished and acquires a lock on the interested table object, acct.