COALESCE function in DB2 . Alternative to NULL indicator variable in DB2 ; SQLCODE - 305.

If a table column is not defined with NOT NULL, then it implies that the column value can be NULL sometimes. For such columns,if we do not use null handling technique, then the application program will give sqlcode -305.
To avoid that we use indicator variables.However , we can also use the DB2 COALESCE  function.

COALESCE is a function that allows you to substitute any numeric or character value for a null, based on the column data type.
Suppose in DEPT table, i have one column for optional subject(optsubj). It can have a value of 'Y'or 'N'. However since the column is not defined with NOT NULL keyword,it can fetch null values.
If we simply use the query below, our application program will return sqlcode -305.

SELECT fname
              ,lname
              ,optsubj
into       :hv-fname
             :hv-lname
             :hv-optsubj
from DEPT

However, we can tweak the same query in the below way using COALESCE function to get rid of -305 without using indicator variable

SELECT fname
       ,lname
       ,coalesce(optsubj,space(1))
into   :hv-fname
       :hv-lname
       :hv-optsubj
from DEPT

What it will do is, it will replace NULL value with space of 1 byte whenever the column optsubj fetches a null value for any record;

This function can be used in many ways to get desired results.