Database Modernization: Methodology To Solve Problems
November 6, 2017 Dan Burger
Innovation is a combination of tools and processes. Big mistakes get made when there is too much emphasis on the tools and too little on the processes. Innovation in a can, a bottle or a box isn’t reality-based strategy, although it’s often thought of that way, particularly at the executive level where the goal of competitive advantage is sometimes tied to specific products and technologies.
Tools and processes support innovation. They aren’t the innovation. Not at the individual business level where competitive advantage is differentiated and honed with years of experience. Too often experience is discounted or even disregarded when executives discuss innovation. Let’s look at database innovation as an example.
Chris Koppe has been at the table with business executives and technical gurus discussing database pros and cons on dozens of occasions. His experiences are magnetized to organizations that rely on the Db2 database running on IBM i. Last week, in an interview with IT Jungle, Koppe shared his insights based on several innovative database projects involving substantial improvements to an underlying database.
“There are a lot of good reasons to have modern database structure and it doesn’t have to be about moving off the i,” Koppe says. “I advocate to everyone to look at the challenges that their database is imposing on the company, the applications, and the ecosystem. Determine how to mediate that in the short- and long-term. These guys [the IBM i shops Koppe works with] are looking at the next 15, 20, or 30 years of their applications and making significant investments now that set them up for the future. Not everyone does that.”
Mergers and acquisitions are everyday occurrences in the world of corporate finance, but merging the IT systems often results in integration nightmares. Koppe explains how one company, with five different databases resulting from acquisitions, came to grips with the realization that the integration problems would only get worse (more acquisitions were likely) if something wasn’t done sooner rather than later. The short list of those problems included reporting, data collection and programming.
Fixing the underlying architectural problems in each of the databases – all were built 15 to 20 years ago – was the first step. Consolidation would follow. As they looked at their future database they asked themselves: How do we want this designed and what problems did we create historically that we want to fix?
In the repair process, all the DDS-based files were changed to DDL SQL. Taking that on as a manual process would be a long and costly slog made even worse by the almost inevitable discovery of bad data. In this case, an automated conversion tool, capable of finding bad data and making repairs, was used. Compared to hand-coding, the conversion tool was a huge time-saver.
“There is a workflow that happens,” Koppe says. “Get the database and load it. Then find errors and problems – things like multi-member tables, multi-format tables, and select/omit challenges – that don’t map cleanly to a DDL database. You look for functional problems with the existing database, but you also look for where the organization is feeling pain. The database team establishes the methodology to solve problems.”
Koppe’s experience is valuable when it comes to methodology, design and implementations. One of the lessons he learned from the database consolidation project is that people often rely on “tribal knowledge” for understanding their database. It’s often defended but less often correct.
“How your database exists in your mind is not how it really is,” he says. “Databases built over time have specific customizations. People store dates in many fun and interesting ways – numeric storage, character storage, separated by year, month, and day.”
Storing dates is just one example. The IT staff at a company Koppe worked with thought there were six different patterns for storing dates in one of the databases. It was later discovered there were 23.
It’s the type of thing that makes database consolidations a tricky proposition without automated tools that can sort through this clutter.
In another instance, Koppe was involved with a company that tried for several years to migrate its home-grown ERP applications and Db2 for i database to SAP and Oracle. It didn’t go well. (We’ve heard that before.) Ultimately, the strategy reverted to staying on IBM i and modernizing the home-grown ERP, however, it was determined the database was holding back the evolution of the applications and the business in general. So, modernizing the database (DDS to DDL conversion) became the priority.
“There are companies with exit strategies that are looking to move applications to SAP and to another relational database,” Koppe acknowledges. “But before making that decision, they should ask themselves why they want to do that. What’s wrong? They need to explain why the platform doesn’t fit the needs of the business.”
Valid reasons for migrations do exist. Some companies are oriented toward Oracle or SQL Server and the IBM i is the odd duck in the IT strategy. By the same token, IBM i and Db2 for i are fully capable of innovations not associated with the platform.
There are IT strategists who believe relational databases are not the best option for the analytics-oriented future, but Koppe doesn’t think companies will get away from using relational databases. However, he doesn’t consider DDS-based Db2 on the i a relational database.
“It’s not that relational databases are not the future,” he contends. “It’s what they have today in DDS that’s not the future. The problem is 95 percent of organizations using Bd2 for i are using DDS. When you look at it at the structural level, it is a bunch of independent files that have few structures and are not connected to each other. There is no relational information defined in the database. It is defined in the application code. Therefore, any other tool or code that want to access the database has no way of discovering the relationships that exist within the database. And I don’t know of any IBM i shops that document their data models.
“Not only do they not have a discoverable database model, they don’t have a documented one either. So, they have a collection of independent files with short, cryptic names limited to ten characters and are relatively nondescript. No one outside of the IT department can understand the database. No one on the business side can understand it. That’s not the definition of a modern relational database.”
Database modernization can be easily dismissed if it’s viewed as simply an exercise of converting from DDS to DDL. That alone does not provide enough value to warrant funding. For a database modernization project to add value it must include application and systems modernization with a measurable and quantifiable plan for improving the business. Discovering a business pain and alleviating it is a plan worth funding.
Database modernization is application modernization. And application modernization cannot be done without fixing the database.