DB2 NULL Indicator use in SQL Queries

A DB2 null Indicator/value represents missing or unknown information at the column level. When a column is set as null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known. It could be a combination of these two situations too. A DB2 null Indicator/value is not the same as Zero or blank. For all Columns that can contain Nulls, an indicator variable should be used in all Select Statements to retrieve data -or- the COALESCE built-in function should be used.

When working with COBOL programs and DB2 databases, it is important to validate that fields in the database do not contain null values. This is because COBOL programs may not be able to handle null values and may not function properly if they encounter them.

DB2 Null Indicator Syntax

INTO :HOSTVAR:NULLIND

The picture clause that should be used for the DB2 null indicator is S9(4) COMP

Meaning:
-1: the field is null
0: the field is not null
-2: the field value is truncated

Inserting a record with a nullable column
• To insert a NULL, move -1 to the null indicator
• To insert a valid value, move 0 to the null indicator

Every column defined in a DB2 table must be designated as either allowing or disallowing nulls. A column is defined as nullable – meaning it can be set to NULL – in the table creation DDL. Null is the default if nothing is specified after the column name. To prohibit the column from being set to NULL you must explicitly specify NOT NULL after the column name. In the following sample table, COL1 and COL3 can be set to null, but not COL2, COL4, or COL5.

Table Declaration with NULL

CREATE TABLE SAMPLE
(COL1 INTEGER,
 COL2 CHAR(10) NOT NULL,
 COL3 CHAR(5),
 COL4 DATE NOT NULL WITH DEFAULT,
 COL5 TIME NOT NULL);

Select Query with NULL

EXEC SQL
SELECT TQ_UOWPRR_DESC
      ,TQ_UOWPRIOR_ID
      ,TQ_PRRACTVTY_DT
      ,TQ_MIGRATION_DT
      ,TQ_DELETION_IND
INTO   TQ_UOWPRR_DESC  :IND-UOWPRR-DESC
      ,TQ_UOWPRIOR_ID  :IND-UOWPRIOR-ID
      ,TQ_PRRACTVTY_DT :IND-PRRACTVTY-DT
      ,TQ_MIGRATION_DT :IND-MIGRATION-DT
      ,TQ_DELETION_IND :IND-DELETION1IND
FROM TQ1
WHERE TSQRY_PRODSGMT_CD = :TSQRY-PRODSGMT-CD
  AND TSQRY_CLNTSGMT_CD = :TSQRY-CLNTSGMT-CD
  AND TQ_REC_ID         = :TQ-REC-ID
  AND TQ_TPPREFIX_CD    = :TQ-TPPREFIX-CD
END-EXEC.

Declare Cursor with NULL

EXEC SQL
DECLARE TQ_CSR CURSOR FOR
 SELECT TQ_UOWPRR_DESC
       ,TQ_UOWPRIOR_ID
       ,TQ_PRRACTVTY_DT
       ,TQ_MIGRATION_DT
       ,TQ_DELETION_IND
FROM TQ1
WHERE TSQRY_PRODSGMT_CD = :TSQRY-PRODSGMT-CD
  AND TSQRY_CLNTSGMT_CD = :TSQRY-CLNTSGMT-CD
  AND TQ_REC_ID = :TQ-REC-ID
  AND TQ_TPPREFIX_CD = :TQ-TPPREFIX-CD
END-EXEC.

Fetch Cursor with NULL

EXEC SQL
FETCH TQ_CSR
INTO :TQ-UOWPRR-DESC:IND-UOWPRR-DESC,
     :TQ-UOWPRIOR-ID:IND-UOWPRIOR-ID,
     :TQ-PRRACTVTY-DT:IND-PRRACTVTY-DT,
     :TQ-MIGRATION-DT:IND-MIGRATION-DT,
     :TQ-DELETION-IND:IND-DELETION-IND
END-EXEC.

Rowset with NULL

EXEC SQL
FETCH NEXT TQ_CSR
FOR 10 ROWS
INTO :TQ-UOWPRR-DESC:IND-UOWPRR-DESC,
     :TQ-UOWPRIOR-ID:IND-UOWPRIOR-ID,
     :TQ-PRRACTVTY-DT:IND-PRRACTVTY-DT,
     :TQ-MIGRATION-DT:IND-MIGRATION-DT,
     :TQ-DELETION-IND:IND-DELETION-IND
END-EXEC.

The purposes of the indicator variable are to

• Specify the null value. A negative value of the indicator variable specifies the null value. A value of -2 indicates a numeric conversion or arithmetic expression error occurred in deriving the result
• Record the original length of a truncated string (if the source of the value is not a large object type)
• Record the seconds portion of time if the time is truncated on assignment to a host variable.

For example, if :TQ-UOWPRR-DESC: IND-UOWPRR-DESC is used to specify an insert or update value, and if IND-UOWPRR-DESC is negative, the value is specified in the null value. If IND-UOWPRR-DESC is not negative the value specified in the value of TQ-UOWPRR-DESC.

Similarly, if TQ-UOWPRR-DESC: IND-UOWPRR-DESC is specified in a VALUES INTO clause or in a FETCH or SELECT INTO statement, and if the value returned is null, TQ-UOWPRR-DESC is not changed and IND-UOWPRR-DESC is set to a negative value. If the value returned is not null, that value is assigned to TQ-UOWPRR-DESC, and IND-UOWPRR-DESC is set to zero (unless the assignment to TQ-UOWPRR-DESC requires string truncation of a non-LOB string; in which case IND-UOWPRR-DESC is set to the original length of the string). If an assignment requires truncation of the seconds part of the time, IND-UOWPRR-DESC is set to the number of seconds.

SQL Code -305

If the table column is defined as NOT NULL (with no default) and if we try to insert a null value we get this error.

Resolution: This should be resolved by making sure that the inserted value is not null. DB2 Null indicator cannot be used here since the column is defined as NOT NULL.

(validate the data, if it’s not numeric or less than spaces then move spaces into it and then insert or update into the table)

A table column is defined as NULL, The host variable has a not null value and the DB2 Null indicator is not set in the host program, so the null indicator defaults to a negative value.

Resolution: This should be resolved by using a null indicator in the host program and moving the relevant value to the null indicator. Here in order to move the null value into the respective column move -1 to the null indicator.

Null indicator validation in COBOL-DB2 Program

When working with COBOL programs and DB2 databases, it is important to validate that fields in the database do not contain null values. This is because COBOL programs may not be able to handle null values and may not function properly if they encounter them. In this blog post, we’ll take a look at how to perform null value validation using COBOL and DB2.

To validate null values using COBOL and DB2, you can follow these steps:

Declare a NULLIND variable for each nullable field in the database. The NULLIND variable should be declared as S9(4) COMP.

 01  CUSTOMER-NAME          PIC X(30).
 01  CUSTOMER-NAME-NULLIND  S9(4) COMP.

In your COBOL program, retrieve the data from the database and check the value of the NULLIND variable for each field. If the value of the NULLIND variable is -1, the field contains a null value. If the value is 0, the field contains a valid value.

EXEC SQL
    SELECT CUSTOMER-NAME, CUSTOMER-NAME-NULLIND
    INTO :CUSTOMER-NAME, :CUSTOMER-NAME-NULLIND
    FROM CUSTOMER
END-EXEC.

IF CUSTOMER-NAME-NULLIND = -1
    DISPLAY 'Error: Customer name is null.'
ELSE
    DISPLAY 'Customer name is ' CUSTOMER-NAME
END-IF.

If a null value is encountered, handle it appropriately in your COBOL program. This may involve setting a default value or taking other corrective action.

By validating null values using COBOL and DB2, you can ensure that your programs are processing only valid data and that they are not encountering unexpected errors due to null values. Additionally, by handling null values appropriately, you can improve the accuracy and reliability of your COBOL programs.

Tips and Tricks – DB2 NULL Indicator

• NULLs can present problems because it is handled differently by different computers and the collating sequence is inconsistent with regard to NULLs.
• Unless you specify NOT NULL, the default is to allow for NULLs
• It’s easy for us to get lazy and allow columns to contain NULLs when it would be better to specify NOT NULL
• Remember to allow for NULLs to create UNKNOWN logical values. Always test your code with NULLs in all possible places.
• The NULL is a global creature, not belonging to any particular data type, but able to replace any of their values.
• The basic rule for math with NULLs is that they propagate. An arithmetic operation with a NULL will return a NULL. If you have a NULL in an expression, the result will be NULL.
• If you concatenate a zero-length string to another string, that string stays the same. If you concatenate a NULL string to a string, the string becomes a NULL.
• In comparison, the results can be TRUE, FALSE, or UNKNOWN. A NULL in a row will give an UNKNOWN result in the comparison.
• Sometimes negating the wording of the problem helps. Instead of saying “Give me the cars that met all the test criteria,” say “Don’t give me any car that failed one of the test criteria.” It is often easier to find what you do not want than what you do want. This is very true when you use the NOT EXISTS, but beware of NULLs and empty tables when you try this.
• You can’t completely avoid NULLs in SQL. However, it is a good idea to try as hard as you can to avoid them whenever possible.
• Make yourself think about whether you really need NULLs to exist in a column before you omit the NOT NULL clause on the column definition.

CASE Statement to handle NULL

CASE Statement can be used to handle NULL values.

EMP_ID NAME EMAIL_ADDRESS       
1 Jane Jane.D@outlook.com 
1 John (null)      
1 Sam  sam.huges@yahoo.com

SELECT
CASE
  WHEN EMAIL_ADDRESS IS NULL THEN 'Not specified'
  ELSE EMAIL_ADDRESS
END AS EMAIL_ADDRESS
FROM EMPLOYEE;

Result
EMAIL_ADDRESS
Jane.D@outlook.com 
Not specified
sam.huges@yahoo.com

COALESCE Function to handle NULL Values

The COALESCE function returns the first non-NULL value it finds in the supplied parameters.

COALESCE (expression1, expression2, expression3…..)

Let’s take an example to find if any of the Personal, Work, or Alternative emails are present for an employee.

EMP_ID NAME WORK_EMAIL         PERSONAL_EMAIL        ALT_EMAIL
1 Jane Jane.D@outlook.com jane.doe@yahoo.com    j.doe@gmail.com
1 John (null)      john.kurian@gmail.com (null)
1 Sam  (null)             (null)                sam.huges@yahoo.com

SELECT
COALESCE(WORK_EMAIL, PERSONAL_EMAIL, ALT_EMAIL)
FROM EMPLOYEE;

This query would return personal_email, but if that is NULL it would return work_email, and if that is NULL then alternative_email is returned.

Result
Jane.D@outlook.com
john.kurian@gmail.com
sam.huges@yahoo.com

Other DB2 Blogs: Click Here IBM DB2 Manual: Click Here