Flat-file style databases were designed to fit the constraints of the systems they were running on. You specify your schema in terms of "how many characters in a file we use to store this data", meaning something like this: JOHN    SMITH    12343rd    StAnytown PA12345 is read in my knowing that the first name field is 8 characters wide, the last name field is 8 characters wide, the street number is 4 digits, and so on.

It's also a terrible schema, and woe to anyone with a long name. But many a mainframe had a similar schema.

Now, let's think about maintenance here. What happens when we also want to store a middle initial? We've created for ourselves a problem. Somehow, I have to insert a character into every row, which basically means making a new table with a new schema, copying every record out of it and updating it to use the new schema. I can't just ALTER TABLE like an RDBMS. And worse, every piece of software that touches the table also needs to be updated. On a large legacy system, a simple task like "add a field to our database" could take weeks of developer time, and depending on the software, be a high risk operation.

Which is why the smart developer, when working with flat files, includes padding. Maybe my schema for an address record looks more like this: JOHN    SMITH    12343rd     StAnytown PA12345                . That's 16 characters of padding at the end of the file. Now somebody says that I need to store a middle initial, I can just shrink the padding by one and add a middle initial field, like so: JOHN    SMITH    12343rd     StAnytown PA12345Q               

Is this elegant? No. But it works. I haven't changed the length of the row at all, so I don't need to move data around. Software modules only need to be updated if they care about what's in the middle initial field; if they're out of date, they just think there's a "Q" in the padding, and don't care.

In real-world applications, instead of putting all the padding at the end, you'd usually put the padding in a few spots in the middle of the table. Any time you need a new column, you just steal a few characters from padding. Sure, someday you'll run out of padding, or at least out of padding blocks big enough for your new field, and then you'll have to do the hard work of shuffling data around. But in practice, you can get very far without that happening.

Which brings us to Brenda's adventure. Her team supports an IBM mainframe storing data in VSAM flat files. In other words, they've been doing the sort of thing I just talked about for many, many years.

Of course, in the modern era, you can't just leave your data sitting in an mainframe. Even if the mainframe is the source of truth, you want to be able to report on it and connect it with your other data systems. You need to, somehow, get the data into a modern RDBMS.

So the company hired a bunch of developers to write an extract-transform-load process, which pulls the data out of the mainframe. The mainframe team handed them a "copybook" for the flat file, which described the structure, and the ETL devs went to work.

And maybe those ETL devs didn't understand the importance of padding. Maybe they just missed the padding. Whatever it was, there were several places where the data was structured like SOME_USEFUL_FIELD PADDING PADDING PADDING SOME_OTHER_FIELD, and they opted to split it like so: SOME_USEFUL_FIELD PADDING PAD, DING PADDING SOME_OTHER_FIELD.

When they released this process, it was fine. The padding characters got stripped before displaying, so the users never saw them. They were stored in the database, though, so when someone tried to reconstruct the data in a way that was compatible with the flat files, you could just concatenate the columns together and get a valid result.

It was fine- until it wasn't. The ETL devs, bless their hearts, only tested against the production mainframe. And why not, they were doing read only operations, what's the harm? Had they tested against the development mainframe, they would have seen new features in flight, features which consumed some of that padding, and realized that they should have paid closer attention to the copybook.

But instead, the test cases all passed. The software was, as far as the project managers and ETL developers could tell, working perfectly. So it was accepted, released to production, and running for a few weeks before the mainframe released its features. Those features then ruined all the beautiful reports with extraneous data.

And since the ETL devs were on contract, any request to have them rework it under the original contract was met with a stern "Works as designed". Instead of paying the contractors to come back and rework the system, the mainframe devs instead were tasked with finding different padding fields they could use, padding fields which wouldn't end up ruining any reports management liked to see.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!