Supported SQL functions and expressions
See which aggregate functions and scalar functions are supported by IBM® Db2® Analytics Accelerator.
Supported aggregate functions
IBM Db2 Analytics Accelerator supports all aggregate functions, except for the XMLAGG function.
The following aggregate functions are supported even if they are used in combination with the DISTINCT or UNIQUE keyword:
- STDDEV
- STDDEV_SAMP
- VARIANCE
- VARIANCE_SAMP
Function, keyword or expression | Restriction |
---|---|
|
If the DISTINCT or UNIQUE keyword is applied to string values with trailing blanks, IBM Db2 Analytics Accelerator might cut off the trailing
blanks from the resulting values.
Example: The SQL
code specifies that a space character or blank is to be added at the end of each value retrieved.
Now see some of the rows returned by this query, once by Db2 for z/OS®, and once by IBM Db2 Analytics Accelerator:
|
|
When multiple tables are joined, the result can differ when LISTAGG runs on an accelerator instead of Db2 for z/OS. To guarantee the same result from both environments, you can use an ORDER BY clause in a unique sort-key expression. This approach ensures that the result set is the always the same. However, the order of rows in the result can still differ, unless the statement also specifies a unique GROUP BY clause for the sort-key expression. |
Supported scalar functions
In addition, IBM Db2 Analytics Accelerator supports a variety of scalar functions and the CAST specification.
If restrictions exist for a function in Table 2, the function is marked with an asterisk(*). The restrictions are described in Table 3.
Function name | Function name | Function name | Function name |
---|---|---|---|
• ABS | • DEGREES | • MOD | • SUBSTR |
• ADD_MONTHS | • DIGITS | • MONTH | • SUBSTRING (see Table 3) |
• BINARY | • DOUBLE_PRECISION (also DOUBLE, FLOAT) | • MONTHS_BETWEEN | • TIME (see Table 3) |
• BIGINT | • EXP | • MULTIPLY_ALT | • TIMESTAMP (see Table 3) |
• BITAND (see Table 3) | • EXTRACT (see Table 3) | • NEXT_DAY | • TIMESTAMPDIFF |
• BITANDNOT | • FLOAT | • NEXT_WEEK | • TIMESTAMP_FORMAT (also TO_DATE; see Table 3) |
• BITNOT | • FLOOR | • NEXT_MONTH | • TIMESTAMP_ISO (see Table 3) |
• BITOR | • GRAPHIC (see Table 3) | • NEXT_QUARTER | • TO_NUMBER (DECFLOAT_ FORMAT) |
• BITXOR | • HOUR (see Table 3) | • NORMALIZE_DECFLOAT | • TRANSLATE (see Table 3) |
• CEILING | • HEX (see Table 3) | • NULLIF | • TRIM (also with two arguments; (see Table 3) |
• CAST specification (see Table 3) | • IFNULL | • NVL | • TRUNCATE(see Table 3) |
• CHAR (see Table 3) | • DOUBLE_PRECISION (also DOUBLE, FLOAT) | • POSITION | • UPPER (UCASE) (see Table 3) |
• CHARACTER_LENGTH | • INTEGER (INT) | • POSSTR | • VALUE |
• COALESCE | • JULIAN_DAY (see Table 3) | • POWER® (see Table 3) | • VARBINARY |
• COMPARE_ DECFLOAT (see Table 3) | • LAST_DAY | • QUARTER (see Table 3) | • VARCHAR |
• CONCAT (see Table 3) | • LEFT (see Table 3) | • RADIANS | • VARCHAR9 (see Table 3) |
• CORR | • LENGTH | • RAND (see Table 3) | • VARCHAR_FORMAT (TO_CHAR; see Table 3) |
• COVAR | • LN | • REAL | • VARGRAPHIC (see Table 3) |
• COVAR_SAMP | • LOCATE (see Table 3) | • REPEAT | • WEEK (see Table 3) |
• DATE (see Table 3) | • LOCATE_IN_STRING (see Table 3) | • REPLACE | • WEEK_ISO (see Table 3) |
• DAY | • LOG10 | • RIGHT (see Table 3) | • YEAR |
• DAYOFMONTH | • LOG | • ROUND (see Table 3) | |
• DAYOFWEEK | • LOWER (LCASE) (see Table 3) | • ROWID (see Table 3) | |
• DAYOFWEEK_ISO (see Table 3) | • LPAD (see Table 3) | • RPAD | |
• DAYOFYEAR (see Table 3) | • LTRIM (also with two arguments) | • RTRIM (also with two arguments) | |
• DAYS | • MAX (GREATEST) | • SECOND (see Table 3) | |
• DECFLOAT (see Table 3) | • MEDIAN | • SIGN 4see Table 3) | |
• DECFLOAT_FORMAT (TO_NUMBER) | • MICROSECOND | • SMALLINT | |
• DECIMAL | • MIDNIGHT_SECONDS (see Table 3) | • SPACE | |
• DECODE (also with two arguments) | • MIN (also LEAST) | • SQRT | |
• MINUTE (see Table 3) | • STRIP (see Table 3) |
Function, keyword, or expression | Restriction | ||||
---|---|---|---|---|---|
|
The lowest possible BIGINT value (-9223372036854775808) at any argument position returns a result data type of DECFLOAT(34) rather than BIGINT, as in Db2 for z/OS). For any other valid input value, the result data type is BIGINT. | ||||
|
Restrictions exist if you want to use CAST expressions to convert
string data types and, by specifying the string unit, determine the number of bytes to be used for
the representation of characters in the resulting strings:
Draft comment: kuester
|
||||
|
|
||||
|
If the result data type is VARCHAR, the maximum length of a result string is limited to 4000 characters. | ||||
|
|
||||
|
|
||||
|
Since the database engine on the accelerator does not support DECFLOAT
constants (see entry for DECFLOAT), the values to be compared are interpreted as floating-point
numbers (DOUBLE), which results in a loss of precision for big numbers. This loss of precision might
cause a value shift that leads to deviating comparison results. Consider the following
cases:
The output of these comparisons is different, depending on where they are executed:
|
||||
|
|
||||
|
If the DISTINCT or UNIQUE keyword is applied to string values with trailing
blanks, IBM Db2 Analytics Accelerator might cut off
the trailing blanks from the resulting values.
Example: The SQL
code specifies that a space character or blank is to be added at the end of each value retrieved.
Now see some of the rows returned by this query, once by Db2 for z/OS, and once by IBM Db2 Analytics Accelerator:
|
||||
|
If the string unit is CODEUNITS16 or CODEUNITS32, IBM Db2 Analytics Accelerator might return a different number of trailing blanks than Db2 for z/OS. | ||||
|
The following restrictions exist for the HEX scalar function:
|
||||
|
Result deviations can occur if input strings contain multibyte or graphic EBCDIC characters. | ||||
|
Result deviations can occur under the following conditions:
|
||||
|
|
||||
|
|
||||
|
If the argument data type is BIGINT, the result data type will be cast to DOUBLE. | ||||
|
Supported without a parameter only. | ||||
|
In Db2 for z/OS, two types of ROWID values exist: the longer, external type, and the shorter internal type. If a query references ROWID values and these values are in the external format, the values are converted to the internal format when the query is routed to an accelerator because IBM Db2 Analytics Accelerator can handle the internal type only. The internal ROWID type is also what the accelerator returns in the query results. If you have an application that relies on the external ROWID type to be returned, do not accelerate the query. | ||||
|
If ROUND works on a numerical expression of type DECFLOAT, and the rounding would result in a range underflow or overflow, Db2 for z/OS returns positive or negative Infinity (-∞ or +∞), whereas IBM Db2 Analytics Accelerator returns not a number (NaN). | ||||
|
If the data type of an input argument is DECIMAL, the result must be cast to this type. | ||||
|
Result deviations can occur if input strings (in fields or columns) contain graphic EBCDIC characters. | ||||
|
SUBSTRING is supported only if the expression refers to character or graphic data types. | ||||
|
Some string literals with a dash (-) or space character ( ) as the
separator (delimiter) between the date and the time portion and a period (.) or colon (:) to
separate hours, minutes, and seconds are not supported or only partially supported as function
arguments: Examples:
See also Dates in TIMESTAMP expressions. |
||||
|
|
||||
|
You cannot use the TRUNCATE function on accelerator-only tables. | ||||
|
Result deviations can occur if input strings contain multibyte or graphic EBCDIC characters. | ||||
|
See LOWER/LCASE. | ||||
|
Result deviations can occur if the VARCHAR9 function is used to
convert DECIMAL values to strings:
|
||||
|
The
following date and timestamp formats are not supported:
|
Unsupported scalar functions
• ACOS | • CCSID_ ENCODING | • DIFFERENCE | • MQ <xxx> | • SCORE | • TOTALORDER |
• ASCII | • CLOB | • EBCDIC_CHR | • NORMALIZE_ STRING | • SIN | • TRUNC_ TIMESTAMP |
• ASCII_CHR | • COLLATION_ KEY | • EBCDIC_STR | • OVERLAY | • SINH | • UNICODE |
• ASCII_STR | • COS | • ENCRYPT_ TDES | • QUANTIZE | • SOA <xxx> | • UNICODE_STR |
• ASIN | • COSH | • GENERATE_ UNIQUE | • PACK | • SOUNDEX | • VERIFY_GROUP_ FOR_USER |
• ATAN | • CONTAINS | • GETHINT | • RAISE_ERROR | • TAN | • VERIFY_ROLE_ FOR_USER |
• ATAN2 | • DBCLOB | • GETVARIABLE | • RID | • TANH | • VERIFY_ TRUSTED_ CONTENT_ FOR _USER |
• ATANH | • DECFLOAT_ SORTKEY | • IDENTITY_VAL_ LOCVAL | • ROUND_ TIMESTAMP | • TIMESTAMP ADD | • <xxx>XML<xxx> |
• BLOB | • DECRYPT <xxx> | • INSERT | • TIMESTAMP_ TZ |