PTC Refines Handling Of IBM i Database Logic
July 18, 2018 Alex Woodie
More organizations are moving business logic from RPG and COBOL programs into the DB2 for i database, which most community members agree is a good thing. The IBM i server’s core strength is its integrated a database, after all. However, when business logic moves into the database, it requires customers to rethink how they’re managing the code, which is a mantle that PTC has taken up with its Implementer software change management (SCM) tool.
When it comes to application modernization, fancy HTML5 screens and mobile apps usually grab the spotlight. We’re often moved by the things we can see, and going from a dingy 5250 green-screen interface to slick graphical user interface is a great way to stimulate our visual cortex. But the biggest business returns can sometimes be delivered by updating the stuff that you can’t see – the stuff that lies under the covers, like the DB2 for i database, the workhorse engine at the heart of the Power Systems server.
Customers seeking the biggest database improvement might opt to migrate from the native I/O to the SQL engine, which is the ultimate end goal in the database modernization playbook. But there are other things one can do to leverage the database, such as by incorporating database functions — such as stored procedures, built-in functions (BIFs), triggers, and constraints.
PTC, which acquired MKS way back in 2011, has already taken steps to shore up its support of database logic with Implementer. According to principal developer David Gibbs, the product already managed stored procedures and BIFs. And with the delivery of Implementer 12.2 in March, the company has taken the next step in managing business logic in the IBM i database by adding support for database triggers.
With Implementer 12.2, PTC tracks three types of triggers in DB2 for i, Gibbs told IT Jungle at COMMON‘s recent PowerUp conference in San Antonio, Texas. The first type of trigger managed by Implementer 12.2 is a source-based SQL trigger. “That’s a trigger program written in the SQL language,” Gibbs said.
Implementer 12.2 tracks two types of external triggers, including non-source SQL triggers, which is “a trigger developed with a third-party tool that we don’t know anything about and the user just attaches it to their database object,” Gibbs said.
And finally there are PF triggers, which are added using the ADDPFTRG command, the developer said. “Those are native RPG, CL, or COBOL programs that are called by the database engine when the appropriate action occurs,” Gibbs said. “We manage the association of those triggers with the file object.” PTC already supported PF triggers with Implementer, and that support was enhanced with 12.2.
From a traditional RPG or COBOL developer’s point of view, triggers, stored procedures, and BIFs aren’t typical business logic. The same methods that Implementer uses to track changes to RPG or COBOL source code by teams of programmers over the course of time do not work the same way in the database.
PTC had to figure out a different way to track the business logic that’s encapsulated within the database. For the database trigger support, Implementer “looks at system tables to get the information that we need so we can properly move things around and recreate when necessary,” Gibbs explained.
Building support for sourced-based and non-sourced-based triggers into Implementer was more difficult than Gibbs initially thought it would be. “We did our initial analysis of what we thought it was going to take, and it took a little longer than anticipated,” he said, “but we’re really pleased with our implementation. It’s going to work well.”
PTC isn’t finished with supporting database-based business logic with Implementer. “Constraints are next on the agenda,” Gibbs said. “We started with that and will end up that in 12.3.”
Other enhancements with Implementer 12.2 include certification with the latest release of Rational Developer for i (RDi), version 9.6.
The company is constantly looking at other technologies to support with Implementer. Currently, it’s evaluating whether or not to support the Git version control tracking system with Implementer. Gibbs said Git would give customers a better ability to do source-code compare and analysis of source code, but wouldn’t dramatically change how Implementer works. “It’s something we’ve been kicking around,” Gibbs said. “We haven’t put it directly on the schedule.”
If enough Implementer customers request Git support, it’s likely the company would take the steps to incorporate it. “We are very responsive to customer request and customer demands,” said PTC product manager Dawn Blohm, technical support manager for Implementer. “Certainly there’s always a small group of people who are way on the cutting edge and they want something they just heard about. But do they actually go back to their shop and implement it? Not usually right away.”
