What's new in Teradata SQL Assistant 13.10

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

What's new in Teradata SQL Assistant 13.10

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.

23 REPLIES

Re: What's new in Teradata SQL Assistant 13.10

Mike,

Is this available online to download?

Thanks
Teradata Employee

Re: What's new in Teradata SQL Assistant 13.10

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.
Enthusiast

Re: What's new in Teradata SQL Assistant 13.10

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!!
Teradata Employee

Re: What's new in Teradata SQL Assistant 13.10

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.
N/A

Re: What's new in Teradata SQL Assistant 13.10

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

Re: What's new in Teradata SQL Assistant 13.10

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.)
N/A

Re: What's new in Teradata SQL Assistant 13.10

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...
Teradata Employee

Re: What's new in Teradata SQL Assistant 13.10

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

Re: What's new in Teradata SQL Assistant 13.10

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