Type and function mapping factors

Both the default data type mappings and default function mappings are built into the data source wrappers. Data type mappings describe the relationship between the data source data type and the federated server data type. You can customize default data type mappings. Function mappings describe the relationship between a data source function and a semantically equivalent function at the federated server. In certain cases, the federated database will compensate for functions mappings that are not supported by a data source.

The default data type mappings are designed so that sufficient buffer space is given to each data source data type to avoid runtime buffer overflow and truncation. You can customize the type mapping for a specific data source or for a particular nickname to suit specific applications and in some cases improve performance. For example, Oracle DATE types can contain both a date and a timestamp portion and are therefore mapped to TIMESTAMPs by default. If you are accessing an Oracle date column, and you know that it contains only date portions (no timestamps), you can use the ALTER NICKNAME statement to change the local data type of the nickname from TIMESTAMP to DATE when the date_compat server option is off. When evaluating predicates based purely on a date, such as SalesDate=DATE('2009-01-04'), this change bypasses the use of a SCALAR function that is used to extract the date from the date and timestamp information held in the column, which can improve performance.

The federated database compensates for functions that are not supported by a data source. Functional compensation usually involves retrieving the necessary data from the data source and applying the function locally, which often has a performance impact. There are several cases where function compensation occurs:
  • A function simply does not exist at the data source. Some of the SYSFUN functions, for example, do not exist on Db2® for z/OS® data sources, and thus require local compensation.
  • A function exists at the data source; however, the characteristics of the operand violate function restrictions. An example is the IS NULL relational operator. Most data sources support it, but some have restrictions such as only allowing a column name on the left hand side of the IS NULL operator.
  • A function, if evaluated remotely, can return a different result. An example is the '>' (greater than) operator. For those data sources with different collating sequences, the greater than operator can return different results than if it is evaluated locally by the federated database.