I am finding that I am encountering variables containing null more often as I interface data to the IBM i that originated from other software and databases. How to handle nulls is becoming one of the more popular questions I am asked.

What is null? Dictionary.com defines it as:

without value, effect, consequence, or significance. being or amounting to nothing; nil; lacking; nonexistent.

A simple example of where I could use null in a database would be the date of death of a patient in a hospital application. If the person is alive we have no idea of when their date of death would be, so assigning any value to a "Date of death" field would be meaningless. To use null in that field would indicate that the "Date of death" is unknown.

In the past AS400 programmers did not use nulls as neither DDS nor RPG III could use them, using blank in a character field or zero in a numeric one to denote a value of "not known" or "nothing". With modern RPG and using either updated DDS files or SQL tables/indexes/views we can handle nulls within our RPG code.

In many non-IBM i databases a field is null when it contains the value of hexadecimal '00'. But not DDS files or SQL tables, they use a hidden area within the file/table called the "null byte map". There is a byte for each field in the file, and each byte acts as an indicator value to show that the field either is null or not. Those of you who have built trigger programs would have encountered the "null byte map", which I will explain later.

How to code a DDS file to contain nulls?

01 A UNIQUE 02 A R TESTFILER 03 A F001 5A 04 A F002 L DATFMT(*USA) 05 A ALWNULL 06 A F003 9P 3 ALWNULL 07 A F004 Z ALWNULL 08 A F005 1S 0 ALWNULL 09 A K F001

Line 1: Those of you who are regular readers of this site know that I like to keep my file's key unique.

Line 2: Is the record format name.

Line 3: F001 does not allow null values.

Line 4 – 8: As these fields have the ALWNULL they can contain be null.

Line 9: Is the key field for this file.

The contents of this file look like this. A field that is null shows a hyphen ( - ).

F001 F002 F003 F004 F005 01 1 - 8.160 - - 02 2 - 12.345 - - 03 3 - 170.000 - -

This is my example RPG program to read the file and change the null values of the fields.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt:*nounref) alwnull(*usrctl) ; 03 dcl-f TESTFILE keyed usage(*input:*update) ; 04 dow (1 = 1) ; 05 read TESTFILER ; 06 if (%eof) ; 07 leave ; 08 endif ; 09 dsply ('1. Null is ''' + %nullind(F003) + ''' F001 = ''' + %trimr(F001) + ''' F003 = ' + %triml(%editc(F003:'J'))) ; 10 if %nullind(F003) ; 11 %nullind(F003) = *off ; 12 else ; 13 %nullind(F003) = *on ; 14 endif ; 15 dsply ('2. Null is ''' + %nullind(F003) + ''' F001 = ''' + %trimr(F001) + ''' F003 = ' + %triml(%editc(F003:'J'))) ; 16 update TESTFILER %fields(F003) ; 17 enddo ; 18 *inlr = *on ;

Line 1: **FREE has to be the first line as this program is written in fully free RPG code.

Line 2: The ALWNULL(*USRCTL) in the control options allows me to have control of the way the nulls are handled.

Line 3: The file TESTFILE is defined to be used for input and update.

Line 4: The start of my Do-loop, which ends on line 17.

Line 5: The record format from TESTFILE is read.

Lines 6 – 8: If the end of files is encountered the logic exits the Do-loop.

Line 9: This DSPLY statement displays the null indicator for field F003 , F001 is the key of the file, and the value in F003 .

Lines 10 – 14: This code just reverses the null indicator. If it is on it is turned off, and if it is off it is turned on.

Line 15: The changed value of the null indicator for F003 is shown, along with the file's key, and the value in F003 .

Line 16: I am just updating the record format with value in F003 , which includes the null indicator for that field, and no other fields.

This is how TESTFILE looks before I call this program:

F001 F002 F003 F004 F005 01 1 - 8.160 - - 02 2 - 12.345 - - 03 3 - 170.000 - -

When I call the program these messages are displayed by the DSPLY operation code:

DSPLY 1. Null is '0' F001 = '1' F003 = 8.160 DSPLY 2. Null is '1' F001 = '1' F003 = 8.160 DSPLY 1. Null is '0' F001 = '2' F003 = 12.345 DSPLY 2. Null is '1' F001 = '2' F003 = 12.345 DSPLY 1. Null is '0' F001 = '3' F003 = 170.000 DSPLY 2. Null is '1' F001 = '3' F003 = 170.000

I can see that F003 was not null at the start as the null indicator was '0', *off . The following line shows that I have turned on the null indicator, it is '1' or *on . This is repeated for the three records. It should come as no surprise that when I look at the file F003 is now null in all the records.

F001 F002 F003 F004 F005 01 1 - - - - 02 2 - - - - 03 3 - - - -

When I run the program a second time:

DSPLY 1. Null is '1' F001 = '1' F003 = .000 DSPLY 2. Null is '0' F001 = '1' F003 = .000 DSPLY 1. Null is '1' F001 = '2' F003 = .000 DSPLY 2. Null is '0' F001 = '2' F003 = .000 DSPLY 1. Null is '1' F001 = '3' F003 = .000 DSPLY 2. Null is '0' F001 = '3' F003 = .000

I have turned off the null indicator for all three records, and as the fields were null they now are all zero.

F001 F002 F003 F004 F005 01 1 - .000 - - 02 2 - .000 - - 03 3 - .000 - -

I mentioned above that those of us who have worked with triggers have encountered the "null byte map" as part of the buffer passed to the trigger program. The example below has been pared down just to show the parts relevant for displaying the "null byte map".

01 dcl-ds BufferDs qualified ; . . . 11 OffsetBeforeRcd uns(10) ; 12 LengthBeforeRcd uns(10) ; 13 OffsetBeforeNullMap uns(10) ; 14 LengthBeforeNullMap uns(10) ; 15 OffsetAfterRcd uns(10) ; 16 LengthAfterRcd uns(10) ; 17 OffsetAfterNullMap uns(10) ; 18 LengthAfterNullMap uns(10) ; 19 Reserved3 char(16) ; 20 TheRest char(1000) ; 21 end-ds ; 24 dcl-ds BeforeNulls qualified ; 25 F001 ind ; 26 F002 ind ; 27 F003 ind ; 28 F004 ind ; 29 F005 ind ; 30 end-ds ; 33 dcl-ds AfterNulls likeds(BeforeNulls) ; 37 BeforeNulls = %subst(BufferDs.TheRest: BufferDs.OffsetBeforeNullMap - 95: BufferDs.LengthBeforeNullMap) ; 38 AfterNulls = %subst(BufferDs.TheRest: BufferDs.OffsetAfterNullMap - 95: BufferDs.LengthAfterNullMap) ;

I added the full trigger program as a trigger on the file I created previously, and when I ran the first example program the "null byte map" indicators were as follows:

> EVAL BeforeNulls BEFORENULLS.F001 = '0' BEFORENULLS.F002 = '1' BEFORENULLS.F003 = '0' BEFORENULLS.F004 = '1' BEFORENULLS.F005 = '1' > EVAL AfterNulls AFTERNULLS.F001 = '0' AFTERNULLS.F002 = '1' AFTERNULLS.F003 = '1' AFTERNULLS.F004 = '1' AFTERNULLS.F005 = '1'

The null byte of F001 will always be '0' as the field does not allow null.

In the example program I changed the null indicator for F003 from *off , '0', to *on , '1'. This can be seen in the values for the BEFORENULLS.F003 and the AFTERNULLS.F003 .

As you can see handling null in modern RPG is simple. In the next post I will discuss ways of handling nulls using SQL.

You can learn more about this from the IBM website:

This article was written for IBM i 7.2.