Skip to main content

NULL VALUES and NULL INDICATORS in DB2

In DB2, the columns defined as NULL needs to be handled carefully else it will throw null exception error, in order to over come this error data type can be handled by using null indicator.

  • NULL is stored using a special one-byte null indicator that is "attached" to every nullable column.

  • If the column is set to NULL, then the indicator field is used to record this.

  • Using NULL will never save space in a DB2 database design - in fact, it will always add an extra byte for every column that can be NULL. The byte is used whether or not the column is actually set to NULL. The indicator variable is transparent to an end user

Consider below Table :

Create Table SAMP_TAB

SN CHAR (10)

SNAME CHAR (10)

STATUS CHAR (2) NOT NULL BY DEFAULT

CITY CHAR (10) NOT NULL


Note :: Unless you specify NOT NULL, the default is to allow for NULL

In above table SN and SNAME columns holds null values by default, in order to handle these null variables we need to have NULL-INDICATORS declares in the Program as S9(4) comp variable (A indicator variable is shared by both the database manager and the host application. Therefore, this variable must be declared in the application as a host variable, which corresponds to the SQL data type SMALLINT)

Let us declare the Null indicators for above two variables in application program as

02 SNAME-INDNULL S9(4) comp

05 SN-IN S9(4) comp



What values Null indicators will hold :

  1. -1’ : Field is having NULL value

  2. 0’ : Field is Not NULL value

  3. -2’ : Field value is truncated

How /Why to handling Null Values:

  • When processing INSERT or UPDATE… statements, the database manager checks the null-indicator variable, if one exists. If the indicator variable is negative, the database manager sets the target column value to null, if nulls are allowed else it throws sql error code -305, we need null indicators to handle this situation.

  • If the null-indicator variable is zero or positive, the database manager uses the value of the associated host variable.



There are two reasons for getting -305 and Resolution :


1) 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.

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

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

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

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

Eg :

MOVE -1 to SNAME-INDNULL

EXEC SQL INSERT INTO SAMP_TAB

(SN,SNAME,STATUS,CITY) VALUE

(:SN,:SNAME:SNAME-INDNULL,:STATUS,:CITY)

END-EXEC

Eg :

EXEC SQL SELECT SNAME

INTO :SNAME:SNAME-INDNULL

FROM SAMP_TAB

WHERE SN = :SN-IN

END-EXEC


(Note : If SNAME has a value, SNAME-INDNULL contains 0.

If SNAME is NULL, SNAME-INDNULL contains -1. )

If SQL-CODE = -305 and SNAME-INDNULL = -1

Display “ SNAME is having null values “

Else

:

:


End-If


Important Points wrt NULL Variables

  • NULLs can present problems because they are 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 creating 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.

  • A NULL isn't a zero, it isn't a blank string, it isn't a string of length zero.

  • 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 comparisons, 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.

  • Use NULLs sparingly




Comments

Kumaresh T said…
Right on point, I got what I was looking for. Thanks for the post.
Unknown said…
nice article.. well explained.
Ramya said…
Well explained. Thank you.
Sittruli Guna said…
I got the details what i was exactly searching for. Thanks for the post dear!!
Ananth TM said…
just as everyone says... to the point.. what we really want
Anonymous said…
well explained ... Thank u
Unknown said…
Check this
http://mframes.blogspot.com/
Unknown said…
Check out the mainframe blog
http://mframes.blogspot.com
Anonymous said…
correct definition for indicator variable:
02 SNAME-INDNULL PIC S9(4) comp.
Unknown said…
Thanks but I am no more into Mainframes
Anonymous said…
good explanation
Unknown said…
Very well explained.. Can you please explain why we might be needing some columns to contain Null values ? Why not declare every column as Not Null.. I understand it will take one byte extra for each column but won't it be a effective way to reduce the errors ?
Unknown said…
Thanks, well explained.
I've been looking for this solution for days because a variable in cobol is never null but I needed this representation in DB2 to bring all the records in my query.
kumar said…
@Himanshu : Null : is a special value provided by db2 to store the missing values.
Its a subjective decesion wether to have all not null variables or not, based on your data.. .... very well we can define all cols as Not null, then we need to handle all the variable accordinlgy.

Popular posts from this blog

Mainframe Written test @ Accenture

1. What r the 2 function we can get by using INSPECT verb? (c) A. file handling, error handling B. opening a file, closing a file. C. char counting, replacing. D. none of the above 2. Indicate which of the following is not true about the formal parameters in a subroutine? (c) A. every formal parameter must appear in the linkage section of the subroutine B. every formal parameter must appear in the using phrase of the procedure division header of the subroutine C. A formal parameter name can appear more than once in the using phrases of the procedure division header of the subroutine. D. A formal parameter can not be declared with value clause in a subroutine 3. What is the value of A? (B) 01 A pic 9(3). Move 100 to A. Perform 1000-para thru 1000-exit. Display the value of a=A Stop run. 1000-para. If A=100 Perform 1000-exit Else Move 150 to A End-if. Move 200 to A. 1000-exit. Exit. a.100 b.200 c.150 d. none of the above is correct. 4. What is the value of B? (A) Move 1 to A Evaluate tr

How to Solve SOC7 Abend - with screen shots

Below process helps to find out the statement, caused the SOC7 error. Check the Sysout of RUNJCL . This shows the error statement and lists offset value Take the Offset Value 000003C0 Got to respective Compilation Job listing, check the sysprint Search for the offset value 0003C0 (delete +00 -- initial 3 letters of Offset value and search for it) check below 2 screen shots This Offset value is listed under line no 0045 – which refers to Move statement. Take this no. 045 and find for it in same sysprint. This points to the exact statement, caused SOC7 T his 045 pints to the Move statement 1526, this is the exact line in the program Check for the above line no. In source program . This points to the statement highlighted below . Check the statement, variable check-4, which is added to check-6. These are having different Picture clause. check-4 is alfhanumaric, holding some junk data, when this data is moved to Chcek-6 variable(of comp-3) creates SOC7 error. This is just an example to e