All of the following aggregate functions are fully supported:
HCOSS provides either full or partial support for the following z/OS DB2 scalar functions.
| DB2 Function | Support | Implementation1 | Not supported |
|---|---|---|---|
| ABS | Full | ||
| ACOS | Full | ||
| ADD_MONTHS | Full | ESQL runtime | |
| ASCII | Full | ||
| ASCII_CHR | Full | SQL Name Mapping | |
| ASIN | Full | ||
| ATAN | Full | ||
| BIGINT | Full | ESQL runtime | |
| BINARY | Full | ESQL runtime | |
| BLOB | Full | ESQL runtime | |
| CEILING | Full | ||
| CHAR | Partial | ESQL runtime | The third parameter – CODEUNITS16, CODEUNITS32, or OCTETS |
| CLOB | Partial | ESQL runtime | The third parameter – CODEUNITS16, CODEUNITS32, or OCTETS |
| COALESCE | Full | ESQL runtime | |
| COMPARE_DECFLOAT | Full | ||
| CONCAT | Full | SQL Name Mapping using dbo.CONCAT | |
| CONCAT_BINARY | Full | SQL Name Mapping using dbo.CONCAT_BINARY | |
| COS | Full | ||
| DATE | Full | ESQL runtime | |
| DAY | Full | ESQL runtime | |
| DAYOFMONTH | Full | ESQL runtime | |
| DAYOFWEEK | Full | ESQL runtime | |
| DAYOFWEEK_ISO | Full | ESQL runtime | |
| DAYOFYEAR | Full | ESQL runtime | |
| DAYS | Full | ESQL runtime | |
| DBCLOB | Partial | ESQL runtime | The third parameter – CODEUNITS16, or CODEUNITS32 |
| DECFLOAT2 | Partial | ESQL runtime | The second parameter – 16 or 34 |
| DECIMAL or DEC | Partial | ESQL runtime | The optional fourth parameter – decimal character |
| DEGREES | Full | ||
| DIFFERENCE | Full | ||
| DIGITS | Full | SQL Name Mapping using dbo.digits | |
| DOUBLE_PRECISION or DOUBLE | Full | ESQL runtime | |
| EXP | Full | ||
| EXTRACT | Full | ESQL runtime | |
| FLOAT | Full | ESQL runtime | |
| FLOOR | Full | ||
| GRAPHIC | Partial | ESQL runtime | The third parameter – CODEUNITS16, or CODEUNITS32 |
| HEX | Full | SQL Name Mapping using dbo.hex | |
| HOUR | Full | ESQL runtime | |
| IDENTITY_VAL_LOCAL | Full | ESQL runtime | |
| IFNULL | Full | SQL Name Mapping using COALESCE | |
| INTEGER or INT | Full | ESQL runtime | |
| LAST_DAY | Full | SQL Name Mapping using dbo.lastday | |
| JULIAN_DAY | Full | SQL Name Mapping using dbo.julianday | |
| LCASE | Full | SQL Name Mapping using LOWER | |
| LEFT | Partial | ESQL runtime | The optional third parameter – CODEUNITS16, CODEUNITS32, or OCTETS |
| LENGTH | Full | SQL Name Mapping using DATALENGTH | |
| LN | Full | SQL Name Mapping using LOG | |
| LOCATE | Partial | ESQL runtime | The optional fourth parameter – CODEUNITS16, or CODEUNITS32 |
| LOG10 | Full | ||
| LOWER | Partial | The optional second and third parameters are not supported by SQL Server | |
| LPAD | Full | SQL Name Mapping using dbo.LPAD | |
| LTRIM | Partial | The optional second parameter is not supported by SQL Server | |
| MICROSECOND | Full | ESQL runtime | |
| MIDNIGHT_SECONDS | Full | ESQL runtime | |
| MINUTE | Full | ESQL runtime | |
| MOD | Full | ESQL runtime | |
| MONTH | Full | ESQL runtime | |
| NEXT_DAY | Full | SQL Name Mapping using dbo.nextday | |
| POSSTR | Full | ||
| POWER | Full | ESQL runtime | |
| QUARTER | Full | ESQL runtime | |
| RADIANS | Full | ||
| RAND | Full | ||
| REAL | Full | ESQL runtime | |
| REPEAT | Full | SQL Name Mapping using REPLICATE | |
| REPLACE | Full | ||
| RIGHT | Partial | ESQL runtime | The optional third parameter – CODEUNITS16, CODEUNITS32, or OCTETS |
| ROUND | Partial | ROUND function in SQL Server does not automatically expand precision for the result if arithmetic overflow occurs. For example, SQL Server treats ROUND(873.726,-3) as error whereas DB2 yields 1000.000 | |
| RPAD | Full | SQL Name Mapping using dbo.RPAD | |
| RTRIM | Partial | The optional second parameter is not supported by SQL Server | |
| SECOND | Partial | ESQL runtime | The optional second parameter |
| SIGN | Full | ||
| SIN | Full | ||
| SMALLINT | Full | ESQL runtime | |
| SOUNDEX | Full | ||
| SPACE | Full | ||
| SQRT | Full | ||
| STRIP | Full | SQL Name Mapping using dbo.strip | |
| SUBSTR | Full | SQL Name Mapping using SUBSTRING | |
| SUBSTRING | Full | ESQL runtime | The optional fourth parameter – CODEUNITS16, CODEUNITS32, or OCTETS |
| TAN | Full | ||
| TIME | Full | ESQL runtime | |
| TIMESTAMP | Full | ESQL runtime | |
| TIMESTAMP_ISO | Full | ESQL runtime | |
| TIMESTAMPDIFF | Full | ESQL runtime | |
| TRANSLATE | Full | SQL Name Mapping using dbo.translate | |
| TRUNCATE or TRUNC | Full | ESQL runtime | |
| UCASE | Partial | SQL Name Mapping using UPPER | The optional second and third parameters |
| UNICODE | Full | ||
| UPPER | Partial | The optional second and third parameters | |
| VALUE | Full | SQL Name Mapping using COALESCE | |
| VARBINARY | Full | ESQL runtime | |
| VARCHAR | Partial | The optional third parameter – CODEUNITS16, CODEUNITS32, or OCTETS | |
| VARGRAPHIC | Partial | The optional third parameter – CODEUNITS16, CODEUNITS32, or OCTETS | |
| WEEK | Full | ESQL runtime | |
| WEEK_ISO | Full | ESQL runtime | |
| XMLQUERY | Full | ESQL runtime | |
| YEAR | Full | ESQL runtime | |
|
1 When blank, SQL Server and z/OS provide same functionality. 2 The value is converted to FLOAT. |
|||