27
Fri, Dec
0 New Articles

Practical RPG: NULLs in DDS-Defined Files

RPG
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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!

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: