Wednesday, March 30, 2016

Handling null in RPG

rpg null nullind

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:

  1. without value, effect, consequence, or significance.
  2. 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.

21 comments:

  1. The standard algorithm for sequential process is

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

    ReplyDelete
    Replies
    1. We are going to have to disagree on this point.

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

      Delete
    2. Although I appreciate your article IMHO it is far easier to handle null values in embedded SQL than it is in RPG

      Delete
  2. With more and more interfacing with external data sources this is important.

    ReplyDelete
    Replies
    1. Often, columns with null values are imported from other DBs, e.g. MSAccess.

      Still, 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.

      Delete
  3. 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?

    ReplyDelete
    Replies
    1. One 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.

      Delete
    2. In our applications we use a null value in our expiration date column instead of specifying an expiration date like 12-31-2199.

      Delete
  4. I agree NULL values are important if we need to distiungush whether a default entry or no entry was performed.

    Even 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

    ReplyDelete
  5. Two questions, Simon:
    1. 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?

    ReplyDelete
    Replies
    1. 1. Yes, it could.

      2. If you are using fully free RPG you need to start your program with **FREE. See Trying fully free RPG

      Delete
    2. 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! : ))

      Delete
  6. we 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

    ReplyDelete
    Replies
    1. Specifying **FREE on the first line eliminates the restriction that free format code stay between columns 8 and 80, therefore its "fully free".

      Delete
    2. Try 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.

      Since IBM i 7.1 TR7 the /FREE and /END-FREE are not necessary.

      Delete
    3. One other thing to note here is that **FREE (fully free) also means ONLY free and will not allow any fix format code.

      Delete
    4. You can insert fixed format code but you have to /COPY it in. See Trying fully free RPG.

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

      Delete
  7. A few tidbits I've discovered over the years.

    For 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

    ReplyDelete
  8. use this for SQLRPGLE program
    dcl-s gSqlIndArray int(5:0) dim(200) inz ;
    exec SQL
    fetch Next
    from ForecastCursor
    into :pfcsumpfDs :gSqlIndArray ;

    ReplyDelete
  9. Can I move a null value to a field? something like eval fieldX = *null;

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.