Townsend Ponders Future Of DB2 Modernization Tech

What will Townsend Security do with the potentially groundbreaking technology it created to solve a thorny encryption issue in DB2 for i? While the Olympia, Washington-based company is not interested in getting into the database modernization business, it is open to the possibility of licensing its innovative technology–which essentially uses Open Access for RPG to replace record-level I/O calls in legacy RPG apps with the SQL Query Engine–to people who are.

Townsend finds itself in this peculiar situation through no fault of its own. The company ran into a tough technical problem that stymied some of its largest customers in the healthcare and financial services business, and, after some trial and error, created an innovative solution to that problem. The fact that the solution could elegantly solve an equally complex problem in another field entirely (database modernization) is pure happenstance, but it’s also not something that its CEO and founder, Patrick Townsend, is about to turn his back on.

Here’s some background to the story: Back in May, Townsend Security announced that it used OAR to develop a solution to a problem involving the adoption of the so-called FieldProc, which IBM introduced in 2010 to automate the implementation of field-level encryption in DB2 for i, and to eliminate the need to open up and change application source code.

The problem stems from the fact that FieldProc doesn’t work in certain situations–namely, for customers who have encrypted indexes on databases driven by legacy RPG applications that use native record-level I/O access. FieldProc works like a dream for customers whose RPG apps use the newer SQL Query Engine (SQE). But because of the different way that legacy RPG operations process data, the order of fields generated for display files and reports comes out all garbled when FieldProc is implemented.

Empty Subfiles

The Townsend CEO elaborates. “It’s not that the programs won’t run. They will, and you won’t get I/O errors,” he explains in an interview at the recent COMMON conference in New Orleans. “But what you’ll end up with are empty subfiles and empty reports. People are saying, ‘Where’s my data!’ Well it’s there. There’s just this little problem.”

This has been a minor problem for some AES/400 customers. This legacy RPG problem only crops up when the application needs to work with many records at once. It works fine if your application or employee needs to open a single record at a time. And, of course, it works fine if you don’t index by encrypted fields.

The problem is, at many banks and healthcare companies, indexing data according to encrypted fields is a common business practice. In order to speed up processing, database developers decided to build indexes where sensitive data, such as a Social Security number or the date of birth, is the primary key. Moving to the SQE–or eliminating these indexes and changing how these applications work–is not something these IBM i shops are able to get into, either because they lack the IBM i skills or the budget.

“You’re not going to easily re-engineer that,” Townsend says. “It’s a tough one. The RPG mechanisms for the record-type access are very, very different in concept and architecture than SQL. There’s no SQL equivalent SETLL operation. You have to jump through hoops to do something like set lower limits on a file” in SQL.

The problem was contained primarily to companies in healthcare and financial services industries, which face tighter regulations pertaining to the security of their data. “Some customers [with legacy RPG app] in manufacturing and retail are able to deploy FieldProc very successfully,” Townsend says. “But a lot of these folks [in financial services and healthcare] just run into a brick wall. ‘Oh, well, if you re-engineer everything in SQL, you’ll be fine!’ ‘But I’ve got 25,000 RPG programs!'”

An OAR to the Head

After pondering the problem, Townsend and his team wondered if IBM RPG Open Access, which debuted way back with the launch of IBM i version 6.1 in 2008, would provide a way out.

“We sat on this issue for three years before we got around to thinking about it,” Townsend says. “Our path to tackle this business problem was through Open Access for RPG. In essence, the compiler folks have given us a path to replace the legacy I/O engine with anything we want. As long as we play by the rules of the road in how we build these things, we can do whatever we want.”

Townsend built an OAR handler that adapt older RPG applications to use the SQE.

That includes using OAR to essentially bypass record-level I/O in legacy RPG apps and implementing SQL access via the SQE. The problem is, nobody had ever really tried to use RPG OA to build a commercial tool that programmatically replace record-level RPG access calls to using SQE calls. On paper, it looks great. (Actually, it looks fantastic–why didn’t anybody else think of this sooner? Seriously?) But the question of whether it would actually fly in the real world, on real workloads, was a big one.

“The barrier is, there’s a lot of work to map RPG database operations to SQL. That is a tough nut,” Townsend says. “That step in itself took a lot of thinking and planning. We had to scratch our heads and say, Was this really possible? And to Vern’s credit, he really came up and solved a lot of these technical problems along the way.”

That would be Vern Hamberg, the longtime COMMON volunteer, former RJS Software developer, and current senior IT developer at Ecolab in Minnesota, who is credited with building the bulk of the tool.

Hamberg had built something similar while at RJS, but it didn’t need to support all RPG functions. For the last three years, he has given a session at COMMON about how to use OAR technology to call external databases. But building a production tool that’s going to help secure data for some of the largest banks and healthcare companies in the country? That’s a whole different level of complexity, and fear.

“Even in the course of doing that thing at RJS and working with the ISV support at IBM, I had learned a ton of things about how this stuff works with RPG,” Hamberg says. “Just what does a ‘read equal’ mean? I’ve written this stuff for years, right? You know what to do. But if you start looking at what might be dumb to do, but still has to not fail, you find all kinds of all things.”

Eventually Hamberg finished mapping all the RPG operations to SQL equivalents, and made it work. Today Townsend’s AES/400 customers are starting to use the tool, which Hamberg called “a complex little monster,” to essentially migrate all legacy RPG I/O calls to the SQE, with just the addition of a single line of code.

“It supports all the [RPG] operations, with a few exceptions of how they resolve,” Hamberg says. “It ends up being completely transparent. That’s one of the beauties of this.”

New Business

Townsend Security isn’t going to become a database modernization company. “We’re not a database modernization company. We’re a data protection company!” Townsend says.

But Townsend is open to licensing the technology to third-party vendors, consultants, and ISVs for use in their own products and engagements.

Experienced RPG consultant Vern Hamberg did much of the development work on Townsend’s OAR handler.

Townsend understands the impact the technology could have. “An awful lot of folks are stuck on the modernization of the database,” he says. “They’ve got a big investment which has to be respected. They’ve made decades of investment in RPG applications. They’re not going to throw away that investment. That makes no sense. But they’re looking for a path towards a modernized approach in the database. I believe very strongly that what we’ve done will give a stepping stone to modernizing the database.”

While there’s nothing stopping anybody from using the OAR technology to build a handler that passes database calls through the SQE rather than native RPG methods, that’s probably something that only one in 20 RPG pros could pull off, Townsend says.

“The complexity of doing this is over the head of 95 percent of IBM shops, even those with good technical, experienced developers,” he says. “You’ve got to marry and have a deep understanding of legacy RPG I/O operations with SQL. You really have to know the SQL rely well. To marry those two together is a challenge.”

Townsend Security is currently doing a controlled roll-out of the OAR technology with its customers, which must license the technology separately. The company is open to creating partnerships with other companies and individuals who are willing to take a methodical approach to testing the OAR technology for database modernization initiatives.

“I have learned a lot by working with Vern, and looking at that code, the complexity is high,” Townsend says. “I think to be successful here, we have to not get overwhelmed. We may license the source code to certain ISVs where that makes sense. But again, we have to be confident they know what they’re doing, and it’s under the right circumstances. I’m not interested in creating headaches for us or for them.”

So don’t go looking for Townsend’s Magical Record-Level Formula on GitHub anytime soon. But if you’re faced with a flummoxing situation involving legacy record-level access that could be solved with a dose of SQE, then by all means, give him a call.

RELATED STORY

Townsend Brings Modern Crypto Capabilities To Legacy RPG Apps