How to Approach IBM i Database Field Resizing
Robert Arce is the foremost field resizing expert at Fresche Solutions. When he came to Fresche he immediately adopted X‑Resize, Fresche’s solution to help automate resizing projects. He truly believes in the efficacy of the product and its usefulness in any field resizing effort for IBM i shops.
The discussions that Fresche regularly has with clients and potential clients revolve around their need to expand fields in their existing IBM i applications and databases: they are very interested in ways to reduce the time, potential human error, and manual effort required to complete such a complicated task.
Q: Tell us a bit about yourself and your role at Fresche.
A: I am part of the global team of IT Strategists – more specifically, a group of six people selected to provide discovery services to clients that are interested in undertaking a modernization project. For the last three years, I also held a role in the Client Solutions Advisory team.
I joined Fresche because of my experience with the IBM i system. I was the owner of a company in this particular domain, and I also worked for a big consulting firm from 1997 to 2000. I’m considered a Subject Matter Expert (SME) when it comes to IBM i, anything RPG, and anything SQL. As for databases, my experience is not only on IBM i, but also on other systems, such as Microsoft SQL Server. I used to be a Db2 expert, doing modernizations on those as well.
Back in 1998/1999, I managed five different Y2K compliance projects. One of those projects was a huge date field resizing effort for an insurance and financial company, where I had the opportunity to manage 11 people for that project alone. It touched all the company’s core systems. Three were medium-sized projects, and the last one was small in scope.
From customer numbers to product ID and location fields
Q: Field resizing seems to be a big topic for companies. Can you share what you are hearing in the market?
A: That’s an important question. Many companies created their applications 30 or more years ago, with up to five or six characters for their client ID or product ID – limiting themselves to hundreds or thousands of IDs. This setup can work for a very long time. However, as we have seen with our own resizing efforts, it can soon become a problem, especially with mergers and acquisitions. I have worked with a client who acquired new companies 10 to 12 times a year, of which 2 or 3 would be of a significant size with a product list quickly reaching millions… Nobody had thought of this sort of situation when they designed the original system.
Another thing is currency fields – a problem that often comes with internationalization. For example, a company wishes to do business in Mexico using pesos and has to consider how many pesos are needed for a dollar. The system’s limitation of a few thousand dollars for an item turns into millions of pesos. When one does the summarization for the invoice details, they may find that the field for the total amount is insufficient.
Regular company growth can cause the same type of issue, since many companies not only grow by acquisition, but they may grow because they are doing very well in business.
There is also the issue of location (new office, stores, etc.). If a company has been doing really well, they may increase from a few locations to many, perhaps upwards of 100. At that point, they may have almost entirely consumed the location field that is limited to two characters.
The customer number, product ID and locations are the main reasons companies initiate a field resizing project.
Q: What are some of the most common challenges of field resizing?
A: This ties in very well with the previous question. When you think about it, if we had an application written perfectly, many of these projects would be as simple as just changing the database and recompiling some of the programs… and done! Without criticizing anybody, part of the reason these projects are hard is because the code is not always written in a way that makes a correct reference to fields coming from the tables. Often, the true definition of where the data comes from is not used.
A good example is, if I had a customer number, and every definition that relates to a customer number came from a data dictionary or the customer master, it’s as simple as changing those tables to the new definition that you want and recompiling everything.
What is the difficulty? The difficulty is that many things are duplicated, or else the definition is not necessarily located in one particular source and may be spread around their application. Of course, a domino effect occurs when one changes a table: that field is going to impact many things in the programs, because these programs have associated work fields that have been redefined. Those work fields associate with other variables and fields and many times a program calls another program passing as a parameter one or several of these fields or variables. We have seen things that are almost funny in the way they name parms… I have even seen somebody naming them “parm1”, “parm2”, “parm3”, etc.! So, let’s say the client has to analyze this program that is doing a call and the parm is called “parm1”. Just imagine somebody who is trying to figure this out just by doing source scanning, which is what many people do when you don’t have a tool like X-Analysis. That makes a difficult task to find where a particular field is located in a table, which is the starting point of a field expansion project.
The absence of a naming convention of the files and of the parms can make it a challenge to find where they are used across the application, and this is a reason why things get missed if one doesn’t use the right tools.
All of these factors make the projects more complex than they should be, and the sad part of it is that many of our clients experience one or more of these situations.
Q: Is there a reason why people put off their resizing project?
A: These projects can be huge and risky, since they touch a lot of things that they do not want to recompile. Again, they may have started the project, and assume “we will be done a year from now.” Then, three months into the project, they still think they will be completed “a year from now.” Also, many organizations wait two or three years after finding out that they need to do a field resize project. Of course, many times these businesses have other more visible priorities to address first.
For instance, one of our clients wanted to do loans of over 30 years, but had a problem with the way they handled years (going back to the Y2K issue). In this case, they used a technique called “windowing” years to go around the problem, as was commonly done in the past. This solution enables the system to guess the century of two-digit years by comparing the date to the current year. Here is an example: we are in 2019, so if the year is 30 or 39, we will know it means 2030 or 2039. If the year is 70, then the assumption will be 1970, depending on the decided window for each century. Because of today’s needs, the organization thought they would need some lines of business where the loans would last up to 50 years. That’s a clear example of something that is affecting the business, and to be successful with this, the field expansion must be done correctly.
Not everybody has this kind of pressure, some people think they still have a year, or two, or three when they come to us.
Q: What are the different approaches and strategies that companies can take? And how can X-Resize play a role? How are the resizing efforts prioritized?
A: Many of our clients have one or more fields that are preventing them from releasing a new product, which is always top priority. There are also other fields that can be annoying, and they have come up with ways around the sizing issue. For instance, when it comes down to customer numbers, many times they have recycled the numbers? What are the problems with that? Well, a problem is that you could be showing a long history for a customer that is brand new, due to the fact that you are recycling a number. You could be showing the history of a customer who has been inactive for five years, which allowed you to decide to recycle their number, and then put a new customer in the shell of the old one. That’s just mentioning one trick that people used in the past…
Q: Why are companies looking at an automated approach versus today’s typical approach to a field resizing project?
A: The first thing is, in any of these field resizing projects, one of the most important issues is scoping. How do you define your project’s scope?
The first step that you need to take is establishing the tables where the fields to be expanded are used. Once you have done that, you need to reference all programs, objects, and even queries across which these tables are used. In some cases, this will be simple – if you are fortunate enough to still have the original writers of the application around. Their input will be very valuable since this is about knowledge. The reality is that we often don’t have that kind of luxury. Often, the current staff has been working with the IBM i application for very little time. The expertise that the staff has in the company is going to be a very important factor that will determine how much time will be needed to even identify what tables are affected.
Let me give you an example of one client’s database field resizing project: They estimated that just the effort of identifying the inventory of everything that will be in scope would take them around eight months. Think about that: It would take eight months for people just to find the tables, programs, queries, etc., in the inventory so that the scope can be defined. With X-Resize, we were able to do that in a few days – not months.
An automated approach helps you with consistency of changes
Let’s say that you have your scope, and that there are two developers, so each will take on 300 objects to change. What will happen with all these changes? One person may do it one way, and the other will do it another way, and often these people will change how they do it over the course of the project. This problem of inconsistency should be avoided from the very beginning.
Another problem is that there are pieces of code that are duplicated throughout the entire IBM i application, that perhaps can be found many hundreds of times. So even one small mistake at one point, will become inconsistent with the rest of the corrections. These inconsistencies many times result in adding new bugs into the application, leads into the topic of risk mitigation. The consistency of the automated field resizing tool mitigates the risk, the impact of such human error, which can be quite hard to handle.
One of the last parts of the project will be testing, and what happens when the project touches the entire application? A company should be doing integrated testing, but how often do they actually do this? They may not have the tools or the expertise. The project was probably risky to begin with, and if there were time constraints related to it, the final testing is tempting to be cut. This is not a wise decision.
One of the things I love about X-Resize is that it allows us to start testing as soon as possible, instead of waiting until everybody is done. This feature is great because you can double-check things that you would normally find only when it is too late in your project.
When we execute a project, we include data testing as part of the project’s package.
Q: Having helped clients, what are some lessons learned that you can share?
A: First, having visibility to everything that will need to be modified is super important. Many times, we might think that an area does not need to be touched, but the X-Resize reports can identify these areas with much more confidence than when we try to do the inventory and scoping by hand.
Sometimes the X-Resize application cannot handle a particular case in a field resizing project since the code is a bit different. This generally isn’t a big issue, because sometimes it’s easier to make the modification manually than to wait until the application does everything. In other words, in many cases we will be successful at converting everything automatically, but sometimes it will not be possible because of how the code was originally written.
In the end it’s important to know what objects need to be changed, and if the number of manual changes is significant, we can go back to the lab and find a way to automate those as well, instead of converting them manually.
The key benefits of our approach to field resizing are:
• Shorter time frame to complete the project
• Better coverage
• Less risk
• Fewer people required
• Fewer human, manual errors
• Facilitated testing
We invite you to view our field resizing testimonial video with Chris Nickchen, former IT Manager at New Penn to learn much more on this topic.