Nov 7, 2019 7:57:00 AM by Gary Jerep
So you’re using Quest Software’s Toad product. Congratulations. We know you’ve discovered some very powerful features in our product. And you’re happy. But you know there’s more. Every customer I speak to—every single one—realizes that there’s more to gain from the product. But status quo prevails: you’re happy with what you know, and you simply don’t have time to explore other facets of the product.
So, here’s a new blog series to give you a faster path to “hidden” gems.
If the graphical interface of Toad is a landscape to be explored, then imagine that you and I—users of Toad—are archaeologists. So Toad is our dig site. The Toad landscape is strewn with valuable artifacts. Some are clearly seen, and you’ve found them. Others are harder to find, and may require some digging to uncover. But one thing’s for certain: valuable gems exist. Some are powerful to the touch, some are mesmerizing, and some give its holder limitless fame and fortune. (Did I just hear someone chuckle in disbelief? For some of you, this is real stuff. See Bethany’s story for a real-world account.)
Now take, for instance, the “chevron” tool bar button. The icon looks like this: . It appears unobtrusively in the lower right corner of result set data grids in the Editor or Query Builder of the Toad flavors for SQL Server, DB2, SAP and Toad Data Point. Clicking on this icon reveals a “hidden door” that displays the SQL statement that created the data grid result set for that specific tab. And that door isn’t open by default.
A few years ago, one of our Banking customers on the U.S. east coast invited us onsite for a Toad (Data Point) Tips and Tricks session. During a break, one of the data analysts comes up to me—let’s call him Ryan—wondering if Toad can help him with his dilemma. Ryan is a veteran user of Toad (10+ years). He loves that Toad makes it super easy to create queries and mine through data. But as happens often, it may take more than one query to get the desired end result. Ryan’s scenario is one we likely all have lived through:
“Oh, I forgot to specify additional columns in the query. Let’s run that again.”
“Ooh, and one of the extra columns exists in another table…gotta join it to the others.”
“Ah, and I need re-cast this column’s datatype for the join to work properly.”
“OK, getting close. Now, just need to run the same base query with different WHERE clause filters.”
“Home stretch...just one last time to introduce the aggregation the CFO wanted.”
Before you know it, your initial query has evolved, and you have multiple sets of data tabs in the Result Sets section of the Editor to show the data’s footprint at each evolutionary step. But now, today, Ryan wishes that Toad can show him the “fossil record”, that is, the specific query that created each stage of his data. Toad doesn’t do this.
Or so Ryan thought.
When I draw Ryan’s attention to the “chevron” icon that unlocks the query fossil record for my set of data tabs, he beams. “Oooh, this is good!” Ryan is clearly excited. Ryan volunteers why. His company archives certain data sets every week/month/quarter for regulatory purposes. They do this by saving Toad Editor files (.TEF) or Query Builder files (.TSM). Other Toad users with archive access privileges can open these files and can see both the query and the data it produced. Users can even re-execute the queries to yield up-to-date results.
The same fundamental query in most of those files gets used, albeit with different WHERE clause filters. But Ryan and team are saving each of these query variants in separate files, because it’s important legally for team members to see both the SQL statement and its data. There are many hundreds of such queries.
Not so convenient. A nightmare to manage.
As if that's not enough, there are static “stock” queries. These hardened queries don’t change, but the company still needs to keep a record of what the result sets look like from run to run. Ryan realizes that one Toad Editor file or one Query Builder file can now conveniently package everything: a record of the query/queries, and multiple data tabs representing the data change history AND query change history, in one convenient file.
It’s time to resume our demo session, and as I watch Ryan take his seat, I can see that wheels are still turning in his head. Suddenly Ryan gestures with his hand toward me and exclaims “Last ask!”. Ryan expresses his wish that we should add to Toad Data Point the ability to identify which portions of data change from execution to execution.
I tilt my head and smile broadly at Ryan without saying a word.
“Oh yes!” (I try not to be too smug, but, you know, sometimes it’s difficult to hold back.)
I show Ryan—and now a roomful of attendees back from their break—how to quickly right-click on any of the grids, and perform a data compare with data in any other grid within Toad. “So, it’s very easy for you guys to compare, say, this month’s data with last month’s and confirm what’s changed.”
Literally, that sixty seconds of show-and-tell sparked a 20-minute conversation in the room about how the internal teams could possibly revamp their data archival management. I thought much later how super rewarding it is to witness how a solution like Toad can allow companies to evolve. In this case, all because of one icon that no one had the time to discover.
Quest Software offers database management tools that help you simplify complexity, reduce cost and risk, and drive performance. Maybe that’s why we’re consistently voted #1 by DBTA readers and have 95%+ customer satisfaction rate.
Learn more about how Toad database management tools from Quest can help with database development, performance monitoring and database DevOps.
If you think your colleagues would benefit from the Raiders of the “Lost” Toad how-to series, share it now on social media with the buttons located at the top of this blog post. Thanks!
Read Gary’s previous series, Toad Turnpike: Real stories from the road.
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.