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
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
Start of changeARRAY_AGGEnd of change Start of changeReturns an array in which each value of the input set is assigned to an element of the arrayEnd of change
Start of changeARRAY_DELETEEnd of change Start of changeReturns an array with the requested elements deleted.End of change
Start of changeARRAY_FIRSTEnd of change Start of changeReturns the minimum array index value of an array.End of change
Start of changeARRAY_LASTEnd of change Start of changeReturns the maximum array index value of an array.End of change
Start of changeARRAY_NEXTEnd of change Start of changeReturns the next larger array index value for an array, relative to a specified array index argument.End of change
Start of changeARRAY_PRIOREnd of change Start of changeReturns the next smaller array index value for an array, relative to a specified array index argument.End of change
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
Start of changeBLOCKING_THREADSEnd of change Start of changeReturns a table with one row for each lock or claim that threads hold against the databases that are specified in the input parameterEnd of change
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
Start of changeCARDINALITYEnd of change Start of changeReturns a value of that represents the number of elements of an arrayEnd of change
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
CHARACTER_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
COVARIANCE or COVAR Returns the population covariance of a set of number pairs
Start of changeCOVAR_SAMP or COVARIANCE_SAMPEnd of change Start of changeReturns unbiased sample covariance (n-1) of a set of number pairsEnd of change
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
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
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
ENCRYPT_TDES 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
FLOAT Same as DOUBLE
FLOOR Returns the largest integer that is less than or equal to the argument
GENERATE_UNIQUE 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
Start of changeGROUPINGEnd of change Start of changeReturns 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 argumentEnd of change
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.
LAST_DAY Returns a date that represents the last day of the month of the date argument
LCASE Returns a string with the characters converted to lowercase
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
Start of changeMAX_CARDINALITYEnd of change Start of changeReturns 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.End of change
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 MQSeries® 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 MQSeries 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 MQSeries location with a CLOB column and without removing the messages from the queue
MQREADCLOB Returns a message from a specified MQSeries location (return value of CLOB) without removing the message from the queue
MQRECEIVE Returns a message from a specified MQSeries 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 MQSeries 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 MQSeries location with a CLOB column and with removal of messages from the queue
MQRECEIVECLOB Returns a message from a specified MQSeries location (return value of CLOB) with removal of message from the queue
MQSEND Sends data to a specified MQSeries 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
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
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
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
POWER 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
RAND Returns a double precision floating-point random number
REAL Returns a single precision floating-point representation of its argument
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
Start of changeSTDDEV_POP or STDDEVEnd of change Start of changeReturns the population standard deviation (division by n) of a set of numbersEnd of change
Start of changeSTDDEV_SAMPEnd of change Start of changeReturns the sample standard deviation (division by n-1) of a set of numbersEnd of change
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
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
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
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
Start of changeTRIM_ARRAYEnd of change Start of changeReturns an array after removing elements from the end of an ordinary array.End of change
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 UNICODE_STR 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
Start of changeVARCHAR9End of change Start of changeReturns the fixed-length character string representation of its argument End of change
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
Start of changeVAR_POP or VARIANCE or VAREnd of change Start of changeReturns the biased variance (division by n) of a set of numbersEnd of change
Start of changeVAR_SAMP or VARIANCE_SAMPEnd of change Start of changeReturns the sample variance (division by n-1) of a set of numbersEnd of change
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
Start of changeXSLTRANSFORMEnd of change Start of changeThe 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.
End of change
YEAR Returns the year part of its argument