In-Band Nulls and Out-Band Nulls Make Null function

[harishvardhan]

How does In-band null differ from a Out-Band Null. What is the use of the null function MakeNull?

MakeNull Change an in-band null
to out of band null
any (column)
string (string)
-

Thanks,
H

[ray.wurlod]

An out-of-band null is DataStage's internal representation of null. It's a single byte with only the high bit set (128 if it were a uint8).

An in-band null is a value that is legal for the domain of the field, but which your business model has designated will represent "unknown". For example you may have used -99 to represent "unknown" for a set of integer codes. -99 would be the in-band null for that field. Similarly you might choose "9999-12-31" to be the in-band null for a date type.

[zulfi123786]

when we use setnull() it would be out band null but how to convert out band null to in band null; makenull will do the vice versa how abt the other side?
And where do can we make a choice of using in band null or out band null for nullable fields?

[ray.wurlod]

Create in-band nulls simply by assigning the chosen null-representing value.

[zulfi123786]

Create in-band nulls simply by assigning the chosen null-representing value.

That means at all places where we use setnull() to insert null we have to assign this value

Ex: If X=' ' Then SetNull()

has to be modified as

If X=' ' Then -99

and this -99 will be logically treated as NULLS

Did i get you right?

[ray.wurlod]

I don't know - did U respond?

[zulfi123786]

An out-of-band null is DataStage's internal representation of null. It's a single byte with only the high bit set (128 if it were a uint8).

Does datastage reserve one bit for sign and one bit for null indication and use the rest for holding the value
In such case for signed int8 the max value that can be held becomes 32
(int8:-signed integer with 1 byte for storage; please correct if wrong)

[ray.wurlod]

No, the binary representation of NULL in DataStage is 10000000.