How Can I Preserve Trailing Spaces When Replicating Db2 CHAR Columns to Databricks Using HVR?

Question

Trailing spaces are removed from Db2 CHAR or NCHAR values when Fivetran replicates them to Databricks. How can I preserve these trailing spaces in the Databricks target table?

Environment

• HVR 6
• Source: IBM Db2 with CHAR or NCHAR columns
• Target: Databricks with VARCHAR or NVARCHAR columns

Answer

By default, we trim trailing spaces when we convert Db2 CHAR or NCHAR values to VARCHAR or NVARCHAR columns in Databricks. To preserve trailing spaces, set the following environment variables at the channel or job level:

• HVR_CHAR_TRAILING_SPACES_TRIM_DISABLE=1
• HVR_NCHAR_TRAILING_SPACES_TRIM_DISABLE=1

To set these environment variables:

1. In the HVR GUI, go to your channel.
2. Select Actions > New Action > Environment.
3. Add each variable separately and set its value to 1. Select all jobs and all tables.
4. Run Activate Replication with the Jobs and Table Enrollment options selected.