Knocking On The Old Database Door
August 22, 2016 Dan Burger
Keeping up with technology should be systematic and efficient. Avoidance behavior will only reduce your anxiety for so long. At some point, the limitations companies have learned to live with and consider “good enough” will prevent them from keeping up with the herd. Here are three organizations that realized it was time to take on database modernization, after discovering their existing databases lacked documentation, defined relationships and normalized procedures.
The three examples are projects that involve Resolution Software, a company that specializes in database modernization services and developed software that automates much of the labor intensive work in the modernization process and manages the end result. Resolution CEO Elie Muyal will be our guide.
Project #1 took place in Switzerland. It’s a very complete database modernization project conducted by a major insurance company, with 50 software developers on staff. Rigorous procedures were already in place to move from test environments to production, which were applied to the database modernization effort.
The company was a considering a migration from DB2 to a different database, but after risk, cost, and time comparisons, a decision was made to remain on IBM i and undertake a database modernization.
“The accumulated knowledge of 30 years was in the DB2 database and it was determined that evolution in a familiar environment that has new technology to offer was a better choice than drastic change and higher risk,” Muyal told IT Jungle. “They realized that DB2 performance would be better than the performance either Oracle or SQL Server, but only if only if modern techniques such as SQL defined tables, identities, and time stamps were incorporated along with naming conventions that make sense and putting relationships in place–in other words, implementing best practices for a modern database.”
The project was done gradually in four separate steps that made the project more manageable and in four different environments-development, test, staging and production. It also involved the creation of two instances of the same database. One served as a safety net. Resolution’s Xcase software brought process automation to an otherwise labor intensive process. Xcase also was instrumental in the discovery and validating relationships phases, which were a big part of this project, as well as projects two and three.
The modernization was completed in one year, a predetermined time frame dictated by convenient release dates. Next on the agenda is addressing data integrity issues.
Project #2 was accomplished in France for a major logistics company. The starting point was a DDS-based DB2 for i database that lacked documentation and declared relationships, so the main focus of the project was establishing relationships within the database. The DDS to SQL conversion was done manually. Three months into this project the discovery and validation of relationships is in progress.
“We find most databases with no relationships and no normalization,” Muyal says while also noting the conversion from DDS to DDL, whether it’s manual or automated, can be done without recompiling applications.
Although the logistics company recognized the strategic importance of moving its legacy database to SQL, the inability to define relationships caused the project to stumble. The information was spread out over 250 people. There was no database administrator, so the database was not documented nor optimized. It was basically auto-managed, and was suffering from a lack of data integrity.
The company started the project with its own people, but it called on Resolutions to help get it finished.
Project #3 involved a large insurance company in Columbia with a complex and growing DDS database consisting of thousands of tables. The technology and methodology was antiquated. There was no documentation, no normalization, and no relationships. The company was taking a closer look because of government mandates to obfuscate sensitive credit card data from a test environment that was a replication of the production environment. In addition to the regulatory compliance issue, a secondary concern was cleaning up redundant data.
It may be stretching the definition of modernization in this instance because the files remain in DDS. There is no SQL conversion. However, previously unknown relationships were identified and data redundancies were determined and eliminated. So, technically, there was a modernization.
After two months into this project, the test database is completely obfuscated and work in on-going with obfuscation on an SQL Server database that is also under the scrutiny of government security audits.
As you can see from these examples, sometimes it takes a mandate to make people change their database ways. Sometimes it is a matter of escaping self-imposed limitations that hinder company growth. When the time for change comes, however, the surprises may not be happy ones.
One thing for certain is that IBM will continue to make the DB2 for i database the heart of the platform and that database is not the database that the majority of IBM i shops recognize. But the paradox in that is when IBM i shops do stop to take a look at their “good enough” databases, they find limitations. Yes, they are capable of doing what they’ve always done, but you can say the same thing about the Model T Ford. The Model T was a fine automobile and undeniably adequate for its time. Most of us would not choose one to depend on for our transportation today.