Interview Questions   Tutorials   Discussions   Programs   

DB2 - How to handle null values in db2?




724
views
asked mar September 2, 2014 12:09 PM  

How to handle null values in db2?


           

1 Answers



 
answered By Mswami   0  
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’ : Field is having NULL value

‘ 0’ : Field is Not NULL value

‘-2’ : Field value is truncated

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

Example:

MOVE -1 to SNAME-INDNULL

EXEC SQL INSERT INTO SAMP_TAB

(SN,SNAME,STATUS,CITY) VALUE

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

END-EXEC

flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert