Knee Deep In Database Modernization

May 13, 2013 Dan Burger

Twenty years ago, the DB2/400 database from IBM was not being asked to do the things that the DB2 for i database for the IBM i operating system is being asked to do today. Database limitations sometimes sneak up on us. Even though the handwriting has been on the wall for some time, there has to be a reason to stop and read it. Jim Ritchhart read it. He knows database modernization because he is living it. His story of database modernization is a good one.

Ritchhart works for Uline, a shipping supplies and packaging materials manufacturer with headquarters near Kenosha, Wisconsin. The business depends on the IBM i operating system and its integrated database as orders are placed through the Internet, inserted into SQL Server tables, and then replicated to IBM i. Non-Web orders are input through a Java-based order entry system. In both instances, the orders hit the i almost instantaneously and the system handles the business computing from that point on.

As Uline’s business increased, more pressure was applied to the I/O and the old routines began to run more slowly. When system performance becomes noticeable to end users, the complaints begin to mount. To improve performance, many companies compensate with bigger, more powerful hardware. Performance keeps people happy, but it’s not the only business benefit being sought. At the database level, SQL is known to deliver better I/O performance, but equally important is the part it plays in allowing IT to respond to business needs faster. In the case of Uline, company executives were frustrated because IT requests related to programs that needed additional information and were getting bogged down by lengthy deployment times.

A good example was a request that dictated changing the length of a single column. It doesn’t sound difficult until you understand that one column is duplicated across 106 tables and 1,500 programs are affected. That alone didn’t mean much to a non-technical executive committee. What hit home was the amount of time estimated to complete this project: 2,200 days.

Business Value in the Proposal

In the proposal to modernize the database, reducing the number of days to complete projects became a key element. If database modernization could bring about faster responsiveness to departmental requests for application changes and the performance of applications would be a substantial improvement, the modernization effort would likely get executive buy-in. The project was presented as modernization on a fairly large scale, but it emphasized that it could be completed without changing, modifying, or recompiling programs.

Naturally, skepticism was abundant. Nonetheless, approval to start proof of concepts was granted.

Ritchhart was pretty much a one-man data modernization team and he learned the DDS to DDL conversion process as the project advanced through the conceptual stages. Early on the value of modernization tools was realized and several ISV tools were tested. Without a tool, the DDS to DDL conversion is too much hand work. A small-scale project–like a proof of concept–can be done by hand, but a large-scale project cannot be done by a one-person shop without some automation. In the end, Uline chose to have Ritchhart build a tool rather than purchase one.

“I knew from experimenting and reading white papers and Redbooks what I needed,” he says. And I wrote my own tool in a few weeks that could get 95 percent of the conversion completed.”

It was important to Ritchhart when writing his tool that it follow IBM methodology so it had the capability of adding columns on the fly, which he considers very important to database flexibility.

Because the tool would be built in-house, it would be customized for Uline and the company would have the source code. It would also be integrated with Uline’s existing change management tool called Implementer, which was purchased from the third-party vendor MKS before MKS was purchased in April 2011 by PTC. This integration would allow the automatic promotion and swapping of the old code out of production and put the new code into production.

Getting new code into production is a conundrum for companies like Uline that run 24×7. The system has to be down to copy data during the database modernization. And no one likes the system to be down.

“I can quickly modernize 3,000 to 4,000 objects, but I can’t get them into production,” Ritchhart says. “I need the system down to copy data over. Currently we are doing it a few hundred objects at a time, because we can’t take the system down longer than that to implement.”

As of last week, around 1,100 objects out of a total of approximately 3,000 have been converted from DDS to DDL. These are the objects that require conversion. Ritchhart estimates there are 9,000 objects total, but many are work files and report files that do not require modernization. Recognizing what needs modernization and what doesn’t saves a great deal of development time. So does the change management integration. And the conversion tool is essential.

“We had one issue where we wanted to expand our column lengths. It would have been a huge undertaking to change all programs,” Ritchhart said. “We decided to modernize the tables and expand the columns on the base tables while leaving the logical files the shorter length. So instead of hitting hundreds of programs, we were hitting five or six by only changing programs that needed the extended length. It saved a huge amount of time compared to changing every program that touches the file. We were also able to implement the database changes weeks in advance of the program changes. This flexibility is only possible because of our database modernization efforts.”

IBM chose SQL as its stated direction for database enhancements years ago. That was the handwriting on the wall. Its DB2 for i Center of Excellence conservatively estimates database performance gains at 15 percent when modernizing. Ritchhart says Uline is seeing that on the low end, but also is getting 5X on the high end.

“Data access has a lot to do with better performance,” he says. “If you are doing traditional I/O to a file and no buffering, performance gains will be on the low-end side. If doing traditional I/O, let’s say buffered reads in a loop, the index reads are faster and it’s reading more data into memory, SQL objects buffer at eight times more data into memory. So if you are reading into a loop and buffering, you’ll get significantly more performance compared to a regular read with no buffering. We did before and after looks on stored procedures. The performance gains were huge and that’s without touching the programs. Once we start using SQL objects and performance tuning techniques, we’ll get even better performance.”

App Dev Push Back

An important aspect of database modernization is allowing the database to do more work so the application developers don’t have to. Much of this boils down to taking care of repeatable processes, which increases efficiency. This is one of the business values that needs to be emphasized when making presentations to the executives. It also needs to be understood by the RPG application development team because the developers will have to change some of the ways they are used to doing things.

At Uline, a database committee was set up and it meets once a week to review the requests for changes to database structures, indexes, and tables.

“The modernization did not affect the developers that much until we started imposing some standards,” Ritchhart said. “There’s a series of best practice standards that have to be followed because when you code embedded SQL into programs, if you don’t follow best practices, you lose the benefit of flexibility. One of the best practices in place is not allowing SQL objects (tables, indexes, or views) on “F” specs. This avoids the limitations of level checks and keeps the flexibility of adding columns to tables without touching existing programs.

“This starts at the management level. They have to enforce the standards. Once the advantages are seen, it is understood why the changes had to be made. It helps to understand why something is made a rule.”

RPG development is prominent at Uline, but there are also Java and .NET developers. Java and .NET developers have always been able to take advantage of SQL and DDL and the database administrators there can add columns on the fly without affecting the Java or .NET developers.

Ritchhart came to my attention when I noticed he was doing sessions on database modernization at the annual technical conference hosted by the Wisconsin Midrange Computer Professionals Association last month. He was “pleasantly surprised” by the level of interest in the topic. Since then, he has also made a presentation at the Omni User Group in Chicago and attendance was higher than usual there. He has four more speaking engagements lined up, including one at a CIO executive conference.

“Most of people I talk with are thinking about database modernization,” Ritchhart says. “No one is in the deep weeds like we are.”

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search