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. 

RENT compiler option in cobol - Understanding reentrant programming

For understanding RENT compiler option, we need to know  what is a  re-entrant program.
A re-entrant is a program that doesn’t change itself. “How can a program change itself?” Does the word "Multi threading " looks familiar?  Reentrant and mutithreading are related but somewhat different topics.
We can divide our program(logically) into  three parts:
1. Program code – the instructions that are executed when a program runs.
2. Constants – constants used in a program. These are set when the program is written, and never change.
3.Working  Storage area – or variables. This area changes when different user fires the same program.

"A reentrant Cobol program gives each user their own copy of working storage. " 
For every user,MVS supplies the GETMAIN and STORAGE macros to support this and they can be
used by subsystems such as CICS or IMS or whatever to support reentrant programs.

For each user the copy of the working storage is dynamically acquired and initialized from the original copy. When a user interacts with the program , he only modifies his copy of the variables.

When the program is interrupted to give another user a turn to use the program, information about the data area associated with that user is saved.

So, what will happen if we write a CICS program as non-renetrant?
When one transaction waits, a second transaction can come and modify the working storage variables, and thus providing unstable or undesirable results.

Thus, Re-entrant programs allow a single copy of a program or routine to be used concurrently by two or more processes.


For cobol, we use the compiler option RENT to generate reentrant object program.