Classic WTF: The Mainframe Database
As our summer break continues, we tend to get nostalgic for summers past, for the way things used to be. Like they were back in the mainframe days. Wait, what? No, not that. Anything but that. Original --Remy
George, an independent contractor, usually spent his first day home from a business trip plowing through the emails that'd piled up in his absence. In the midst of this grind, he received a call from Lucinda, a Microsoft contact from South Africa.
"We have a small banking client with a tricky performance problem after migrating to SQL Server," she explained. "Apparently, it's so bad that they're about to ditch us and revert to their original COBOL system."
"Oh, wow," George said, swiveling away from his keyboard. "Have you sent any techs over to look at it?"
"Yes. They couldn't find anything wrong." An odd little tone in Lucinda's voice indicated this wasn't the full story. "You're the best database performance-tuner we know. Could you call their IT manager and see what you can do?"
George was a little suspicious. Nevertheless, he told her, "Sure. I enjoy a challenge."
Lucinda gladly provided the contact details. From there, George phoned the client to see if he could indeed be of help.
"We spent a year implementing the system in SQL and C++." The client's IT manager pronounced spent as though he'd wanted to say wasted instead. "We were promised it'd be better, but it's even slower than our current system!"
"Was that implementation done in-house?" George asked.
"Yes-by Dawie, our top COBOL guru. He's the only one who deeply understands the business."
Well, a COBOL guy couldn't be expected to be an expert on SQL or relational databases. To George, this was starting to sound like a slam dunk. Swoop in like a comic book superhero, implement some indexing, head home a champion.
"OK," George said, "I'd like to do an onsite visit to see the problem. From there, I'll see what recommendations I can come up with. Just keep in mind: with performance issues, it's hard to make any guarantees."
"I understand, really," the IT manager said. "Anything you do is appreciated! We're at our wits' end here."
Despite his standard CYA disclaimer, George felt pretty confident in the weeks leading up to his visit to the client site. At the appointed day and time, the IT manager and Dawie met him in the lobby, and from there it was only a short walk to Dawie's cube.
With apologies, the IT manager departed for a meeting.
"Have a seat," Dawie offered George. "I'll walk you through our system."
The newly implemented system allowed specially authorized users to access and maintain customer data. There was no doubt about it, the performance left something to be desired. Queries returning fewer than 100,000 records each took more than a minute to process, making the simplest of tasks an exercise in Zen patience.
"Could we look at the database schema?" George asked.
"Sure." Dawie opened the management studio, drilled down to the database, then expanded the table list.
Only one table appeared, named RECORDS.
George frowned in confusion. "Are you logged into SQL Server properly? Maybe we're viewing this as a user with limited permissions, and this user can't see all the tables."
The question confused Dawie in return. "'All the tables?' This is everything. Here, look."
Dawie brought up the query window and did a SELECT * on the table. This brought up 60 rows of 2 fields: an auto-numbered ID column, and a Data column containing what looked like massive lines of text.
"Can we look at your code?" George sputtered.
Upon switching to Visual Studio, the horror became all too clear. Dawie the COBOL guru had hit upon the idea to store each COBOL data file from the old system as text inside a SQL Server table. To retrieve a customer record, his code queried the RECORDS table, extracted the appropriate text value to memory, saved it to disk, then used file I/O to scan through and find the appropriate record. If customers were added or edited, the code wrote the updates into the text, then packed it back into the SQL record.
Seriously? George struggled not to gape. "Did you ever receive any SQL training or anything?"
"I got some documentation that I breezed through." Dawie glanced around as though to find said manuals, which were thoroughly lost amid the decades of clutter upon his desk. He shrugged. "Honestly, when you've been around as long as I have, these systems all start to look the same."
"Well." George tugged at his collar. "It certainly looks like you've done all you could. I'm sorry, but I don't think I'll be able to help out after all."
Upon returning to his hotel, George called Lucinda, explaining the situation. "I can't help them. I'm not sure who can. You may just want to accept the account as lost."
"Yeah. That's what our technical team said," Lucinda replied. "They didn't have the courage to tell the client, either."
[Advertisement] ProGet's got you covered with security and access controls on your NuGet feeds. Learn more.