using db2 functions

IN THIS CHAPTER

• Column Functions

• Scalar Functions

• The RAISE_ERROR Function

• MQSeries Built-in Functions

• XML Publishing Built-inFunctions

• Built-in Function Guidelines

CHAPTER 3

Using DB2 Functions

Two types of built-in functions can be applied to data in aDB2 table using SQL: column functions and scalar functions.You can use these functions to further simplify the require-ments of complex data access.

NOTE

DB2 also provides the capability for users to create their ownfunctions. This capability, called user-defined functions, isdiscussed in-depth in Chapter 4, “Using DB2 User-DefinedFunctions and Data Types.”

Functions are called by specifying the function name and anyrequired operands. A built-in function can be used any placean expression can be used (with some exceptions).

Column FunctionsColumn functions compute, from a group of rows, a singlevalue for a designated column or expression. This providesthe capability to aggregate data, thereby enabling you toperform statistical calculations across many rows with oneSQL statement. To fully appreciate the column functions, youmust understand SQL’s set-level processing capabilities.

This list shows some rules for the column functions:

• Column functions can be executed only in SELECT state-ments.

• A column function must be specified for an explicitlynamed column or expression.

• Each column function returns only one value for the setof selected rows.

• If you apply a column function to one column in aSELECT statement, you must apply column functions to

04 0672326132 CH03 4/20/04 1:43 PM 10

any other columns specified in the same SELECT statement, unless you also use theGROUP BY clause.

• Use GROUP BY to apply a column function to a group of named columns. Any othercolumn named in the SELECT statement must be operated on by a column function.

• The result of any column function (except the COUNT and COUNT_BIG functions) willhave the same data type as the column to which it was applied. The COUNT functionreturns an integer number; COUNT_BIG returns a decimal number.

• The result of any column function (except the COUNT and COUNT_BIG functions) canbe null. COUNT and COUNT_BIG always return a numeric result.

• Columns functions will not return a SQLCODE of +100 if the predicate specified in theWHERE clause finds no data. Instead, a null is returned. For example, consider thefollowing SQL statement:

SELECT MAX(SALARY)FROM DSN8810.EMPWHERE EMPNO = ‘999999’;

There is no employee with an EMPNO of ‘999999’ in the DSN8810.EMP table. Thisstatement therefore returns a null for the MAX(SALARY). Of course, this does notapply to COUNT and COUNT_BIG, both of which always return a value, never a null.

• When using the AVG, MAX, MIN, STDDEV, SUM, and VARIANCE functions on nullablecolumns, all occurrences of null are eliminated before applying the function.

• You can use the DISTINCT keyword with all column functions to eliminate duplicatesbefore applying the given function. DISTINCT has no effect, however, on the MAX andMIN functions.

• You can use the ALL keyword to indicate that duplicates should not be eliminated.ALL is the default.

A column function can be specified in a WHERE clause only if that clause is part of asubquery of a HAVING clause. Additionally, every column name specified in the expressionof the column function must be a correlated reference to the same group.

The column functions are AVG, COUNT, COUNT_BIG, MAX, MIN, STDDEV, SUM, and VARIANCE.

The AVG FunctionThe AVG function computes the average of the values for the column or expression speci-fied as an argument. This function operates only on numeric arguments. The followingexample calculates the average salary of each department:

SELECT WORKDEPT, AVG(SALARY)FROM DSN8810.EMPGROUP BY WORKDEPT;

Column Functions 1113

04 0672326132 CH03 4/20/04 1:43 PM Page 111

The AVG function is the preferred method of calculating the average of a group of values.Although an average, in theory, is nothing more than a sum divided by a count, DB2 maynot return equivalent values for AVG(COL_NAME) and SUM(COL_NAME)/COUNT(*). The reasonis that the COUNT function will count all rows regardless of value, whereas SUM ignoresnulls.

The COUNT FunctionThe COUNT function counts the number of rows in a table, or the number of distinct valuesfor a given column. It can operate, therefore, at the column or row level. The syntaxdiffers for each. To count the number of rows in the EMP table, issue this SQL statement:

SELECT COUNT(*)FROM DSN8810.EMP;

It does not matter what values are stored in the rows being counted. DB2 will simplycount the number of rows and return the result. To count the number of distinct depart-ments represented in the EMP table, issue the following

SELECT COUNT(DISTINCT WORKDEPT)FROM DSN8810.EMP;

The keyword DISTINCT is not considered an argument of the function. It simply specifiesan operation to be performed before the function is applied. When DISTINCT is coded,duplicate values are eliminated.

If DISTINCT is not specified, then ALL is implicitly specified. ALL also can be explicitly spec-ified in the COUNT function. When ALL is specified, duplicate values are not eliminated.

NOTE

The argument of the COUNT function can be of any built-in data type other than a large object:CLOB, DBCLOB, or BLOB. Character string arguments can be no longer 255 bytes and graphicstring arguments can be no longer than 127 bytes.

The result of the COUNT function cannot be null. COUNT always returns an INTEGER value greaterthan or equal to zero.

The COUNT_BIG FunctionThe COUNT_BIG function is similar to the COUNT function. It counts the number of rows in atable, or the number of distinct values for a given column. However, the COUNT_BIG func-tion returns a result of data type DECIMAL(31,0), whereas COUNT can return a result only aslarge as the largest DB2 integer value, namely +2,147,483,647.

The COUNT_BIG function works the same as the COUNT function, except it returns a decimalvalue. Therefore, the example SQL for COUNT is applicable to COUNT_BIG. Simply substituteCOUNT_BIG for COUNT. For example, the following statement counts the number of rows inthe EMP table (returning a decimal value, instead of an integer):

SELECT COUNT_BIG(*)FROM DSN8810.EMP;

CHAPTER 3 Using DB2 Functions112

04 0672326132 CH03 4/20/04 1:43 PM Page 112

NOTE

The COUNT_BIG function has the same restrictions as the COUNT function. The argument of theCOUNT_BIG function can be of any built-in data type other than a large object: CLOB, DBCLOB, orBLOB. Character string arguments can be no longer than 255 bytes and graphic string argumentscan be no longer than 127 bytes.

The result of the COUNT_BIG function cannot be null. COUNT_BIG returns a decimal value greaterthan or equal to zero.

The MAX FunctionThe MAX function returns the largest value in the specified column or expression. Thefollowing SQL statement determines the project with the latest end date:

SELECT MAX(ACENDATE)FROM DSN8810.PROJACT;

NOTE

The result of the MAX function is of the same data type as the column or expression on which itoperates.

The argument of the MAX function can be of any built-in data type other than a large object:CLOB, DBCLOB, or BLOB. Character string arguments can be no longer than 255 bytes and graphicstring arguments can be no longer than 127 bytes.

A somewhat more complicated example using MAX is shown below. It returns the largestsalary paid to a man in department D01:

SELECT MAX(SALARY)FROM DSN8810.EMPWHERE WORKDEPT = ‘D01’AND SEX = ‘M’;

The MIN FunctionThe MIN function returns the smallest value in the specified column or expression. Toretrieve the smallest bonus given to any employee, issue this SQL statement:

SELECT MIN(BONUS)FROM DSN8810.EMP;

NOTE

The result of the MIN function is of the same data type as the column or expression on which itoperates.

The argument of the MIN function can be of any built-in data type other than a large object:CLOB, DBCLOB, or BLOB. Character string arguments can be no longer than 255 bytes and graphicstring arguments can be no longer than 127 bytes.

Column Functions 1133

04 0672326132 CH03 4/20/04 1:43 PM Page 113

The STDDEV FunctionThe STDDEV function returns the standard deviation of a set of numbers. The standarddeviation is calculated at the square root of the variance. For example

SELECT STDDEV(SALARY)FROM DSN8810.EMPWHERE WORKDEPT = ‘D01’;

NOTE

The argument of the STDDEV function can be any built-in numeric data type. The resulting stan-dard deviation is a double precision floating-point number.

The SUM FunctionThe accumulated total of all values in the specified column or expression are returned bythe SUM column function. For example, the following SQL statement calculates the totalyearly monetary output for the corporation:

SELECT SUM(SALARY+COMM+BONUS)FROM DSN8810.EMP;

This SQL statement adds each employee’s salary, commission, and bonus. It then aggre-gates these results into a single value representing the total amount of compensation paidto all employees.

NOTE

The argument of the SUM function can be any built-in numeric data type. The resulting sum mustbe within the range of acceptable values for the data type. For example, the sum of an INTEGERcolumn must be within the range –2,147,483,648 to +2,147,483,647. This is because the datatype of the result is the same as the data type of the argument values, except:

• The sum of SMALLINT values returns an INTEGER result.

• The sum of single precision floating point values returns a double precision floating-pointresult.

The VARIANCE FunctionThe VARIANCE function returns the variance of a set of numbers. The result is the biasedvariance of the set of numbers. The variance is calculated as follows:

VARIANCE = SUM(X**2)/COUNT(X) - (SUM(X)/COUNT(X))**2

NOTE

The argument of the VARIANCE function can be any built-in numeric data type. The resulting vari-ance is a double precision floating-point number.

For brevity and ease of coding, VARIANCE can be shortened to VAR.


04 0672326132 CH03 4/20/04 1:43 PM Page 114

Scalar FunctionsScalar functions are applied to a column or expression and operate on a single value.Contrast this with the column functions, which are applied to a set of data and returnonly a single result.

There are more than 110 scalar functions, each of which can be applied to a column valueor expression.

NOTE

DB2 V6 significantly improved IBM’s support for built-in scalar functions. Prior to DB2 V6 therewere only 22 built-in scalar functions. Additional built-in functions were added for DB2 V7 andV8, as well.

The result of a scalar function is a transformed version of the column or expression beingoperated on. The transformation of the value is based on the scalar function being appliedand the value itself. Consult the following descriptions of the DB2 scalar functions:

ABSVAL or ABS Converts a value of any numeric data type to its absolute value.

ACOS Returns the arc-cosine of the argument as an angle expressed in radians.

ADD_MONTHS Returns a date value that is the result of adding the second expression (which

is a duration) to the first expression (which is a date).

ASIN Returns the arc-sine of the argument as an angle expressed in radians.

ATAN Returns the arc-tangent of the argument as an angle expressed in radians.

ATANH Returns the hyperbolic arc-tangent of the argument as an angle expressed in

radians.

ATAN2 Returns the arc-tangent of the specified x and y coordinates as an angle

expressed in radians.

BLOB Converts a string or ROWID data type into a value of data type BLOB.

CCSID_ENCODING Returns the encoding scheme of the specified CCSID. The result will be one of

the following: ASCII, EBCDIC, UNICODE, or UNKNOWN.

CEILING or CEIL Converts the argument, represented as any numeric data type, to the small-

est integer value greater than or equal to the argument value.

CHAR Converts a DB2 date, time, timestamp, ROWID, floating point, integer, or

decimal value to a character value. For example

SELECT CHAR(HIREDATE, USA)FROM DSN8810.EMPWHERE EMPNO = ‘000140’;

This SQL statement returns the value for HIREDATE, in USA date format, of

the employee with the EMPNO of ‘000140’.

CLOB Converts a string or ROWID data type into a value of data type CLOB.

Scalar Functions 1153

V7

V7

04 0672326132 CH03 4/20/04 1:43 PM Page 115

COALESCE For nullable columns, returns a value instead of a null (equivalent to VALUE

function).

CONCAT Converts two strings into the concatenation of the two strings.

COS Returns the cosine of the argument as an angle expressed in radians.

COSH Returns the hyperbolic cosine of the argument as an angle expressed in

radians.

DATE Converts a value representing a date to a DB2 date. The value to be

converted can be a DB2 timestamp, a DB2 date, a positive integer, or a char-

acter string.

DAY Returns the day portion of a DB2 date or timestamp.

DAYOFMONTH Similar to DAY except DAYOFMONTH cannot accept a date duration or time

duration as an argument.

DAYOFWEEK Converts a date, timestamp, or string representation of a date or timestamp

into an integer that represents the day of the week. The value 1 represents

Sunday, 2 Monday, 3 Tuesday, 4 Wednesday, 5 Thursday, 6 Friday, and 7

Saturday.

DAYOFWEEK_ISO Similar to the DAYOFWEEK function, but results in different numbers to repre-

sent the day of the week. Converts a date, timestamp, or string representa-

tion of a date or timestamp into an integer that represents the day of the

week. The value 1 represents Monday, 2 Tuesday, 3 Wednesday, 4 Thursday,

5 Friday, 6 Saturday, and 7 Sunday.

DAYOFYEAR Converts a date, timestamp, or string representation of a date or timestamp

into an integer that represents the day within the year. The value 1 repre-

sents January 1st, 2 January 2nd, and so on.

DAYS Converts a DB2 date or timestamp into an integer value representing one

more than the number of days since January 1, 0001.

DBCLOB Converts a string or ROWID data type into a value of data type DBCLOB.

DECIMAL or DEC Converts any numeric value, or character representation of a numeric value,

to a decimal value.

DECRYPT_BIT Decrypts an encrypted column into a binary value using a user-provided

encryption password.

DECRYPT_CHAR Decrypts an encrypted column into a character value using a user-provided

encryption password.

DECRYPT_DB Decrypts an encrypted column into a variable graphic value using a user-

provided encryption password.

DEGREES Returns the number of degrees for the number of radians supplied as an

argument.


V7

V8

V8

V8

04 0672326132 CH03 4/20/04 1:43 PM Page 116

DIGITS Converts a number to a character string of digits. Be aware that the DIGITS

function will truncate the negative sign for negative numbers.

DOUBLE or FLOAT Converts any numeric value, or character representation of a numeric value,

into a double precision floating point value. Another synonym for this func-

tion is DOUBLE-PRECISION.

ENCRYPT_TDES Uses the Triple DES encryption algorithm to encrypt a column in a table

using a user-provided encryption password.

EXP Returns the exponential function of the numeric argument. The EXP and LOG

functions are inverse operations.

FLOOR Converts the argument, represented as any numeric data type, to the largest

integer value less than or equal to the argument value.

GENERATE_UNIQUE Generates a CHAR(13) FOR BIT DATA value that is unique across the Sysplex.

GETHINT Obtains a hint to help remember the encryption password.

GETVARIABLE Retrieves session variable values. Details on this function’s operation can be

found in Chapter 10, “DB2 Security and Authorization.”

GRAPHIC Converts a string data type into a value of data type GRAPHIC.

GREATEST Returns the maximum value in a supplied set of values. The argument values

can be of any built-in data type other than CLOB, DBCLOB, BLOB, or ROWID.

HEX Converts any value other than a long string to hexadecimal.

HOUR Returns the hour portion of a time, a timestamp, or a duration.

IDENTITY_VAL_ Returns the most recently assigned value for an identity column. (No input

LOCAL() parameters are used by this function.)

IFNULL Returns the first argument in a set of two arguments that is not null. For

example

SELECT EMPNO, IFNULL(WORKDEPT, ‘N/A’)FROM DSN8810.EMP;

This SQL statement returns the value for WORKDEPT for all employees, unless

WORKDEPT is null, in which case it returns the string ‘N/A’.

INSERT Accepts four arguments. Returns a string with the first argument value

inserted into the fourth argument value at the position specified by the

second argument value. The third argument value indicates the number of

bytes to delete (starting at the position indicated by the third argument

value). For example

SELECT INSERT(‘FLAMING’, 2, 1, ‘R’)FROM SYSIBM.SYSDUMMY1;


V8

V8

V8

V8

V7

V7

04 0672326132 CH03 4/20/04 1:43 PM Page 117

This SQL statement returns the value ‘FLAMING’. Here is another example

SELECT INSERT(‘BOSTON CHOWDER’, 8, 0, ‘CLAM ‘)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns the value ‘BOSTON CLAM CHOWDER’.

CAUTION

Both the value of the argument being inserted into, and the value of the argument that isbeing inserted, must have the same string data type. That is, both expressions must be charac-ter strings, or both expressions must be graphic strings. If the expressions are character strings,neither can be a CLOB. If the expressions are graphic strings, neither can be a DBCLOB.

INTEGER or INT Converts any number, or character representation of a number, to an integer

by truncating the portion of the number to the right of the decimal point. If

the whole number portion of the number is not a valid integer (for example,

the value is out of range), an error results.

JULIAN_DAY Converts a DB2 date or timestamp, or character representation of a date or

timestamp, into an integer value representing the number of days from

January 1, 4712 B.C. to the date specified in the argument.

NOTE

January 1, 4712 B.C. is the start date of the Julian calendar.

LAST_DAY Returns the last day of the month for the specified DB2 date or timestamp,

or character representation of a date or timestamp.

LEAST Returns the minimum value in a supplied set of values. The argument values


LEFT Returns a string containing only the leftmost characters of the string in the

first argument, starting at the position indicated by the second argument.

For example

SELECT LEFT(‘THIS IS RETURNED’, 4)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns ‘THIS’, which is the four leftmost characters of

the first argument.

LENGTH Returns the length of any column, which may be null. Does not include the

length of null indicators or variable character-length control values, but does

include trailing blanks for character columns.

LOCATE Returns the position of the first occurrence of the first string the second

string. For example

SELECT LOCATE(‘I’, ‘CRAIG MULLINS’)FROM SYSIBM.SYSDUMMY1;


V7

V7

04 0672326132 CH03 4/20/04 1:43 PM Page 118

This SQL statement returns the value 4, because the value ‘I’ first appears in

position four within the searched string. It also appears in the 11th position,

but that is of no concern to the LOCATE function. Optionally, a third argu-

ment can be supplied indicating where the search should start. For example

SELECT LOCATE(‘I’, ‘CRAIG MULLINS’, 7)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns the value 11, because after position 7, the value

‘I’ first appears in the 11th position. When the third argument is not speci-

fied, LOCATE defaults to the beginning of the second string.

LOG or LN Returns the natural logarithm of the numeric argument. The EXP and LOG

functions are inverse operations.

LOG10 Returns the base 10 logarithm of the numeric argument.

LOWER or LCASE Converts a character string into all lowercase characters.

LTRIM Removes the leading blanks from a character string.

MAX Returns the maximum value in a supplied set of values. The argument values


(This scalar function is not the same as the MAX column function discussed

earlier.) GREATEST is a synonym for the MAX scalar function.

MICROSECOND Returns the microsecond component of a timestamp or the character repre-

sentation of a timestamp.

MIDNIGHT_SECONDS Returns the number of seconds since midnight for the specified argument,

which must be a time, timestamp, or character representation of a time or

timestamp.

MIN Returns the minimum value in a supplied set of values. The argument values


(This scalar function is not the same as the MIN column function discussed

earlier.) LEAST is a synonym for MIN.

MINUTE Returns the minute portion of a time, a timestamp, a character

representation of a time or timestamp, or a duration.

MOD Returns the remainder of the division of the first argument by the second

argument. Both arguments must be numeric.

MONTH Returns the month portion of a date, a timestamp, a character representation

of a date or timestamp, or a duration.

MULTIPLY_ALT Can be used as an alternative to the multiplication operator. This function

returns a decimal value that is the product of multiplying the two arguments

together.


V7

V7

V7

04 0672326132 CH03 4/20/04 1:43 PM Page 119

NEXT_DAY Returns a timestamp indicating the first day of the week as specified in the

second argument that is later than the date expression specified in the first

argument. Valid values for the second argument are text representations of

the days of the week; that is, MONDAY, TUESDAY, and so on. For example

SELECT NEXT_DAY(CURRENT DATE, ‘FRIDAY’)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns a timestamp specifying the first Friday after

today.

NULLIF Returns a null when two specified expressions are equal; if not equal, the first

expression is returned.

POSSTR Similar to the LOCATE function, but with the arguments reversed. POSSTR

returns the position of the first occurrence of the second argument within

the first argument. For example

SELECT POSSTR(‘DATABASE ADMINISTRATION’, ‘ADMIN’)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns the value 10; the value ‘ADMIN’ first appears in

the 10th position.

POWER Returns the value of the first argument raised to the power of the second

argument.

QUARTER Converts a date, timestamp, or string representation of a date or timestamp

into an integer that represents the quarter within the year. The value 1 repre-

sents first quarter, 2 second quarter, 3 third quarter, and 4 fourth quarter.

RADIANS Returns the number of radians for the numeric argument expressed in

degrees.

RAND Returns a random floating-point number between 0 and 1. Optionally, an

integer value can be supplied as a seed value for the random value genera-

tor. For example

SELECT (RAND() * 100)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns a random number between 0 and 100.

REAL Converts any numeric value, or character representation of a numeric value,

into a single precision floating point value.

REPEAT Returns a character string that consists of the first argument repeated the

number of times specified in the second argument. For example

SELECT REPEAT(‘HO ‘, 3)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns the character string ‘HO HO HO ‘.


V7

04 0672326132 CH03 4/20/04 1:43 PM Page 120

REPLACE Returns a character string with the value of the second argument replaced by

each instance of the third argument in the first argument. For example

SELECT REPLACE(‘BATATA’, ‘TA’, ‘NA’)FROM SYSIBM.SYSDUMMY1;

This SQL statement replaces all instances of ‘TA’ with ‘NA’ changing the

character string ‘BATATA’ into ‘BANANA’.

CAUTION

Neither the first nor the second argument may be empty strings. The third argument, however,can be an empty string. If the third argument is an empty string, the REPLACE function willsimply replace each instance of the second argument with an empty string.

RIGHT Returns a string containing only the rightmost characters of the string in the

first argument, starting at the position indicated by the second argument.

For example

SELECT RIGHT(‘RETURN ONLY THIS’, 4)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns ‘THIS’, which is the four rightmost characters of

the first argument.

ROUND Rounds the first numeric argument to the number of places specified in the

second argument.

ROUND_TIMESTAMP Rounds the timestamp value specified in the first argument based on the unit

specified in the second argument. The timestamp can be rounded to the

nearest year, quarter, month, week, day, hour, minute, or second.

NOTE

Table 3.1 highlights the valid unit arguments for both the ROUND_TIMESTAMP and TRUNC_TIMESTAMP function.

ROWID Casts the specified argument to a ROWID data type. Although the argument

can be any character string, it should be a row ID value that was previously

generated by DB2. Otherwise, the value may not be an accurate DB2 ROWID.

RTRIM Removes the trailing blanks from a character string.

SECOND Returns the seconds portion of a time, a timestamp, a character representa-

tion of a time or timestamp, or a duration.

SIGN Returns a value that indicates the sign of the numeric argument. The

returned value will be –1 if the argument is less than zero, +1 if the argument

is greater than zero, and 0 if the argument equals zero.

SIN Returns the sine of the argument as an angle expressed in radians.

SINH Returns the hyperbolic sine of the argument as an angle expressed in

radians.


V7

04 0672326132 CH03 4/20/04 1:43 PM 1

SMALLINT Converts any number, or character representation of a number, to an integer

by truncating the portion of the number to the right of the decimal point. If

the whole number portion of the number is not a valid integer (for example,

the value is out of range), an error results.

SPACE Returns a string of blanks whose length is specified by the numeric argu-

ment. The string of blanks is an SBCS character string.

SQRT Returns the square root of the numeric argument.

STRIP Removes leading, trailing, or both leading and trailing blanks (or any specific

character) from a string expression.

SUBSTR Returns the specified portion of a character column from any starting point

to any ending point.

TAN Returns the tangent of the argument as an angle expressed in radians.

TANH Returns the hyperbolic tangent of the argument as an angle expressed in

radians.

TIME Converts a value representing a valid time to a DB2 time. The value to be

converted can be a DB2 timestamp, a DB2 time, or a character string.

TIMESTAMP Obtains a timestamp from another timestamp, a valid character-string repre-

sentation of a timestamp, or a combination of date and time values.

TIMESTAMP_FORMAT Returns a DB2 timestamp for the data in the first argument (which must be a

character expression) based on the formatting specified in the second argu-

ment. For example

SELECT TIMESTAMP_FORMAT(‘2004-12-15 23:59:59’, ‘YYYY-MM-DD HH24:MI:SS’)FROM SYSIBM.SYSDUMMY1;

This SQL statement converts the non-standard timestamp representation into

a standard DB2 timestamp.

TRANSLATE Translates characters from one expression to another. There are two forms of

the TRANSLATE function. If only one argument is specified, the character

string is translated to uppercase. Alternately, three arguments can be

supplied. In this case, the first argument is transformed by replacing the

character string specified in the third argument with the character string

specified in the second argument. For example

SELECT TRANSLATE(‘BACK’, ‘R’, ‘C’)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns ‘BARK’, because the character string ‘C’ is

replaced with the character string ‘R’.


V7

04 0672326132 CH03 4/20/04 1:43 PM Page 122

Optionally, a fourth argument can be specified. This is the pad character. If

the length of the second argument is less than the length of the third argu-

ment, the second argument will be padded with the pad character (or

blanks) to make up the difference in size. For example

SELECT TRANSLATE(‘BACK’, ‘Y’, ‘ACK’, ‘.’)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns ‘BY..’, because the character string ‘ACK’ is

replaced with the character string ‘Y’, and is padded with ‘.’ characters to

make up the difference in size.

The string to be translated must be a character string not exceeding 255

bytes or a graphic string of no more than 127 bytes. The string cannot be a

CLOB or DBCLOB.

TRUNCATE or TRUNC Converts the first numeric argument by truncating it to the right of the

decimal place by the integer number specified in the second numeric argu-

ment. For example

SELECT TRUNC(3.014015,2)FROM SYSIBM.SYSDUMMY1;

This SQL statement returns the number 3.010000, because the second argu-

ment specified that only 2 significant digits are required. The rest was trun-

cated.

TRUNC_TIMESTAMP Truncates the timestamp value specified in the first argument based on the

unit specified in the second argument. The timestamp can be truncated by

year, quarter, month, week, day, hour, minute, or second.

NOTE

Table 3.1 highlights the valid unit arguments for both the ROUND_TIMESTAMP and TRUNC_TIMESTAMP function.

UPPER or UCASE Converts a character string into all uppercase characters.

VALUE For nullable columns, returns a value instead of a null (equivalent to the

COALESCE function).

VARCHAR Converts a character string, date, time, timestamp, integer, decimal, floating

point, or ROWID value into a corresponding variable character string represen-

tation.

VARCHAR_FORMAT Returns the character representation for the timestamp expression specified

in the first argument based on the formatting specified in the second argu-

ment. For example

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP,’YYYY-MM-DD HH24:MI:SS’)FROM SYSIBM.SYSDUMMY1;


V7

V7

04 0672326132 CH03 4/20/04 1:43 PM Page 123

This SQL statement converts a standard timestamp into a non-standard char-

acter representation of that timestamp.

VARGRAPHIC Converts a character string to a graphic string.

WEEK Returns an integer between 1 and 54 based on the week of the year in which

a date, timestamp, or string representation of a date or timestamp falls. The

assumption is that a week begins on Sunday and ends on Saturday. The

value 1 represents the first week, 2 the second week, and so on.

WEEK_ISO Returns an integer between 1 and 53 based on the week of the year in which

a date, timestamp, or string representation of a date or timestamp falls. The

assumption is that a week begins on Monday and ends on Sunday. Week 1 is

the first week of the year to contain a Thursday. So, it is possible to have up

to three days at the beginning of the year appear as the last week of the

previous year, or to have up to three days at the end of a year appear as the

first week of the next year.

YEAR Returns the year portion of a date, a timestamp, or a duration.

TABLE 3.1 Units for Rounding and Truncating TIMESTAMPs

Unit Definition Explanation

CC Century Rounds up to the next century starting in the 50th year of the

century or truncates to the first day of the current century. SCC can

be used as a synonym for CC.

YYYY Year Rounds up to the next year starting on July 1st or truncates to the

first day of the current year. The following can be used as

synonyms in place of YYYY: SYYY, YEAR, SYEAR, YYY, YY, and Y.

IYYY ISO Year Provides the same functionality as YYYY. The following can be used

as synonyms in place of IYYY: IYY, IY, and I.

Q Quarter Rounds up to the next quarter starting on the sixteenth day of the

second month of the quarter or truncates to the first day of the

current quarter.

MM Month Rounds up to the next month on the sixteenth day of the month

or truncates to the first day of the current month. The following

can be used as synonyms in place of MM: MONTH, MON, and RM.

WW Week Rounds up to the next week on the twelfth hour of the third day

of the week (with respect to the first day of the year) or truncates

to the first day of the current week.

IW ISO Week Rounds up to the next week on the twelfth hour of the third day

of the week (with respect to the first day of the ISO year) or trun-

cates to the first day of the current ISO week.

W Week Rounds up to the next week on the twelfth hour of the third day

of the week (with respect to the first day of the month) or trun-

cates to the first day of the current week (also with respect to the

first day of the month).


V7

04 0672326132 CH03 4/20/04 1:43 PM Page 124

DDD Day Rounds up to the next day on the twelfth hour of the day or trun-

cates to the beginning of the current day. DD and J can be used as

synonyms in place of DDD.

DAY Start Day Differs from DDD by rounding to the starting day of a week. Rounds

up to the next week on the twelfth hour of the third day of the

week, otherwise it truncates to the starting day of the current

week. DY and D can be used as synonyms in place of DAY.

HH Hour Rounds up to the next hour at 30 minutes or truncates to the

beginning of the current hour. HH12 and HH24 can be used as

synonyms in place of HH.

MI Minute Rounds up to the next minute at 30 seconds or truncates to the

beginning of the current minute.

SS Second Rounds up to the next second at 500000 microseconds or trun-

cates to the beginning of the current second.

Some rules for the scalar functions follow:

• Scalar functions can be executed in the select-list of the SQL SELECT statement or aspart of a WHERE or HAVING clause.

• A scalar function can be used wherever an expression can be used.

• The argument for a scalar function can be a column function.

The RAISE_ERROR FunctionThe RAISE_ERROR function is a different type of function than we have discussed so far. Itis not a column function because it does not take a group of rows and return a singlevalue. Nor is RAISE_ERROR truly a scalar function because it does not transform columndata from one state to another.

Instead, the RAISE_ERROR function is used to raise an error condition in the SQLCA. Theuser supplies the SQLSTATE and error description for the error to be raised. The error will beraised with the specified SQLSTATE and a SQLCODE of –438.

The RAISE_ERROR function can be used to signal application program and data problems.One situation where RAISE_ERROR may prove useful is in a CASE statement such as

SELECT EMPNO,CASE WHEN SEX = ‘M’ THEN ‘MALE ‘

WHEN SEX = ‘F’ THEN ‘FEMALE’ELSE RAISE_ERROR(‘70SX1’, ‘INVALID DATA, SEX IS NEITHER F NOR M.’)ENDFROM DSN8810.EMP;

The RAISE_ERROR Function 1253

TABLE 3.1 Continued

Unit Definition Explanation

04 0672326132 CH03 4/20/04 1:43 PM Page 125

The value specified for SQLSTATE must conform to the following rules:

• The value must be a character string of exactly five characters in length.

• Only the characters ‘0’ through ‘9’ and uppercase ‘A’ through ‘Z’ may be used.

• The first two characters cannot be ‘00’, ‘01’, or ‘02’.

• If the first character is ‘0’ through ‘6’ or ‘A’ through ‘H’, the last three charactersmust start with a letter from ‘I’ through ‘Z’.

• If the first character is ‘7’, ‘8’, ‘9’, or ‘I’ though ‘Z’, the last three characters canbe any valid character.

NOTE

Technically, the RAISE_ERROR function does return a value. It always returns NULL with an unde-fined data type. You must use the CAST function to cast it to a defined data type to return thevalue to a program.

MQSeries Built-in FunctionsDB2 Version 7 adds a number of new built-in scalar and table functions for use with IBM’smessage queuing software, MQSeries. These functions enable MQSeries messages to bereceived and sent. The MQSeries scalar functions are

MQREAD Accepts two parameters; returns a message (as VARCHAR(4000)) from the

MQSeries location specified in the first expression, using the quality of service

policy defined in the second expression.

MQREADCLOB Accepts two parameters; returns a message (as a CLOB) from the MQSeries

location specified in the first expression, using the quality of service policy

defined in the second expression.

NOTE

When performing either the MQREAD or MQREADCLOB function the operation does not remove themessage from the queue specified in the first expression. Additionally, for both functions, if nomessages are available a NULL is returned.

MQRECEIVE Same as MQREAD, except the operation will remove the messages from the

queue.

MQRECEIVECLOB Same as MQREADCLOB, except the operation will remove the messages from

the queue.

NOTE

When performing either the MQRECEIVE or MQRECEIVECLOB function, the operation will removethe message from the queue specified in the first expression. Additionally, for both functions, ifno messages are available a NULL is returned.


V7

04 0672326132 CH03 4/20/04 1:43 PM Page 126

MQSEND This function is used to send messages to an MQSeries queue. It returns a

value of 1 if successful; 0 if unsuccessful. It accepts three (possibly, four) para-

meters. The data contained in the first expression will be sent to the

MQSeries location specified in the second expression, using the quality of

service policy defined in the third expression. A user defined by the message

correlation identifier may be specified as an optional fourth expression.

Using these scalar functions you can easily read, retrieve, and send information from andto MQSeries message queues. The scalar functions operate one message at a time. At times,though, you might want to operate on multiple MQSeries messages. This requires tablefunctions, and DB2 Version 7 supplies several of these as well. The MQSeries table func-tions are

MQREADALL Returns all of the messages (as VARCHAR) from the MQSeries location speci-

fied in the first expression, using the quality of service policy defined in the

second expression. An optional third parameter can be used to limit the

number of rows to return.

MQREADALLCLOB Returns all of the messages (as CLOB) from the MQSeries location specified in

the first expression, using the quality of service policy defined in the second

expression. An optional third parameter can be used to limit the number of

rows to return.

MQRECEIVEALL Same as MQREADALL except the operation will remove the messages from the

queue.

MQRECEIVECLOBALL Same as MQREADALLCLOB except the operation will remove the messages from

the queue.

NOTE

When performing any of the MQSeries functions that read or receive data, an operation returns atable with the following columns:

• MSG—Contains the contents of the MQSeries message, either a VARCHAR(4000) or CLOBbased on which function was used.

• CORRELID—Correlation ID used to relate messages—VARCHAR(24).

• TOPIC—The topic that the message was published with, if available—VARCHAR(40).

• QNAME—The queue name where the message was received—VARCHAR(48).

• MSGID—The assigned MQSeries unique identifier for this message—CHAR(24).

• MSGFORMAT—The format (typically MQSTR) of the message, as defined by MQSeries—VARCHAR(8).

XML Publishing Built-in FunctionsDB2 Version 8 adds a number of new built-in scalar functions to allow applications to effi-ciently generate XML data from DB2 data. The XML publishing functions are

XML Publishing Built-in Functions 1273

V8

04 0672326132 CH03 4/20/04 1:43 PM Page 127

XMLELEMENT Returns an XML element given an element name, an optional collection of

attributes, and zero or more arguments that make up the contents of the

element.

XMLATTRIBUTES Used within the XMLELEMENT function to specify attributes of the XML

element.

XMLFOREST Returns a forest of XML elements that share a pattern from a list of expres-

sions.

XMLCONCAT Returns a forest of XML elements generated from a concatenation of two or

more elements.

XMLAGG Returns a concatenation of XML elements generated from a collection of

XML elements.

XML2CLOB Returns a CLOB representation of an XML expression.

Built-in Function GuidelinesUse the following guidelines to implement an effective strategy for deploying built-infunctions in your DB2 applications.

Use Functions Instead of Program LogicUse the built-in functions provided by DB2 instead of coding your own application logicto perform the same tasks. You can be sure the DB2 built-in functions will perform thecorrect tasks with no bugs. But you will have to take the time to code, debug, and testyour application code. This is time you can better spend on developing application-specific functionality.

Avoid Function SynonymsSeveral of the built-in functions have synonymous names that perform the same function.For example, VALUES and COALESCE perform the same exact function. You should standard-ize on one of the forms in your applications. By using only one of the forms your SQL willbe easier to understand and maintain. Of course, your purchased applications may use anyof the forms.

The following are my recommendations, but of course, yours may differ:

Use This Instead of This

CEILING CEIL

COALESCE VALUES

DAY DAYOFMONTH

DECIMAL DEC

DOUBLE FLOAT

GREATEST MAX (scalar)

INTEGER INT


04 0672326132 CH03 4/20/04 1:43 PM Page 128

LEAST MIN (scalar)

LOG LN

LOWER LCASE

TIMESTAMP_FORMAT TO_DATE

TRUNCATE TRUNC

UPPER UCASE

VARIANCE VAR

VARIANCE_SAMP VAR_SAMP

In general, it is better to use the long form of the function instead of the abbreviated formbecause it is easier to quickly understand the purpose of the function. For example, onemight easily assume that VAR is short for the VARCHAR function, instead of the VARIANCEfunction.

I suggest using DAY instead of DAYOFMONTH because DAYOFMONTH does not support using adate duration or a timestamp duration as an argument. However, if you do not use dura-tions in your applications you might want to standardize on DAYOFMONTH instead of DAYbecause it is similar in name to other related functions such as DAYOFWEEK and DAYOFYEAR.

I suggest using DOUBLE instead of FLOAT because one might confuse FLOAT with REAL. Ifthere were a synonym for REAL, such as SINGLE, I would suggest using SINGLE. But there isnot.

I suggest using the scalar functions LEAST and GREATEST instead of the scalar functions MINand MAX to avoid possible confusion with the column functions MIN and MAX.

Use UPPER instead of TRANSLATEUsing the TRANSLATE function with a single argument serves the same purpose as theUPPER function—to convert a character string into uppercase. However, the UPPER functionshould be used for this purpose instead of TRANSLATE because

• The UPPER function can be used only for the purpose of converting character stringsto uppercase.

• The TRANSLATE function is not as easily identified by developers as converting text touppercase and is therefore more difficult to debug, maintain, and test SQL changes.

Use HAVING to Search Column Function ResultsWhen using column functions, remember that the WHERE clause applies to the data priorto modification. To remove results after the data has been modified by the function, youmust use the HAVING clause in conjunction with a GROUP BY clause.

Built-in Function Guidelines 1293

Use This Instead of This

V7

04 0672326132 CH03 4/20/04 1:43 PM Page 129

The GROUP BY clause collates the resultant rows after the column function(s) have beenapplied. When the data is grouped, users can eliminate non-pertinent groups of data withthe HAVING clause.

For example, the following query groups employee data by department, returning theaggregate salary for each department, unless the average salary is $10,000 or less:

SELECT WORKDEPT, SUM(SALARY)FROM DSN8810.EMPGROUP BY WORKDEPTHAVING AVG(SALARY) > 10000 ;

The HAVING clause eliminates groups of non-required data after the data is summarized.

Be Aware of NULLs When Using Column FunctionsNulls can be one of the more difficult features of DB2 to understand and master. This isespecially so when using certain built-in DB2 column functions. In some cases, you canwrite a query against a column using a built-in function and have the result be NULL—even if the column itself is defined as NOT NULL. Don’t believe it? Run this query:

SELECT SUM(ACTNO)FROM DSN8810.EMPPROJACTWHERE PROJNO = ‘QRSTUV’;

ACTNO is defined as NOT NULL, yet this query returns a NULL (unless someone inserted a rowwith the value of ‘QRSTUV’ for PROJNO). Why? The sum of all ACTNO values for project‘QRSTUV’ is not zero, but is not applicable—at least as defined by DB2.

Basically, if there are no results that apply to the predicate, the result of using a functionsuch as SUM or AVG is NULL because the sum or average of no rows is not zero, but unde-fined.


04 0672326132 CH03 4/20/04 1:43 PM Page 130

using db2 functions

Documents

given column

designated column

given function

columns functions

variance functions

count functionreturns

big functions willhave

integer number count