List of supported built-in functions
This section contains an alphabetical list of the built-in functions that Db2 for z/OS® supports.
| 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 |
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 |
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 |
| 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 |
COVAR_SAMP or COVARIANCE_SAMP![]() |
Returns unbiased sample
covariance (n-1) of a set of number pairs![]() |
| 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 |
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![]() |
| 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 |
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 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 |
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 |
| 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 |
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 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 |
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 |

