- Column Functions
- Scalar Functions
- The RAISE_ERROR Function
- MQSeries Built-in Functions
- XML Publishing Built-in Functions
- Built-in Function Guidelines
This chapter is from the book
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.