List of supported built-in functions

This section contains an alphabetical list of the built-in functions that Db2 for z/OS® supports.

Table 1. Supported built-in functions

Function name	Description
ABS or ABSVAL	Returns the absolute value of its argument
ACOS	Returns the arc cosine of an argument as an angle, expressed in radians
FL 507 ADD_DAYS1	Returns a datetime value that represents the first argument plus a specified number of days
ADD_MONTHS	Returns a date that represents the date argument plus the number of months argument
ADMIN_TASK_LIST	Returns a table with one row for each of the tasks that are defined in the administrative task scheduler task list
ADMIN_TASK_OUTPUT	Returns the output parameter values and result sets, if available. If the task that was executed is not a stored procedure or the requested execution status is not available, the function returns an empty table.
ADMIN_TASK_STATUS	Returns a table with one row for each task in the administrative task scheduler task list that contains the status for the last time the task was run
ARRAY_AGG	Returns an array in which each value of the input set is assigned to an element of the array
ARRAY_DELETE	Returns an array with the requested elements deleted.
ARRAY_FIRST	Returns the minimum array index value of an array.
ARRAY_LAST	Returns the maximum array index value of an array.
ARRAY_NEXT	Returns the next larger array index value for an array, relative to a specified array index argument.
ARRAY_PRIOR	Returns the next smaller array index value for an array, relative to a specified array index argument.
ARRAY_TRIM	Returns an array after removing elements from the end of an ordinary array.
ASCII	Returns the ASCII code value of the most character of the argument as an integer
ASCII_CHR	Returns the character that corresponds to the ASCII code value that is specified by the argument
ASCII_STR or ASCIISTR	Returns an ASCII version of the character or graphic string argument
ASIN	Returns the arc sine of an argument as an angle, expressed in radians
ATAN	Returns the arc tangent of an argument as an angle, expressed in radians
ATANH	Returns the hyperbolic arc tangent of an argument as an angle, expressed in radians
ATAN2	Returns the arc tangent of x and y coordinates as an angle, expressed in radians
AVG	Returns the average of a set of numbers
BLOB	Returns a BLOB representation of its argument
BLOCKING_THREADS	Returns a table with one row for each lock or claim that threads hold against the databases that are specified in the input parameter
BIGINT	Returns a big integer representation of its argument
BITAND, BITANDNOT, BITOR, BITXOR, and BITNOT	Return a corresponding base 10 integer value in a data type that is based on the data type of the input arguments
BINARY	Returns a fixed-length binary string representation of its argument
FL 507 BTRIM1	Removes the characters that are specified in a trim string from the beginning and end of a source string
CARDINALITY	Returns a value of that represents the number of elements of an array
CCSID_ENCODING	Returns the encoding scheme of a CCSID with a value of ASCII, EBCDIC, UNICODE, or UNKNOWN
CEILING or CEIL	Returns the smallest integer greater than or equal to the argument
CHAR	Returns a fixed-length character string representation of its argument
FL 506CHARACTER_LENGTH or CHAR_LENGTH	Returns the length of its argument in the number of string units that are specified
CHR	Returns the character that corresponds to the ASCII code value that is specified by the argument
CLOB	Returns a CLOB representation of the first argument
COALESCE	Returns the first argument in a set of arguments that is not null
COLLATION_KEY	Returns a string that represents the collation key of the argument in the specified collation
COMPARE_DECFLOAT	Returns a SMALLINT value that indicates whether two arguments are equal, or unordered, or whether one argument is greater than the other
CONCAT	Returns the concatenation of two strings
CONTAINS	Returns a result about whether or not a match was found during a search of a text search index
CORR or CORRELATION	Returns the coefficient of the correlation of a set of number pairs
COS	Returns the cosine of an argument that is expressed as an angle in radians
COSH	Returns the hyperbolic cosine of an argument that is expressed as an angle in radians
COUNT	Returns the number of rows or values in a set of rows or values
COUNT_BIG	Same as COUNT, except the result can be greater than the maximum value of an integer
FL 506 COVAR_POP or COVARIANCE or COVAR	Returns the population covariance of a set of number pairs
COVAR_SAMP or COVARIANCE_SAMP	Returns unbiased sample covariance (n-1) of a set of number pairs
FL 504 CUME_DIST1	Returns a cumulative distribution of a row in an OLAP window
FL 504 CUME_DIST (aggregate)1	Returns the cumulative distribution of a row that is hypothetically inserted into a group of rows
DATE	Returns a date derived from its argument
DAY	Returns the day part of its argument
DAYOFMONTH	Similar to DAY
DAYOFWEEK	Returns an integer in the range 1 - 7, where 1 represents Sunday
DAYOFWEEK_ISO	Returns an integer in the range 1 - 7, where 1 represents Monday
DAYOFYEAR	Returns an integer in the range 1 - 366, where 1 represents January 1
DAYS	Returns an integer representation of a date
FL 507 DAYS_BETWEEN1	Returns the number of full days between the specified arguments
DBCLOB	Returns a DBCLOB representation of its argument
DECIMAL or DEC	Returns a decimal representation of its argument
DECFLOAT	Returns a DECFLOAT representation of its argument
DECFLOAT_FORMAT	Returns a DECFLOAT(34) value that is based on the interpretation of the input string using the specified format
DECFLOAT_SORTKEY	Returns a binary value that can be used when sorting DECFLOAT values
DECODE	Returns a specified result-expression based on a comparison of input expressions (similar to the CASE expression)
DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR, and DECRYPT_DB	Returns the decrypted value of an encrypted argument that was encrypted using the ENCRYPT_TDES function
FL 505 DECRYPT_DATAKEY_INTEGER, DECRYPT_DATAKEY_BIGINT, DECRYPT_DATAKEY_DECIMAL, DECRYPT_DATAKEY_VARCHAR, DECRYPT_DATAKEY_CLOB, DECRYPT_DATAKEY_VARGRAPHIC, DECRYPT_DATAKEY_DBCLOB, and DECRYPT_DATAKEY_BIT	Returns the decrypted value of an encrypted argument that was encrypted using the algorithm that was specified when the data was encrypted
DEGREES	Returns the number of degrees for an argument that is expressed in radians
DIFFERENCE	Returns a value that represents the difference between the sounds of two strings based on applying the SOUNDEX function to the strings
DIGITS	Returns a character string representation of a number
DOUBLE_PRECISION or DOUBLE	Returns a double precision floating-point representation of its argument
DSN_XMLVALIDATE	Returns an XML value that is the result of applying XML schema validation to the first argument
EBCDIC_CHR	Returns the character that corresponds to the EBCDIC code value that is specified by the argument
EBCDIC_STR	Returns an EBCDIC version of the string argument
FL 505 ENCRYPT_DATAKEY	Returns the argument as an encrypted value using the specified key label and algorithm
ENCRYPT_TDES or ENCRYPT	Returns the argument as an encrypted value using the Triple DES encryption algorithm
EXP	Returns the exponential function of an argument
EXTRACT	Returns a portion of a date or timestamp based on its arguments
FL 504 FIRST_VALUE1	Returns the expression value for the first row in an OLAP window
FLOAT	Same as DOUBLE
FLOOR	Returns the largest integer that is less than or equal to the argument
GENERATE_UNIQUE and GENERATE_UNIQUE_BINARY	Returns a character string of bit data that is unique compared to any other execution of the function
GETHINT	Returns the embedded password hint from encrypted data, if one exists
GETVARIABLE	Returns a varying-length character string representation of the value of a session variable
GRAPHIC	Returns a fixed-length graphic string representation of its argument
GREATEST	Returns the maximum value in a set of values
GROUPING	Returns a value that indicates if a row returned in a GROUP BY result is a row generated by a grouping set that excludes the column represented by its argument
FL 506 HASH	Returns a varying-length value that is the result of applying the specified algorithm to the first argument. It is intended for cryptographic purposes.
HASH_CRC32, HASH_MD5, HASH_SHA1, and HASH_SHA256	Return a fixed-length value that is the result of applying a hash algorithm to an input argument. They are intended for cryptographic purposes.
FL 506 HEX	Returns a hexadecimal representation of its argument
HOUR	Returns the hour part of its argument
IDENTITY_VAL_LOCAL	Returns the most recently assigned value for an identity column
IFNULL	Returns the first argument in a set of two arguments that is not null
INSERT	Returns a string that is composed of an argument inserted into another argument at the same position where some number of bytes have been deleted
INSTR	Returns the starting position of the first occurrence of one string within another string
INTEGER or INT	Returns an integer representation of its argument
JULIAN_DAY	Returns an integer that represents the number of days from January 1, 4712 B.C.
FL 504 LAG1	Returns the expression value for the row at offset rows before the current row
LAST_DAY	Returns a date that represents the last day of the month of the date argument
FL 504 LAST_VALUE1	Returns the expression value for the last row in an OLAP window
LCASE	Returns a string with the characters converted to lowercase
FL 504 LEAD1	Returns the expression value for the row at offset rows after the current row
LEAST	Returns the minimum value in a set of values
LEFT	Returns a string that consists of the specified number of most bytes or the specified string units
LENGTH	Returns the length of its argument
LN	Returns the natural logarithm of an argument
LOCATE	Returns the starting position of one string within another string
LOCATE_IN_STRING	Returns the starting position of the first occurrence of one string within another string
LOG10	Returns the base 10 logarithm of an argument
LOWER	Returns a string with the characters converted to lowercase
LPAD	Returns a string that is padded on the with blanks or a specified string
LTRIM	Returns the characters of a string with the leading blanks or hexadecimal zeros removed
MAX (aggregate)	Returns the maximum value in a set of column values in a group
MAX (scalar)	Returns the maximum value in a set of values
MAX_CARDINALITY	Returns a value of type BIGINT that represents the maximum number of elements that an array can contain. This value is the cardinality that was specified in the CREATE TYPE statement for an ordinary array type.
MICROSECOND	Returns the microsecond part of its argument
MIDNIGHT_SECONDS	Returns an integer in the range 0 - 86400 that represents the number of seconds between midnight and the argument
MIN (aggregate)	Returns the minimum value in a set of values in a group
MIN (scalar)	Returns the minimum value in a set of values
MINUTE	Returns the minute part of its argument
MOD	Returns the remainder of one argument divided by a second argument
MONTH	Returns the month part of its argument
MONTHS_BETWEEN	Returns an estimate of the number of months between two arguments
MQREAD	Returns a message from a specified IBM® MQ location (return value of VARCHAR) without removing the message from the queue
MQREADALL	Returns a table containing the messages and message metadata from a specified IBM MQ location with a VARCHAR column and without removing the messages from the queue
MQREADALLCLOB	Returns a table containing the messages and message metadata from a specified IBM MQ location with a CLOB column and without removing the messages from the queue
MQREADCLOB	Returns a message from a specified IBM MQ location (return value of CLOB) without removing the message from the queue
MQRECEIVE	Returns a message from a specified IBM MQ location (return value of VARCHAR) with removal of message from the queue
MQRECEIVEALL	Returns a table containing the messages and message metadata from a specified IBM MQ location with a VARCHAR column and with removal of messages from the queue
MQRECEIVEALLCLOB	Returns a table containing the messages and message metadata from a specified IBM MQ location with a CLOB column and with removal of messages from the queue
MQRECEIVECLOB	Returns a message from a specified IBM MQ location (return value of CLOB) with removal of message from the queue
MQSEND	Sends data to a specified IBM MQ location, and returns a varying-length character string that indicates whether the function was successful or unsuccessful
MULTIPLY_ALT	Returns the product of the two arguments as a decimal value, used when the sum of the argument precisions exceeds 31
NEXT_DAY	Returns a timestamp that represents the first weekday, specified by the second argument, after the date argument
FL 507 NEXT_MONTH1	Returns the first day of the next month after the specified date
NORMALIZE_DECFLOAT	Returns a DECFLOAT value that is the result of normalizing the input argument
NORMALIZE_STRING	Returns a string value that is the result of normalizing the input Unicode value
FL 504 NTH_VALUE1	Returns the expression value for the nth-row row in an OLAP window
FL 504 NTILE1	Returns the quantile rank of a row
NULLIF	Returns NULL if the arguments are equal; else the first argument
NVL	Returns the first argument that is not null
OVERLAY	Returns a string that is composed of an argument inserted into another argument at the same position where some number of bytes have been deleted
PACK	Returns a binary string value that contains a data type array and a packed representation of each non-null argument
PERCENTILE_DISC	Returns a percentile of a set of values
FL 504 PERCENT_RANK1	Returns a relative percentile rank of a row in an OLAP window
FL 504 PERCENT_RANK (aggregate)1	Returns the relative percentile rank of a row that is hypothetically inserted into a group of rows
POSITION	Returns the position of the first occurrence of an argument within another argument where the position is expressed in terms of the string units that are specified
POSSTR	Returns the position of the first occurrence of an argument within another argument
FL 506 POWER or POW	Returns the value of one argument raised to the power of a second argument
QUANTIZE	Returns a DECFLOAT value that is equal in value (except for any rounding) and sign to the first argument and which has an exponent set to be equal to the exponent of the second argument
QUARTER	Returns an integer in the range 1 - 4 that represents the quarter of the year for the date specified in the argument
RADIANS	Returns the number of radians for an argument that is expressed in degrees
RAISE_ERROR	Raises an error in the SQLCA with the specified SQLSTATE and error description
FL 506 RANDOM or RAND	Returns a double precision floating-point random number
FL 504 RATIO_TO_REPORT1	Returns the ratio of an argument to the sum of the arguments in an OLAP partition
REAL	Returns a single precision floating-point representation of its argument
FL 504 REGEXP_COUNT1	Returns a count of the number of times that a regular expression pattern is matched in a string
FL 504 REGEXP_INSTR1	Returns the starting or ending position of the matched substring, depending on the value of the return_option argument
FL 504 REGEXP_LIKE1	Returns an INTEGER value of 0 or 1 indicating if the regular expression pattern is found in a string
FL 504 REGEXP_REPLACE1	Returns a modified version of the source string where occurrences of the regular expression pattern found in the source string are replaced with the specified replacement string
FL 504 REGEXP_SUBSTR1	Returns one occurrence of a substring of a string that matches the regular expression pattern
FL 507 REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SXY, and REGR_SYY1	Return quantities that can be used to compute various diagnostic statistics needed for the evaluation of the quality and statistical validity of a regression model
FL 507 REGR_COUNT1	Returns the number of non-null number pairs used to fit a regression line
FL 507 REGR_INTERCEPT or REGR_ICPT1	Returns the y-intercept of a regression line ("b" in the equation y = a * x + b)
FL 507 REGR_R21	Returns the coefficient of determination ("R-squared" or "goodness-of-fit") for a regression
FL 507 REGR_SLOPE1	Returns the slope of a regression line ("a" in the equation y = a * x + b)
REPEAT	Returns a character string composed of an argument repeated a specified number of times
REPLACE	Returns a string in which all occurrences of an argument within a second argument are replaced with a third argument
RID	Returns the RID of a row
RIGHT	Returns a string that consists of the specified number of rightmost bytes or specified string units
ROUND	Returns a number rounded to the specified number of places to the right or of the decimal place
ROUND_TIMESTAMP	Returns a timestamp rounded to the unit specified by the timestamp format string
ROWID	Returns a row ID representation of its argument
RPAD	Returns a string that is padded on the right with blanks or a specified string
RTRIM	Returns the characters of an argument with the trailing blanks or hexadecimal zeros removed
SCORE	Returns a relevance score that measures how well a document matches the query used to search a text search index
SECOND	Returns the second part of its argument
SIGN	Returns the sign of an argument
SIN	Returns the sine of an argument that is expressed as an angle in radians
SINH	Returns the hyperbolic sine of an argument that is expressed as an angle in radians
SMALLINT	Returns a small integer representation of its argument
SOAPHTTPC and SOAPHTTPV	Returns a CLOB or VARCHAR representation of XML data from a request to a web service
SOAPHTTPNC and SOAPHTTPNV	Returns a complete CLOB or VARCHAR representation of XML data from a complete request to a web service
SOUNDEX	Returns a value that represents the sound of the words in the argument
SPACE	Returns a string that consists of the number of blanks the argument specifies
SQRT	Returns the square root of its argument
STDDEV_POP or STDDEV	Returns the population standard deviation (division by n) of a set of numbers
STDDEV_SAMP	Returns the sample standard deviation (division by n-1) of a set of numbers
STRIP	Returns the characters of a string with the blanks (or specified character) at the beginning, end, or both beginning and end of the string removed
FL 506 STRLEFT	Returns a string that consists of the specified number of most bytes or the specified string units
FL 506 STRPOS	Returns the position of the first occurrence of an argument within another argument
FL 506 STRRIGHT	Returns a string that consists of the specified number of rightmost bytes or specified string units
SUBSTR	Returns a substring of a string
SUBSTRING	Returns a substring of a string using the specified string units
SUM	Returns the sum of a set of numbers
TAN	Returns the tangent of an argument that is expressed as an angle in radians
TANH	Returns the hyperbolic tangent of an argument that is expressed as an angle in radians
TIME	Returns a time derived from its argument
TIMESTAMP	Returns a timestamp derived from its arguments
TIMESTAMPADD	Returns a timestamp derived from adding the specified interval to a timestamp
TIMESTAMP_FORMAT	Returns a timestamp for a character string expression, using a specified format to interpret the string
TIMESTAMP_ISO	Returns a timestamp derived from its arguments
TIMESTAMPDIFF	Returns an estimated number of the specified intervals based on the difference between two timestamps
TIMESTAMP_TZ	Returns a timestamp with a time zone derived from its arguments
TO_CHAR	Returns a character string representation of a timestamp value that has been formatted using a specified character template
FL 506 TO_CLOB	Returns a CLOB representation of the first argument
TO_DATE	Returns a timestamp value that is based on the interpretation of the input string using the specified format
TO_NUMBER	Returns a DECFLOAT(34) value that is based on the interpretation of the input string using the specified format
TOTALORDER	Returns a SMALLINT value that indicates the comparison order of two arguments
FL 506 TO_TIMESTAMP	Returns a timestamp for a character string expression, using a specified format to interpret the string
TRANSLATE	Returns a string with one or more characters translated
TRIM	Removes bytes from the beginning, from the end, or from both the beginning and end of a string expression
TRIM_ARRAY	Returns an array after removing elements from the end of an ordinary array.
TRUNCATE or TRUNC	Returns a number truncated to the specified number of places to the right or of the decimal point
TRUNC_TIMESTAMP	Returns a timestamp truncated to the unit specified by the timestamp format string
UCASE	Returns a string with the characters converted to uppercase
UNICODE	Returns the Unicode (UTF-16) code value of the most character of the argument as an integer
UNICODE_STR or UNISTR	Returns a string in Unicode (UTF-8 or UTF-16) that represents a Unicode encoding of the argument
UNPACK	Returns a row of values that are derived from unpacking the input binary string. It is used to unpack a string that was encoded according to the PACK function
UPPER	Returns a string with the characters converted to uppercase
VALUE	Same as COALESCE
VARBINARY	Returns a varying-length binary string representation of its argument
VARCHAR	Returns the varying-length character string representation of its argument
VARCHAR9	Returns the fixed-length character string representation of its argument
VARCHAR_FORMAT	Returns a varying-length character string representation of a timestamp, with the string in a specified format
VARGRAPHIC	Returns a varying-length graphic string representation of its argument
VAR_POP or VARIANCE or VAR	Returns the biased variance (division by n) of a set of numbers
VAR_SAMP or VARIANCE_SAMP	Returns the sample variance (division by n-1) of a set of numbers
VERIFY_GROUP_FOR_USER	Returns a value that indicates whether the primary authorization ID and the group authorization IDs that are associated with the first argument are included in the authorization names that are specified in the list of the second argument.
VERIFY_ROLE_FOR_USER	Returns a value that indicates whether the roles that are associated with the first argument are included in the role names that are specified in the list of the second argument
VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER	Returns a value that indicates whether the authorization ID that is associated with first argument has acquired a role in a trusted connection and whether that acquired role is included in the role names that are specified in the list of the second argument
WEEK	Returns an integer that represents the week of the year with Sunday as the first day of the week
WEEK_ISO	Returns an integer that represents the week of the year with Monday as first day of a week
XMLAGG	Returns an XML type that represents a concatenation of XML elements from a collection of XML elements
XMLATTRIBUTES	Returns an XML sequence that contains an XQuery attribute node for each non-null argument
XMLCOMMENT	Returns an XML value with a single comment node from a string expression
XMLCONCAT	Returns an XML value that represents a forest of XML elements generated by concatenating a variable number of arguments
XMLDOCUMENT	Returns an XML value with a single document node and zero or more nodes as its children
XMLELEMENT	Returns an XML value that represents an XML element
XMLFOREST	Returns an XML value that represents a forest of XML elements that all share a specific pattern
XMLMODIFY	Returns an XML value that might have been modified by the evaluation of an XQuery updating expression and XQuery variables that are specified as input arguments
XMLNAMESPACES	Returns the declaration of one or more XML namespaces
XMLPARSE	Returns an XML value from parsing the argument as an XML document
XMLPI	Returns an XML value with a single processing instruction node
XMLQUERY	Returns an XML value from the evaluation of an XPath expression against a set of arguments
XMLSERIALIZE	Returns an SQL character string or a BLOB value from an XML value
XMLTABLE	Returns a result table from the evaluation of XQuery expressions, possibly using specified input arguments as XQuery variables
XMLTEXT	Returns an XML value with a single text node that contains the value of the argument
XMLXSROBJECTID	Returns the XSR object identifier of the XML schema that is used to validate the XML document specified in the argument
XSLTRANSFORM	The XSLTRANSFORM function transforms an XML document into a different data format. The output can be any form possible for the XSLT processor, including but not limited to XML, HTML, and plain text. Note: This is a Db2-supplied user-defined function.
YEAR	Returns the year part of its argument

Notes:

1. Supported in Db2 for z/OS as a passthrough-only expression, which is passed through to IBM Db2 Analytics Accelerator for z/OS. For more information, see Accelerating queries with passthrough-only expressions.