Setting the Federation server options

Adjust the Federation server options to improve your Data Virtualization querying experience.

Prerequisites

Setting a Federation server option

To set the value for any of the server options, replace the values in this command, and then run it in the Db2 CLI:
Alter server qplex options(add/set <CID>@<server_option> <value>)
  • Replace <CID> with your Data Virtualization connection ID.
  • Replace <server_option> with the Federation server option as listed in the following table.
  • Replace <value> with the value as of the server option as listed in the following table.
For example, 
Alter server qplex options(add DB21001@COLLATING_SEQUENCE ‘Y’)

Federation server options

Refer to the following table to learn more about the Federation server options.
Table 1. Federation server options available for Data Virtualization
Server option Purpose Applicable data source Values
COLLATING_SEQUENCE This server option indicates whether the remote data source sorts characters the same way as the local Data Virtualization server.

This impacts whether predicates with string comparisons and operations like ORDER BY, GROUP BY, DISTINCT, and UNION can be performed on the data source, or pushed down.

 All Data Virtualization relational data sources. Consider the following values to configure this server option:
  • Y: This default value is for when the remote data source uses the same character sorting order as Big SQL, which sorts characters based on the order of their UTF-8 encoding code points.

    This value pushes down predicates with string comparisons and operations to the remote source because the sorting results will be identical. However, further validation might by required.

  • N: Set this value for when the remote data source does not use the same character sorting order as Big SQL, which sorts characters based on the order of their UTF-8 encoding code points.

    This value does not push down predicates with string comparisons and operations to the remote source because the sorting results might not match after you sort it locally.
DB2_CHAR_BLANKPADDED_COMPARISON This option is used to indicate whether a remote data source treats trailing blanks (spaces) in fixed-length character strings as insignificant during comparison. This is done by adding trailing blanks (spaces) to the common length and then comparing the string.

For example, ‘ABC’=‘ABC ‘ since ‘ABC’ is padded to ‘ABC ‘.

This option impacts whether the fixed-length string comparison, including predicates and other string sorting operations, can be pushed down.

MySQL and MariaDB. Consider the following values to configure this server option:
  • Y: Set this value if the remote fixed-length string comparison semantics is insignificant.
  • N: Set this value if the remote fixed-length string comparison semantics is significant.
DB2_SAME_CODESET

This option indicates whether the remote data source has the same code page as the local Data Virtualization server. Data Virtualization has functions that take character positions as input or output, and if that position is determined by byte, then it might point to another position of the string when the string is represented in a different code page.

This option impacts whether some string functions can be pushed down.

 All Data Virtualization relational data sources. Consider the following values to configure this server option:
  • Y: Set this value if the remote database code page is UTF-8.
  • N: Set this value if the remote database code page is not UTF-8.
DB2_VARCHAR_BLANKPADDED_COMPARISON This option is used to indicate whether a remote data source treats trailing blanks (spaces) in variable-length character strings as insignificant during comparison. This is done by adding trailing blanks (spaces) to the common length and then comparing the string.

For example, ‘ABC’=‘ABC ‘ since ‘ABC’ is padded to ‘ABC ‘.

This option impacts whether the variable length string comparison, including predicates and other string sorting operations, can be pushed down.

MySQL and MariaDB. Consider the following values to configure this server option:
  • Y: Set this value if the remote variable length string comparison semantics is insignificant.
  • N: Set this value if the remote variable length string comparison semantics is significant.
SAME_DECFLT_ROUNDING This option indicates whether the remote data source uses the same rounding mode for decimal floating-point numbers (DECFLOAT) as the localData Virtualization server.

This option impacts whether the functions calculating DECLOAT can be pushed down.

 Db2 and Oracle. Consider the following values to configure this server option:
  • Y: Set this value if the remote database uses the same rounding mode as the local Data Virtualization server.
  • N: Set this value if the remote database uses the same rounding mode as the local Data Virtualization server.
SAME_STR_COMP_SEMANTICS This option checks whether the remote data source handles string comparisons in the same way as the local Data Virtualization server, particularly whether trailing blanks (spaces) are considered significant to different CHAR and VARCHAR string types.

This option impacts whether it’s necessary to further check CHAR and VARCHAR type string comparison semantics during string comparison pushdown analysis.

 All Data Virtualization relational data sources. Consider the following values to configure this server option:
  • Y: Set this value if the remote data source has the same string comparison semantics as the local Data Virtualization server regarding string trailing blank sensitivity.
  • N: Set this value if the remote data source does not have the same string comparison semantics as the local Data Virtualization server regarding string trailing blank sensitivity.
SERVER_VERSION This option indicates the version of the remote data source. For some data sources, different versions might have different capabilities, and pushdown levels for a particular SQL.

This option impacts the whole pushdown analysis for the data sources.

 Hive and Db2 for z/OS.

For Hive, set the Hive version as one of the following:

  • 1.2
  • 2.1
  • 3.0

For Db2 for z/OS data sources, set this option by using the prefix 'DSN'.

For example,
DB21001@server_version'DSN1215'
STRING_UNITS This option indicates the string unit of the remote data source. A string unit is a way that the system counts of measures the length of a string or the position of characters within a string. If the data source uses the same string unit, then the functions can be pushed down.

This option impacts whether the functions that are related to a string unit can be pushed down.

 Db2 Check the remote Db2 database configuration parameter STRING_UNITS by running the following command: 
 db2 get db cfg for <database name> | grep STRING_UNITS
Consider the following values:
  • Set this option to S if the remote database configure parameter is SYSTEM.
  • Set this value to C if the remote data base configure parameter is CODEUNITS32.
VARCHAR2_COMPAT This option indicates whether remote data sources have character types that are compatible with Oracle varchar2.

Empty strings in Oracle are treated as 'NULL'. Oracle is also sensitive to trailing blanks (spaces) in string comparisons, meaning that 'ABC' is not equal to 'ABC '.

This option impacts whether the string comparisons can be pushed down.

 Db2 Consider the following values to configure this server option:
  • Y: Set this value if the remote Db2 database is compatible with Oracle varchar2.
  • N: Set this value if the remote Db2 database is not compatible with Oracle varchar2.
VARCHAR_NO_TRAILING_BLANKS This option indicates whether the remote data source has trailing blanks (spaces) in any of the data that is stored in VARCHAR columns.

If the data in the VARCHAR columns does not contain trailing blanks (spaces), then the results are consistent with the local Data Virtualization server, which is sensitive to trailing blank (spaces).

This option impacts whether the variable length string comparison including predicates and other operations that are involved in string sorting, can be pushed down when the remote data source is not sensitive to trailing blanks (spaces).

All Data Virtualization relational data sources. Consider the following values to configure this server option:
  • Y: Select this option if your remote data source has no trailing blanks (spaces) in any of the data that is stored in VARCHAR columns.
  • N: Select this option if your remote data source has trailing blanks (spaces) in any of the data that is stored in VARCHAR columns.