Toad World Blog

Oracle OpenWorld Experiences- Part 2

Nov 17, 2017 2:50:59 PM by Gary Jerep

Real stories.  Real people.  Doing real things.  With Toad solutions.

Cliffhanger Revisited.  Recall that in my previous blog post, an IT Director came up to me in Quest’s booth at Oracle OpenWorld last month.  He asked me for intel on convincing his executive management team why they should continue paying for Toad in the face of cost-cutting mandates.  Oh, and he only has a few minutes to hear me out! 

How do you take what could easily be a 15 minute cost-justification presentation to your board of directors and condense it into a one-minute elevator pitch?   No pressure, right?   I chose to tell this gentleman the account of a customer situation in which Toad saved two calendar months on their project plan to automate QA department processes.   I only got in the one-minute version of the story before his cell phone took him out of the booth.  He didn’t get to hear the “how” portion of the way Toad saved the day.  Here’s his chance—and yours—to read the full version. 


Plot Development.   One of our mortgage lending customers asked Quest to come onsite to present a Toad Tips and Tricks workshop.   It’s something Quest Software does quite often for our customers.  The workshops are designed to help users leverage their Toad investment more effectively.  (Talk to your friendly Toad Account Manager from Quest if you’re interested in scheduling one of these!)

Through additional dialog with QA Manager “Joe” before the demo, we discovered that corporate management challenged Joe and team to embark on automating their database-centric QA efforts.  Team members were already using Toad for Oracle to conduct about “60-70%” of their QA exercises on their databases. 

What were the QA team members doing with Toad? 

  • Investigating data quality issues. 
  • Reverse-engineering Creation DDL scripts for various objects or entire schemas.
  • Confirming object attributes on Production after database change roll-outs.
  • Generating reports to document the before/after change “states” of the database (schema, config parameters)
  • Copying/sub-setting/masking Production data to create real-world data on QA databases, because Dev/Test/QA instances were only 10-30% the size of the production databases.
  • Refreshing test and development schemas and data. 
  • Generating test data for tables on the Test and Dev instances. 
  • Unit testing stored procedures by keeping a CSV file of various input value sets for each procedural object, and comparing procedural output values to expected “gold standard” tables, which were created by importing values from “gold standard output” CSV files.  

The Crime.   Joe’s team had been using Toad for about 3-4 years, but oddly enough:

  • Users didn’t know that Toad could reverse-engineer creation scripts for selected, multiple objects, not just one object at a time.
  • Only a few engineers knew about some of the Toad Editor’s productivity shortcut keys.
  • None of the users had done enough “off-roading” within Toad to discover on their own useful features like query building, or—especially for this group—Toad’s Automation Engine to automate workflows inside of Toad.

As I conduct the demonstration, Toad capabilities like the Editor shortcut keys, data sub-setting, and the Automation engine get particularly high eye-brow raises.   I’m often tempted to think that it’s a crime when users don’t discover these productivity-enhancing features in our product.  After the session, Joe and team are buzzing about their “opportunities to work smarter” as we leave their office that day.   It’s not surprising, then, that Joe asked us back to do another tips and tricks session about six months later.

The Suspense is Killing Me.   On our second visit, after the demo, Joe walks the Account Manager and me back to his office for a “chat”.   Joe explains that Toad has been very instrumental in helping them to automate their QA processes.  He even points out a detailed, poster-sized project plan taped on one of his walls.  It looks daunting.  I had forgotten that Joe mentioned the automation project at our first visit, so I ask how progress is going on the plan.

“We’re done!”  Joe laughs out loud, and spontaneously claps his hands together, as if he’s been dying for us to ask.  The clap sounds like a bottle uncorking. “We actually finished the project two months ahead of plan, thanks to Toad.  Can’t remember when we finished any project ahead of schedule!   Got me a promo last month.”    Yes, here before my eyes is a human version of a champagne bottle uncorked, with happiness flowing freely in all its bubbly effervescence.  It’s so beautiful to see customers in euphoric states like this.  We congratulate Joe on his promotion, and I mention how super cool it is to hear that a product like Toad can make a difference professionally, and personally.   

Joe’s euphoria tempts me to feel the need to be recognized for part of Joe’s success.  Why not?  After all, I did the demo and set the stage for success.  So I slather my next comment with prideful smugness as freely as I would dollop mayonnaise on a rewarding ham sandwich after a month-long diet.

“Joe, I knew that Toad’s automation would be a game changer. You found some uber-productivity gains there, I’m sure.”  It’s an invitation for Joe to elaborate.

 “Oh, we are using Toad automation,” Joe explains, “but it’s Toad’s transparency that saved us months of work.” 

At this point, I’m still trying to hold onto my pride sandwich, but Joe’s statement has effectively scraped off the mayonnaise of smugness that made my mouth water.   I was so sure I knew why Toad made Joe and team successful.   Now my wheels are turning as I search for meaning and context in what Joe said.  “Transparency, Joe?”  Transparency... Transparency...

“You know, the spooling feature.”

 

Figure 1.   Toad’s spooling feature pipes to a file (or to a Toad Output panel, as shown here) the SQL code that Toad generates on users’ behalf as they perform various tasks within Toad’s graphical interface.  Note the date-time stamp of the very first SQL statement and compare with the next Figure.

 

 

Figure 2.   Spooled SQL output after working in the Toad interface for several minutes, and generating tens of thousands of lines of code.  The transparency of Toad’s communication with the database points to volumes of productivity behind the scenes.

The Climax.    Joe provides the context.  His group had initially attempted to create their QA scripts from scratch.  But progress came slowly.  For one, the team did not have sufficient database or SQL language expertise to script out advanced tasks on their databases.  In addition, team members were spending longer hours in the week to make up for slow progress because, after a month, they were falling behind in meeting target dates for planned milestones.   The team started peppering the DBA group with how-to questions, but too much pepper was making diplomatic relations between the groups “unsavory”.  There was serious talk of hiring a consultant to do the hard stuff.  Yes, they were using Toad’s Automation Engine to assist with some processes, but not everything they did within Toad translated to pre-defined Toad automation tasks.

It was after our very first demo that Joe and team got the idea to put Toad’s spooling feature to a test.  They would continue using Toad as they normally would to conduct their QA efforts, with one small difference:  Users toggled the spooling mode on.  During each QA engineer’s Toad session, then, Toad would be spooling in the background all SQL it was manufacturing on the user’s behalf to a file.  The spooling of Toad’s own code was the “transparency” that Joe was referring to.  With some diligence in tweaking the code, the group packaged those files into SQL and shell scripts that are currently scheduled to run automatically on the customer’s (UNIX) database servers.  Some scripts are executed in parallel and have the effect of “virtualizing” the team’s overall efforts.   Joe estimates that at certain times, work is being kicked off in parallel that would rival the task output of five teams the same size as his.

 

Reflection.   It’s easy to stop here, give Toad a thumbs up, and move on to your next blog.  But reflect just 10 more seconds and you’ll see a bigger picture of success here.   One simple Toad feature had such profound effects on Joe, his team, and his company.  Consider:  

  • A project completes two months ahead of schedule. 
  • Team members get back to more reasonable work hours, while fitting in more productive work per week.
  • QA engineers increase their knowledge about SQL and Oracle’s inner workings.  Think personal development.
  • DBAs are now friendly allies again.
  • The company avoids the unplanned cost of a database/training consultant.
  • A team manager gets promoted. 

Toad has many more such productivity features that can translate to these same big-picture effects.  If you’re a Toad user, I encourage you to deviate from your normal navigational paths within the product—do some off-roading—and discover some new scenery.  You’re going to like what you see.

If you’re not a Toad user, well…

 

Tags: Toad for Oracle

Gary Jerep

Written by Gary Jerep

As a Software Consultant within Quest, Gary Jerep has over 20 years of experience assisting DBAs, Developers and Analysts with relational database needs. Prior experience includes systems design and analysis, operations research, Business Intelligence, and end-user experience monitoring.