Dealing With Nulls

Our most recent IBM i EXTRA article covers a bit about dealing with nulls when embedding SQL into RPG. But it is by no means a complete study of all the options.

That fact was quickly pointed out to us by reader Rusty Gadberry from Arkansas Data Services, who offered his much simpler solution. So we thought we'd introduce that solution here and start a discussion on when this might be an appropriate way to deal with nulls

Here's the suggested alternative to our logic:

   Exec SQL   

    Select CustID, CustName, ifnull (CustSales,dec(.00,7,2))

     Into :CustID, :CustName, :CustSales

     From Customer

     Where CustID = :InputCust;

Using the IfNull function, this shop avoids the possibility of indeterminate data coming into the program. We've used this approach sometimes, as well as a similar alternative approach using the Coalesce function shown below. In both cases, if the CustSales column value is null, it will be replaced with a value of zero in the result set returned to the RPG program. 

    Exec SQL

       Select CustID, CustName, Coalesce (CustSales,dec(.00,7,2))

         Into :CustID, :CustName, :CustSales

         From Customer

         Where CustID = :InputCust;

Approaches such as these are certainly simpler (and we like simple!) for those cases where the only logic we would have performed once we discovered a null is to replace the associated column value with a "generic" default.

However, neither of these approaches caters to cases where the "nullness" of a value is significant compared to zero (or some other "default" value). If there weren't such cases, then why were the columns made null-capable in the first place? Perhaps by default/mistake? Admittedly, in the very simplified example in the article, such a distinction is a bit of stretch.

It seems to us that it would often be significant in the case of outer joins to know about the presence of nulls, regardless of whether a column were otherwise null-capable or not. And, of course, date columns rarely have an easy or obvious default value to substitute. So we stand by the need to have a way to interrogate the nullness of a column in many circumstances.

Perhaps this is a good time to encourage you to think about null capability in your databases. Traditionally, it has been rare to have null-capable columns, but since there is a definite difference between the concept of "no value" and a zero, blank or other special value, perhaps we should consider more carefully which columns should be null-capable and which should not (i.e., a blank or zero value in this particular column is not significantly different to null).

Many shops these days are "modernizing" their databases, so this makes it a good time to consider making such changes if/when they make sense. Of course, in doing so, we must also potentially add to the logic of all our RPG (and other language) programs that access that data. We would certainly have to add logic to use %Null when using RPG's "native" I/O. Perhaps for such databases we could even mask this additional complexity by using an OA handler? For those programs using SQL to access the data, the techniques shown here or illustrated in the EXTRA article will need to be utilized.

Of course, you may have your own approaches, which we would love to see you share via the comments!

Tidbits on Tools, Amazon Prime Air and Mobile

Sometimes there is no one "big thing" that piques our interest in a given week - particularly around holiday periods when there is less in the way of IT-related news. But often we have a backlog of little things that we think worthy of mention but which are not big enough to warrant a complete blog entry. So this week a few of those.

Finding the Tools You Need

First up is the (relatively) new list of Web development tools on Midrange.com. The idea is to provide a single point where you can look for tools in the arena of refacing, building new Web applications, development IDEs and more. Since this is a community-developed page, the hope is that it is less likely to be subject to overblown vendor rhetoric and that tools will be categorized where they best fit. By no means a comprehensive list and we know of a few tools that are missing, but it is a good start. If you find something missing by all means add it.

When You Just Gotta Have It (Almost) Now!

In the category of "Is it April 1st already?" comes the latest from Amazon--Prime Air. This is the official piece on the Amazon site, but simply Google "Amazon Prime Air" and you'll get more than 30 pages referencing that exact term! You can say what you like about Jeff Bezos, but the man certainly knows how to get the world talking!

Will Prime Air ever happen? Well there are any number of issues ranging from gun toting individuals taking pot-shots at the delivery vehicles, to insurance issues as a drone slices up the family cat as it lands, to the sheer number of distribution centers that would be needed to ensure 30-minute delivery. But we have a feeling that it, or something very much like it, is in our future--just maybe not in the two- to five-year timeframe that Bezos is talking.

The Next Step in Mobile

One of the issues in developing mobile applications is that it can take a lot of effort to make applications that will work even when you are not able to connect. Certainly the new local storage features of HTML5 has made this less of a problem than it has been in the past, but even so, a lot of Javascript skill is required if you want such apps to work on both Android and iOS systems.

Enter the latest update to Lansa's Long Range product. It provides local storage via the SQLite database engine that is built-in to both operating systems. No Javascript required according to Lansa. Just your RPG or COBOL skills to configure the components. You can find the details here. We look forward to seeing how this feature works.

Lansa, of course, isn't the only vendor to offer mobile capabilities in their products. Asna, BCD, CNX, look, Profound, Rocket, and countless others, offer such capabilities in their tools. But Lansa isthe first that we are aware of that directly support stand-alone disconnected operation. If indeed another vendor does support this capability and we've missed it, please let us know.

That's all for this time. We hope all our American readers enjoyed their Thanksgiving. Ours was almost derailed by a major plumbing problem, but all went well in the end, just a little more chaotic than we had intended. In fact we're awaiting the arrival of the plumber as we write this; wish us luck!
 

Educating New RPGers

We had a discussion with a client this week that we've had many times before. It usually starts off with a question about what language they should use for future development--the implication (sometimes stated) is that RPG isn't an option.

Why? Not because it's an old language, not that it can't do the things they need it to do, but because it has become too hard to find RPG programmers. More specifically, it has become even harder to find RPGers who are willing and able to do modern things.

We responded with the same suggestion that we normally do (and we have done here in the blog before. If you can't find RPGers, look for a PHPer (or Java or .Net or a ...) who seems to have a business mindset (vs. wanting to write games or the next Facebook application) and teach them RPG and DB2. If you stick to /Free and other modern RPG practices--at least in the beginning--our experience has been that they not only learn RPG easily, they actually quite like the simplicity of the language. After they are hooked on RPG, then you can (if you must) gradually show them the ancient-style code that you almost certainly still have lying around the shop.

This idea has been used with great success in a number of IBM i shops. However, to our knowledge anyway, they have tended to be relatively large shops where there were a number of people who could share the mentorship role. In this specific case, we're talking about a relatively small shop. So the time commitment to not only teach the RPG language to the newbie but also to mentor them in the intricacies of IBM i, DB2 for i, etc. would require a very signifiant proportion of the shop's developer resources. And that's a problem.

Schools in the the IBM Academic Initiative program rarely seem to teach RPG these days. This is mostly because, as we blogged previously, even when they graduate RPG-literate students, too many potential employers still insist on three to five years' experience. But, even if all of the school programs were as successful in turning out RPG literate graduates as our friend Jim Buck at Gateway Technical College, the time it takes for students to graduate from such programs is longer than some of our clients can wait. They need to build their five-year plans today--before their existing programmers hit retirement age--and they can't build a plan simply based on the vague hope that there will be RPG programmers around to replace those staff.

A dilemma to be sure. What's the answer? We wish we knew, but increasingly we think that this is an area where IBM, perhaps in conjunction with their business partners, needs to take a lead. There are a lot of unemployed folks out there who have extensive business experience. Many of them probably did some programming with TRS-80, Commodore 64s, IBM PCs, etc. in their youth. Maybe--just maybe--they are a resource that could be tapped to help fill the programmer gap. But it will need an initiative from someone of the stature of IBM to make it a reality and ensure that "graduates" of such an initiative would be able to break through the insane experience barrier that so many shops erect around their hiring process.

Have you encountered this problem? Come up with and solutions of your own?  We'd love to hear about them through comments to the blog.

CL Rides Again

Those of you who have followed our writings (ramblings?) for a while know that we have a kind of love-hate relationship with CL. The syntax, while effective for commands, just feels clunky and unnatural when used for an entire program. Back in the pre-RPG IV days we would begrudgingly call CL programs to do the stuff that we just couldn't do in RPG. But as RPG IV evolved, and the number of system APIs increased, there was less and less that we couldn't do directly in RPG. Combine that with the fact that CL effectively stagnated for many years and there was little to convince us otherwise, hard as CL aficionados such as Bruce Vining and Jim Sloan tried to convince us.

But things have changed. Under the stewardship of Guy Vig, CL has been given a new lease on life with enhancements in many areas. It started back in OS/400 V5R3 with additions to the types of variables that could be defined, new looping and control structures, increases in the number of files that could be declared and more. The enhancements continued in V5R4 with the addition of subroutines and enhancements to data definition that effectively provided a form of data structure (DS) definition. Since having to use string/substring operations to fake out DS definitions was one of our pet peeves in CL, this was a big step forward. IBM i 6.1 continued the forward movement with the addition of INCLUDE, the equivalent of RPG's /COPY. It also finally added a CLOSE command--now you can actually close a file that has been opened in the CL program and open another (or the same) file in the program without performing unnatural acts.

Now with the latest round of PTFs to 7.1 a number of our remaining frustrations have been addressed and we really have been forced to start to take more notice of this IBM i stalwart. To use this new CL functionality on a 7.1 system, PTF SI49061 must be loaded and applied*. Note that support is _not_ going to be PTF'd to the 6.1 or 5.4 releases, but you will be able to compile CL code that uses these new functions on your 7.1 system by specifying the appropriate target release (TGTRLS) value.

These latest enhancements provide some major upgrades to CL's string handling capabilities. Even better, because they are almost identical in name and operation to their RPG equivalents, there's nothing much new to learn. The new functions are:

%CHECK    - Check Characters
%CHECKR - Check Reverse
%SCAN    - Scan for Characters
%TRIM    - Trim Characters at Edges
%TRIML    - Trim Leading Characters
%TRIMR    - Trim Trailing Characters 

These new built-ins can be used wherever CL would permit the use of an expression of the same type. For example %CHECK can be used in an IF or WHEN command. %TRIM (and its variants) can be used directly in a character expression (e.g., CHGVAR VAR(&ADDRESS) VALUE(%TRIM(&STREETNAME) *BCAT %TRIM(&CITY)) ) or to set the value of any command parameter that permits expressions (i.e. ,the parameter is specified as EXPR(*YES) ). You can get more details on the %TRIMx functions here and on the %CHECKx and %Scan functions here.

We understand more enhancements are planned for CL in the future, but nobody will actually tell us what they are yet so we'll all have to wait. What is clear though is that the days of CL as a stagnant, unchanging language are over and that we really need to start paying more attention to it.

Many thanks to Guy and the CL team!
 
* Note: The %Trim functions were actually snuck in via PTF back in October 2012 but went largely unnoticed.

/Free at 12

RPG's /Free format has been with us since i5/OS V5R1. That release GA'd in 2001 so this May will mark it's 12th birthday. Doesn't time fly when you are having fun! Despite the fact that /Free will soon enter its teens, some questions about its design still surface on a regular basis. This week one of those perennial questions appeared on the RPG list at midrange.com. The resulting discussion made us think about one of the "features" of traditional RPG op-codes that was not implemented in /Free. Read on and see what you think.

The questioner in this case needed to truncate the result field on an EVAL operation in the same manner that a Z-ADD would do. As is usual in such cases, responders posted many different solutions to the problem--several of the solutions were truly ugly. Certainly the majority would have required substantive commenting in order to avoid confusing the heck out any future maintenance programmers.

When /Free came out we were very much in favor of dropping some of the more arcane RPG op-code such as MOVE and ADD. ADD because EVAL was a much better option and we always thought old-style RPG's numeric truncation was an extremely dangerous "feature" that we were better off without. MOVE because you had no idea what it was doing until you knew the data types and lengths of both fields. We won't get back into the MOVE discussion here, but this latest go-round of postings did make us wonder about solutions for the truncation issue.

One of the few valid uses for numeric truncation that we have encountered involves the rolling-over of counters. For example, to avoid generating duplicate keys, some people use a rolling counter coupled with the time. If you use a four-digit counter, it is useful to have it roll directly from 9999 to 0 when one is added to it. Note that we said "useful" and not "good." It was always poor technique because what it is doing (the roll-over) is not obvious when looking at the code. Regardless, to achieve the same thing in /Free requires that you test the value of the counter before incrementing it and then increment or zero it as appropriate. At least what is happening is more obvious but it does clutter up the mainline logic.

Normally in such circumstances we would recommend that folks use a simple generic subprocedure to implement the function. For the sake of argument let's call it IncrementCounter. This has the advantage that everyone knows its purpose when seeing it in the code and will know that it rolls over on hitting the maximum. It also has the advantage of hiding the complexity of the logic and, as we have said many times before, using subprocedures in this way saves a lot of time in understanding what is going on in the code just so you can ignore it.

But the whole discussion did make us wonder whether there is indeed a place for a new op-code (EVALT) or op-code extender EVAL(T) in RPG. It would handle the scenario posed in the original question on the RPG list and it would also handle the roll-over scenario. We tend to lean toward the use of a T(runcate) extender on numeric EVALs because there is really no need for a new op-code and also because extenders are not frequently used so they tend to jump out at you and make their use obvious. Indeed since EVAL itself is rarely used in /Free an EVAL(T), it really is going to stand out.

What do you think?  Is this the kind of simple RPG extension that is worth developing? Let us know via the blog comments.

P.S. No, we haven't changed our minds about implementing MOVE in /Free. That was one death we were pleased to see.

Happy Holidays!

It's the Christmas season and this is our last blog of the year, which means it's our last chance to extend seasons greetings to our readers. In particular thanks to those of you who have contributed to the blog via your comments. It's always nice to know that some of you find the posts interesting enough to join in the discussion.

In the spirit of the season a little reminiscing is in order. Some of you may have already seen this but we got a real chuckle out of it and thought we'd pass it along. It is a 1960s (i.e., Mad Men era) impression of Google. Give it a test drive here. It's a lot of fun. When we fed in our own names some of the images of Susan were recognizable but Jon's--well, not so much. Jon's Google hits are always a bit odd anyway because a young rock and blues artist has the same name. For those of you too young to remember, it's a pretty darn good impression of what a '60s version of Google might have been like. Assuming there had been an Internet for Google to search, of course.

Our Naughty and Nice List Winners for 2012

Santa will shortly be on his way and we thought we'd just let him know via this blog who are our personal naughty and nice champions this year.

Naughty: Software companies that treat their existing customers badly. There's a certain purveyor of accounting software (you know who they are Santa) whose software we use on our Macs to do our bookkeeping. Every year they come out with a new release. And every year we get the same email.

"As a loyal customer ... we are pleased to offer you this special upgrade rate of only $..."

Only one problem. The "special rate" is invariably at least 20 percent higher than the cost of a brand new copy of the same software at any number of local computer retailers--and that is their normal price not a special sale offering. So for being a "loyal customer" we get to pay 20 percent more. Thanks but no thanks.

Nice: IBM Rational Software for removing the charges from Open Access. We were very critical of Rational's handling of Open Access so it only seems fair to offer kudos to them for making the right decision, reversing their position, removing the charges and incorporating it into the compiler.

Now that it has been liberated we are beginning to see more articles on the topic and people sharing their own ideas for handlers. Hopefully we'll see some collaborative open-source projects in the future and then, as Wallace (of Wallace and Gromit), the voice of our GPS, would say, "Let's see what this baby can do!"

We hope you all have a wonderful festive season--no matter how or where you celebrate it.

Rational Developer for Power Systems 8.5 & Beyond

You don't have to have been following this blog for long to know that we are both big fans of RSE, which is currently packaged in Rational Developer for Power Systems (RDP). Many of you are probably RSE users as well and you may have noticed that Rational came out with a new version, RDP 8.5, back in June. If so, you may have wondered why you haven't seen us blog about it.

Well, we installed 8.5 and while there was at least one really major new feature we liked, we also encountered a few idiosyncrasies that gave us some problems. In particular if a connection dropped while we had source files open, it took forever to get the workbench back in action as you had to respond to a seemingly endless series of connection-related dialogs. None of them offering the option to work off-line, etc. Admittedly we are perhaps unusual in that we often travel to a client site, connect to their system, and then go back to our hotel with the sources still open, knowing that we will be back on site next day. Once we open up the laptop again it can take 10 to 15 minutes to regain control. There were other issues, but to be honest, neither of us can remember exactly what those were. We're also not sure whether they were new features that we just needed to learn how to utilize properly or whether they were bugs. We also saw several reports on the WDSC-L list on Midrange.com and other places of other people who were having trouble with 8.5.

Bottom line, we are both so addicted to using RDP for our development that we decided to postpone digging into it for a while. Fortunately, we had also kept our old installation of RDP 8.0.3 so it was an option for us to keep on working with that version.

The good news is that a fix pack for RDP 8.5 has recently become available. So maybe it's time that we give 8.5.1 a try and see if it works better for us than our earlier attempts at 8.5.

Another reason for postponing the use of the new release is that we didn't have a really burning requirement for the only significant new feature that we heard about in the community--a dynamically updated Outline View. It's a nice feature, to be sure, but since we don't work as much with very large programs, occasionally refreshing our Outline Views isn't exactly a huge burden.

And we also had real difficulty finding a good list of any other enhancements in 8.5 that would cause us to want to dive in right away. We have now found such a list on developerWorks and thought we'd share with you, just in case some of you are also postponing the upgrade. So here it is--courtesy of Dave Dykstal--a nicely detailed list of new features, complete with link to a video of the RPG Outline View enhancements by Edmund Reinhardt.

But wait, that's not all! (sounds a bit like a late night infomercial, doesn't it?). The 8.5.1 fix pack includes several new features, not just bug fixes. You can read all about the 8.5.1 fixes and enhancements here. Spoiler alert: any IBM i COBOL programmers out there may want to pay particular attention to the Outline View in 8.5.1, according to this list. They have waited a long time for a usable Outline View, and it looks like their patience has been rewarded.

Maybe in a future blog post, we'll report on our findings of all the cool new things we've experienced in 8.5.1. As soon as we're both able to get the fix pack installed, that is. Jon's update to 8.5.1 worked flawlessly, but Susan seems to have hit a (hopefully temporary) snag.

In the meantime, any of you who have used 8.5 or 8.5.1, please feel free to share your experiences in the comments.

Mobile Is ... Different

To our U.S. readers, welcome back to reality after the Thanksgiving break. Hope you had a wonderful holiday surrounded by friends and family and that this week hasn't proven too much of a come-down for you.

Three things triggered this week's blog. The first was an email from a friend and client who, having just gotten his hands on an iPad, was looking for recommendations for a 5250 app. The second was an email that announced the publication of the latest part of IBM Systems Magazine's Special Report on mobile applications. This particular report focuses on security and should be compulsory reading for all those moving into the mobile universe. The third component originally came to our attention back in October at the Fall RPG & DB2 Summit where the folks from CNX showed us a preview version of their new iAdmin product. Having had the experience of wrestling with a smartphone 5250 app just to enable a user profile, we were thrilled to see this product in action. Now that it has been officially released, we can tell you about it. For those of us with older eyes and/or fat fingers this really is a great idea, and a great example of just what can be done with mobile apps. Give it a test drive from your phone or tablet at iadmin.cnxcorp.com and see what you think.

One thing that all of this highlighted for us is that mobile is different. The design issues are different. The security issues are different. And different challenges require different thinking. We might have been able to get away with designing our Web applications as giant multitabbed and (hopefully) prettier versions of 5250 applications, but not mobile apps. We need to rethink our design perspective and look for our inspiration elsewhere. The folks at CNX initially developed their new application for in-house use based on their own frustrations with teeny tiny 5250 screens (and they are all much younger than us!). What is missing in your shop? What problems could mobile apps solve for you? Things are changing quickly and embracing mobile technology may well be a make-or-break point for many IBM i shops. Because if we don't do it somebody else will.

Happy Endings

We're writing this on the plane flying from Stockholm to Heathrow en-route to a brief stopover at home before we head to Minneapolis for the upcoming RPG & DB2 Summit.

Our friends at Data3 did a wonderful job and the event was very successful with more than 100 of the faithful gathered together to hear about the "latest and greatest" in all things IBM i. The location was beautiful too - just outside the downtown core right alongside the channel into the main harbor. In fact one of the sights at the hotel is to watch the daily arrival and departure of the large and very modern ferries, which the locals affectionately refer to as the "booze cruise." Two or three of these leave Stockholm each day and make the overnight run to Finland or sometimes, apparently, simply into the nautical equivalent of no-man's land. Why? Because Sweden has very high taxes on liquor and apparently these cruises take long enough for you to qualify for a substantial duty-free allowance. As our friends Trevor Perry and Alison Butterill noted, this accounts for the people getting off what appears to be a cruise liner carrying only tiny overnight bags--and very large bags of duty-free items!

But telling you how lucky we were to have been teaching in such a gorgeous location was not intended to be the point of this blog. Instead we wanted to pass on a story related to us by our hosts of one Swedish company's successful approach to the problem of finding new RPG blood. Like many of our customers, the company faced an aging RPG population. While some of their work is outsourced, the organization felt the need to have a strong in-house core to the development team. The solution was to hire recent computer studies graduates and have them trained in RPG. Those of you who read our blog regularly will know that this is an approach we have long advocated, and we have been involved in several such programs. It was encouraging, therefore, to see others successfully using this approach. The training consisted of a three-week classroom period, followed by four weeks of practical hands-on experience and then an additional week in the classroom. Needless to say, crucial to this effort to persuade these new hires that RPG did not mean "Role Playing Game" was the use of modern tools like RDP--no green-screen chains for these newcomers!

If you can read Swedish you can find the details in Data3's excellent magazine. We tried Google translate but it just isn't good enough to publish in that form. So, we hope to work with our friends at Data3 to produce an English version, which with luck we can share with you in next month's IBM i EXTRA eNewsletter.

It is nice to hear a story with a happy ending! But Jon will probably never persuade Susan that herring  is a real delicacy worth exploring.

IBM Mobile Database Syncs with DB2 on IBM i

Following our recent blog post on mobile computing and IBM i, we were contacted by our old friend George Farr. As some of you may know, George moved out of the IBM i Rational team a while back--remember the letters "IBM"stand for "I've Been Moved"--and joined the DB2 team. He's now the product manager for DB2 on mobile devices.

Luckily for us, George hasn't forgotten his roots and wrote to tell us that he had made sure that his first new product (IBM Mobile Database) would sync with DB2 on IBM i. The product is free of charge and allows you to sync to both DB2 and Informix databases. The product is part of the "IBM solidDB" family and you can find the details on this page. If you want to just jump into the details try this link. We had a lot of trouble finding these documents, but it turns out the trick is to Google with the following: "IBM Mobile Database" site:ibm.com. Note the quote marks and the "site:" part. Without those you'll get a lot of hits but nothing terribly useful! To download go to this Web page. You'll notice on the right-hand side a download link for the binary and for a sample development set-up for Eclipse. Hopefully one of our friends that works with Android will give this thing a try and get back to us with a report. Aaron Bartell are you listening?

We've also been thinking a lot about database administration of late. Several of our clients now have really huge amounts of data and are beginning to wonder if they need a dedicated DBA. We started off intending to write a blog comment on the topic, but it turned out there was a lot more to say than could really be accommodated in a blog entry, so instead you'll find it in an article in the most recent edition of the magazine's IBM i Extra e-newsletter.