Previous Topic Next topic Print topic


Default Function Mappings

The default mappings HCOSS uses to convert DB2 aggregate and scalar functions to SQL Server equivalents.
Restriction: This topic applies to Windows environments only.
Note: In addition to these default mappings, you can also define customized mappings. See Customizing Functions and Special Registers for more information.

z/OS DB2 Aggregate Functions in HCOSS

All of the following aggregate functions are fully supported:

  • AVG
  • COUNT
  • COUNT_BIG
  • MAX
  • MIN
  • STDDEV or STDDEV_SAMP
  • SUM

z/OS DB2 Scalar Functions in HCOSS

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.

Previous Topic Next topic Print topic