DDL-defined tables are very good about handling null values, but sometimes we still need to deal with them in DDS-defined files as well; this article shows the RPG techniques required to do that.

When you create a table in SQL using DDL statements, the default is to allow nulls, which means that, for any row, you can populate the column with a null value. Nulls have a number of specific characteristics; you can read about them here. We won't go into those issues in detail, although they can be quite interesting. The issue of nulls in WHERE clauses is particularly fascinating but not relevant to our discussion. Instead, today we're going to see how to support null result values in more-traditional RPG and DDS settings.

When Do We See Nulls?

The most common cause of a null value is the LEFT OUTER JOIN function in SQL. Let's review a very simple case. We'll start with a query that shows the customer name for every customer order.

select OHORD, OHCUST, CMNAME from ORDHDR join CUSMAS on OHCUST = CMCUST

This is simple enough: Get the customer name field CMNAME from the associated customer master record. But here we run into one of the first issues with SQL: What happens when a record doesn't exist? Let's say that, for whatever reason, the customer master record has been deleted, so there is no CUSMAS record with the required value in CMCUST. In that case, this query would simply ignore the unmatched order header and wouldn't report it at all. And while that might be the desired outcome, chances are the end user would rather see the data. So that brings us to the LEFT OUTER JOIN:

select OHORD, OHCUST, CMNAME from                

  ORDHDR left outer join CUSMAS on OHCUST = CMCUST

Notice the difference? It's not much: just adding the words LEFT OUTER to the JOIN. This causes all the order header records to be retrieved even if there is no matching customer master. However, the problem is that the value returned for CMNAME in those cases is the null value. This isn't the same as a blank (or zero for a numeric), and it can cause some issues later on.

One quick note: There is a standard way to get around this issue. If you want to always replace the null value with another specific replacement (such as blank), you can do so using the SQL IFNULL clause as shown below.

select OHORD, OHCUST, ifnull(CMNAME, ' ') from                

  ORDHDR left outer join CUSMAS on OHCUST = CMCUST

This technique is not difficult, although it can be rather tedious when you have many different values that can be null. However, it's probably the more-common practice and one you should know. Let's move on, though, to an alternate method.

Storing Nulls in a DDS-Defined File

I use SQL all the time to load files. A traditional business requirement will be to extract a bunch of data from an ERP into one or more simple files that can then be downloaded into a data warehouse for reporting purposes. I see more and more of that these days, and SQL is a fantastic tool, especially for the initial data load. I may then run the initial extract file(s) through an RPG program to get some of the more-complex data points, but the initial load is done via a traditional INSERT INTO statement. The problem comes when that interim file is DDS-defined (rather than DDL-defined).

It's a problem because DDS files don't normally support nulls. If you try to put a null into a field in a traditional DDS file, you'll get a CPF5035 data mapping exception with an error code of 20, which simply means you're trying to put a null where it's not allowed. That error will eventually lead to an SQL0407, and you'll be done. So how do we get around it?

One option is to use the IFNULL clause as shown earlier. But as I said, this can expand your SQL statement quite a bit, especially when you have many nullable fields. The alternative is the ALWNULL keyword in DDS. Let's take a quick peek at our hypothetical file above. The DDS might look like this:

A         R ORDEXTR                                        

A           OHORD     R          REFFLD(OHORD ORDHDR)

A           OHCUST   R          REFFLD(OHCUST ORDHDR)

A           CMNAME   R          REFFLD(CMNAME CUSMAS)

As I said, the problem comes when we don't get a hit in CUSMAS on the OHCUST value. We end up with a null value, which causes an error. But that can be fixed easily enough by simply changing the definition of the CMNAME field:

A           CMNAME   R          REFFLD(CMNAME CUSMAS) ALWNULL

The addition of the ALWNULL keyword signifies to DB2 that we can have nulls in this field. And we're done!

Supporting Nulls in RPG

Well, not quite. Because by default, RPG doesn't understand nulls either. There's plenty of support in the language for nulls, but you'll have to do some work to get at it. First, you'll have to tell the RPG compiler that you're planning to use nulls via a keyword in the program's specification. There are a couple of different settings, but let's go all the way to the most null-friendly version:

ctl-opt actgrp(*new) option(*srcstmt:*nodebugio) alwnull(*usrctl);

Those who know my programming style know that this is my go-to control specification. Unless I have a good reason, I default to a new activation group, especially during development, and *SRCSTMT and *NODEBUGIO are essential to productive debugging for me. But the important bit is the ALWNULL(*USRCTL), which directs the compiler to not only allow nulls in the database, but also let me set the null indicator for those fields. Why do I do that? So that I can reset those null fields to normal as I see fit.

So let's say I've extracted a bunch of data to the file. Now I can run a simple fix program to repair those nulls. Here's the program in all its glory:

read ORDEXT;                      

dow not %eof(ORDEXT);              

// Remove null indicator if found

if %nullind(CMNAME);            

   %nullind(CMNAME) = *off;      

   CMNAME = '*';      

   update ORDEXTR;                  

endif;                          

read ORDEXT;                    

enddo;                            

*inlr = *on;                      

return;                            

The logic is simple. The %NULLIND built-in function (BIF) is used to test the CMNAME field to see if it does indeed contain a null value. If so, the program clears the null indicator, sets the field to an appropriate default value, and updates the record. The program does this for every record in the file.

I realize that this program is very simple and that this logic is in fact just duplicating the IFNULL scalar function from SQL. But this technique allows much more sophisticated handling of these conditions. For example, we might have many fields from the CUSMAS file; with this technique, the RPG program can easily clear all the null indicators instead of having to resort to a whole host of IFNULL clauses in the SQL. And it would only have to check one field for null; it would assume that if one field is null, they'll all be null. Another feature would be to invoke a notification routine of some kind whenever a null was found; maybe send an email to someone. That would be easy to do in the RPG program, much more difficult in the SQL-only solution. Or maybe you might want to set the default value differently, depending on some other business logic; that's again something easy to do in RPG but not always so easy in SQL.

A Last Caveat

If you do find yourself wanting to use this solution, here's one oddity I found. The only way to clear the null indicator is to use the following statement:

   %nullind(CMNAME) = *off;      

I originally tried this code, which did not work:

   clear %nullind(CMNAME);      

Just one of those strange compiler behaviors that aren't entirely intuitive. To me, those two statements should function identically, but they do not. I'm just sharing that little nugget with you so that you don't have to find it the hard way.

Summing It Up

Using nulls in DDS-defined files isn't something you'll probably need to do a lot, but if you run across that unique circumstance, then hopefully this article will help you through it. Enjoy!

Joe Pluta is the founder and chief architect of Pluta Brothers Design, Inc. He has been extending the IBM midrange since the days of the IBM System/3. Joe uses WebSphere extensively, especially as the base for PSC/400, the only product that can move your legacy systems to the Web using simple green-screen commands. He has written several books, including Developing Web 2.0 Applications with EGL for IBM i, E-Deployment: The Fastest Path to the Web, Eclipse: Step by Step, and WDSC: Step by Step. Joe performs onsite mentoring and speaks at user groups around the country. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Joe Pluta available now on the MC Press Bookstore.

		Developing Web 2.0 Applications with EGL for IBM i Joe Pluta introduces you to EGL Rich UI and IBM’s Rational Developer for the IBM i platform. List Price $39.95 Now On Sale
		WDSC: Step by Step Discover incredibly powerful WDSC with this easy-to-understand yet thorough introduction. List Price $74.95 Now On Sale
		Eclipse: Step by Step Quickly get up to speed and productivity using Eclipse. List Price $59.00 Now On Sale

Also Read

Advanced/Free - Function Calls and Return Values

PRACTICAL ILE - ILE Service Programs

Data-Manipulation Operations Using Free Format, Part Two