Incompatibilities between Db2 for z/OS and IBM Db2 Analytics Accelerator

See how IBM Db2 Analytics Accelerator handles certain Db2 data types:

FLOAT

The FLOAT data type is an approximate data type. The result can be different due to platform differences (hexadecimal floating-point numbers (HFP) versus IEEE754 floating-point numbers) or other internal handling differences. This applies to expressions that return FLOAT data and to expressions that contain a FLOAT data-type argument.

CREATE TABLE TEST_TABLE(
D_DECIMAL_4_2 DECIMAL(4,2),
D_DECIMAL_17_2 DECIMAL(17,2),
D_DECIMAL_30_6 DECIMAL(30,6)
);
INSERT INTO TEST_TABLE VALUES(3.5, 3.5, 0.5);
INSERT INTO TEST_TABLE VALUES(1.5, 1.5, 1.5);

SELECT D_DECIMAL_17_2/D_DECIMAL_30_6, D_DECIMAL_17_2*D_DECIMAL_30_6
FROM TEST_TABLE;

SELECT D_DECIMAL_4_2/D_DECIMAL_30_6, D_DECIMAL_4_2*D_DECIMAL_30_6
FROM TEST_TABLE;

Db2 for z/OS returns results as follows:

• For the first calculation in the query:

  +----------+
  | ?  | 1.5 |
  | 1  | 1.5 |
  +----------+
  

• For the second calculation in the query:

  +------------+
  | ?   | 1.75 |
  | 1.5 | 2.25 |
  +------------+

The question mark (?) means that an overflow has occurred (division by zero) because Db2 for z/OS has internally reduced the value 0.5 to 0.

+------------+
| 7   | 1.75 |
| 1   | 2.25 |
+------------+

The truncation problem is discussed in subsections of the Arithmetic with two decimal operands topic of the SQL reference in the Db2 11 for z/OS Knowledge Center. A link to this topic is provided under Related information.

Consider the following points when you have to deal with Unicode SQL statements, multibyte (mixed) data in connection with parameter markers, and multibyte or double-byte EBCDIC columns.

When an exception, such as a buffer overflow, a division by zero, or an out-of-range value occurs, Db2 returns the value NULL combined with a +802 warning, provided that the faulty expression is in the outermost SELECT statement and affects only one row. This is also called friendly arithmetic. IBM Db2 Analytics Accelerator will always issue an error in cases like these.

The maximum row length for a table on an accelerator is 1048319 bytes. If the combined length of all column fields in a Db2 table exceeds this limit, you cannot define that table on the accelerator.

The reason for choosing this evaluation method is that Db2 sometimes changes the order of term evaluation internally. The change is not announced or reported, and therefore goes unnoticed. Depending on the order of term evaluation, Db2 returns an error in one case, but not the other.

The behavior is very similar for the CASE and DECODE functions. These are basically SQL implementations of IF ... THEN ... ELSE constructs and exist in Db2 and IBM Db2 Analytics Accelerator. Db2 will ignore a wrongly defined option as long as it can select a correct one that evaluates to true. IBM Db2 Analytics Accelerator, on the other hand, will first check all available options, and return an error if one of these is not correctly defined. In the latter case, it does not matter whether selecting one of the correctly defined options would have led to a correct result.

In a grouping select, the HAVING clause can only reference expressions that are single-valued within a group. That is, you can only reference group fields, aggregates, or single-valued expressions that are derived from group fields or aggregates (which must include constants).

Time and date constructs in the dashDB® database often differ from the constructs used by Db2 for z/OS, so that values and expressions must be converted. The conversion is often accompanied by limitations.