2.13 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 useADD
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 ofGGS_
, for exampleGGS_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
ADD TRANDATA dbo.*
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
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 ofcontainer.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 useADD TRANDATA
for objects that are not system objects but have names that match those of system objects in a wildcard pattern, issueADD 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 theJOURNAL
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
toFULL
.
-
-
COLS (
columns
)
-
Valid for Oracle.
Use the
COLS
option to log specific non-key columns. Can be used to log columns specified in aKEYCOLS
clause and to log columns that will be needed for filtering or manipulation purposes, which might be more efficient than fetching those values with aFETCHCOLS
clause in aTABLE
statement. Separate multiple columns with commas, for exampleNAME
,ID
,DOB
. -
INCLUDELONG | EXCLUDELONG
-
Valid for DB2 LUW.
Controls whether or not the
ALTER TABLE
issued byADD TRANDATA
includes theINCLUDE LONGVAR COLUMNS
attribute.INCLUDELONG
is the default. WhenADD 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 includeLONGVAR
columns. For example, theGETUPDATEBEFORES
parameter. To support this functionality, changes toLONGVAR
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 toFULL
, 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 theCOLS
option to log alternate columns that can serve as keys, and designate those columns as substitute keys by using theKEYCOLS
option of theTABLE
orMAP
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
andNOT VALID
primary keys in the supplemental logging. These keys override the normal key selection criteria that is used by Oracle GoldenGate. If theGLOBALS
parameterALLOWNONVALIDATEDKEYS
is being used,ADD SCHEMATRANDATA
runs withALLOWNONVALIDATEDKEYS
whether or not it is specified. By default,NON VALIDATED
andNOT 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.
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
andpid
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
Parent topic: Common Command Line Interface Commands