Raising Intelligence - Viewpoint SQL Scratchpad Query Mgt

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

This is the second in the series of blogs regarding cool features and tidbits within Teradata Viewpoint. Today's discussion is going to focus on SQL Scratchpad, a new Self Service portlet released with Viewpoint 13.0.1 in June 2009. SQL Scratchpad is the most significant addition to the Self Service aspect of Viewpoint since the "My Queries" portlet release in August 2008. SQL Scratchpad enables Teradata users in submitting ad-hoc SQL, getting result sets, and managing queries through a Viewpoint portlet. A very nice flexible compliment to the client based query tools like SQL Assistant. Worth noting, Teradata will continue to offer SQL Assistant client variants, Windows and Java editions, but does intend to discontinue the SQLA Web version after the TTU 13.0 release. If you didn't know there was a web version of SQLA, don't fret, you're not alone.

Today's focus is going to be on query management capabilities of SQL Scratchpad.

SQL Scratchpad certainly allows you to build, submit, and retrieve multiple queries as well as having result sets returned in your SQL Scratchpad view. For processing of result sets you also have Export and Pin options. What I'd like to discuss further here is the query management capabilities in this portlet.

Object Browser

The object browser will assist in building your queries. It allows you to view a list of objects from your Teradata System with insert capability directly into your SQL statement. Use of the object browser can reduce time for query entry and eliminate errors from typing in object names, in particular those long rascals. Here's a hint when using the object browser, maximize SQL Scratchpad to provide enough real estate for all three panes (object browser, your queries, and your results) to be displayed at the same time. If you don't maximize, it tends to be a bit more cumbersome.  In SQL Scratchpad preferences, you can even choose your preference for locating the object browser to be the left or right panel. Here's an example of what I'm talking about:

Result Tabs

Besides help for building queries, SQL Scratchpad provides result tabs to assist in managing your submitted queries. The Explain tab (see above in the Results pane) provides the unique capability to monitor Explain step by step progress of your longer running queries. Yes this is the same basic functionality you find in the other query portlets like My Queries or Query Monitor. Very nice to have it built in here. The "Blocked By" tab will identify if your query is being blocked and by who and why providing additional insight into the status of your submitted query.

Rerunning Queries

You can also rerun saved and historical queries through the Previous Queries button. However there is an important distinction between the two. Queries listed in History are the previously run queries from your current session. So these stay around as long as you maintain your current session. Logout of Viewpoint and you're back to a clean slate.

If you truly want a query available for easy re-submittal, then use the Save option. When saving a query, you'll be prompted for a saved query name for easier identification and sorting. Saved queries stay around as long as you want. Rerunning either History or Saved queries is as easy as selecting load and run. Note that you can put multiple SQL statements into a single saved query like the "Duo Query" below.

So that's SQL Scratchpad and its management capabilities. A very nice Self Service addition for the Teradata user community enjoying Viewpoint. Thanks for listening and until next time ...

"What we have to learn to do, we learn by doing" - Aristotle