IBMMainframes.com

Quick References for IBM Mainframe Programming

DB2 BUILT-IN SQL FUNCTIONS

SQL COLUMN FUNCTIONS:

AVG

 Returns the Average of a set of numbers
CORRELATION or CORR  Returns the Coefficient of the correlation of a set of number pairs

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

 Returns the covariance (population) of a set of number pairs

MAX

 Returns the maximum value in a set of values
MIN  Returns the minimum value in a set of values

MEDIAN

 Returns the median of a set of numbers, The median of a set of data is the middlemost number in the set

STDDEV

 Returns the standard deviation of a set of numbers

SUM

 Returns the sum of a set of numbers
VARIANCE or VAR  Returns the variance of a set of numbers
XMLAGG  Returns an XML sequence that contains an item for each non-null value in a set of XML values.

 

 

SQL SCALAR FUNCTIONS:

   
ABS  Returns the absolute value of its argument
ACOS  Returns the arc cosine of an argument as an angle, expressed in radians, The ACOS and COS functions are inverse operations.
ADD_MONTHS  Returns a date that represents expression plus a specified number of months.
ASIN  Returns the arc sine of the argument as an angle, expressed in radians. The ASIN and SIN functions are inverse operations.
ATAN  Returns the arc tangent of the argument as an angle, expressed in radians. The ATAN and TAN functions are inverse operations.
BIGINT  Returns a big integer representation of either a number or a character or graphic string representation of a number.
BLOB  Returns a BLOB representation of a string of any type or of a row ID type.
CEIL or CEILING  Returns the smallest integer value that is greater than or equal to the argument.
CHAR  Returns a fixed-length character string representation of the argument.
CHARACTER_LENGTH  Returns the length of the first argument in the specified string unit.
CLOB  Returns a CLOB representation of a string.
COALESCE  Returns the value of the first nonnull expression.
COLLATION_KEY  Returns a varying-length binary string that represents the collation key of the argument in the specified collation.
COMPARE_DECFLOAT  Returns a SMALLINT value that indicates whether the two arguments are equal or unordered, or whether one argument is greater than the other.
CONCAT  Returns the concatenation of two strings.
COS  Returns the cosine of the argument, where the argument is an angle, expressed in radians. The COS and ACOS functions are inverse operations.
COSH  Returns the hyperbolic cosine of the argument, where the argument is an angle, expressed in radians.
DATE  Returns a date that is derived from a value.
DAY  Returns the day part of a value.
DAYOFMONTH  Returns the day part of a value. The function is similar to the DAY function, except DAYOFMONTH does not support a date or timestamp duration as an argument.
DAYOFWEEK  Returns an integer, in the range of 1 to 7, that represents the day of the week, where 1 is Sunday and 7 is Saturday.
DAYOFYEAR  Returns an integer, in the range of 1 to 366, that represents the day of the year, where 1 is January 1.
DAYS  Returns an integer representation of a date.
DBCLOB  Returns a DBCLOB representation of a character string value (with the single-byte characters converted to double-byte characters) or a graphic string value.
DECFLOAT  Returns a decimal floating-point representation of either a number or a character string representation of a number, a decimal number, an integer or a floating-point number.
DECIMAL  Returns a decimal representation of either a number or a character-string or graphic-string representation of a number, an integer, or a decimal number.
DECODE  Returns the absolute value of its argument
DEGREES  Returns the number of degrees of the argument, which is an angle, expressed in radians.
DIFFERENCE  Returns a value, from 0 to 4, that represents the difference between the sounds of two strings, based on applying the SOUNDEX function to the strings. A value of 4 is the best possible sound match.
DIGITS  Returns a character string representation of the absolute value of a number.
DOUBLE_PRECISION or DOUBLE  Returns a floating-point representation of either a number or a character-string or graphic-string representation of a number, an integer, a decimal number, or a floating-point number.
DSN_XMLVALIDATE  Returns an XML value that is the result of applying XML schema validation to the first argument of the function.
EXP  Returns a value that is the base of the natural logarithm (e), raised to a power that is specified by the argument. The EXP and LN functions are inverse operations.
EXTRACT  Returns a portion of a date or timestamp, based on its arguments.
FLOAT  Returns a floating-point representation of either a number or a string representation of a number. FLOAT is a synonym for the DOUBLE function.
FLOOR  Returns the largest integer value that is less than or equal to the argument.
GENERATE_UNIQUE  Returns a bit data character string that is unique, compared to any other execution of the same function.
GETVARIABLE  Returns a varying-length character-string representation of the current value of the session variable that is identified by the argument.
GRAPHIC  Returns a fixed-length graphic-string representation of a character string or a graphic string value, depending on the type of the first argument.
HEX  Returns a hexadecimal representation of a value.
HOUR  Returns the hour part of a value.
IFNULL  Returns the first nonnull expression.
INSERT  Returns a string where, beginning at start in source-string, length characters have been deleted and insert-string has been inserted.
INTEGER or INT  Returns an integer representation of either a number or a character string or graphic string representation of an integer.
JULIAN_DAY  Returns an integer value that represents a number of days from January 1, 4713 B.C. (the start of the Julian date calendar) to the date that is specified in the argument.
LAST_DAY  Returns a date that represents the last day of the month of the date argument.
LCASE or LOWER  Returns a string in which all the characters are converted to lowercase characters.
LEFT  Returns a string that consists of the specified number of leftmost bytes of the specified string units.
LENGTH  Returns the length of a value.
LN  Returns the natural logarithm of the argument. Same as LOG function of the old DB2 versions. The LN and EXP functions are inverse operations.
LOCATE  Returns the position at which the first occurrence of an argument starts within another argument.
LOCATE_IN_STRING  Returns  the position at which an argument starts within a specified string.
LOG10  Returns  the common logarithm (base 10) of a number.
LPAD  Returns a string that is composed of string-expression that is padded on the left, with pad or blanks. The LPAD function treats leading or trailing blanks in string-expression as significant.
LTRIM  Returns the characters of a string with the leading blanks removed
MAX  Returns the maximum value in a set of values.
MICROSECOND  Returns the microsecond part of a value.
MIDNIGHT_SECONDS  Returns an integer, in the range of 0 to 86400, that represents the number of seconds between midnight and the time that is specified in the argument.
MIN  Returns the minimum value in a set of values.
MINUTE  Returns the minute part of a value.
MOD  Returns the remainder of one argument divided by second argument.
MONTH  Returns the month part of a value.
MONTHS_BETWEEN  Returns an estimate of the number of months between two arguments.
MQREAD  Returns a message from a specified MQSeries location without removing the message from the queue.
MQRECEIVE  Returns a message from a specified MQSeries location and removes the message from the queue.
MQSEND  Returns a varying-length character string that indicates whether the function of sending data to a specified MQSeries location was successful or unsuccessful.
MULTIPLY_ALT  Returns the product of the two arguments. This function is an alternative to the multiplication operator and is especially useful when the sum of the precisions of the arguments exceeds 31.
NEXT_DAY  Returns a datetime value that represents the first weekday, named by string-expression, that is later than the date in expression.
NORMALIZE_STRING  Returns a normalized string that can be used for comparison
NULLIF  Returns the null value if the two arguments are equal; otherwise, it returns the value of the first argument.
NVL  Returns the first argument that is not null.
OVERLAY  Returns a string that is composed of one argument that is 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 expression 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 the first argument to the power of the second argument. (Xy)
QUANTIZE  Returns a DECFLOAT value that is equal in value (except for any rounding) and sign to the first argument and that has an exponent that is set to equal the exponent of the second argument.
QUARTER  Returns an integer between 1 and 4 that represents the quarter of the year in which the date resides. For example, any dates in January, February, or March return the integer 1.
RADIANS  Returns the number of radians for an argument that is expressed in degrees.
RAISE_ERROR  Returns an error with the specified SQLSTATE (along with SQLCODE -438) and error condition. The RAISE_ERROR function always returns the null value with an undefined data type.
RAND  Returns a random floating-point value between 0 and 1. An argument can be specified as an optional seed value.
REAL  Returns a single-precision floating-point representation of either a number or a string representation of a number.
REPEAT  Returns a character string that is composed of an argument that is repeated a specified number of times.
REPLACE  Returns a string in which all occurrences of an search-string within a source-string replaced with a replace-string. If search-string is not found in source-string, source-string is returned unchanged.
RID  Returns the record ID (RID) of a row. The RID is used to uniquely identify a row.
RIGHT  Returns a string that consists of the specified number of rightmost bytes or specified string unit from a string
ROUND  Returns a number that is rounded to the specified number of places to the right or left of the decimal place.
ROUND_TIMESTAMP  Returns a timestamp that is rounded to the unit that is 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 removed.
SECOND  Returns the seconds part of a value with optional fractional seconds.
SIGN  Returns an indicator of the sign of the argument. (-1 / 0 / 1)
SIN  Returns the sine of the argument, where the argument is an angle, expressed in radians.
SINH  Returns the hyperbolic sine of the argument, where the argument is an angle, expressed in radians.
SMALLINT  Returns a small integer representation either of a number or of a string representation of a number.
SOUNDEX  Returns a 4-character code that represents the sound of the words in the argument. The result can be compared to the results of the SOUNDEX function of other strings.
SPACE  Returns a character string that consists of the number of SBCS blanks that the argument specifies.
SQRT  Returns the square root of the argument.
STRIP  Returns a string with the blanks at the beginning, end, or both of the string removed.
SUBSTR  or SUBSTRING  Returns a substring of a string.
TAN  Returns the tangent of the argument, where the argument is an angle, expressed in radians.
TANH  Returns the hyperbolic tangent of the argument, where the argument is an angle, expressed in radians.
TIME  Returns a time that is derived from a value.
TIMESTAMP  Returns a TIMESTAMP WITHOUT TIME ZONE value from its argument or arguments.
TIMESTAMPADD  Returns the result of adding the specified number of the designated interval to the timestamp value.
TIMESTAMP_FORMAT  Returns a TIMESTAMP WITHOUT TIME ZONE value that is based on the interpretation of the input string using the specified format.
TIMESTAMPDIFF  Returns an estimated number of intervals of the type that is defined by the first argument, based on the difference between two timestamps.
TIMESTAMP_TZ  Returns a TIMESTAMP WITH TIME ZONE value from the input 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 an ordering for DECFLOAT values. The TOTALORDER function returns a small integer value that indicates how expression1 compares with expression2.
TRANSLATE  Returns a value in which one or more characters of the first argument might have been converted to other characters.
TRIM  Returns a string with the specified bytes at the beginning, end, or both of the string removed.
TRUNCATE or TRUNC  Returns a number truncated to the specified spaces to the right/left of the decimal
UCASE or UPPER  Returns a string in which all the characters have been converted to uppercase characters, based on the CCSID of the argument.
UNICODE  Returns the Unicode UTF-16 code value of the leftmost character of the argument as an integer.
UNICODE_STR  Returns a string in Unicode UTF-8 or UTF-16, depending on the specified option.
VALUE  Returns the value of the first non-null expression. Same as COALESCE.
VARBINARY  Returns a varying-length binary string representation of a string of any type.
VARCHAR  Returns a varying-length character string representation of the value specified by the first argument.
VARCHAR_FORMAT  Returns a character string representation of the first argument in the format indicated by format-string.
VARGRAPHIC  Returns a varying-length graphic string representation of a the first argument. The first argument can be a character string value or a graphic string value.
WEEK  Returns an integer in the range of 1 to 54 that represents the week of the year. The week starts with Sunday, and January 1 is always in the first week.
YEAR  Returns the year part of a value that is a character or graphic string. The value must be a valid string representation of a date or timestamp.

                                                             

 

 

Home | Mainframe Wiki | Downloads | Programs | References | Mainframe Forum