Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Replication 9.8.0 HotFix 1
  • All Products

Table of Contents

Search

  1. Preface
  2. Data Replication Overview
  3. Understanding Data Replication
  4. Sources - Preparation and Replication Considerations
  5. Targets - Preparation and Replication Considerations
  6. Starting the Server Manager
  7. Getting Started with the Data Replication Console
  8. Defining and Managing Server Manager Main Servers and Subservers
  9. Creating and Managing User Accounts
  10. Creating and Managing Connections
  11. Creating Replication Configurations
  12. Materializing Targets with InitialSync
  13. Scheduling and Running Replication Tasks
  14. Implementing Advanced Replication Topologies
  15. Monitoring Data Replication
  16. Managing Replication Configurations
  17. Handling Replication Environment Changes and Failures
  18. Troubleshooting
  19. Data Replication Files and Subdirectories
  20. Data Replication Runtime Parameters
  21. Command Line Parameters for Data Replication Components
  22. Updating Configurations in the Replication Configuration CLI
  23. DDL Statements for Manually Creating Recovery Tables
  24. Sample Scripts for Enabling or Disabling SQL Server Change Data Capture
  25. Glossary

User Guide

User Guide

Back Next

Audit Log Tables

Data Replication uses audit log tables in Audit Apply and Merge Apply modes for targets other than
Apache Kafka,
Cloudera,
Flat File,
and
Hortonworks
. Also, InitialSync uses audit log tables to process SQL expressions that are defined for source tables in configurations that use SQL Apply mode and have Microsoft SQL Server or MemSQL targets, in certain load scenarios.
An audit log table records a row for every SQL Update, Insert, or Delete operation on a source table. Each row in an audit table stores metadata about the SQL operation and the before- and after-images of the data for each column in the mapped table.
For
Apache Kafka,
Cloudera,
Flat File,
and
Hortonworks
 targets, which must use Audit Apply mode, Data Replication records data and metadata in Kafka messages or in output files instead of in audit log tables.
The following table describes the columns that an audit log table contains:
Column
Description
OP_XID
The transaction ID.
OP_CODE
The code for the operation type.
OP_TIME
The date and time of the operation.
OP_CMT_SCN
The commit SCN.
OP_CMT_TIME
The commit time of the SQL operation.
OP_NUM_IN_TX
The sequence number of the SQL operation in a transaction.
OP_KEY_LEVEL
The recursive level for row key changes. Data Replication uses this column to process primary key changes in Merge Apply mode.
OP_ROOT_KEY_ROWID
The root ID for recursive key changes. Data Replication uses this column to process primary key changes in Merge Apply mode.
database_user_ID
The user ID of a database user who performed a DML operation, as recorded in the transaction log record for the operation.
Data Replication includes this column in audit log tables only if you entered the column name in the
User ID
 field on the
Runtime Settings
 tab >
Calculated Columns
 view.
database_user_name
The name of a database user who performed a DML operation, as recorded in the transaction log record for the operation.
Data Replication includes this column in audit log tables only if you entered the column name in the
User name
 field on the
Runtime Settings
 tab >
Calculated Columns
 view.
<
column_name
>_OLD
The before-image value of the specified mapped source column in rows for Update and Delete operations. For Insert operations that are processed in Merge Apply mode, the Applier copies the value from the <
column_name
>_NEW column for the primary key to the <
column_name
>_OLD column to simplify merge-apply processing.
In the name <
column_name
>_OLD, the <
column_name
> value must match either the name of a source column if you are using Audit Apply mode or the name of a target column if you are using Merge Apply mode.
<
column_name
>_NEW
The after-image value of the specified mapped source column in rows for Insert and Update operations.
In the name <
column_name
>_NEW, the <
column_name
> value must match either the name of a source column if you are using Audit Apply mode or the name of a target column if you are using Merge Apply mode.
OLD_<
column_name
>
The before-image value of an unmapped source column that is used in a SQL expression associated with a virtual column. Data Replication uses the OLD_<
column_name
> column when processing SQL expressions in Merge Apply mode.
The names of the columns that contain before- and after-images of Insert, Update, and Delete operations must match the names of the source columns for Audit Apply mode or match the names of the target columns for Merge Apply mode.
NEW_<
column_name
>
The after-image value of an unmapped source column that is used in a SQL expression associated with a virtual column. Data Replication uses the NEW_<
column_name
> column to process SQL expressions in Merge Apply mode.
The names of the columns that contain before- and after-images of Insert and Update operations must match the names of the source columns for Audit Apply mode or match the names of the target columns for Merge Apply mode.
On the
Runtime Settings
 tab >
Calculated Columns
 view, you can change the names of these metadata columns and the suffixes that Data Replication appends to the names of the columns for before images and after images. The default suffixes are _OLD and _NEW.
On the
Calculated Columns
 view, you can override the default suffixes in the
Before image column name ending
 and
After image column name ending
 fields. At least one of these fields must specify a suffix. If both fields are empty, the Applier cannot identify the before-image and after-image columns in the audit log table.
Data Replication generates the prefixes for OLD_<
column_name
> and NEW_<
column_name
> columns that are associated with SQL expressions based on the suffixes that are specified for the before-image and after-image columns. To create a prefix from a suffix, Data Replication removes the leading underscore character, if any, and appends the underscore character to the end of the suffix value. By default, Data Replication uses the suffixes _OLD and _NEW and the prefixes OLD_ and NEW_.
For Audit Apply mode, if you add a column to the audit log table using a source column name and the audit log table already contains generated columns that match against that source column name, the Data Replication Console unmaps the audit log table in the configuration. For example, you add column PK to the audit log table and the source table contains a column of the same name. Also, the audit log table already contains the columns PK_NEW and PK_OLD, which were generated for the source column. In this case, the column mappings become invalid and the audit log table is unmapped.
From the Data Replication Console, you can generate audit log tables based on source or target schemas:
  • For Audit Apply mode, use the source schema to generate audit log tables.
    The name of an audit log table can differ from the source table name. However, in this case, you must map the source table with the target audit log table manually.
    If you plan to use virtual columns with SQL expressions in Audit Apply mode, Informatica recommends that you manually add an index on the combination of the OP_CODE and OP_CMT_SCN columns in each audit log table for faster processing of Update operations that the Applier runs to apply SQL expressions to the loaded table rows.
  • If you use InitialSync with the Bulk Copy Program (BCP) to load data to MemSQL or Microsoft SQL Server targets and require audit log tables for InitialSync to process SQL expressions defined for source tables in SQL Apply mode, use the target schema to generate audit log tables. The names of the audit log tables and columns must be based on the names of the target tables and columns.
  • If you define SQL expressions for source tables in a configuration, InitialSync requires audit log tables to process the SQL expressions in the following load scenarios:
    • When using the Bulk Copy Program (BCP) to load data to Microsoft SQL Server targets
    • When loading data from temporary text files to MemSQL targets by using the MySQL ODBC driver and MemSQL LOAD DATA LOCAL INFILE command
    To generate the audit log tables, use the target schema. The names of the audit log tables and columns must be based on the names of the target tables and columns.
    You must generate the audit log tables even if the source tables with SQL expressions are mapped in SQL Apply mode.
  • For Merge Apply mode, use the target schema to generate audit log tables. The names of the audit log tables and columns must be based on the names of the target tables and columns.
    The following additional considerations apply to audit log tables for Merge Apply:
    • If you use SQL expressions, first map virtual columns to target columns and then generate the audit log tables based on the target schema.
    • An audit log table name must match the corresponding target table name and end with the log suffix that is specified in the
      Log table suffix for merge apply
       field on the
      Runtime Settings
       tab >
      Calculated Columns
       view. The default audit log table suffix is _LOG.
    • If you generated the target table from the Data Replication Console, you can generate an audit log table by using the source or target schema. However, if you created the target table manually outside of the Data Replication Console, you must generate the audit log table for the target table based on the target table schema.
    • If you want to use a schema other than the target schema to create the audit log tables, specify this schema in the
      Log table schema for merge apply
       field on the
      Runtime Settings
       tab >
      Calculated Columns
       view.
0 COMMENTS
We’d like to hear from you!