Setting the VARCHAR_NO_TRAILING_BLANKS column option

If the data source string column does not contain trailing blanks, set the VARCHAR_NO_TRAILING_BLANKS column option to Y.

Some data sources, such as Oracle, do not use the same blank-padded string comparison logic that the federated database uses. This applies to data types such as VARCHAR and VARCHAR2. As a result, predicates that involve these data types must be rewritten by the query optimizer to ensure consistent query results.

Rewriting query statements can impact performance. Setting this option for a specific column provides the query optimizer with information about these columns so that it can generate more efficient SQL statements.

The first time that you add an option, use the ADD keyword to add the option. If the option was added previously, use the SET keyword to change the option.

For example:

ALTER NICKNAME nickname 
   ALTER COLUMN local_column_name 
   OPTIONS (ADD VARCHAR_NO_TRAILING_BLANKS 'Y')