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.
The standard algorithm for sequential process is
ReplyDelete01 Raed file;
02 while record found; (Not end of file)
03 process breakings start
04 process record;
06 read next record;
07 process breakings end
08 end while;
The code example is inefficient because it generates a lot of unnecessary comparisons and jumps.
Example code:
01 dow (1=1) => comparison
02 read...
03 if %eof; => comparison & jump
04 leave => jump
05 endif;
06 // Process
07 enddo; => Jump
standard algorithm
01 read...
02 dow not %eof; => comparison
03 // process
04 read next
04 enddo; => jump
with large volumes of data performance improvement can be very sensitive.
We are going to have to disagree on this point.
DeleteIn my opinion the need to have two reads is redundant. I find it is clearer that if I am to ignore a record that I have read an ITER is easier to follow than trying to find the corresponding ENDIF to an IF, which might be many lines of code later.
But “each to his own”. This is not mathematics and there is more than one way to write a program.
Although I appreciate your article IMHO it is far easier to handle null values in embedded SQL than it is in RPG
DeleteWith more and more interfacing with external data sources this is important.
ReplyDeleteOften, columns with null values are imported from other DBs, e.g. MSAccess.
DeleteStill, using Simon's date of death example, if you're using true date fields, how else do you indicate "unknown"?
Of course, you could use 0001-01-01 or 9999-12-31, but then you have a "shop" standard when there is already a global standard.
I find it counter-intuitive and counter-productive to allow nulls in DB columns. Nice to know that they can be "handled", but why allow them in the first place?
ReplyDeleteOne of the areas that we have found this is useful is when trying to implement relational tables. We have foreign-keyed columns that are optional. It was either use a column that allows nulls or create a dummy record in the master table for blanks.
DeleteIn our applications we use a null value in our expiration date column instead of specifying an expiration date like 12-31-2199.
DeleteI agree NULL values are important if we need to distiungush whether a default entry or no entry was performed.
ReplyDeleteEven though the NULL capability is the default for defining SQL columns, I only use them sparingly.
For a NULL value an additional flag is added to the column which must be checked separately.
In native I/O with %NULLIND and with (embedded) SQL by using an indicator variable or converting the NULL value explicitely with a sclar function into a default value.
Even though we talk about nano seconds, additional checks are time consuming.
Birgitta
Two questions, Simon:
ReplyDelete1. Could the 5 lines (10 thru 14) be replaced with:
%nullind(F003) = not %nullind(F003) ; // Reverse Ind
?
2. I've written several "fully free" RPG programs (though I prefer not to) and have never before seen or heard of "**FREE". What's up with that?
1. Yes, it could.
Delete2. If you are using fully free RPG you need to start your program with **FREE. See Trying fully free RPG
Ah, I see. My code is all between 8 and 80. Now I'm in a quandry. I LIKE being able to use the entire line - A LOT! But I'm not so fond of ALL the free code. e.g., I like using the P spec for sub-procs. I might have to get over it just to use the full line! : ))
Deletewe are using V7R2M0, none of our "fully free RPG" programs start with **FREE or have any /free or /end-free tags, these are no longer required
ReplyDeleteSpecifying **FREE on the first line eliminates the restriction that free format code stay between columns 8 and 80, therefore its "fully free".
DeleteTry starting your RPG code in the first column of the source member, not in the eighth. If you are not starting your code in the first column you are not using "fully free" RPG.
DeleteSince IBM i 7.1 TR7 the /FREE and /END-FREE are not necessary.
One other thing to note here is that **FREE (fully free) also means ONLY free and will not allow any fix format code.
DeleteYou can insert fixed format code but you have to /COPY it in. See Trying fully free RPG.
DeleteThis has got way off subject. If you have any more comments about "fully free" RPG please post them on Trying fully free RPG. Thank you.
A few tidbits I've discovered over the years.
ReplyDeleteFor VARLEN (VARCHAR) DB2 fields:
1. %NullInd(ErrMsg) = *On ;
This also sets the field length to zero. The old field value is still viewable as hex in debug via
Eval Errmsg:X
2. If ( %Subst(ErrMsg:1:1) <> ' ' ) ;
This abends in RPG with substring length error if NULL because field Len = 0 for a NULL field.
but
3. If ( ErrMsg <> ' ' ) ;
runs without any errors if the field is NULL, probably not the desired behavior!
4. CLEAR RECFMT of a table does not change the current NULL field indicators so the CLEAR honors the current %NULLIND DB2 field indicators. You should sent them individually with %NullInd after the CLEAR.
And in general:
1. SELECT * from MYTABLE WHERE MYFIELD IS NOT NULL
is correct syntax.
SELECT * from MYTABLE WHERE MYFIELD <> NULL
is incorrect syntax.
2. The default value for a NULL capable field is NULL on an SQL INSERT that does not reference the field UNLESS the field had an explicit default value listed when the table was created (DDS DFT keyword or DEFAULT in SQL).
3. When NULL is ON for a field and you set it OFF, the field (column) gets set to it's default value (implicit or explicit default value).
Chris Ringer
use this for SQLRPGLE program
ReplyDeletedcl-s gSqlIndArray int(5:0) dim(200) inz ;
exec SQL
fetch Next
from ForecastCursor
into :pfcsumpfDs :gSqlIndArray ;
Can I move a null value to a field? something like eval fieldX = *null;
ReplyDeleteAlas no, *NULL is not a special value in RPG
Delete