DBzTech-Technology Dossier

Handling NULLs in DB2 with COALESCE

In DB2, if a column is not defined with the NOT NULL constraint, it means the column can sometimes contain NULL values. When such columns are fetched in an application program without proper null handling, you may encounter SQLCODE -305.

Traditionally, this is handled by using indicator variables. However, DB2 also provides a simpler alternative: the COALESCE function.

What is COALESCE?

The COALESCE function lets you substitute a default value whenever a column contains a NULL.

• If the column is character type, you can replace NULL with a string or space.
• If the column is numeric type, you can replace NULL with a number.

Example

Suppose in the DEPT table, we have a column OPTSUBJ (optional subject) that can contain values 'Y' or 'N'. Since it is not defined as NOT NULL, it may also contain NULLs.

If we run this query:

SELECT fname,
       lname,
       optsubj
INTO   :hv-fname,
       :hv-lname,
       :hv-optsubj
FROM DEPT;

The program may return SQLCODE -305 if OPTSUBJ contains NULL.

Using COALESCE

We can rewrite the query as follows:

SELECT fname,
       lname,
       COALESCE(optsubj, SPACE(1))
INTO   :hv-fname,
       :hv-lname,
       :hv-optsubj
FROM DEPT;

Here, if OPTSUBJ is NULL, DB2 will substitute it with a 1-byte space instead. This prevents SQLCODE -305 without the need for an indicator variable.

Key Takeaway

The COALESCE function is a powerful tool in DB2 that helps you handle NULLs gracefully by providing a default replacement value. It can be used with both character and numeric columns to avoid errors and make query results more predictable.