This documentation is for an older version of HVR.

Table Compare Reports Identical When Data Is Not Identical

Issue

In our test setup, running HVR version 5.3.0/17 (linux_glibc2.5-x64-64bit), we performed an HVR Refresh from Oracle 11 to PostgreSQL 11. While most of the data was successfully loaded into PostgreSQL, there is an issue with one specific table column. This column has a varchar2 length of 172 on the Oracle side. However, on the PostgreSQL side, it is defined with a varchar length of 50, indicating that the data in this column has been truncated.

However, when we run an HVR Compare on that table, the report indicates that the tables are "identical", despite the data clearly being different. The data has been truncated to 50 characters on the PostgreSQL side. Is this normal behavior? I would expect HVR to report that the tables are not identical.

Environment

HVR 5

Resolution

HVR Compare performs a data comparison and not a table structure comparison. In heterogeneous environments, it is not uncommon for table structures to differ although your example is an obvious case that you would expect to be reported if table structure validation was part of the comparison.

Is it fair to assume that with your column width 172 on the source there is data with more than 50 characters in the table? If this is the case, then in order to use HVR to move the data into PostgreSQL you must have instructed HVR to allow the truncation using TableProperties /IgnoreCoerceError or in recent versions TableProperties /CoerceErrorPolicy=<some value> and /CoerceErrorType=STRING_TRUNCATION. Since that would become part of your channel definition, it is then also part of the basis for HVR to run the comparison. You told HVR that you would be OK with string truncation, so we will not flag data differences due to this (as long as, taking string truncation into consideration, data is identical otherwise). If there is no source data exceeding 50 characters then obviously the data validation would also report identical results because no string truncation would have taken place as part of the data movement.

So as a general note, HVR Compare looks for differences in the data, based on the data flow settings. Table definitions can be different, and you may have even performed transformations along the way. When HVR runs the compare we take all of this into consideration when we run the compare.