Home > Articles

Using DB2 Functions

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Built-in Function Guidelines

Use the following guidelines to implement an effective strategy for deploying built-in functions in your DB2 applications.

Use Functions Instead of Program Logic

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

Avoid Function Synonyms

Several 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 standardize on one of the forms in your applications. By using only one of the forms your SQL will be easier to understand and maintain. Of course, your purchased applications may use any of 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

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 form because it is easier to quickly understand the purpose of the function. For example, one might easily assume that VAR is short for the VARCHAR function, instead of the VARIANCE function.

I suggest using DAY instead of DAYOFMONTH because DAYOFMONTH does not support using a date duration or a timestamp duration as an argument. However, if you do not use durations in your applications you might want to standardize on DAYOFMONTH instead of DAY because 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. If there were a synonym for REAL, such as SINGLE, I would suggest using SINGLE. But there is not.

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

Use UPPER instead of TRANSLATE

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

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

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

Use HAVING to Search Column Function Results

When using column functions, remember that the WHERE clause applies to the data prior to modification. To remove results after the data has been modified by the function, you must use the HAVING clause in conjunction with a GROUP BY clause.

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

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

  SELECT  WORKDEPT, SUM(SALARY)
  FROM   DSN8810.EMP
  GROUP BY WORKDEPT
  HAVING  AVG(SALARY) > 10000 ;

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

Be Aware of NULLs When Using Column Functions

Nulls can be one of the more difficult features of DB2 to understand and master. This is especially so when using certain built-in DB2 column functions. In some cases, you can write 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.EMPPROJACT
  WHERE PROJNO = 'QRSTUV';

ACTNO is defined as NOT NULL, yet this query returns a NULL (unless someone inserted a row with 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 function such as SUM or AVG is NULL because the sum or average of no rows is not zero, but undefined.

  • + Share This
  • 🔖 Save To Your Account