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:
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.
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.
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.
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:
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:
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:
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:
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.
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.
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.
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.
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’.
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.
In SQL Assistant:
In Studio Express:
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.
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.
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.
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.
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 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.
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.
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, ).
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.
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’.
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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.
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:
Option on the Macintosh),
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.
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.
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.
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?
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?
The Data Source information can be supplied with a silent install. Documentation will be posted soon.
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.
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).
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.
I've been waiting for this. Thanks. Is there a way to perform a case in-sensitive filter search on the Teradata SQL history?
Jorge, We have an Issue opened to get this resolved in our next release. Thanks for the feedback.
Sam, The Find for Teradata SQL History has an option to 'Match Case' so if that is unchecked, it should be case in-sensitive.