Data source restrictions on data type values

For some data types, the federated server supports a wider range of values than the data source.

When you use these data types, use the values that the only data source supports. If you insert a value that is outside the supported range, the data source either converts the value or returns an error.

If you use a predicate that contains a value that is outside the supported range and the predicate is evaluated at the data source, either of the following results can occur:

  • The predicate can return a different result than the same predicate on the federated server.
  • The predicate can result in an error.

Times and timestamps with 24 in the hours field

If you use a predicate with a time or timestamp that contains 24 in the hours field, you might receive an error. To prevent problems, you can convert these times or timestamps. You can use a statement similar to the following UPDATE statement:

UPDATE my_table SET timestamp_col = timestamp_col + 0 SECONDS;

This update changes the time field to 00:00:00 and increments the date by one day.

The federated server allows times and timestamps to contain 24 in the hours field. The distinction between a timestamp with 24 hours and a timestamp with 00 hours is that they are unequal in comparisons but equal in arithmetic.

Example
  • The following predicate returns false:
    2008-05-14-24:00:00.000000 = 2008-05-15-00:00:00.000000
  • The following predicate returns true:
    (2008-05-14-24:00:00.000000 + 0 SECONDS) = 
 (2008-05-15-00:00:00.000000 + 0 SECONDS)

Some data sources such as Oracle and Sybase do not allow times or timestamps with 24 in the hours field.

Empty strings on Oracle

You can avoid empty string problems by not using empty strings in applications that use Oracle nicknames. Instead you can use the NULL value or a string consisting of a single blank (' ').

In VARCHAR columns on a federated server that is not VARCHAR2 compatible, a distinction is made between the empty string and NULL values, which results in different behaviors between the local tables and nicknames. Whereas, in VARCHAR columns on a remote data source that is VARCHAR2 compatible, the empty string and NULL values are considered equivalent. If an empty string ('') is inserted into a VARCHAR column, it is converted into a NULL value.

In the following examples, MY_TABLE is a local table in the federated database and MY_NICK is a nickname on a remote table in an Oracle database:

Example 1
In this example, an empty string is inserted into MY_TABLE and MY_NICK, which both contain a NOT NULL column named NOT_NULL_COL. The INSERT statement on MY_TABLE succeeds but the INSERT on MY_NICK fails with an error because the empty string is converted to a NULL value, which conflicts with the NOT NULL constraint.
INSERT INTO my_table(not_null_col) VALUES('');
INSERT INTO my_nick(not_null_col) VALUES('');
Example 2
In this example, both the federated and remote tables are initially empty. The first SELECT statement returns no rows because the federated server distinguishes between the empty string and NULL values but the second SELECT statement returns one row because during the insert, the Oracle database converts the empty string to a NULL value.
INSERT INTO my_table(my_col) VALUES('');
SELECT * FROM my_table WHERE my_col IS NULL;  
INSERT INTO my_nick(my_col) VALUES('');
SELECT * FROM my_nick WHERE my_col IS NULL;

Infinite numeric type

If you defined a column in numeric type, the federated server maps it to local decimal type with counterpart precision and scale.

However, numeric type for some remote server is not limited. For example, PostgreSQL is numeric type and MySQL is decimal type, yet the maximum federated server decimal precision is 32.

If the numeric type precision is over the max decimal precision, SQL0604 is returned.

Example
  • The following type is allowed in PostgreSQL, but overflowed in federated database:
    numeric (33, 10) ·
  • The following type is right in both PostgreSQL and federated database:
    numeric (32, 10)

Unsigned BigInt type

If you defined a column in bigint unsigned in MySQL, the federation server could report SQL0802 if the value scope exceeds the maximal Db2® bigint scope.

However, numeric type for some remote server is not limited. For example, PostgreSQL numeric type and MySQL decimal type, yet the maximum federated server decimal precision is 32.

If the numeric type precision is over the max decimal precision, SQL0604 is returned.

Example
  • The following type is allowed in MySQL, but overflowed in federated database:
     select from nickname, the returned value over 9233372036854775807 from bigint unsigned column, reports SQL0802

Longblob type

If you defined a column in longblob type in MariaDB, when creating a nickname, the federation server could report SQL1844W. MariaDB longblob max length is 4G and DB2® only has blob, for which the max length is 2G. Therefore the federation server truncates the data to 2G.

Example
  • The following type is allowed in MariaDB, but truncated in federated database:
     longblob