Off-roading, Maps, and Nine Gray Hairs

We all tend to spend most of our time on the “turnpike” of a product’s feature set: you know, the “power” set of its features that get us to many of our endpoint destinations within the product as productively as possible.  Once we’re comfortable with how we’re navigating inside the product, we tend not to take too many exits off that familiar highway. Quest’s Toad product line are full of those exit points that lead to “off-roading” experiences.  Remember: you’re reading real accounts in the Toad Turnpike series part 1, about real people doing real things.  So let’s rewind life to just over a year ago.

The Scenario.   I am on the phone speaking to Vern, a software developer lead.  Vern (not his real name) has gathered some of his team members for a “What’s New in Toad” demo session.

Not long after we start the demo, we discuss the complexity of relationships that exist between objects in Vern’s data models.  He jokes that some of their models look like a big plates of spaghetti if he were to show me their diagrams.  Our conversation at this point goes something like this…

“Vern, do you know about Toad’s ER Diagramming?  Might find it easier to get quicker insight rather than messing with the full data model…” and I make Toad generate an ER diagram that finds all tables related to a small set of objects.   Vern doesn’t speak, and I hear the team members do a discussion rumble in the background.

Presuming that they’re seeing merit in the ER diagramming feature, I continue. “If you guys like the ER diagramming, you’ll love the Code Road Maps.”   I have Toad create a Code Road Map for one of my stored database functions.  I explain that the Code Road Map conveniently pictures the procedural calling hierarchy and database object dependencies within his functions, packages and procedures.

The background rumble I hear gets noticeably louder.  Vern finally pipes up as the noisy group’s chatter dies down. 

“Code Road Maps, they’re new?”   No, Code Road mapping has been in the Oracle flavor of Toad for years. 

“Does this work with triggers too?” Vern asks.   I manage to find a Code Road Map example involving a trigger, like the one below.  Vern’s group immediately breaks out into a full roar.  It’s hard for me to tell if they are excited or agitated.  It seems that this part of Toad has hit a raw nerve of some kind.

“Is this something that you guys find valuable?” I ask loudly, trying not to sound overbearing, but wanting to cut through the background din.  “Vern?  Guys?!?”

Vern finally interjects above the discussion volume.  “Man, this could have saved our hide a few months ago!” he exclaims.  Vern didn’t wait for me to ask him to elaborate.   He continues with a tone in his voice that makes me think his experience happened only yesterday.

The Problem.    Three months prior to our call, Vern and his team faced a dilemma. An important round of application code changes and database object changes were ready to push into production.   On schedule, no less.  But the User Acceptance Test team would not give the final green light, delaying the production rollout.  The hold up?  Test transactions entered by users through the application side did not match test audit log history.  To make matters worse, this roll-out was strategic in that the company and several of its Distributor-Partners were expecting an on-time release of the system to support a new product solution line.  And they had customers waiting in the wings for these new products.   Phones started to ring.  Management banged on office doors.  VPs wanted answers.  Vern and teams were feeling the heat.

Turns out that this problem was a subtle one to troubleshoot.  Despite double- and triple-checking application and database procedural code, two anxious days passed before the team leaders identified the issue. 

The Cause.    Here’s what happened.  In the previous Production release a few months prior to this attempt, one database object got pushed into Production unawares:  a trigger.  That trigger contained logic that updates an audit log for validation purposes on the User Acceptance Test database.  So during the months that followed, production-side updates to one of the new master tables were being logged to the TEST system’s audit log, NOT TO THE PRODUCTION system’s audit log.  No one noticed until it was time to validate tests for the system’s next scheduled release, when it became clear that end-user transactions during tests were not matching logged entries in the test audit log.

Developers were not able to see the problem directly because none of the main procedural lines of code ever touch the audit log.  The audit logs in this customer's system are maintained by triggers, and triggers don’t get “called” within procedural code directly; they typically fire before or after events like update/insert/delete activity on tables.  That subtle relationship is easy to miss, and took one half-week for the team leads to realize that this was the cause creating undesired side-effects in the audit history.

Vern ventures a guess that Toad’s Code Road Map feature would have saved them two man-days of effort by quickly identifying dependencies between procedural code logic and the database objects.  But for Vern personally, the value of Toad’s ability to shorten time to problem resolution pales in comparison to the anguish Toad could have helped him avoid. 

The Anxiety.   Vern's words and tone of voice were the pigment and brush strokes that painted a dark picture of how he felt during that half-week.  Imagine that a “fire” breaks out in the building you’re in, and the building is ready to come down.  You’re throwing at it all the fire-prevention arsenal you have, but the fire still rages, and people scream at you “to do something” because you’re “the fireman”, and you get paid to put these kinds of fires out to save lives.  But who saves the fireman?  There’s screaming, and phone calls, and animated triage meetings.  The wife misses you, and the kids wonder why Daddy’s late coming home from work tired and grumpy.  There’s moments of doubt that make you wonder why you ever took this job.  And all this gut-wrenching, Tums-popping, gray hair-producing stress takes its toll. 

Vern laments that he found nine gray hairs after that particular week’s ordeal.  I think he’s joking, and chuckle at his comment.  But Vern is not chuckling with me.   Wow, ok, maybe he really was counting.  I play it safe and empathize with him, explaining that I spent the first 10 years of my career in IT.  I've been in similar situations.  I have lived through pain like that.

We manage to continue the Toad demonstration, and in time I conclude our session by encouraging Vern and his colleagues: there’s much to explore and discover within Toad’s interface.  Just take a little time and be open to do some exploring.  Do some off-roading.

The Road Less Traveled.    It's been over a year, and I've yet to reconnect with Vern and company.  But I like to think that he took me up on my advice, and that he's able to paint a far brighter scene these days.  I see a happy Toad DBA software user who has learned to let his fingers exit the main-features turnpike once in a while.  I see him free-wheeling down some scenic terrain within Toad's interface that few users make time to discover.  He's feeling the wind in his hair and loving it.  Oh, and look at his gray-hair count... still holding at nine!