Handling string trailing spaces in comparisons

Comparison-dependent processing is pushed down to a data source based on server options that you select. This pushdown can improve performance.

Different databases handle trailing spaces in their own way when comparing string data types. When a database ignores trailing spaces when comparing strings, the process is referred to as a blank padded comparison. For example, ‘a’ is considered equal to ‘a ‘. When a database treats trailing spaces as significant when comparing strings, the process is referred to as a no blank padded comparison.

If a string comparison is pushed down from a federated server to a data source that handles trailing spaces differently, the query result might be incorrect.

For example, t1 is a local Federation table and n1 is a nickname, and all compared columns are string type. The data source uses blank padded comparison while the Federation server uses no blank padded comparison. If n1.c1 = ‘a ’ is executed on the data source and n1.c2 = t1.c2 is executed on the Federation Server, the final result of the query is incorrect on both the data source side and the Federation server side.

select * from t1, n1 where n1.c1 = ‘a ’ and n1.c2 = t1.c2

The Db2 Federation server resolves the issue by controlling the pushdown through several server options:

These server options are already optimized for data sources, using the string trailing blank comparison mechanism that is recorded in their documents. The default settings are designed to push down your string comparisons in the most efficient and stable way, and you do not need to reset these server options. However, you can change the default values when required as explained in the following sections:

The db2_char_blankpadded_comparison option

This server option indicates whether the string comparison uses a blank padded comparison for fixed length. If you choose to change the setting, check the behavior of the data sources and enter the appropriate value:

Y: On the data source, trailing spaces are insignificant and ignored when strings are compared.
N: On the data source, trailing spaces are significant in comparisons, like any other character.

You might need to change these options when working with MySQL and MariaDB data sources. For these data sources, the trailing spaces comparison behavior can change along with the collation setting. For more information, see How to use string trailing blank comparison related server options to get expect results on MySQL and MariaDB.

The db2_varchar_blankpadded_comparison option

This server option indicates whether the string comparison uses a blank padded comparison for variable length strings on the data source. If you need to change the setting, check the behavior of the data sources and enter the appropriate value:

Y: On the data source, trailing spaces are insignificant and ignored when strings are compared.
N: On data source trailing spaces are significant in comparisons, like any other character.

You might need to change these options when working with MySQL and MariaDB data sources. For these data sources, the trailing spaces comparison behavior can change along with the collation setting.

For more information, see How to use string trailing blank comparison related server options to get expect results on MySQL and MariaDB

The varchar2_compat option

The varchar2_compat option is only useful for DRDA and Oracle data source. It is related to VARCHAR2 and NVARCHAR2 support. If the compatibility semantics associated with VARCHAR2 and NVARCHAR2 are applied to a data source, the value of this option should be Y.

For Oracle data sources, the varchar2_compat value is Y by default.

For DRDA data sources, check the VARCHAR2 compatibility database configuration parameter varchar2_compat to see whether the VARCHAR2 compatibility semantics are applied to data source. If varchar2_compat is ON, set the varchar2_compat option to Y.

Run on data source:

db2 get db cfg

       Database Configuration for Database

 Database configuration release level                    = 0x1500
 Database release level                                  = 0x1500

 Update to database level pending                        = NO (0x0)
 Database territory                                      = US
 Database code page                                      = 1208
 Database code set                                       = UTF-8
 Database country/region code                            = 1
 Database collating sequence                             = IDENTITY
 Alternate collating sequence              (ALT_COLLATE) =
 Number compatibility                                    = OFF
 Varchar2 compatibility                                  = ON
 Date compatibility                                      = OFF

The varchar_no_trailing_blanks option

The varchar_no_trailing_blanks option indicates whether trailing blanks are present in VARCHAR columns. If trailing blanks are not present in VARCHAR columns of data sources, then the value is set to Y. The default value is N. This setting can result in more data being pushed down when the method of handling trailing spaces of variable length string data is different on Federation Server and a remote data source. For example, when your Federation Server uses no blank padded comparison and the remote data source uses blank padded comparison.

The db2_same_str_comp_semantics option

The db2_same_str_comp_semantics option indicates whether a remote data source has the same string comparison semantics as your Federation server.

Y: Pushdown analysis bypasses the string comparison semantics checking for both character and varchar type.
N (the default value): pushdown analysis occurs based on the string type:
- character type string comparison semantics are checked when character type string comparison is involved.
- varchar type string comparison semantics are checked when varchar type string comparison is involved.

Note: Setting the option to N does not mean blocking every string comparison pushdown. Also, setting the option to Y results in a faster path for string comparison pushdown analysis. If your query only involves a single data source type (for example, Oracle), and no conjunctive query is run between your data source and your local federation database, setting the option to Y uses the comparison that is pushed down into the data source. This setting improves the pushdown and the query result keeps in accordance with the data source instead of the Federation server.