This article is intended to help users as they transition from Teradata SQL Assistant to Teradata Studio Express. Included are some of the more frequently used SQL Assistant features along with a detailed 'How To' guide for the same features in Studio Express. Note: this article is relevant for Studio Express 15.10 or later.

The following topics are discussed:

 

Connect to a Database Server

In SQL Assistant, the user would define an ODBC or .NET Data Source (Tools>Define ODBC Data Source, for example).

Studio Express uses JDBC to connect to your Teradata Database, Aster Database, or Hadoop system. ODBC is not supported with Studio Express. The Teradata JDBC driver, Aster JDBC driver, and HiveServer2 (v0.13) JDBC driver come embedded in the Studio Express product. You can create connections to your database using the Connection Profile Wizard. The wizard is invoked from the Data Source Explorer by right clicking on the Database Connections folder or selecting the 'New Connection Profile' button, , from the Data Source Explorer toolbar.

          

Studio Express provides an option to create connection profiles for additional database servers other than Teradata.  You must provide the specific database server's JDBC driver for databases other than the default connection profile types: Teradata Database, Aster Database, or Hadoop Hortonworks. Refer to the section, Connecting to Non Teradata Database, for configurating connections to Oracle, DB2, or SQL Server.

           

Select Teradata Database from the Connection Profile Types, enter a Name and click Next.

            

The Connection Profile Wizard is based on a driver template that references an embedded Teradata JDBC driver. Enter the database server name, user name, password, and default database name into the wizard dialog screen. The Authentication Mechanism allows you to choose an advanced logon mechanism, such as LDAP or Kerberos, or select the default mechanism, PASSWORD_PROTECTED. If you wish to save your password to disk, check the Save Password box. Otherwise, the password is stored in the profile and only active throughout the Teradata Studio Express session. When Teradata Studio Express is closed, the unsaved password is removed before the profile is written to disk. You will be prompted for the password the next time you connect.

Optionally, you can choose a different Teradata JDBC driver than the embedded Teradata JDBC driver. You can also add or remove the JDBC properties, as well as save the properties to an external file. The external file is called jdbcConnectionProperties.properties and located in the workspace .metadata/Teradata directory. The jdbcConnectionProperties.properties file allows you to share a set of JDBC properties with other Teradata Studio Express users. If the file exists when creating a new Teradata connection profile, it will load those property values into the list of JDBC connection properties. If the file exists but it is empty, the connection wizard will assume that no JDBC properties are specified and the Teradata JDBC driver will use the default settings.

NOTE: In Studio Express, the default Teradata Mode (TMODE) is set to ANSI and the default TMODE setting with SQL Assistant is TERA. As the recommended TMODE setting is ANSI, users can change the TMODE to TERA. To change the TMODE property, press the Add button and select the property TMODE. Choose the property value TERA and click OK to replace the existing TMODE property. Please refer to the Teradata Documentation (SQL Request and Transaction Processing) for differences between Teradata and ANSI Session Modes. One particular difference is that by default, character comparisons are always CASESPECIFIC in ANSI session mode, where character comparisons are always NOT CASESPECIFIC in Teradata session mode.

Once you have changed or added new JDBC Properties, you can set your new properties as the default properties for any new connection profiles by clicking the Save Properties button. This will save the properties to a file in your workspace called jdbcConnectionProperties.properties located in your workspace in the <WORKSPACE>/.metadata/Teradata directory.

Aster Connection Profiles are created using a similar connection wizard with the embedded Aster JDBC Driver. Database server name, user name, password, port number, and database name fields are provided for user input.

Multiple Connections

With Studio Express, you can display multiple connection profiles going to the same (or different) Database Server(s). This is useful when you want to compare the database objects or, for the same Database Server, use filters to control what database objects are displayed in one connection profile versus the other. For example, one connection profile may require quering your metadata databases and include filters to just include those databases. And the other connection profile (to the same Database Server) is used for activities that involve a semantic layer so provide a filter to just include those databases.

Refer to the section below, Creating Database Tree Filters, for the steps to create a filter on a Data Source Explorer folder.

Change Password

In SQL Assistant, go to Tools and select 'Change Password ...' menu option

In Studio Express, select the database node in the Data Source Explorer tree and right click. Choose Teradata>Change Password... menu option.

Connecting to Non Teradata Database

In SQL Assistant, the user would create a non Teradata Data Source and choose it for the connection

In Studio Express, the default database types are Aster, Hadoop, and Teradata. To connect to DB2, Oracle, or SQL Server, the user would do the following:

  1. Go to Window>Preferences>Teradata Datatools Preferences>New Profile Connection Types and enable the non Teradata connection type in the New Profile Connection Types list. The default values are Aster Database, Hadoop System, Hortonworks, and Teradata Database. In the screen capture below, we have checked the Oracle connection type.

  1. Press the New Connection Profile and choose Oracle. Enter a Name for the connection profile and press Next.

  1. Click the 'New Driver Definition' button,  and create an Oracle Driver definition.

  1. Choose the Oracle Database and enter a Driver name, then click the JAR List tab to specify the location of the Oracle JDBC driver. Note: You must download from Oracle the Oracle JDBC driver and place it on your desktop. Then press the Add JAR/Zip... and specify the location of the JDBC driver jar.

  1. Click OK to return to the New Connection Profile Wizard. Here you will specify the SID, Host, User name and Password, as well as any optional JDBC properties. Click the Test Connection button to test the connection, then click Finish to create the connection profile and return to Studio Express.

Add a Database

In SQL Assistant, the user would right click in the Database Explorer and choose 'Add Database'.

In Studio Express, the Data Source Explorer view provides the database tree with its database objects presented in folders, such as Databases, Tables, Macros, Views, etc. By default, the database tree is presented in a hierarchical display with child databases and users nested under their parent database or user. Also, by default, all of the database and users are displayed. To change this and only add one database at a time, the user would follow these steps:

  1. Go to the Data Source Explorer Preference page and uncheck the option 'Show Databases and Users in Hierarchical Display'
  2. Select the 'User Choice' option.

  1. Return to the Data Source Explorer, right click on the Databases folder and choose Teradata>Load Database...

  1. This will invoke the Add Database dialog. Enter the name of the database or user you wish to add and click the Add button. You can repeat this process, adding additional names. Close the dialog when all names have been added.

  1. The Databases folder is refreshed and the newly added database or user is loaded in the Data Source Explorer.

Set Root for Database Tree

Another option provided in Studio Express is to set the Root location for the database tree. If you would prefer to display the database tree in hierarchical format (nesting child databases and users) but still reducing the number of databases and users displayed in the tree, you can set the Root of the database tree. This will restrict the display to your database or user and its child databases and users. For this option:

  1. Go to the Data Source Explorer Preference page and choose the 'All Database and Users' option and check the option 'Show Databases and Users in Hierarchical Display'
  2. Next, return to the Database Source Explorer and right click on the Databases folder and choose Set Root...

  1. This will invoke the Set Root Database dialog. Enter the Root database or user name and click OK.
  2. The Databases folder is refreshed and the ROOT is set to the value specified.
  3. Open the root database or user and any nested child databases or users are loaded in the Data Source Explorer.

 

Creating Database Tree Filter

Studio Express allows you to set a filter on the folders displayed in the Data Source Explorer (DSE), filtering out unwanted database objects. This applies to not only the Databases folder, but also the Tables, Views, Stored Procedures, and User Defined Functions. Setting a filter on the Database folder is another way to control what databases you see in the DSE. The following are the steps to create a filter:

  1. Right click on the DSE folder and choose the Filter... option.  This will invoke the Filter Dialog.

  1. Next, in the Filter Dialog, uncheck the 'Disable filter' option and choose Expression or Selection. The Expression option allows you to choose between such expressions as: 'Starts with the characters', 'Contains the characters', 'Ends with the characters', 'Does not start with the characters', 'Does not contain the characters', and 'Does not end with the characters'. The Selection allows you to choose the objects from the list to be included or excluded in the display.

  1. Click OK to reflect the filter in the DSE tree.

SQL Editor/Query window

Entering SQL statements and Executing Queries

In SQL Assistant, the user would enter the SQL statement in the Query window and press the feet icon, . The statement(s) is executed and the result set is displayed in the Answserset window. In SQL Assistant, users can also press the F5 key to execute the SQL statements.

In Studio Express, the user would open a SQL Editor associated with the connection profile.  Select the connection profile in the Data Source Explorer and press the toolbar option, 'Open SQL Editor', , or choose File>Open SQL Editor

Then enter the SQL statement in the SQL Editor and press the execute icon, . The user can also press Ctrl+ALT+X. The statement(s) is executed and the result set is displayed in the Result Set Viewer. The Execute icon is available on the top toolbar, as well in the SQL Editor toolbar. The user can also right click in the SQL Editor and choose an 'Execute' option from the context menu.

For multiple statements, the Execute All option, , is provided. This will send all of the statements in the SQL Editor to the database to be executed and return a group of result sets displayed in the Result Set Viewer.

Code Assist and Syntax Checking

As the user enters SQL statements in the SQL Editor, code assist will help the user with a list of 'next' words in the SQL syntax. By default, Code Assist Autoactivation is set to true. The user can toggle this option on or off by pressing the Code Assist Autoactivation icon, on the toolbar.

Studio Express parses the SQL statements entered in the SQL Editor and underscores syntax errors in red. It uses the grammar for the version of the database that is chosen in the Connection Profile drop down list in the SQL Editor. A list of expected words is provided if the user hovers at the error location.

The user can choose to ignore the errors and still execute the SQL statement using the Execute All toolbar icon or Ctrl+Alt+X key sequence.

Clear Query Text in SQL Editor/Query window

In SQL Assistant, choose the top level toolbar option, 'Clear Query', to remove the text in the Query window.

In Studio Express, choose the SQL Editor toolbar option, 'Clear Query' , to remove the text in the SQL Editor.

Displaying Line Numbers

In SQL Assistant, go to Tools>Options>Code Editor and check the 'Display line numbers' option.

In Studio Express, right click on the left margin of the SQL Editor and check the 'Show Line Numbers' option.

Execute Highlighted Text in the SQL Editor/Query window

In SQL Assistant, go to Tools>Options>Query and check the option ‘Submit only the selected query text, when highlighted’.

In Studio Express, select the text in the SQL Editor, right click and choose the option ‘Execute Selected Text’.

SQL Templates

In SQL Assistant, go to Help>Query Builder and choose a template from list of SQL Statements.

In Studio Express, in the SQL Editor, go to the beginning of the line and enter Ctrl+Space. A pop up window appears. Scroll down and choose the SQL template from list. Once the template is presented in the SQL Editor, press the tab key to move from value to value within the template definition.

Named Parameterized Queries

In SQL Assistant:

  • Usage: SELECT * FROM db.customer WHERE region = ‘?region’;
  • Go to Tools>Query and check the option ‘Allow use of Named Parameters in queries’
  • Run the query and enter the value for the region parameter.

In Studio Express:

  • Usage: SELECT * FROM db.customer WHERE region = ?\region;
  • No preference is needed. Within the SQL Editor, choose the option to execute the query. The Configure Parameters dialog appears for the user to enter the value for the region parameters.

  • Studio Express also allows for non-named parameterized queries to be executed, such as " SELECT * FROM db.customer WHERE region = ?;"
  • As with SQL Assistant, parameter data can also come from a file. Click on 'Specify file' and an ellipse icon,, will appear. Click on the ellipse to choose the file that contains the parameter data.
  • Also note that in Studio Express, parameters ('?') are used for only data value substituions in the SQL. It does not perform direct string substitution as in SQL Assistant.

After executing the parameterized query, users can view the parameters in the SQL History by scrolling over to the Parameters column and double clicking on the Parameters button. The user can then re-execute the same query from the SQL History and it will prompt the user for new parameters.

Provide Audible Notification on Queries

In SQL Assistant, go to Tools>Options and set the option ‘Provide audible notification when query ends’.

In Studio Express, go to Window>Preferences>Teradata Datatools Preferences>SQL Handling and click the Sounds tab. Choose the option 'System Beep' or 'Select Sound' and choose a sound file for successes and failures. There is a shortcut to the SQL Handling Preferences page by clicking the preferences icon, , on the SQL Editor toolbar.

Changing the Font

In SQL Assistant, right click in the Query window and choose 'Set Font' from the menu.

In Studio Express, go to Window>Preferences>General>Appearance>Colors and Fonts. For changing the font in the SQL Editor, select Basic>Text Font. Click the Edit button and choose the font and size desired. To restore the font back to its default, select Text Font and click Restore Defaults. For changing the font in the Result Set Viewer, choose the Dialog Font.

Formatting and Syntax coloring of the SQL in the SQL Editor/Query window

In SQL Assistant, go to Tools>Options>Code Editor and choose formatting and syntax color options.

In Studio Express, for Formatter Preferences, go to Window>Preferences>Teradata Datatools Preferences>SQL Formatter Preferences and choose formatting options. For Syntax Color, go to Window>Preferences>Data Management>SQL Development>SQL Editor>Syntax Coloring and choose syntax color options.

SQL Statement Outline Marks

In SQL Assistant, outline marks are always displayed in the Query window.

In Studio Express, the outline is presented in a separate view called the Outline view. To open the Outline view, go to Window>Show View and choose Outline. The Outline view will appear at the bottom of the screen. Click and drag the Outline view up to the right hand side of the SQL Editor. As you select SELECT Statements from the Outline view, the SQL statement is highlighted in the SQL Editor window.

Rectangular Block Selection

Rectangular block selection or column mode selection allows you to select the text vertically within the text.

In SQL Assistant, SHIFT+ALT+arrow key will allow the user to select text in column mode.

In Studio Express, SHIFT+ALT+A enables the SQL Editor for column mode selection or also known as Rectangular Selection. SHIFT+ALT+A again will return to normal mode selection.

Result Set Viewer/Answerset Options

Result Set Viewer Preferences

In SQL Assistant, go to Tools>Options>Answerset.

In Studio Express, go to Window>Preferences>Teradata Datatools Preferences>Result Set Viewer Preferences page. There is also a shortcut to the Result Set Viewer Preferences page by clicking the preferences icon, , on the Result Set Viewer toolbar.

Executing Multiple Queries and Display in Separate Result Set/Answerset Windows

In SQL Assistant, go to Tools>Options>General. For the option 'Use a separate Answer window for' and select 'Each Result Set'.

In Studio Express, asa mentioned above, choose the Execute All option, . This will send all of the statements in the SQL Editor to the database to be executed and return a group of result sets displayed in the Result Set Viewer. Once you have the result sets, you can display them side by side or in a tab format (choose toggle Sash or Tab, ).

Close All Result Sets/Answer Sets

In SQL Assistant, right click in Answerset and close the individual Answerset window.

In Studio Express, in the SQL Editor toolbar, choose Clear Result Sets, , to close all opened result set windows.

Reuse Result Set window

In SQL Assistant, go to Tools>Options>Query and check the option ‘Close Answerset windows before submitting new query’.

In Studio Express, go to the Result Set Viewer Preferences and check the option ‘Reuse Result Set Viewer Window’.

Saving Result Set/Answerset Data to a File

In SQL Assistant, select the Answerset window and go to File>Save As. Choose the name of the file to save the answerset data.

In Studio Express, there are two options for saving the result set data. You can save the result set data to the file system (Export) or to a file stored in the workspace (Save). Select the Result Set window and from the Result Set Viewer toolbar, choose Save or Export. This will invoke the Export or Save Result Set dialog to choose the name of the file to save the result set data.

Copy/Paste Result Set Data to Excel

In SQL Assistant, go to Answerset and select the cells and press Ctrl+C. Open Excel file and press Ctrl+V.

In Studio Express, go to Result Set Viewer and select the cells and press Ctrl+C. Open Excel file and press Ctrl+V. To include Column Headers, set the Result Set Viewer Preference, 'Copy Include Column Headers'. Select the cells to copy, right click and choose the 'Copy Cells' menu option. In your Excel file, press Ctrl+V. The Copy Cells option will copy the data using HTML format, which allows the copy to include format and font information, that can then be pasted into other products, such as Excel, Outlook, or HTML.

Below is an example of changing the background and foreground of cells in the Result Set Viewer, selecting the cells and copying (using 'Copy Cells') then pasting the cells into Excel. The 'Copy Include Column Headers' and 'Copy Include Grid Lines' preferences were also checked.

Export Mode (Automatically exporting result set/answerset data to a file)

In SQL Assistant, go to the File menu and toggle the 'Export Results' option. Then enter your SELECT statement into the Query window and click the icon to execute the query. The Export File dialog appears for you to enter the name of the file to export the data to.

In Studio Express, go to Window>Preferences>Teradata Datatools Preferences>SQL Handling preference page and set the Results Handler to 'Teradata Export Wizard (File Export)'.

Then enter your SELECT statement into the SQL Editor and click the icon to execute the query. The Export Data wizard appears for you to enter the file options to export the data to. As mentioned above, there is a shortcut to the SQL Handling Preferences page by clicking the preferences icon, , on the SQL Editor toolbar.

Handling LOB column sizes

In SQL Assistant, go to Tools>Options>Data Format and choose the CLOB and BLOB size options.

In Studio Express, go to Window>Preferences>Teradata Datatools Preferences>SQL Handling and choose the tab Teradata or Aster. Enter the CLOB and BLOB size options. If a LOB is encountered while processing the result set, a pop up dialog is presented for the LOB dispositioning.

Export Data From a Table

In SQL Assistant, you would repeat the steps above by going to the File menu and toggle on the 'Export Results' option. Then in the Query window, run a SELECT * statement for the table in question. This will launch the Export File dialog.

In Studio Express, locate and select the table in the Data Source Explorer. Right click and choose the Data>Export Data... option. This will launch the Export Data Wizard.

  1. Choose the name of the Output File and File Type (Delimited Text, Excel (.xlsx), Excel 97-2003 (.xls))
  2. Check the box if you want column labels in the first row.
  3. Choose the File Options, such as column delimiters, character string delimiter, and file encoding. Click Next
  4. Here you can filter out columns you don't want to export by unchecking the 'Use' checkbox.
  5. Click Finish to run the Export Data job.
  6. To view the output from the Export Data job, you will need to open the Transfer History View. Go to Window>Show View and choose the Transfer History View from the drop down menu.

     7. This will open the Transfer History View. Select your Export Data entry from the transfer history table and click the Job Details toolbar icon, , to view the details of the export job.

Load Data into a Table

In SQL Assistant, go to the File menu and toggle on the 'Import Data' option. Then enter your INSERT statement into the Query window and click the icon to execute the query. The Import File dialog appears for you to enter the name of the file to import the data from.

In Studio Express, locate and select the table in the Data Source Explorer. Right click and choose the Data>Load Data... option. This will launch the Load Data Wizard.

  1. Browse the file system for the Input File.
  2. Choose the File Type (Delimited Text, Excel (.xlsx), Excel 97-2003 (.xls)).
  3. Check the box if your input file has the column labels in the first row.
  4. If you want the load to stop after 'X' number of errors that occur, check the box and enter the error row count value 'X'.
  5. Check the box if you want to lock the table during the load
  6. The Load Data Wizard will detect if data already exists in the table. Check the box if you are replacing existing data. Otherwise, it will append the data to the table.
  7. Choose the File Options, such as column delimiters, character string delimiter, file encoding, and start loading row number. The Load Data Wizard will display a preview of the data to be loaded, using the file options specified.
  8. Click Finish to run the Load Data job.
  9. To view the output from the Load Data job, you will need to open the Transfer History View. Select your Load Data entry from the transfer history table and click the Job Details toolbar icon, , to view the details of the load data job.

Open an Existing SQL File in the SQL Editor/Query window

In SQL Assistant, go to the File menu and choose Open Query... This will launch the Open Query dialog to locate the SQL file. The SQL file is opened in the Query window.

In Studio Express, SQL files are managed by the Project Explorer. When Studio Express is first launched, a 'SQL' project is created for you. You can create additional projects to organize your SQL files via the Project Wizard. Go to File>New Wizards>Project.

To open an existing SQL File in Studio Express, go to the File>Import... menu:

  1. Select General>File System
  2. Press 'Next' and browse for the directory that contains the SQL File.  This will display the folder in the left hand side with all of the files in the right hand side.
  3. Check the box next to the file(s) that you want to import into Studio Express.
  4. Next, browse your Studio Express workspace to locate a project folder to import the SQL file into ('Into folder'). Choose the SQL folder and click Finish.

  1. Next, go to the Project Explorer view and open the SQL folder.
  2. You can also import SQL Files directly into your SQL folder by right clicking on the SQL folder in the Project Explorer and choosing Import... menu option. This will launch the same Import dialog.
  3. Locate your SQL file. Right click and choose the menu option 'Open With>Teradata SQL Editor'. This will open the SQL file in the SQL Editor. The SQL Editor will default to the connection profile selected. You can change this to a different connection profile in the Connection Profile drop down list.

As with SQL Assistant, drag and drop is yet another way to import SQL file into Studio Express. Locate the SQL file in Windows Explorer, drag and drop it into the Project Explorer. This will invoke the File Operation dialog. Choose 'Copy files' option to copy the file into your SQL folder.

Comparing SQL Files

Another feature of the Project Explorer is the ability to compare two or three SQL files. Select the SQL files in the Project Explorer, right click and choose the Compare With>Each Other menu option.

This will open the files in the Compare Editor, showing a side-by-side comparison with toolbar buttons to navigate the differences.

Save a SQL Query to the File System

In SQL Assistant, select the Query window and go to File>Save As menu option. Then choose the file location to save the query in the Query window.

In Studio Express, go to the SQL Editor toolbar and choose 'Export to File System'. Then choose the file location to save the query in the SQL Editor window.

Changing Shortcut Keys/Key Bindings

In SQL Assistant, go to Tools>Customize... and press the Keyboard... button. Select the command from the list of Categories and Commands.

Studio Express has tried to replicate the shortcut keys provided by SQL Assistant. But you can change the key binding for a command via the Keys preference page. In Studio Express, go to Window>Preferences>General>Keys. Select the command from the list of Commands. The 'key binding' is displayed for you to edit.

A 'key binding' is the assignment of a 'key sequence' to a command. A 'key sequence' is one or more key strokes. A 'key stroke' is the pressing of a key on the keyboard, while optionally holding down one or more of these modifier keys: Ctrl, Alt (Option on the Macintosh), Shift, or Command (only on the Macintosh.) For example, holding down Ctrl then pressing A produces the key stroke Ctrl+A. The pressing of the modifier keys themselves do not constitute key strokes. Keyboard shortcuts should not be more than four strokes in length. 

You can restore bindings to their default values by pressing the Restore Defaults button.

Import SQL Assistant History into Studio Express

Studio Express provides an option to import the history file from SQL Assistant. Go to the Teradata SQL History view and press the Import History option, , on the SQL History toolbar. This will invoke the Import History dialog.

Chose the Source Type (.mdb, .xml) and browse for your SQL Assistant history file. Click OK to import the history data. You will notice that the Destination column is blank for the entries that were imported from SQL Assistant.

Getting Help

In SQL Assistant, go to the Help menu option and click 'Help Topics'.

In Studio Express, go to Help> Help Contents. Navigate the help topics on the left or type in a search text in the Search box.

  • Context sensitive help is also available on dialogs or views by pressing the F1 key.
  • Studio Express also provides help via the Teradata Studio Forum located on the Teradata Developer Exchange site. Open a new topic or post your comments or questions to an existing topic.
  • Studio Express is a fully supported product. Customer support is available around-the-clock, seven days a week through the Global Technical Support Center (GSC). To learn more, go to http://tays.teradata.com.
Discussion
wmmiteff 8 comments Joined 02/11
23 Apr 2015

Thank you for this very helpful guide on the transition from SQL Assistant to SQL Studio Express.  I was very happy to see that the new version of SQL Express allows for creation of Data Sources at install time.  Is it possible to have it do this as part of a silent install?

Tuen 24 comments Joined 07/05
23 Apr 2015

Francine, is there any chance we can get this information in a PDF that we could provide our users to ease transition to Studio/Studio Express?

gseller 5 comments Joined 05/09
23 Apr 2015

The Data Source information can be supplied with a silent install. Documentation will be posted soon.

pinaldba 7 comments Joined 09/10
23 Apr 2015

Thank you. Great Article. It will be great if Teradata studio provides the Explain Plan Display (GUI) facility, Comparing Explain Plan like eclipse Data tools Platform(DTP). Eclipse Data tools Platform reads the explain plan from QCD and it display explain plan GUI. This explain plan, I find it very easy to navigate comparing to Visual explain.
 
 
 
 
 
 

Mikec 21 comments Joined 03/09
24 Apr 2015

To the PDF question above, you can use the print option in the top right hand corner of the article to get a printer friendly version that you can export to a PDF. Not going to be perfectly formatted and the table of contents links still point back to this article (not sure why your users can't just us the article, which is available to registered users and guests alike).

jlasheras 5 comments Joined 08/12
27 Apr 2015

Very nice article, thank you.
Regarding "Load Data Into a Table", unfortunately Studio Express is missing the capability to load volatile tables, which are local to the session, this type of object is unknown to the app due to the way the Data Source Explorer is built.
So our users still want to keep an instance of the SQL Assistant available.
Regards
 

SGalvez 2 comments Joined 05/14
29 Apr 2015

I've been waiting for this.  Thanks.  Is there a way to perform a case in-sensitive filter search on the Teradata SQL history?

fgrimmer 660 comments Joined 04/09
29 Apr 2015

Jorge, We have an Issue opened to get this resolved in our next release. Thanks for the feedback.

fgrimmer 660 comments Joined 04/09
29 Apr 2015

Sam, The Find for Teradata SQL History has an option to 'Match Case' so if that is unchecked, it should be case in-sensitive.

SGalvez 2 comments Joined 05/14
29 Apr 2015

Thank you very much for the prompt response.  However, I like to use the filter option.  Is there an option to do case in-sensitive searches for the History Filter?

fgrimmer 660 comments Joined 04/09
29 Apr 2015

Sam, Not at this time. I have opened a JIRA for this to get added.

jlasheras 5 comments Joined 08/12
06 May 2015

Thanks so much for your comment, Francine
That's very good news
 

doormaster 2 comments Joined 04/15
11 May 2015

Thanks a lot for the guide! I just wonder if there is any way I can quickly write and execute commonly used queries like select count(*)? You know in sql assistant we can do this by recording a macro. 
BR, 
Kevin

fgrimmer 660 comments Joined 04/09
12 May 2015

Kevin, Studio Express does not have a macro feature but you can re-execute queries from the SQL History. Right click on the SQL Statement cell in the SQL History and choose Reexecute Statement.

doormaster 2 comments Joined 04/15
12 May 2015

Thank you for your comments!

luiszeta 4 comments Joined 02/12
02 Jun 2015

Great article and great upgrade of this tool.
I think that the "Code Assist" feature seems to have been improved with every release but, when I'm trying to be assisted with the possible columns in a table,  I'd like "Code Assist" was more flexible regarding to use any alias instead of the precise owner+table name.
Also related to table qualification, I think it would be nice that Teradata, but if not, this tool itself, provided a way to avoid the cartesian product when we misspell a table alias in FROM and WHERE clause. A warning of "not found table in FROM" wolud be enough.
Thank you!
Luis Zapata

rparman 4 comments Joined 07/13
19 Jun 2015

I attended your webinar on 6/18 and have downloaded Teradata Express 15.10.  Question.  In SQL Assistant you have an abort button to abort the query that is running.  I cannot find a similar function in Query Development.  Is there a way to issue an abort for a running query? 

rparman 4 comments Joined 07/13
19 Jun 2015

In SQL Assistant, if the result set exceeds a certain number, i.e. over 2000 rows, you are prompted to only return the 2000 rows or all rows.  If you only return 2000 rows, the SQL History will still show the total number of rows that were generated as the result set.  This is valuable as to be able to determine the magnitude of the query result.
In Studio, the History only shows the result set as 2000.  This can be misleading if you are try to measure the impact of the sql or trying to compare the result set total rows to some other query.

fgrimmer 660 comments Joined 04/09
19 Jun 2015

Randall, Regarding your first question about an Abort button. There currently is not an abort button but we have an open Jira issue for this improvement.
For your second question: This issue has been raised numerous times. There are 2 issues when trying to retrieve the row count from the JDBC result set. 1)We ran into a problem with very large result sets in that the call to the JDBC driver appeared to be hung do to the time it took to reposition the cursor to the end of the result set. And we could not cancel the query at that time. 2) The row count API returns an Integer and for result sets larger than MAX_INTEGER, the value was incorrect. There is an RFC opened against the JDBC driver to resolve this issue so that Studio can accurately retrieve the row count.

rparman 4 comments Joined 07/13
19 Jun 2015

Thank you for the quick response and detailed information.

pj.routledge 5 comments Joined 04/08
22 Jun 2015

I'm keen to try Studio Express and was just about to download it when I noticed the minimum hard disk requirement of 250 GB. Obviously not a thin client!
Is 250 GB correct? Have I misunderstood what Studio Express is? I thought it was an IDE.
Peter

fgrimmer 660 comments Joined 04/09
23 Jun 2015

Peter, 4 GB is required but 6 GB is preferred. Where did you see 250 GB mentioned so we can correct it?

pj.routledge 5 comments Joined 04/08
23 Jun 2015

Francine, it's in the document 'InstallationGuideAll-2037.pdf' which I downloaded from the downloads section (one of the docs listed below the actual software downloads).
The reference to 250GB of disk space is in Chapter 1, under 'Hardware Requirements'.
The 4GB with 6GB preferred is also mentioned, but as the memory (RAM) required for Studio, with Studio Express only requiring 4GB.
250GB seems unlikely, and I appreciate your follow up.
Peter

fgrimmer 660 comments Joined 04/09
23 Jun 2015

Peter, Sorry, I was refering to RAM. Users aren't usually concerned with disk space. :-) But I just installed our latest version of Studio Express and the install area took up around 200 MB and workspace is around 3.5 MB, which can certainly grow. But even so, it seems to be way overstated. We will take a look at the documentation. Thank you for pointing this out.

pj.routledge 5 comments Joined 04/08
23 Jun 2015

Thanks Francine. :) That seems more like it, maybe the 250 GB should be 250 MB in the doc.

ms255087 2 comments Joined 03/14
23 Jun 2015

Hi Francine,
 
Really helpful post. I am planning to migrate to Studio completely from SQL Assistant.
However, i have two concerns here:
1) In SQL Assistant there is an option to right click in the Database Explorer region and select 'Add Database' to have multiple databases added in the explorer view for a connection. I cannot find this option in Studio Express. It is really helpful in SQL assistant as once the database was added, it helped in code assist during query writing like table name or column name suggestions as well helpful in viewing database structure.
In Studio Express, I can only view the default database for my account. However, i found that in the connection properties there is an option 'Cache Properties' which allowed me to select all the databases over that connection to be cached. But still even after selecting them, these databases are not shown in the Data  Source Explorer view nor code assist is aware of these databases.
This is the only option preventing me from switching to Studio completely.
I would like to know if there is any other option that can enable Code Assist for multiple databases during query development.
2) Another question that i wanted to know is Apart from Administrator tasks, is there any other difference in Studio and Studio Express?
 
Thanks,
Mujeeb

fgrimmer 660 comments Joined 04/09
24 Jun 2015

Mujeeb, 1) To add a single Database to the Data Source Explorer, see the section above titles 'Add a Database'. You need to change your Data Source Explorer Load Preference to uncheck the show as hierarchy option and select 'User Choice', in order to see the menu option.
For Code Assist, make sure the Code Assist is toggled on in the top toolbar or Tools menu. Then when you pause during typing your SQL query, the code assist will kick in.
2) Refer to the DevX article: Teradata Studio Products - Choosing the right tool (http://developer.teradata.com/tools/articles/teradata-studio-products-choosing-the-right-client-tool), for a comparison of the two procuts.

dennis.jones 2 comments Joined 08/13
24 Jun 2015

Hello, I am wondering if there is a way to get the out parameter to show in the result set in studio. Here is the simplest version of a stored procedure that demonstrates my issue.

REPLACE PROCEDURE USER_WORKING.test

(
IN intxt VARCHAR(30),
OUT outtxt VARCHAR(30)
)

BEGIN

 SET outtxt = intxt ;

END;

When I run this is SQLA whatever I pass in to the procedure gets returned in the result set. However when I run this same procedure in Studio nothing gets returned in the result set.  On the bigger procedure all the underlying statements get run but for whatever reason the out parameter does not show. I have been looking for quite a while for a setting that allows the output parameter to show up in the result set but have not found anything yet.
 
Any help you can offer is greatly appreciated 
 
Dennis

ms255087 2 comments Joined 03/14
24 Jun 2015

Thanks Francine for the help.
 
One thing i noticied however is that while adding a database to DSE, the database name is case sensitive unlike SQL Assistant. Studio was not accepting the database name if the case was different.
But once i figured that out I was able to add my choice of databases. 
 
Thanks Again,
Mujeeb

fgrimmer 660 comments Joined 04/09
25 Jun 2015

Dennis, You can get the OUT parameters for a stored procedure by running it from the Data Source Explorer Run option for stored procedures. Locate the stored procedure in the DSE, right click and choose Run.

dennis.jones 2 comments Joined 08/13
29 Jun 2015

Thanks for the quick response. Now at least there is a way to run them, but I am wondering if there is any way to run it from Query Development so we dont have to drill through data source explorer to find the procedure. It is simpler/quicker for us to simply type call user_working.test('hello') to run our sp.
 
Thanks again for your help
 
Dennis

fgrimmer 660 comments Joined 04/09
29 Jun 2015

Dennis, We have an issue opened to resolve this problem in a future release.

DaveWellman 5 comments Joined 01/06
16 Jul 2015

I've recently found this and have started to use it.
Having used SQLA for @15 years, I am finding this article very useful.
Cheers, Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

chaitu.k 1 comment Joined 07/15
05 Aug 2015

Hello All,
Can any one help to download the SQLA tool to connect the teradata instance from external connection. Please provide the link to download for windows server.
Thx
______
 

fgrimmer 660 comments Joined 04/09
05 Aug 2015

Krishna, I believe SQLA is only available from the Teradata Patch Server as part of TTU install.

yq186003 2 comments Joined 07/15
10 Aug 2015

Hello all,
I am new to this forum and find this is a great place to post questions and share idea. I have some quesitons about posting on this website.
 
First, I am wondering how can I post my own question here instead of commenting other posts?
 
Second, I am currently using Tearadata Warehouse Miner. When I try to run analysis like: descriptive analysis, regression. It always shows the error message: No more room in database "my user id". I am wondering if I can solve the issue.
 
Your kind help is highly appreciated!
Best,
Yang

fgrimmer 660 comments Joined 04/09
10 Aug 2015

Yang, Please post your question to the Tools Forum (http://forums.teradata.com/forum/tools). In the Forum site, you can open a new Topic that will just be for your question.

yq186003 2 comments Joined 07/15
10 Aug 2015

Thank you so much! :)

Karam 25 comments Joined 07/09
25 Aug 2015

Very infomative arcticle .Thanks for sharing. One question for you
Is there any way to control - Max Display Row Count on users machine? Scenerio is that I want only 2000 rows to dispaly for all answerset for certain set of users. Is there any plugin or API which can be applied while installing SQL Assistant or Studio Express which can force this limit?

fgrimmer 660 comments Joined 04/09
25 Aug 2015

Karam, For Studio Express, this is controlled by the Result Set VIewer preference (default 2000) that users can change. Are you wanting a way to prevent a user from changing this preference value?

Karam 25 comments Joined 07/09
26 Aug 2015

Yes, one of analytics manager requirement is to allow only limited set of rows to a certain business group. I also tried TASM but it is dependent on explain estimates which can vary due to multiple factors like stats , joins, numerous view layers etc. In short , its not behaving the way it should because of mentioned issues. So wondering , if such filter can be hard applied on SQLA or Express.

fgrimmer 660 comments Joined 04/09
26 Aug 2015

Karam, Setting and locking preferences at install time is a feature that is planned for Studio 15.11 release. So are looking to be able to lock the Result Set Viewer 'Max Display Row Count'? Of course this does not limit the number of rows obtained in the database, just the number of rows displayed. 

Karam 25 comments Joined 07/09
26 Aug 2015

Thanks Francine. What are tentitive release dates for Studio 15.11 ?

Pat GoHabsGo 3 comments Joined 01/10
27 Aug 2015

Bonjour Francine,
 
Need some hep here , I'm trying to set Teradata Studio Express on my Mac OS X Yosemite to connect a generic JDBC database. I just can't get to find where to declare new profile. Your paper provides instructions for Windows, but none for Mac.
I'm leading VoltDB (in-memory Fast Data) integration with Teradata. Our partnership was announced in June.  I would like to connect Studio to both databases (Teradata and VoltDB) .
 
Can you help!
 
Personal email pberube@voltDB.com

fgrimmer 660 comments Joined 04/09
27 Aug 2015

Pat, To create a Generic JDBC connection profile, you want to first add (check the box) 'Generic JDBC' to the list of Connection Profile Types, on the New Profile Connection Types preference page. Then select that type when creating the Connection Profile. It will want the location of the JDBC driver jar. Also, you will want to use the SQL File Editor (instead of the Teradata SQL Editor) and result sets will go to the SQL Results view instead of the Teradata Result Set Viewer.

mojave 2 comments Joined 02/13
28 Aug 2015

I was initially really looking forward to exploring the capabilities of SQL Studio Express, but I’m going to have to pass due to the complexity of the Eclipse install, especially:
 
  Before letting Eclipse restart to finish the installation,
          remove the file plugins\org.eclipse.emf.ecp.view.swt.layout_versionNumber.jar
             - Locate your Eclipse directory, and delete plugins
               \org.eclipse.emf.ecp.view.swt.layout_<versionNumber>.jar
 
There’s no way that’s going to happen on a highly locked down corporate laptop.  I’ll check back in a year, maybe by that time Teradata will have a Studio Express installer that includes everything.
 

fgrimmer 660 comments Joined 04/09
30 Aug 2015

Paul, You have misunderstood. The steps you mention are for installing Teradata Plugin for Eclipse, where a user would have an existing Eclipse environment and they want to add the Teradata functionality. The install for Studio Express is very simple:
1. Unzip the download file
2. Run the setip.exe.
The only pre-requisite is that you install the Java Runtime Environment 1.6, 1.7, or 1.8.
Enjoy.

wk514079 3 comments Joined 10/10
23 Oct 2015

I have questions about the system font and formatting SQL result sets.
Is there a way to change the pre-configures system font ?
I would like to change the font of the result sets permanently. So far I had to make the change for each result set separately, a bit of a cumbersome activity.
Werner Paul
werner.kohs@teradata.com

fgrimmer 660 comments Joined 04/09
23 Oct 2015

Werner, You can change the font for the different displays in Studio/Studio Express via the Colors and Fonts Preference Page. Refer the section above called 'Changing the Font' under 'Using the SQL Editor/Query Window'. The Result Set Viewer font uses the Dialog Font under the Basic folder.

wk514079 3 comments Joined 10/10
28 Oct 2015

Thanks Francine.
Werner Paul

pj.routledge 5 comments Joined 04/08
28 Oct 2015

I often want to peruse one or more tables to find particular data or get familiar with it and I use the Data | Sample Contents menu item (on the right-click context menu for tables in the Data Source Explorer).
But the column names in the results are prefixed with the table name and are left justified. So instead of seeing the useful part of a column name, such as 'Address', I see something like 'customer_ord...' repeated along the column headers.
Is there a way of displaying column names only when sampling tables? I haven't found a setting or preference that does it.
Peter

fgrimmer 660 comments Joined 04/09
28 Oct 2015

Peter, The columns names are from the ResultSet metadata returned for the result set by the JDBC driver. The only time I am seeing that it is prefixed by the table name is for Hive queries. Are you perusing HDP system, Teradata, or Aster?

pj.routledge 5 comments Joined 04/08
28 Oct 2015

Thanks for the prompt response. It's HDP.
So, I gather it's Hive returning the fully qualified names.

fgrimmer 660 comments Joined 04/09
28 Oct 2015

Yes as fully qualified names.

mannkothari 1 comment Joined 01/16
20 Jan 2016

Hi - 
I am using SQL Assistant 14.10.0.02, and i want to chnage settings for multiple answerset. Currently its opening in multiple tiles, making 4 tiles - 1 for Query,1 for History and 2 for Answerset(more depend on queries). How can we merge Answer set in one tile with multiple Tabs, the way multiple Query window works. Please guide.

fgrimmer 660 comments Joined 04/09
20 Jan 2016

Manish, I am not an expert on SQLA. Please try posting your question to the Tools Forum. thanks.

cmorrisx 2 comments Joined 10/10
25 Jan 2016

When can we expect SSO to work in Studio Express? I get a pretty long exception error when I try to use Kerberos and no username/password. In another thread  you suggested modifying the registry but we simply do not allow our users to edit their registry.

fgrimmer 660 comments Joined 04/09
28 Jan 2016

Cameron, As you mention, the process does require the Windows user to modify a setting in the registry. We have your RFC request for this to be more automatic and will access from development and product management to see when it can be delivered.

huanged 2 comments Joined 04/16
21 Apr 2016

Hi Frgrimmer,
 
This is really cool!
I am wondering if the fearture of "SQL Statement Outline Marks" is available for other database source, e.g. Oracle. I tried in SQL Filer Editor connecting to Oracle, but there is nothing shown in Outline view.
 
Regards,
Edgar
 
 

fgrimmer 660 comments Joined 04/09
22 Apr 2016

Edgar, So for the other database types, we include the "enablement" modules from Eclipse to provide the SQL development environment within Studio. In the future, we are looking at extending this functionality, like we have done with Teradata database types.

huanged 2 comments Joined 04/16
25 Apr 2016

Hi Fgrimmer, thanks for reply. Is there an article to instruduce how to enable these modules in Eclipse?
I am keen to see this feautre to be extended :)

fgrimmer 660 comments Joined 04/09
25 Apr 2016

I am assuming you have already created the connection profile for the database, such as Oracle, DB2, or SQL Server. For now you have the basic SQL File Editor and a Result set window to view the results. We have requests for additional functionality such as the result set viewer, loading data into tables, and moving data to Teradata database. What additional features would you be interested in?

ocirej23 3 comments Joined 04/11
25 Apr 2016

Hi fgrimmer, I would like to know the performance comparison of TD Studio Export Mode vs using SQL Assistant. Would it be much faster exporting data set using TD Studio? Thank you in advance.

fgrimmer 660 comments Joined 04/09
26 Apr 2016

jerico, We have not run performance tests specifically comparing exporting data. But for Studio and large tables, we are taking advantage of the JDBC FastExport option.

ocirej23 3 comments Joined 04/11
26 Apr 2016

Thank you. Just a follow-up question, when exporting large data set, will TD Studio automatically use JDBCFastExport or it needs to be enabled first?

fgrimmer 660 comments Joined 04/09
27 Apr 2016

It will automatically use JDBC FastLoad option based on the table size.

03 May 2016

Hi Fgrimmer,
It was a nice article. 
I am facing few challenges while exporting/importing huge tables.I noticed that Teradata studio is not using fastexport while exporting the data, it's taking 20 -30 minutes for exporting a table having 2 million records. Please let me know If I would need to change the settings to have the studio to use fastexport/fastload .

Sudheer Vankayala

fgrimmer 660 comments Joined 04/09
03 May 2016

Sudheer, It will pass the JDBC FASTLOAD option to the JDBC driver for tables with more than 100,000 rows, but if there are column types not supported by the FastLoad protocol, the JDBC driver will revert to batch load. What does the DDL look like for your table?

04 May 2016

I can just see a DDL and a SELECT statement from source server(I don't fastload/fastexport scripts). I would need to install any Teradata utilities on my local drive (the drive am running Teradata studio)? 

Sudheer Vankayala

04 May 2016

Adding to above one,I get the below error.

Copy Table Data Task

Opening connection...

Starting Export...

Starting Load...

Writing data...

Export Error Occurred: Message = Pipe closed

Export Failed 

CopyJob: Error trying to copy data from DBKUPD01.tduns_fact_marketd15_Jun161 : Pipe closed

Sudheer Vankayala

fgrimmer 660 comments Joined 04/09
04 May 2016

Sudheer, You don't have to configure FastLoad scripts. It uses the JDBC FastLoad option, assuming not column data type issues.
The failure above can happen if the database you are loading into does not respond and causes the pipe to close on the export side.  Does this happen each time or temporary performance issue on loading database? Also, what version of Studio are you running?

04 May 2016

fgrimmer,If I try giving a filter condition it loads fine. I face the said error when I go for full table refresh(2 million recs). For your question,it happens all the times and am using Teradata Studio 15.10.

Sudheer Vankayala

fgrimmer 660 comments Joined 04/09
04 May 2016

Sudheer, Can you open an incident with T@YS so we can collect your error logs and configuration information to look further into this problem?
You mentioned using a filter on the data transfer. Is that on the column or data? Is that just for table load? Are you able to use the filter for table copy?

05 May 2016

I wil raise a ticket.
Actually I was able to export and load the data after installing utilities in my local drive. But it's clearly visible in my case that Teradata studio was using regular EXPORT/IMPORT options as in SQL assistant. It took me around 45 mins to load 2 miliion records.
 

Sudheer Vankayala

fgrimmer 660 comments Joined 04/09
05 May 2016

Sudheer, Thanks. Please attach the DDL for your table, if that is possible, so we can see if there are any issues with column datatypes. You mention installing on local drives.  How were you running Studio prior to that?

StevenSchmid 13 comments Joined 07/11
28 Jun 2016

Hi Francine
I'm creating a fairly simple stored procedure in Studio.  When I compile it, if there are errors, it doesn't display the line numbers like SQLA does, so not easy to debug, as just get "Syntax error: Invalid SQL statement".
I also have a case that the stored procedure compiles fine in SQLA, but not in Studio.
Have you got ant suggestions.
Cheers
Steven

Steven Schmid
Teradata DBA
Canberra, Australia

StevenSchmid 13 comments Joined 07/11
28 Jun 2016
"New Teradata Studio Administration User Experience" POST:

Randy, I am seeing the statement fails with [3606 : 42000] Syntax error: Invalid SQL Statement when the comment comes before the REPLACE PROCEDURE.
&nbsp;I found a similar post on our Studio Forum and a detailed explanation from Tom Nolan:
&nbsp;-------------------------
&nbsp;The Teradata Database requires a special wire protocol to be used for the CREATE PROCEDURE and REPLACE PROCEDURE statements, that is slightly different than the wire protocol used for all other SQL statements.
&nbsp; 
&nbsp;Because of the need to use a different wire protocol, the Teradata JDBC Driver must examine the SQL request text and determine whether or not each SQL statement is CREATE/REPLACE PROCEDURE.
&nbsp; 
&nbsp;If you place a comment before the CREATE/REPLACE PROCEDURE keywords, then you will confuse the Teradata JDBC Driver, it won't recognize the SQL statement as CREATE/REPLACE PROCEDURE, and then it will send the SQL request to the Teradata Database using the normal wire protocol, and you will get the 3706 syntax error.
&nbsp; 
&nbsp;For comparison, BTEQ deals with this issue by forcing the user to put a CREATE/REPLACE PROCEDURE in a separate file, and use the special .COMPILE command to submit the CREATE/REPLACE PROCEDURE command to the Teradata Database. That is also a clunky workaround.
&nbsp; 
&nbsp;There is an outstanding RFC 94094 against the Teradata Database to remove the need for the special wire protocol for CREATE PROCEDURE and REPLACE PROCEDURE statements.
&nbsp;--------------------------------

Hi Francine
I dug a little deeper and found the post above.  When I removed the comment at the start of the SP, the compile worked, and if an error the line number was displayed in the error message.
Cheers
Steven
 

Steven Schmid
Teradata DBA
Canberra, Australia

fgrimmer 660 comments Joined 04/09
28 Jun 2016

Steven,
Glad you found the answer! Cheers.

You must sign in to leave a comment.