Null Indicator Variable (Indicator Variable)

The Null indicator variable are used in an Application Program as a part of exception handling while dealing with the columns which are defined as NULL ( Or say the one which is not defined with NOT NULL).

Let us consider the following scenario:

EXEC SQL

  SELECT EMP_NUMBER,EMP_ADDRESS
  INTO :EMP_NUMBER,:EMP_ADDRESS
  FROM EMP

END-EXEC

Let's say EMP_ADDRESS column in EMP tables is defined as NULL column and one of the Employee has not updated his address in database so it will be considered as null. Now what should happen if we try to fetch the row for that employee, what the host variable: EMP_ADDRESS suppose to contain after the Select operation. No it will not contain spaces as Null is absence of value and not blanks, spaces or any other character.

In this case you will get the error with -305 as SQLCODE.

To avoid this we need to use null indicator in our program which can be used to process the program flow in case of null.

The Null indicator variable should be defined for the every column which may contain the null value.

It must be defined as 2 Byte binary variable.

01   EMP_ADDRESS_INDICATOR S9(4) COMP

You can then refer this variable in your query as below.

EXEC SQL

  SELECT EMP_NUMBER,EMP_ADDRESS
  INTO :EMP_NUMBER,:EMP_ADDRESS :EMP_ADDRESS_INDICATOR
  FROM EMP

END-EXEC

Note there is no comma between EMP_ADDRESS and EMP_ADDRESS_INDICATOR.

Now see how this null indicator resolves our problem.

If one of the employees have EMP_ADDRESS as NULL then EMP_ADDRESS_INDICATOR will be automatically updated with Negative value.

Then you can check like

IF EMP_ADDRESS_INDICATOR IS NOT ZERO
THEN < do some processing>

EMP_ADDRESS_INDICATOR will contain zero if the EMP_ADDRESS has some value (NOT NULL)

Null indicator can contain three value

0 : If column is not null that is it have some value in it.
-1: If column contains null
-2: If column contains null as a part of data conversion.

Some Tricky Pointes to remember with Null indicator:

1> You have to define the Null indicator with S9(4) COMP ( 2 BYTE Binary) or else you will get the error as " Undefined Host variable " even though you have defined it ( but with some other picture clause)

2>  The value which host variable going to contain depends on the null indicator irrespective of content we move to it.

For ex : Suppose i have to insert some record in table.

If i move some address to the variable EMP_ADDRESS

Move 'address' to EMP_ADDRESS

Then insert this record to EMP table but i move -1 to Null indicator variable EMP_ADDRESS_INDICATOR associated with  EMP_ADDRESS then irrespective of 'address' moved to EMP_ADDRESS null will be moved EMP_ADDRESS in EMP table.

3> You have used Null indicator in your query but didn't check it's value later in your program you may not get -305 sqlcode but your program will behave in unpredicted manner.

17 comments:

srajasheakr said...

Hi,
Iam s rajashekar iam reading conceps on your blog it is very useful to me and i need complete db2 material and realtime scenario and i want to know how to work in realtime environment so please reply me on srajashekar007@gmail.com or please call me on 8121104062

vinod said...

Hi,

i am vinod, i have done course mainframes. and at present i am in search of job in mainframes. will it be useful if i do course UDB DB2 admin. will it give more weight to my resume.

Nitin Gandhi said...

Hi srajasheakr

Thanks for your Feedback.

For DB2 Material you can refer the "DB2 REAL TIME"
section on this blog (top right).This blog is not only intended for clearing DB2 concepts but include real time scenario too.

For further contact ,

My mail id : DB2PGUIDE@GMAIL.COM

Anonymous said...

Ηeyа i'm for the primary time here. I found this board and I in finding It truly useful & it helped me out much. I'm hοpіng to рroѵiԁe
something again and aid others such as you hеlped me.



Check out my blog :: jedi.wv.com.br

Anonymous said...

Greetings! Very helpful advice within this article!

It's the little changes that produce the greatest changes. Thanks a lot for sharing!

my weblog: Best binary trading platforms

Anonymous said...

Very good article. I'm going through a few of these issues as well..

My weblog; what should i weigh

Anonymous said...

Аdmiгing thе time and effort yοu put into уour sіtе
аnd dеtаileԁ infοrmation you offer.
It's nice to come across a blog every once in a while that isn't the
same οutdаteԁ rehaѕhеd
matеrial. Greаt read! I've saved your site and I'm adԁіng yοur RSS fеeds tο
my Goоgle aсcount.

Feеl free to suгf tο my
homepage - payday loans

Unknown said...

Thanks

Nidhi Singh said...

Well written useful information, thanks for sharing.

Suriya said...
This comment has been removed by the author.
Suriya said...

Thanks Nitin. The information provided helped me a lot as a beginner in DB2 COBOL Developing. Could you please help me in suggesting a book or online website to learn COBOL/DB2 .. It would be helpful for me

Unknown said...

refer murach books for mainframe, WWW.MURACH.COM

Unknown said...

Hi,

Now i am Clear with the Null Indicator Concept.

Durgesh kesharwanin said...

Very good explanation and thank you very much for this post. Please keep it up.

Unknown said...

Hi nitin, from where I can find a complete guide

Unknown said...

Hi Please help to anyone...

I dint understand what are you saying this concept...

thennarasu.selvam@gmail.com

Unknown said...

Hi,

The way Your explanation is very understandable and pretty easy to understand. Could you please send me complete DB2 material to "ravidudimatla181@gmail.com" if you have. It would be Great if you do that

Thanks so much foe

Post a Comment