Compare Pair Details

The Compare Pair Pair Details section of the Groups and Compare Pairs page lists the following details of the selected Compare Pair. Click a Compare Pair to view the following Compare Pair details:

• Source Table: The source table or database.

• Source Catalog: The catalog of the source database objects that are to be compared.

• Source Schema: The owner of the source database objects that are to be compared.

• Compare Pair Name: This is either the default name format of <source>=<target> or a user-defined name. In either case, if you hover the mouse cursor over a Compare Pair name, the actual source and target object names are displayed.

• Profile: If a run profile exists for a compare pair, it is shown here. Otherwise, this field is blank and the default profile will be used during comparisons.

  Note: If you select a profile with comparison type set to Row Count, the delta feature is not applicable.

• Target Table: The target table or database.

• Target Catalog: The catalog of the target database objects that are to be compared.

• Target Schema: The owner of the target database objects that are to be compared.

• Comparison Type: Displays the opted comparison type of the profile that you have selected.

  Supported Values: Full, Row Count.

  Full: stands for regular Veridata (data) comparison of a table.

  Row Count : Only row count comparison for a table, without any actual data comparison.

• Validation Status: Shows whether or not the columns of the source and target objects are suitable for being compared, based on the results of any previous validation that was performed. You can modify the Source Table, Source Catalog, Source Schema, Target Table, Target Catalog, Target Schema and update the Profile of the Compare Pair, and then click Save to save to Compare Pair details.

Click Full Screen to view the view details of the Compare Pair on full screen. To go back to the default view, click Partial Screen.

Click Back to Compare Pairs List to go back to the Compare Pair section of the Groups and Compare Pairs page.

For a selected Compare Pair, you can also perform the following from the Compare Pair Details section:

• Column Mappings

• Delta Processing

• Row Partitions

• Deleting a Compare Pair

Column Mappings

Use the Column Mapping tab to create or change a column mapping.

You must have the Administrator or Super User role to create or edit column mappings.

The Column Mapping tab lists the following Compare Pair details:

• All: Lists all tables or files that are contained by the specified datasources. Ensure to give this compare pair a different name.

• Mapped: Lists only previously-mapped columns.

• Unmapped: Lists only columns not mapped with any other column of the compare pair.

• Source: Details of the source table, such as Include, Name, and Data Type.

• Target: Details of the target table, such as Name, Data Type, Format, and Key.

Note: MongoDB does not support column mappings.

• Validate Column Mapping

• Key Mapping Method

Validate Column Mapping

Validation is a preliminary test to determine whether or not the source and target table structures are compatible, and that they both have primary or unique key columns that match. You can perform a manual validation at any time. Oracle GoldenGate Veridata always performs a validation at runtime.

To validate column mapping, on the Groups and Compare Pairs page > Compare Pairs section, select a Compare Pair, click More Actions, and then select Validate Column Mapping. By default, the Validation Status is Never Validated.

The selected Compare Pair is validated and the following message is displayed: Compare Pairs Validated Successfully.

Key Mapping Method

There are two methods for mapping key columns and comparison columns:

• System Generated: Column mappings are configured automatically by Oracle GoldenGate Veridata at runtime based on current object metadata.

• User Defined: Column mappings are configured manually by an Oracle GoldenGate Veridata user who has the Administrator or Super User role.

All new compare pairs default to System Generated for key columns and for comparison columns. You can change to a different mapping method at any time.

How these methods apply to keys

• System Generated: If you know that the objects in a compare pair both contain a primary key or a unique index, you can leave the key mapping method set to the default of System Generated. The key columns will be mapped automatically. To map keys, Oracle GoldenGate Veridata finds all indexes on the source and target objects and tries to find a primary key on each one. If primary keys are not found, Oracle GoldenGate Veridata tries to use the smallest index (least number of columns), and then it maps the columns that have identical names and comparison formats. Any columns that cannot be matched are excluded from the configuration.

• User Defined: If an object has neither a primary key or unique key, you can use the User Defined method to map key columns manually, one by one. You can also use the user-defined method to override existing keys or indexes, but the columns that you select to use as a key must ensure the uniqueness of rows. Also avoid using source and target indexes that have different precision levels or other characteristics that can reduce the accuracy of row selection, especially in a heterogeneous environment.

How these methods apply to comparison columns

• System Generated: If the source and target comparison columns have the same names and comparison formats, you can leave the comparison column mapping method set to the default of System Generated. Oracle GoldenGate Veridata will map those columns automatically at runtime. Non-matching columns are excluded from the configuration. By default, this method includes all of the columns in a comparison. This method defaults to the hash comparison method. You can change the comparison method later by editing the compare pair.

• User Defined: Use this method to map source and target columns manually and to control the comparison method.

You can combine these methods to speed up the mapping process. If most of the column names support system-generated mapping, you can use it and then switch to the user-defined method to map the remaining columns, or to exclude columns from the comparison. For example, you can exclude columns if you know that their values never change or if you expect their values to be out-of-sync.

Delta Processing

This topic provides answers to typical questions about the delta processing feature that is available for all Oracle GoldenGate Veridata supported databases.

In Oracle GoldenGate Veridata, the source and target tables are configured using compare pairs, which are grouped and added to a job to run the comparison.

During the subsequent runs of a comparison job, the comparison of the tables can be performed based on what has changed in the tables from the previous job run; these jobs are Delta Processing Jobs.

• For all supported databases, you can use the delta processing performance feature if you are using server-side sorting.

• For the NonStop platform, Oracle GoldenGate Veridata finds a changed block by detecting a change in its Volume Sequence Number (VSN) since the time of the last comparison.

• For all platforms, the delta comparison column must contain a value that is modified every time the row is modified and this value must always keep increasing. For example, TIMESTAMP or NUMBER. For the Oracle database, ROW_SCN is a default delta column, which keeps on increasing at every delta run.

• The comparison of the tables can be performed based on what has changed during the subsequent runs of a comparison job.

With delta processing enabled, Oracle GoldenGate Veridata compares the difference between the 2 consecutive jobs.

To enable Delta Processing:

1. In the Groups and Compare Pairs page, click the Delta Processing tab under the Compare Pairs Details section.

2. Ensure that the column names are populated in the Use Source Delta Column and Use Target Delta Column. Note that you cannot edit the source and target queries.

3. From the Edit Group Compare Pair page also, you can enable delta processing by selecting the particular Compare Pair and by selecting the dropdown option to enable delta processing.

Note: MongoDB does not support delta processing.

• What is Delta Processing?

What is Delta Processing?

How does it work on NonStop Platforms?

Oracle GoldenGate Veridata finds a changed block by detecting a change in its Volume Sequence Number (VSN) since the time of the last comparison. The VSN is a disk-specific change number that increments sequentially with each database operation that is performed on the data. Each time that a row changes, there is a change in the VSN of the disk block where the row resides.

There is no relationship between a VSN in a file on one disk and a VSN on another. Oracle GoldenGate Veridata tracks VSNs on a per-partition basis on the source and target disks and maintains its own correlations to perform accurate delta comparisons. Once you enable delta processing, it is used for all subsequent runs until you disable it again.

Note: The first run of a compare pair always compares all of the rows in the source and target objects to establish an initial VSN state from which to evaluate deltas in future runs.

How does it work on all other Platforms?

Oracle GoldenGate Veridata compares a source database table to the target database table. The source and target tables are configured using compare pairs, which are grouped and added to a job to run the comparison (see Compare Pairs). When all the rows in the table are compared, it is a Full Comparison Job.

During the subsequent runs of a comparison job, the comparison of the tables can be performed based on what has changed in the tables from previous job run; these jobs are Delta Processing Jobs. Delta processing is usually performed on tables that contain a large number of rows so it is probable that in these tables there will be columns eligible for delta processing. The delta comparison column must contain a value that is modified every time that the row is modified and this value must always be increasing. Any data type that meets this requirement is supported. By default, the columns of the table that are mapped to Numeric or Timestamp comparison formats are supported. For example, TIMESTAMP, TIMESTAMP_TZ, and NUMBER.

The delta base is the value of the Delta Column on the basis of which the delta comparison was performed. Every time a comparison is run, a delta base value is captured. Depending on the number of delta comparison jobs performed, there can be multiple delta base values so a list of delta base values for the compare pair is generated. For example, the first time a Full Comparison is run and the maximum value of the Delta Column is the delta base, DeltaBase-1. A second Delta Processing Job run based on DeltaBase-1 results in DeltaBase-2 being captured again as the maximum of Delta Column. In the third run, you can use either DeltaBase-1 or DeltaBase-2 for the comparison or run a Full Comparison Job.

When Should I use Delta Processing?

Delta processing is suitable for use with very large Enscribe files and SQL tables that, otherwise, would take a long time to process. It does consume additional overhead, so it is probably not practical for use with smaller sets of data. Try running a test comparison without delta processing first. If, in your opinion, the compare pair takes too long to process, try running it again with delta processing enabled. If the delta-enabled run is significantly shorter than the first test, continue to use it. If there is only marginal improvement, it might be better to disable delta processing to prevent the added overhead. The performance gains of delta processing are in the initial comparison step of the run. Delta processing can cause the confirmation step to be longer if the source and target rows end up on different data blocks.

What Process Performs the Delta Processing?

The delta processing is performed by the Oracle GoldenGate Veridata Agent.

For NonStop platforms, the VSN information is retrieved by a privileged process named vsnserv. During the installation of Oracle GoldenGate Veridata Agent on a NonStop platform, PROGID was used for the vsnserv program to run as SUPER.SUPER to be able to read the file labels for this purpose.

For all other platforms, the delta processing queries the compare pair to retrieve the delta base values for both the source and target tables, which creates the column mapping.

What sorting method can be used with delta processing?

To use delta processing, you must enable server-side sorting by setting the sorting method to Server within the profile that is associated with the compare pair or the one that is associated with the job when you run it.

Note: If you always will be using delta comparisons, then consider setting the sorting method to Server within the default Oracle GoldenGate Veridata profile. That way, nobody will forget to select the correct profile when the jobs are run.

What other important things should I know when using delta processing?

The delta processing mechanism can fail to detect an out-of-sync delete, if that delete was the only source row that was modified in a block, and if that delete did not get propagated to the target. In such a case, the block on the target that contains the relevant row does not get modified, so it is skipped by the target Veridata Agent during delta processing.

The delta value field is pre-populated in the column mapping UI, based on the query for retrieving the delta value.

Support Considerations

The following table shows the supported delta column types. The possible list of delta columns for the delta configuration UI is identified by reviewing the compare formats for the corresponding column-pairs in the source and target tables.

Veridata Comparison Format	Can it be Delta Column?
STRING	No
STRING_EN	No
TIMESTAMP	Yes
TIMESTAMP_TZ	Yes*
DATETIME	Yes
SMALLDATETIME	No
DATE	No
TIME	No
NUMBER	Yes
FLOAT	No
BINARY	Yes*
BLOB	No
CLOB	No
INTERVAL	No
DEC_FLOAT	No
BINARY_TIMESTAMP	Yes*
SYBTIME	No
CLOB_NFC	No
STRING_NFC	No

Note: * denotes that Hive doesn’t support the delta column. Supported data types for Hive are NUMBER, TIMESTAMP and DATETIME.

For Oracle Database, the ORA_ROWSCN pseudo delta column is supported, and selected by default when ROWDEPENDENCIS are enabled for that table.

For DB2 for i, z/OS, and LUW, columns having the GENERATED FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP clause is selected by default.

For all supported databases, TIMESTAMP columns are shown in order first then followed by NUMERIC columns.

Only one column is supported for delta processing and is similar to:

SELECT column-names from table name where delta_column delta_condition supplied_delta_value

For example:

select * from TableA where startdate >= '12-01-2012 21:24:00'

How do I enable or disable delta processing?

You can enable or disable delta processing for database compare pair from the Delta Processing tab and the Existing Compare Pairs tab of the Compare Pair Configuration page. In addition, you can use the Delta Processing Enabled check box on the Run Configuration page to toggle this feature.

How do I configure delta processing?

On the Column Mapping Configuration page, click the Delta Processing tab.By default, the options are automatically populated and the Enable Delta Processing check box is selected. You can disable the feature by clearing the check box, which renders all other options inactive.

You can use the defaults or change any of the following:

• the source or target columns for processing,

• whether to use a source or target query

• the source or target database query

After you have configured the delta processing for the compare pair, click Save to apply your changes.

How do I know that delta processing is being used?

When a compare pair is configured for delta processing, Delta Enabled text is displayed in the compare pair table of the Edit Group and Compare Pair Configuration pages.

Can I override delta processing when I run a job?

What if I perform maintenance on the tables or files for which I am using delta processing?

When you perform maintenance on objects in a compare pair that has delta processing enabled, the best practice is to disable delta processing for the next run so that Oracle GoldenGate Veridata compares all of the rows. You can disable delta processing at the compare pair level or as a job override. Starting again with a full comparison allows a new delta base state to be established and will make subsequent delta comparisons faster. Otherwise, delta processing could actually take longer than a complete comparison of all rows.

For example, if a ‘FUP RELOAD’ is performed on the source, but not on the target, it could cause delta processing to return a much larger number of rows from the source than from the target, based on the last delta state. The source rows that are returned would be rows that actually did not change. This happens because FUP moves records around and combines blocks, but does not change the data. However, the VSNs for the affected blocks will change. Oracle GoldenGate Veridata cannot detect that the reload was done since the last delta state. Thus, the next time that the VSN for a block changes, all of the rows in that block will be returned.

Conversely, on the target, no VSNs are changed for the corresponding data (because a reload was not done there), so those rows are not returned for delta processing. This anomaly will be resolved by the confirmation step, but this slows the overall comparison process because that step is much slower than the initial comparison step.

Row Partitions

You can specify which rows to include or not to include in a comparison by specifying a SQL predicate statement or an Enscribe partition.

Oracle GoldenGate Veridata supports selecting a subset of rows for comparison by row partioning (SQL predicate statement) or by Automatic Row Partition.

Using partitions allows you to compare source and target tables or files that have the same structure but a different number of rows. For example, you could compare a production table to a data warehouse table that may contain more rows because of historical data. The usage of partitions also speeds throughput by splitting the load into multiple processing streams.

1. Click the plus (+) icon in the Source Partition area. The Source Partition button is toggled on by default.

2. In the Name box, type a name for this partition, such as emp. Use one word that can include underscores, hyphens, and other standard keyboard special characters.

3. Enter a SQL Predicate. For example, where emp_salary>1000. The SQL Predicate Statement is a condition expression, where the WHERE clause is used to evaluate to a boolean value, either true or false. You can also edit or delete the SQL Predicate by clicking the Pencil or the Delete icons adjacent to it.

4. Similarly, enable Target Partition, and click Copy from Source to copy the SQL Predicate statement details from the source.

Note: MongoDB does not support row partitions.

Deleting a Compare Pair

Use the Compare Pair Configuration page to delete compare pairs.

You must have the Administrator or Super User role to delete compare pairs.

1. To delete a Compare Pair, on the Groups and Compare Pairs page > Compare Pairs section, select a Compare Pair, click More Actions, and then select Delete.

2. Click Delete in the confirmation dialog box to confirmation deletion.