Values in a VARCHAR(36) FOR BIT DATA Column Differ Between the Source and Target

Issue

After HVR replicates a VARCHAR(36) FOR BIT DATA column from Db2 to PostgreSQL, the source and target values differ.

Environment

• HVR 6
• Source: Db2
• Target: PostgreSQL

Resolution

To troubleshoot this issue:

1. In HVR, run Compare on the affected table with Keep Difference Files enabled.
2. Check whether the result is IDENTICAL or DIFFERENT.
3. Compare the hexadecimal values in each database:
   • In Db2, run:

     SELECT HEX(environment_value_uid)
     FROM <schema>.<table>
     WHERE ...;
     

   • In PostgreSQL, run:

     SELECT encode(environment_value_uid, 'hex')
     FROM <schema>.<table>
     WHERE ...;
     

If the hexadecimal values match, the difference is only in how each database displays the binary data.

To display PostgreSQL query results in hexadecimal format, run:

SET bytea_output = 'hex';

Cause

This issue occurs because Db2 treats VARCHAR(36) FOR BIT DATA as binary data, which HVR maps to BYTEA in PostgreSQL. Db2 and PostgreSQL may display the same binary value differently, even when HVR has replicated the underlying data correctly.