This article will introduce the new features and performance enhancements that have been added to Teradata SQL Assistant 13.10. The focus of this release is on usability and performance.

The Query Window

New options have been added to the Code Editor tab to allow you to control

  • Whether matching braces are highlighted.
    (Quotes, Parentheses, Comment delimiters, CASE/END, etc)
  • Whether new lines are automatically indented to match the line above
  • The column position at which the select list is wrapped to a new line during Format Query
    By default it may format as:
       SELECT aaa, bbb, ccc
       FROM xxx
    If you change the 'Wrap lists after' value to 3 it will now format as:
       SELECT aaa,
             bbb,
             ccc
       FROM xxx
  •  The colors used for highlighting of the Explain text output

An option has been added to save the Query if you close the application, or Query window, and the Query has been changed since it was last saved or executed.

An option has been added to allow the Query Log to be saved to a file after the query completes.

An Add To Favorites command has been added to the Query context menu. This will add the highlighted text [or all text] to any SQL Set. [Default ‘Favorites’] This SQL can be quickly reused from the Query Builder

When the DBS returns a syntax error the location of the error will be highlighted in yellow. (This applies only when using the Teradata.Net connection option)

Support has been added for Oracle 11g, DB2 9.5 and SQL Server 2008

You may now use simple conditional logic in queries.
For example:
   .SET FOLDLINE ON 6
   .SET SUPRESS ON 1,2
   SELECT col1, ... ;
   .IF ACTIVITYCOUNT < 5 THEN .GOTO missing
   SELECT col2, ... ;
   .EXIT 0
   .LABEL missing
   INSERT INTO ErrorTable ... ;
   .EXIT 1
The following commands are supported :
  .IF {ACTIVITYCOUNT} oper n [THEN] {.GOTO label}
      {ERRORCODE    }               {.EXIT [n]  }
  .GOTO label
  .LABEL label
        [ n             ]
  .EXIT [ ERRORCODE     ] 
        [ ACTIVITYCOUNT ]
  .SET value
  .QUIT
can be used as a synonym for .EXIT
{ } – Items in curly braces are required.
[ ]  – Items in square brackets are optional.
‘.SET’ statements are simply ignored.
The period prefix is optional when connected to Teradata (except for the .SET command)

The Database Explorer Tree

You may now choose whether to load:

  • Your own list of specific databases (as in previous versions)
  • All the databases that you have access to on the connected Data Source
  • A list of database names obtained from a specified table or view
  • If loading from a table – load Databases only, or both Databases and Users

There is a new option to save the Database Tree information to disk when the application exits, and to reload it when you next open the application. This makes the tree information available when you are not connected to a data source. When this option is in effect all tables and views will automatically be expanded to retrieve their column information whenever you expand a Tables or Views node

Since it can take a long time to load the column information for views, there is a new ‘Fast Path’ option that will display only the column names for views, instead of displaying both the names and the data types. (This option is used only if the above 'save' option is in effect)

The Browse command has been replaced by a Generate SQL menu. This menu contains 4 commands that will automatically generate SQL for the selected table, and insert it into the Query window - Select, Insert, Insert (Import form) and Create.
Example SQL generated:

   SELECT colA, colB, colC FROM mytable

   INSERT INTO mytable(colA, colB, colC)
   VALUES (colA [INTEGER], colB [CHAR(20)], colC [CLOB])

   INSERT INTO mytable(colA, colB, colC) VALUES (?, ?, ?C)

The Answer set window

Page Setup now supports a Scale Factor (zoom) option to control the size of the printed output.

Additional Save file types - PDF and Microsoft Excel 2007 - have been added.

Numeric values can now be displayed using scientific notation. eg. 1.23456e10

Find now offers a dropdown list of previous ‘Find’ strings.

A Find All button has been added to the Find dialog. This displays a list containing references to all occurrences of the string. Clicking on a list item will move the active cell of the associated spreadsheet to the corresponding cell.

A new option controls whether column headers will be wrapped across 2 lines when the data is narrower than the column header.

A new command - Copy As HTML - has been added to the Answerset context menu. This copies the selected cells to the clipboard in HTML format. You may then paste the result into an application that supports HTLML on the clipboard to insert the data as a table. For example, Microsoft Outlook supports pasting of HTML when using the HTML format.

Decimal columns larger than 28 digits will now be included when adding column Totals to the bottom of the Answerset, or when highlighting blocks of cells to see the Sum, Average, etc. on the status bar.

The Data Source Name will be displayed in the Answerset window caption when you are connected to multiple data sources.
(This will not apply if all results are being directed to a single Answerset window)

The History Window

An option has been added to Display the SQL Text either as a single line (without carriage returns) or in the standard multi-line format.
When using the option to display the SQL Text in multi-line format, a new option controls whether a tooltip should display the SQL Text when the mouse hovers over this column and some of the text is not visible.
Alternatively the height of the rows can be increased, by dragging the bottom edge of the row header cell or using the Wrap Text menu, in order to see all the SQL.

An option has been added to prevent accidentally closing the History window. When this option is selected a click on the 'close' icon at the top right of the window will be ignored. The window can still be closed using either the File, Close or View, Show History menus.

A Clear Filter command has been added to the History context menu, and a Clear button has been added to the History Filter dialog.

Other Changes

Data can now be exported in Microsoft Access 2007 format.

A drop down list of standard date formats has been added to the Display dates in this format option.

A Rename SQL command has been added to the context menu in Query Builder.

Additional options have been added to the Advanced tab of the Connection Information Dialog for Teradata.Net.

Tab Group settings will be reset to their most recent state whenever you enter Tabbed window mode.

The .Net Data provider for Teradata is no longer required if you plan to use only an ODBC connection. (However we recommend using the Teradata.Net connection in order to avoid the many limitations of ODBC in a .Net environment)

Performance

Data retrieval is usually much faster; especially when using a Teradata.Net connection:
   Retrieve 40000 rows from dbc.columns - 30 sec vs 75 sec

The use of multiple threads also provides a performance boost on multi-core systems when returning data for multiple queries at the same time. 

Simple Import operations are considerably faster:
For example, Insert 5000 rows:

SQLA Version Batch Size 70 byte rows / sec 280 byte rows / sec
12.0 1 12 12
13.x 1 46 43
13.10 10 250 190
13.10 100 1000 570
13.10 500 3300 -
13.10 0 3600 1500

Limitations & Restrictions

 

General Limitations

  • You can not save a multi-tab Answerset as a single Excel workbook.
  • Exported UTF8 or UTF16 text files will always contain a BOM prefix.

Teradata.Net Specific Limitations

  • A WITH (summary) clause in a Select statement will cause the data return to fail.
  • An ECHO statement in a macro will cause the query to fail.

ODBC Specific Limitations

  • Retrieving Interval data, or vendor specific types, may limit the functionality of the result set.
  • CLOBs over 2K can only be returned using the UTF16 Session Character Set.
  • If a macro (or execute parallel) returns zero rows from a Select statement, no rows will be returned from any later Select statements in that macro or parallel query
  • Time fields may not display fractional seconds or Time Zone information.
  • Decimal values with more than 28 digits may display incorrectly.
  • The location of Teradata Database parser errors will not be reported.
  • Only type 'F' User Defined Functions will be listed in the Database Explorer tree.
  • Queue tables will not be identified as such.

Conclusion

Here we have described many small enhancements that should make the application easier to use, and more responsive.
I hope this article has made you eager to try the new version which is now available as part of the Teradata Tools and Utilities (TTU) 13.10 release.

 

Discussion
MickeySox 1 comment Joined 11/09
16 Jun 2010

Mike,

Is this available online to download?

Thanks

MikeDempsey 94 comments Joined 10/06
28 Jun 2010

No. Currently it is available only on the TTU 13.10 CD since we have not released any efixes yet.
When the TTU13.10 Edition 2 CD is released it will contain the 13.10.0.2 efix ... so that will be available for download from the patch server.

Karam 25 comments Joined 07/09
09 Jul 2010

Mike ,

Having seen SQL Assistant improving with each release is a satisfying experience.Considering your expertise with TD tools and utilities , I wanted to reach to you to log few issues with current 12.0 version, which surely find echo with numerous users.
Possibly, you can note these and pass to the tool's dev team.
They are :
1.In your post , i didnt understand clearly if this is taken care off but if not then the ability to export result set in MS excel. Its long pending but missing till 12.0
2. This is bit peculiar but if you highlight a query on a tab and click 2nd tab, on coming back to the 1st tab the cursor would be on the 1st line of the page rather than at the highlighted query which alas wont be highlighted at all!!

MikeDempsey 94 comments Joined 10/06
16 Sep 2010

No version of SQL Assistant can directly export to an Excel file. By that I mean writing the results directly to Excel without first displaying them in the Answerset.
You can however save the contents of an Answerset to Excel. The ability to save to Excel 2003 format has been available for a long time. The ability to save in the newer Excel 2007 format was added in 13.0.
Version 13.0 is not able to save multiple tabs (sheets) to a single Excel workbook, but version 12 and 13.10.0.2 are able to do this.

Your second problem applies to version 12.0 but has been fixed in 13.0.

mali 1 comment Joined 10/10
21 Oct 2010

Hi I would like to download Teradata SQL Assistant 13.10 for my windows 7 laptop.... Please help

MikeDempsey 94 comments Joined 10/06
26 Oct 2010

If you have access to the Teradata Patch Server you can download the latest version from there.
Otherwise the only way to obtain it would be from the TTU CDs or from the Teradata Express 13.10 install package. (Not certain if that is released yet but it will be soon, if not already.)

sandu 1 comment Joined 03/11
17 Mar 2011

hi , i have formating prob in generating report using teradata sql assistant...But, the same query generates the report as expected in SQL Server Client...
Do i need to change the settings?
Anyone help me pls...

MikeDempsey 94 comments Joined 10/06
17 Mar 2011

I would need more details to answer this. Please send the details of the problem, and the query, to mike.dempsey@Teradata.com.

Q 6 comments Joined 10/09
17 Mar 2011

Thank you Mike for keeping all the tools current. Your attention to detail is second to none.

rah1729 1 comment Joined 01/12
18 Jan 2012

Mike,

I am running SQL Assistant 12.00. There are a few problems with the Query editor I'm hoping will be fixed in the next release. Please add these to Karam's list above. Thanks. Overall, the tool is very useful...

3) When editing just one query, the associated file name is not displayed. The only way to determine which file is open is to save your work.

4) The only way to open another query, without overwriting the previous one displayed, is to have not saved some changes in the previous one. If the previously opened query is saved, it is overwritten by the newly selected query. As I usually save my work on a regular basis, I am always having to add or delete a space character in the previously opened query before I open another one, so that I can have them both displayed simultaneously.

5) When saving my work, the editor always tries to save to the name of the most recently opened query, no matter which one I am editing.

6) The bookmarks are a nice idea, but not very useful when editing multiple buffers. Whenever I change buffers, my bookmarks in the previous buffer disappear. Also, bookmarks would be even more useful if they were somehow saved (maybe in a separate file) with the query information so that when I return to editing a query I can still use them.

7) In most, if not all, windows apps the file open window provides full functionality, that is, it provides delete, copy, move, rename capabilities. It would be nice to have these capabiliteies added to the SQL Assitant file open window as it would save time jumping over to Explore to perform these actions.

MikeDempsey 94 comments Joined 10/06
18 Jan 2012

#3: The file name will be displayed on the status bar when you first load it but if you have only one query open the name will not be visible anywhere else since it is only displayed on the tab. (youcould click 'New' to create a second tab even if you don't use it though)

#4: Click 'New' before you click Open. This will create a new tab and Open will use it. The reason we overwrite unchanged queries is that most people store their queries in History (not in files) and this allows them to click on a bunch of different history items without opening a bunch of new tabs that they don't want.

#5: File names are remembered on a If the query was NOT opened from a file however, the first time you save it will still default to the previously saved name if one exists. (since it does not have its own file name yet)

#6: Bookmarks are defined on a 'per Query' basis in the newer versions of SQL Assistant. (They are not saved with the file for future use though.)

#7: We use the standard Windows 'Open File' dialog. This allows you to create a new directory but not to Delete or Rename files.
(Microsoft extended the dialog for their office apps by adding the Delete and Tools buttons. I'm not aware that these extensions are available to other apps though.)

Darin 4 comments Joined 09/05
23 Jan 2012

Hello Mike,

We are on 13.10.02.

Cut and Paste to Excel does not seem to be behaving well. It seems if there is an existing buffer from a previous paste it does not work.

Can an icon be assigned to the Save As ... menu item so it can be used on the toolbar. Some of our users used in the old version would open an existing file and save it as a new version.

MikeDempsey 94 comments Joined 10/06
26 Jan 2012

The Copy/Paste problem is in the third party spreadsheet we use. It will be fixed in SQLA 14.01 when we will use a newer version of that product.
The workaround is to press ESC before leaving Excel if Excel is currently in 'copy' mode.

There will be an icon for SaveAs in SQLA 14.01.
Currently due for release at the beginning of April.

Sina 2 comments Joined 05/09
11 Apr 2012

Hi Mike,

Are there any plans on improving the error reporting within the Query Window so the parser could at least highlight or focus the cursor on the part of SQL that caused the error? That would help immensely with annoying errors like 30 character limitations, bad aliasing, incorrect number of brackets e.t.c

MikeDempsey 94 comments Joined 10/06
11 Apr 2012

We implemented that back in SQLA 13.10.
You do however need to use Teradata.Net - not ODBC - since only Teradata.Net gives us the error position.

Sina 2 comments Joined 05/09
25 Apr 2012

Thanks a bunch!

MarkVYoung 20 comments Joined 03/12
24 Feb 2013

Hi Mike,
I am hoping you can help me with an issue being experienced by one of our users. He has installed TD SQLA 13.11.00.06 and finds that his profile space is being used up by the DB Tree data and he does not have the option to turn the save capability off. The "Save Database Tree Information to disk and reload it at startup" is grayed out and cannot be turned off. I cannot replicate this on my machine and was wondering if there is any config file that needs to be edited to reflect a different directory for the DB Tree file or one that can make this selection no longer grayed out.

MikeDempsey 94 comments Joined 10/06
25 Feb 2013

If that option is greyed out it means they have the option "Display suggestions" checked on the Code Editor tab. For performance reasons we need to store the tree data in order to support that option.
The only options here would be to clear that option - and lose the dropdown lists, or delete everything from the DB tree for data sources, or databases, that they don't often use.
Expanding database DBC will add a lot of info to the tree, and you may have other databases that do the same. If you delete some of those the size may become manageable. However they may get re-added if you reference them in a From clause or by dot qualifier.

mzs 11 comments Joined 09/10
29 Jan 2014

It is an old thread, but we just recently moved to TD 13 SQL Assistant.  Along with several improvements, I noticed couple of minor, but in my opinion very useful things are missing.  Specifically - TD 12 SQL Assistant  will display current logon name and current database.  I did not find the way to have this information displayed in TD 13 SQL Assistant.  In many cases this information is very useful, foe example, when using several windows with different user names in each to test security.  Actually, there is no easy way to find out where you are and what id you are using - something like sp_who in SQL Server.  Is there a possibility to return this information bach to SQL Assistant?
 
  

MikeDempsey 94 comments Joined 10/06
29 Jan 2014

In 13.0 and onwards you just hover the mouse over the data source name in the explorer Tree to see the User name and Default database for the current Query window. 

mzs 11 comments Joined 09/10
29 Jan 2014

Thank you - it works, but when you are way down in the database explorer, you need to scroll all the way back up to find out.  But, as you said, it is not going to be there in a future, so we just need to put up with this. Maybe you will reconsider...
Thank you for quick reply 
 

forum_1210 1 comment Joined 07/12
06 Aug 2014

Hey, Please some one tell me where can I download Teradata 13.10?

MikeDempsey 94 comments Joined 10/06
07 Aug 2014

13.10 is quite old so i don't know if it can still be downloaded. If so you would need a T@YS account in order to download it from the download center.
Otherwise the only downloadable version is the latest version of Teradata Express (currently 14.10 I think) which is intended for evaluation , testing, and developer support. This can be downloaded from the Downloads page here in the Developer Exchange.

You must sign in to leave a comment.