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
Note: However, restrictions exist if you use these keywords. See Table 1.
Table 1. Restrictions on aggregate functions, keywords and expressions
Function, keyword or expression Restriction
  • DISTINCT
  • UNIQUE
 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:
SELECT DISTINCT N_S_VCHR CONCAT ' ' FROM MATRIX.MBCS_EBCDIC;
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:
Db2 for z/OS:
  1. [ ] (four blanks)
  2. [ HIJ ] (two blanks at end of string)
  3. [!"$%¢&\/ ] (one blank at end of string)
IBM Db2 Analytics Accelerator:
  1. [] (empty string)
  2. [ HIJ] (no blanks at end of string)
  3. [!"$%¢&\/] (no blanks at end of string)
  • LISTAGG
 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.

Table 2. Supported scalar functions and specifications
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)  
Table 3. Restrictions
Function, keyword, or expression Restriction
  • BITAND
 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.
  • CAST specification
 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:
CAST TO CHAR
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.
CAST TO GRAPHIC
Allowed string units are CODEUNITS16 and CODEUNITS32.
When compared with the output from Db2 for z/OS, IBM Db2 Analytics Accelerator might return a different number of trailing blanks.
CAST TO VARGRAPHIC
Allowed string units are CODEUNITS16 and CODEUNITS32.
Draft comment: kuester
  • If CAST is used to convert a graphic or a character string (of type GRAPHIC, VARGRAPHIC, CHAR or VARCHAR) to a character string (CHAR or VARCHAR), you might receive incorrect results if CODEUNITS16 or CODEUNITS32 is specified for the string unit.

See also Strings as time values and their conversion.
  • CHAR
  • 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.
  • If a string of space characters is requested in a query, the CHAR function always returns strings with a length of 255, no matter which string length is specified in the query. Suppose, for example, that a table with the name TMP contains strings consisting of (subsequent) space characters. If you run a query like 
    SELECT CHAR(SPACE(20)) FROM TMP;

    you will find blank strings with a length of 255 rather than a length of 20 in the results.

  • If the function is used to retrieve a substring of a multibyte string, so that it will split a multibyte-character, Db2 for z/OS returns the correct byte-code of the split character, whereas IBM Db2 Analytics Accelerator replaces this portion of the byte-code with blanks. You can avoid this by using the length argument (OCTETS, CODEUNITS16, or CODEUNITS32).

    Suppose, for example, that you run the following query to retrieve the first five bytes of the string ＭＡＭＡ, consisting of full-width Latin capital letters. Each character is represented by three bytes. The mode of representation is UTF-8.

    SELECT HEX(CHAR('ＭＡＭＡ',5));

    To illustrate the differences in the outputs, the HEX function is used:

    Result in Db2 for z/OS:
    EFBCADEFBC

    This value represents the first letter 'M' (in full) and the first two bytes of the first letter 'A'.

    Result returned by accelerator:
    EFBCAD2020

    This value represents the first letter 'M' (in full) and two blanks instead of the first two bytes of the letter 'A'.

    You could specify OCTETS as the length argument to avoid this problem: 

    SELECT HEX(CHAR('ＭＡＭＡ',5,OCTETS));

    The result would then be EFBCADEFBC in both cases.
  • CONCAT
 If the result data type is VARCHAR, the maximum length of a result string is limited to 4000 characters.
  • DATE
  • Db2 accepts an argument in the format yyyynnn as input for the DATE function. IBM Db2 Analytics Accelerator does not accept this format and therefore issues an error.
  • If the DATE function is used on numeric expressions, it is converted to DATE(to_date('0000-12-31','YYYY-MM-DD')+trunc(numeric_exp)) during query routing to an accelerator. If the numeric_exp result is a negative number, IBM Db2 Analytics Accelerator adds BC to the date. An error is returned when the converted scalar function is evaluated in Db2.
  • DAYOFWEEK_ISO
  • DAYOFYEAR
  • JULIAN_DAY
  • MIDNIGHT_SECONDS
  • QUARTER
  • TIMESTAMP_ISO
  • WEEK
  • WEEK_ISO
  • If Db2 for z/OS is configured to use a local DATE format (DATE=LOCAL on DSNTIP4 panel) and a query uses a DATE constant as an argument for any of these functions, the query is not accelerated, and SQLCODE -4742 is returned.
  • Queries containing the DAYOFWEEK_ISO, DAYOFYEAR, JULIAN_DAY, QUARTER, TIMESTAMP_ISO, or WEEK_ISO function fail with SQLCODE=-443 under the following conditions:
    1. The query includes the clause FETCH FIRST n ROWS.
    2. Any of the functions mentioned before uses string representations of U.S. or European date formats as arguments.

    The same applies to queries containing MIDNIGHT_SECONDS, except that string representations of European date formats are allowed.

  • Queries containing the TIMESTAMP_ISO function fail during the attempt to route them to an accelerator if string representations of TIME values are used as arguments.
  • COMPARE_DECFLOAT
 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:
COMPARE_DECFLOAT(0.123456789012345678901E-187, 
                 1.234567890123456789010E-188), 
COMPARE_DECFLOAT(0.123456789012345678901E-185, 
                 1.234567890123456789010E-188),
COMPARE_DECFLOAT(0.123456789012345678901E-187, 
                 1.234567890123456789010E-185)

The output of these comparisons is different, depending on where they are executed:

Db2 for z/OS:
'2','2','1'
IBM Db2 Analytics Accelerator:
'0','2','1'
  • DECFLOAT
  • The database engine on the accelerator does not check, and therefore does not necessarily preserve the scale of the original Db2 for z/OS values. Db2 for z/OS defines the scale as the number of digits to the right of the decimal point. For example, 0.03E3 might be returned as 0.3E2. As long as the value is numerically accurate according to the IEEE754 specification, it is considered to be correct.
  • Different representations of the same numerical value (for example 1E2, 100, and 10E1) also have slightly different representations in memory and the database engine on the accelerator rarely normalizes these values. If the database is a row store, the values are not normalized at all. If it is a columnar store, normalization might occur more often, especially if a GROUP BY operation is performed on the values. However, you can never rely on the normalization of differently represented values.
  • The database engine on the accelerator does not support DECFLOAT constants. If the accelerator receives a DECFLOAT constant from Db2 for z/OS, and the value of this constant is within the range of DOUBLE values accepted by the database engine, the value is interpreted as DOUBLE, and the accelerator might return a value with a different precision. If a received value is not within the range of DOUBLE, SQL code -405 or -410 is returned. For example:
    • SQLCODE = -405, ERROR:  THE NUMERIC CONSTANT 1.0E-308 
CANNOT BE USED AS SPECIFIED BECAUSE IT IS OUT OF RANGE ...
    • SQLCODE = -410, ERROR:  A NUMERIC VALUE 
1234.5678901234567890123456E-55 IS TOO LONG, OR IT HAS A VALUE 
THAT IS NOT WITHIN THE RANGE OF ITS DATA TYPE ...
  • Db2 for z/OS and the database engine of IBM Db2 Analytics Accelerator compute different DECFLOAT values if one of the operands is Infinity (∞) and none of the other operands are of the type not a number (NaN). Signaling (sNaN) values are also counted as NaN.

    In the majority of these cases, Db2 for z/OS returns NaN as the result, whereas IBM Db2 Analytics Accelerator returns positive or negative Infinity.
  • DISTINCT
  • UNIQUE
 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:
SELECT DISTINCT N_S_VCHR CONCAT ' ' FROM MATRIX.MBCS_EBCDIC;
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:
Db2 for z/OS:
  1. [ ] (four blanks)
  2. [ HIJ ] (two blanks at end of string)
  3. [!"$%¢&\/ ] (one blank at end of string)
IBM Db2 Analytics Accelerator:
  1. [] (empty string)
  2. [ HIJ] (no blanks at end of string)
  3. [!"$%¢&\/] (no blanks at end of string)
  • GRAPHIC
 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.
  • HEX
 The following restrictions exist for the HEX scalar function:
  • A CHAR value is returned by Db2 for z/OS. If the query is accelerated by IBM Db2 Analytics Accelerator, the function returns a VARCHAR value.
  • The function reverses the byte order of the character values in the results if the function is processed by IBM Db2 Analytics Accelerator on an IBM Integrated Analytics System. This is because the query originates from a big-endian system Db2 for z/OS, but is processed by a little-endian system (IBM Integrated Analytics System).

    Db2 Analytics Accelerator on Z, which is deployed on a big-endian system, returns the same value as Db2 for z/OS.

    Example of a reverse-order result returned by IBM Db2 Analytics Accelerator on IBM Integrated Analytics System:

    SELECT HEX(NATIONKEY)
 FROM TEST.NATION
 WHERE NATIONKEY = 41308488
 WITH UR;
    Result returned by Db2 for z/OS or Db2 Analytics Accelerator on Z: 02765148
    Result returned by IBM Db2 Analytics Accelerator on IBM Integrated Analytics System: 48517602
  • LEFT
  • LPAD
  • RIGHT
 Result deviations can occur if input strings contain multibyte or graphic EBCDIC characters.
  • LOCATE
  • LOCATE_IN_STRING
 Result deviations can occur under the following conditions:
  • Input strings contain multibyte or graphic characters and the search begins in the middle of the string.
  • A string unit (OCTETS, CODEUNITS16, or CODEUNITS32) is not specified.
  • LCASE
  • LOWER
  • UCASE
  • UPPER
  • If a string argument contains characters with diacritical marks, Db2 for z/OS does not deliver the expected result, but IBM Db2 Analytics Accelerator does. For example:
    CREATE TABLE XYZ.TMP (A GRAPHIC(64), B GRAPHIC(64) NOT NULL) 
CCSID UNICODE;
INSERT INTO XYZ.TMP VALUES ('Ü','Ü');
SELECT LOWER(B) XYZ.TMP;
    Db2 for z/OS:
    Returns Ü, same as before because characters with diacritical marks are not converted.
    IBM Db2 Analytics Accelerator:
    Returns ü (correct result). Reason: The string argument is converted before the function is executed.
  • Accelerated queries might return different results (including invalid values) or an error with SQLCODE=-171, SQLSTATE=42815 if any of these functions is applied to EBCDIC or ASCII data. For example, take the following query, which returns invalid values:
    SELECT UCASE('µ~stuvwxyz', 'De_CH') FROM SINGLE_EBCDIC;

    The query is supposed to find the string 'µ~stuvwxyz' in a table containing single-byte EBCDIC data and convert the resulting strings to uppercase. The Swiss variant of the German character set is used.

    Depending on where the query is run, the following values are returned:
    Db2 for z/OS:
    µ˜STUVWXYZ (correct result)
    Accelerator:
    <unprintable symbol>˜STUVWXYZ (incorrect result)
  • If the second argument is a string consisting of space characters only, the query is not accelerated. For example, the query:
    SELECT UPPER('abc', ' ') FROM schema.table;
    is run in Db2 for z/OS. If the setting of the CURRENT QUER ACCELERATION special register or QUERY_ACCELERATION ZPARM does not allow this, the query fails with SQLCODE -4742 and REASON_CODE=11.
  • If a German locale setting is used as an argument of the UPPER function, such as DE_DE, instances of the lower sharp s character (ß) are converted to SS. This might lead to an SQL error (SQLCODE -171) because the conversion changes the length of the CHAR or VARCHAR character string.

    If you omit the locale setting, the ß character is preserved.
  • LCASE
  • LOWER
  • UCASE
  • UPPER
(continued)
  • Result deviations can occur if input strings contain multibyte or graphic EBCDIC characters.
  • Db2 for z/OS and IBM Db2 Analytics Accelerator return different length values if any of these functions is applied to GRAPHIC data. For example:
    CREATE TABLE TMP (A GRAPHIC(64), 
B GRAPHIC(64) NOT NULL) 
CCSID UNICODE;
INSERT INTO TMP VALUES ('a','b');
    After that, you run the following query:
    SELECT LENGTH(UPPER(B)) FROM TMP;
    Depending on where the query is run, the following values are returned:
    Db2 for z/OS:
    64
    Accelerator:
    192

    The reason is that the accelerator uses a different result data type, that is, CHAR(192) instead of GRAPHIC(64).
  • POWER
 If the argument data type is BIGINT, the result data type will be cast to DOUBLE.
  • RAND
 Supported without a parameter only.
  • ROWID
 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.
  • ROUND
 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).
  • SIGN
 If the data type of an input argument is DECIMAL, the result must be cast to this type.
  • STRIP
 Result deviations can occur if input strings (in fields or columns) contain graphic EBCDIC characters.
  • SUBSTRING
 SUBSTRING is supported only if the expression refers to character or graphic data types.
  • TIMESTAMP
  • TIMESTAMP_FORMAT
  • TO_DATE
 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:
  • yyyy-mm-dd[ hh.mm.ss[.nnnnnn]] (space character before hh; period to separate hours, minutes, and seconds).

    This format is not supported.

  • yyyy-mm-dd[-hh:mm:ss[.nnnnnn]] (dash before hh; colon to separate hours, minutes, and seconds).

    This format is supported by the current version of IBM Db2 Analytics Accelerator, but only in connection with Db2 12 for z/OS or later.

See also Dates in TIMESTAMP expressions.
  • TRANSLATE
  • If the arguments are of data type CHAR or VARCHAR, the corresponding characters in the to-string expression and the from-string expression must have the same number of bytes (except in the case of a zero-length string). This means, for example, that you cannot convert a single-byte character to a multibyte character, or a multibyte character to a single-byte character. The padding character (pad-char-exp argument) cannot be the first byte of a valid multibyte character. If it is, error -171 is returned.
  • Result deviations can occur if arguments contain multibyte characters.
  • TRUNCATE
 You cannot use the TRUNCATE function on accelerator-only tables.
  • TRIM
 Result deviations can occur if input strings contain multibyte or graphic EBCDIC characters.
  • UPPER
  • UCASE
 See LOWER/LCASE.
  • VARCHAR9
 Result deviations can occur if the VARCHAR9 function is used to convert DECIMAL values to strings:
  • When compared with the results returned by Db2 for z/OS, an accelerated query might show a leading blank in the results. For example:
    SELECT VARCHAR9(13.1) FROM TABLE_1
    gives you the following results:
    Db2 for z/OS:
    13.1
    IBM Db2 Analytics Accelerator
    13.1 (leading blank)

    In accordance with these results, the length function also returns different values:

    SELECT LENGTH(VARCHAR9(13.1)) FROM TABLE_1
    Db2 for z/OS:
    4
    IBM Db2 Analytics Accelerator:
    5
  • When compared with the results returned by Db2 for z/OS, an accelerated query might show leading zeros (0s) in places where the result value does not require all of the formatted digits. For example, if 1.0 is one of the values returned by the following query:
    SELECT VARCHAR9(DECIMAL(col1,7,5)) FROM TABLE_1
    you get the following results:
    Db2 for z/OS:
    1.00000
    IBM Db2 Analytics Accelerator
    01.00000 (leading zero)
  • VARCHAR_FORMAT
 The following date and timestamp formats are not supported:
  • FF[n] - fractional seconds (000000-999999)

    The optional number n is used to specify the number of digits to include in the return value. Valid values for n are the integers from 1-12. The default is 6.

  • ID - ISO day of the week (1-7)

    The value 1 represents Monday. The value 7 represents Sunday.

  • IYYY - ISO year (0000-9999)

    The last four digits of the year based on the ISO week that is returned.

  • NNNNNN - microseconds (000000-999999)

    This format is equivalent to FF6.

  • RR - last two digits of the year (00-99)
  • SSSSS - seconds since the previous midnight

Unsupported scalar functions

Table 4. 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