Teradata Studio is a client based graphical interface used to perform database administration tasks on Teradata and Aster Databases, as well as copying data between Aster, Teradata, and Hadoop Database Systems. Teradata Studio is built on the Eclipse® Rich Client Platform (RCP), taking advantage of the RCP framework for building and deploying native GUI applications. It extends the Eclipse Data Tools Platform (DTP) to enhance Teradata Database access. Teradata Studio can be run on Windows, Linux, and Mac OSX operating systems. The Eclipse platform is designed as an open tools platform but architected so that its components (or a subset of its components) can be used to build RCP applications. This allows Teradata Studio to benefit from the many high quality Eclipse features available while focusing on value-add for Teradata.

With the Studio 15.11 release, a new user interface was introduced in the Administration Perspective. Refer to the article, New Studio Administration User Experience for more information.

In addition to this article, there is a Teradata Studio User Guide document now available on the Teradata Studio Download page.

Teradata Studio includes the following key features:

  • Task-based Perspective Display.
  • Modular display that allows you to move panels around, creating a custom look and feel.
  • Connection Profile Wizard to create connection profiles using the embedded Teradata or Aster JDBC Driver or Hadoop connection service.
  • Data Source Explorer to view database objects, such as databases, schemas, tables, macros, stored procedures, map reduce functions, user-defined functions, user-defined types, views, and triggers.
    • Add Database - Allows you to add a single Teradata Database or User to the DSE tree, one at a time.
    • Set Root - Allows you to set the root of a hierarchical tree display.
    • Create Filter - Allows you to filter out objects from the Data Source Explorer folders.
  • SQL Editor to create, modify, format, and execute SQL queries.
  • SQL Query Builder to visually build and edit SQL queries (Teradata only).
  • AutoCommit options allows user to manually control autocommit of SQL Statements.
  • Result Set Viewer that displays result set data, allowing the user to select rows and cells, copy and format data, sort, hide, and move columns, and search for result set data values
  • Export Result Sets to Excel, XML, or text files
  • SQL History to maintain a historical record of submitted SQL queries with timings and status information
  • Object List Viewer to display the list of objects and open forms to display more detail information about Databases, Users, Schemas, Tables, Views, Macros, Stored Procedures, User Defined Functions, User Defined Types
  • Drop Databases, Schemas, Tables, Views, Stored Procedures, Macros, or User Defined Functions
  • Data Transfer Wizard to transfer table data between Teradata, Aster, Hadoop, and the external file system.
  • Data Export wizard to extract data from the Teradata or Aster Database to a file on the desktop.
  • Data Load wizard to load data from the desktop file system to the Teradata or Aster Database.
  • Smart Loader to easily load delimited text or Excel files into Teradata or Aster.
  • Table Data Editor to easily Update, Insert, or Delete data for a table.
  • Transfer History View to display the information about data transfers.
  • Copy Object Wizard to copy Teradata objects from one Teradata system to another.
  • Hadoop Transfer Wizard to transfer Hadoop tables between Teradata and Hadoop (aka Smart Loader for Hadoop).
  • Compare Objects Wizard to compare two Teradata or two Aster (6.10) database object definitions
  • Generate DDL to display the DDL for a given database object and its contained objects. This feature is provided in Teradata and Aster 6.10.

Teradata Database Only Features -

  • Teradata Administration forms to create and view Teradata objects: Databases, Users, Tables, Views, Stored Procedures, and User Defined Functions
  • Dialogs to create User Defined Types
  • Modify and Drop Actions: Databases and Users
  • Administer Roles and Profiles
  • Grant or Revoke of access and system rights
  • Manage Privileges on database objects
  • Copy Table, View, Stored Procedure, Macro, or User Defined Type definitions to another database, or to another system
  • Rename Tables, Views, Stored Procedures, Macros, or User Defined Functions
  • Data Source Explorer menu options to run Stored Procedures, Macros, and User-defined Functions
  • Move space from one database to another
  • Find objects in the Data Source Explorer
  • Set up the rules for Query and Access Logging
  • Administer Foreign Servers (Create, Drop, Alter, and View)
  • Administer Secure Zones (Create, Drop, Modify, and add Zone Users)

Aster Database Only Features -

  • Aster Administration forms to create and view Aster objects: Databases, Users, Roles, Schemas, Tables, and Views
  • Grant and Revoke Privileges for Aster objects
  • Aster Analytical Templates that provide the SQL syntax for the support analytical functions within Aster.

For administrative functions, Teradata Studio will provide a preview of the SQL statements and allow the user to save the SQL to a file or display it in the SQL Editor.

Usage Videos

For detailed step-by-step demonstration of the Studio features, refer to the Studio Usage Videos article.

Studio Perspectives

A perspective is a visual container for a set of views, menus, toolbars, and actions.  Each perspective provides a set of functionality aimed at accomplishing a specific type of task or works with specific types of resources. Teradata Studio includes three perspectives: Query Development, Administrator, and Data Transfer. The Query Developement perspective focuses on SQL query developement tasks. The views displayed within the Query Development perspective include the Data Source Explorer, Project Explorer, SQL Editor, Result Set Viewer, and SQL History View. The topmost menus provide the user with tools to create and edit SQL statements.

The Administration perspective focuses on database development and administrative tasks for your Teradata Database.  The Administration perspective includes the Navigator View, the Filter View, and the Object List Viewer. The Navigator View allows you to select the connection profile for administering objects. The Filter View allows you to filter the objects displayed in the Object List Viewer. The Object List Viewer provides a list of the objects and 'Open' actions to view more detailed information about the objects. The Object List Viewer toolbar and menus include actions to create and administer database objects.

The Data Transfer perspective provides support for transferring data and objects in Teradata and Aster Databases, and Hadoop systems. The Data Transfer perspective includes the Data Source Explorer, Transfer View, Teradata Progress View, and Transfer History View. The Transfer View provides a drag and drop palatte for copying table objects between Teradata and Hadoop systems and copying all objects between Teradata systems. The Progress View shows the status of active data transfer jobs. When the data transfer job completes, an entry is stored in the Transfer History View.

Modular Display

As mentioned above, within each of the Studio perspectives are views that provide the modular display for the perspective.

The Data Source Explorer View panel contains the database object tree display. It is here that you can create connection profiles to your Teradata Database, Aster Database, or Hadoop systems and interact with the database objects.

The Project Explorer View panel provides a hierarchical view of the Projects and their resource files It is here that you can select SQL files for editing or create new SQL files.

The SQL Editor is displayed when a SQL file is opened. Initially, the SQL Editor is opened to provide an area to enter SQL statements. The user must provide a connection profile in order to execute the SQL statements. Double clicking on a file in the Project Explorer will cause the file to be opened in the SQL Editor View.

The Teradata SQL History contains the history information when executing SQL statements and running database routines, user-defined functions, and triggers.

The Navigator View provides a hierarchical tree view of a selected connection profile with categories of objects to display in the Object List Viewer.

The Filter View allows users to create filters to confine the list of objects displayed in the Object List Viewer.

The Object List Viewer displays a list of objects based on the category selected in the Navigator. Toolbar and menu actions are provided to display more detailed information about database objects or create and modify database objects.

The Teradata Result Set Viewer displays the result set data. Individual Result Set tabs are created for each result set. The Result Set Viewer also contains a toolbar with actions that can occur on the result set data, such as saving, exporting, or printing the result set data.

The Transfer View provides a mirror display of the Data Source Explorer object tree and used to drag and drop database tables or objects for copying from one system to another.

The Teradata Progress View provides a display of the active data transfer jobs.

The Transfer History View provides a display of the completed data transfer jobs.

Studio Toolbars

Teradata Studio contains a toolbar that provides buttons to allow quick access to dialogs for creating database objects or invoking functions that are used while creating and modifying SQL files.

Teradata Studio toolbar is divided into the following sections:

File Actions: The File Actions toolbar buttons are used when editing a SQL file. They include Save, SaveAs, Cut, Copy, Paste, Undo, Redo, Print, and Show Whitespace.

 

SQL Tools Toolbar — The SQL Tools toolbar buttons are used to perform actions on the SQL statements within the SQL Editor. They include Execute as Individual Statements, Execute as One Statement, Execute a Single Transaction (Aster Only), Explain, Format, Prompt for Notes, Code Assist Auto-activation. SELECT Statement and SHOW DDL(Teradata Only) are activated when a table is selected in the DSE.

Data Tools Toolbar — The Data Tools toolbar buttons provide actions that are performed on a database table, such as Show Row Count, Teradata Load, and Teradata Export. Teradata Load and Export are enabled for Teradata Connections only.

 

Artifact Maintenance Toolbar - The Artifact Maintenance toolbar buttons provide actions to Move Space from one database to another, Copy and Compare objects, Rename objects, Drop objects, and Delete Databases. These actions are enabled for Teradata Connections only.

 

Find Actions Toolbar - The Find Actions toolbar provides quick access to the Find Object and Find Next Object. These actions are enabled for Teradata Connections only.

 

Navigation Toolbars — The Navigation toolbar buttons are used to reference locations within the SQL file. They include Next Annotation, Previous Annotation, and Last Edit Location.

A view can also contain its own toolbar and drop down menu. These toolbar buttons and menu items provide actions on the data managed by the view. Included in the view toolbar are buttons to minimize, maximize, and restore the view. Maximizing the view will cause the view to take over the entire Teradata Studio window display. Click the Restore button to return the view back to its previous display.

Customize the Display

You can customize the look and feel of Teradata Studio by moving view panels within the main display window. A view can be moved by selecting the view and dragging it to a new location. As you move the view over the display, the mouse pointer will change depending on where you want to drop or dock the view. You can also drag the view outside the display area. The view will then become a "detached" view. You can also remove a view panel by clicking the "X" on the view tab. A view can be re-displayed by selecting the view from the Window>Show View option in the main menu. Or to return the perspective and views to their original display, select the perspective, then click Window>Reset Perspective...

You can also customize the Toolbars, Menus, and Command Groups of the display. Choose the Customize Perspecive... option from the Window menu.

The Customize Perspective dialog is presented for you to choose the Toolbar buttons, Menu options, or Command Groups you would like to change. Un-select the buttons or options so that they are not shown in the Teradata Studio's Query Development perspective.

You can customize the toolbar and menu items in the Object List Viewer via the Object List Viewer preference page. Uncheck the actions or action groups that you do not want to display. You can also change the double-click action in the Menu tab by right clicking on the action and choose 'Set as Double-Click Action'.

Creating Connections

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 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 Systems. 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, 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.

You can also cache schema metadata, saving time when loading the database objects in the Data Source Explorer. Click the Cache Properties button to select schemas (or databases) to cache.

              

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. You can also cache Aster Database schema metadata to save time loading the Aster database objects in the Data Source Explorer.

There are three options for creating Hadoop Connection Profiles:

  • Hadoop Generic System - The Hadoop Generic System profile supports migrating Hadoop connections from Studio releases prior to Studio 15.10. It is also used to support Cloudera Hadoop connections.  Hadoop Generic System connections are created using the WebHCat protocol to connect and discover database and tables information. It requires that the ConfigureOozie script is run on the Hadoop System.

             

  • Hadoop Hortonworks - The Hadoop Hortonworks connection profile provides additional options for connecting to Hortonworks Hadoop systems. It is based on the desired functionality between Studio and your Hadoop System: Knox Gateway (Secure connection), TDCH (Teradata data transfers), JDBC (creating and running SQL), or SQL-H (Hadoop to Aster data transfers). Note that the Knox Gateway option also supports JDBC connections. Click next to enter the Host name, Port number, User name, and Password, if required. The TDCH option is equivalent to the Hadoop Generic System connection profile described above.

            

  • Hadoop Cloudera - The Hadoop Cloudera connection profile provides additional options for connecting to Cloudera Hadoop systems. It is based on the desired functionality between Studio and your Hadoop System: TDCH (Teradata data transfers), JDBC (creating and running SQL), or SQL-H (Hadoop to Aster data transfers). Note that Cloudera uses the Impala JDBC driver that is bundles with Studio. Click next to enter the Host name, Port number, User name, and Password, if required. The TDCH option is equivalent to the Hadoop Generic System connection profile described above.     

            

Data Source Explorer View

The Data Source Explorer View panel contains the database object tree display. It is here that you can create connection profiles to your Teradata Database, Aster Database, or Hadoop systems and interact with the database objects. The Data Source Explorer view provides a hierarchical view of Teradata Database objects, nesting child databases and users within the parent database or user. Preferences are provided that allow you to choose which objects to load and display in the Data Source Explorer.

                  

Displaying Multiple Connections

With Studio, 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.

          

Connecting to Non Teradata Database

In Studio, 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 Studio, 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 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 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.

      

Creating Database Objects

You can easily create database objects in your Teradata Database using the Teradata Studio administration forms. First, switch to the Teradata Administration perspective to access the create and modify actions. The forms are accessed from the Object List Viewer toolbar and menu actions. For example, to create a new Table, choose (double-click) the Databases category in the Navigator. Locate the database in the Object List Viewer that you wish to create the table in and double-click. This will show you the list of tables and provide a toolbar action "Create Table''.

This will open a new 'Create Table' form below the Object List Viewer. First, in the General tab, enter a name for the table. Then select the Columns tab, press the Add button to add columns for your table.  Enter a column name and select the column type. The drop down list will present the list of valid column types for the Teradata version you are creating your table in.Click Add to create another column definition. Next, choose the column Groupings, Indexes, Constraints, or additional information for the table. Lastly, select the SQL tab to view the generated SQL. Press the Commit toolbar button o execute the CREATE TABLE statement. This will create the table in the database and cause the list of tables to be refreshed in the Object List Viewer.

Creating a SQL Statement (SQL Editor)

When Teradata Studio is launched, a SQL Editor area is provided for you to enter your SQL statements. First switch to the Query Development perspective. Once you have a connection to your Teradata or Aster database or Hadoop JDBC system, you can begin to enter SQL statements in the SQL Editor window.  Click the Save to Project button to save the SQL Editor to a file in the workspace or Export to File System to export the file to the desktop file system.

           

Another option to create a SQL file is through the Data Source Explorer. Within the Data Source Explorer, select your database connection and click the SQL Editor icon, , from the Data Source Explorer toolbar. This will create a SQL file in the SQL Editor with the name 'SQL Editor [n]' and the connection profile set to your database connection. You are now ready to enter your SQL statement. 

You can also open an existing SQL file, located in the Project Explorer, into the SQL Editor and associate it with a connection profile. SQL files can be imported into the Project Explorer from your local desktop file system. Right click on the SQL project and choose the Import menu option.

               

The SQL Editor provides a scratchpad for you to enter your SQL statements. As an option, you can enable the auto activation of code assist by clicking the  toolbar button. If this option is enabled, as you type your SQL statement, code assist will pop up annotations showing a list of expected keywords. If a database name is entered followed by a dot, the content assist will display the list of tables from your database for you to select from. (Note: By default, auto activation of code assist is disabled. Also, you must select a Teradata or Aster Database connection profile type in order for SQL code assist to work.)

Code assist will also provide a list of SQL Templates. At the start of the SQL statement, hit the Cntrl+SPACE keys and a pop up list of templates is displayed. Scroll down until you find the template you want and double click on it to insert the template into your SQL Editor file. Anchor fields are provided that you can navigate by tabbing. You can also edit or add your own SQL Templates via the SQL Editor Preferences page.

Parameterized queries can also be created in the SQL Editor. The query contains parameters indicated by question marks. When the query is executed, the user is prompted for the parameter value(s) in the Configure Parameters dialog. Named parameters (appending backslash ,'\',<parameter name>) allows the user to specify a name for the parameter. When the Configure Parameters dialog is presented, the parameter names will be used to reference the parameters.

           

 

          

The SQL Editor also provides syntax coloring for your SQL statements. You can change the colors of comments, keywords, identifiers, types, or other syntax items via the SQL Editor Preferences page.

When right clicking in the SQL Editor window, a SQL Editor Context menu appears with options to execute the SQL statement(s) in your SQL file.

                 

The following options are presented:

  • Execute all of the SQL statements
  • Execute the selected SQL statement
  • Execute the SQL statements as individual statements (with a commit occurring between statements)
  • Execute the selected statement as one statement
  • Execute the current statement where the cursor is located
  • Execute the SQL statements as a single transaction (Aster only)
  • Visual Query Builder (Teradata only)

You can also choose the Execute options from the SQL Editor or top level toolbar, , .

Manual commit and rollback of the SQL Execution is also provided via SQL Editor toolbar options.

An Autocommit checkbox is provided in the SQL Editor to allow the user to toggle Autocommit on or off. By default, Autocommit is ON. If the option is turned off, then the Commit and Rollback options are enabled for the user to manually control the commit of their SQL statements to the database or rollback if an error occurs.

SQL Query Builder

Teradata Studio provides a graphical interface for creating SQL statements for Teradata, referred to as the SQL Query Builder. You can edit an existing statement using the SQL Query Builder by selecting the statement within the SQL Editor and choosing the ‘Edit in SQL Query Builder…’ menu option. (Refer to figure above).

    

In addition, you can open a SQL file in the SQL Query Builder via the Project Explorer menu item, Open With...>Teradata SQL Query Builder.

This option will open the SQL Query Builder inside the SQL Editor view. This allows you to drag and drop the table objects from the Data Source Explorer into the diagram area of the SQL Query Builder.

You can also add additional tables and table joins by right clicking in the SQL diagram area and select Add Table or Create Join options. When adding a table, a list of tables and views is presented for you to choose from. Click OK to save your SQL statement back to the SQL Editor.

Data Transfer Wizard

Teradata Studio supports Teradata's Unified Data Architecture (UDA) by providing access to Teradata, Aster, and Hadoop via a single client tool. The Data Transfer Wizard provides a single dialog for transfering data between Teradata, Aster, Hadoop, and the external file system.

              

You can use the Data Transfer Wizard to invoke the Smart Loader on Teradata or Aster Databases, load or export table data for Teradata or Aster Databases, and Copy table objects and data between Teradata Databases or between Teradata databases and Hadoop systems or Hadoop systems and Aster Databases.

      

For example, when choosing to transfer data from a Teradata table, the Data Transfer Wizard will provide options to transfer the data to various destinations, such as: exporting to an external file, transfering to another Teradata system or table, or transfering to Hadoop.

Extract and Load Data

The Data Source Explorer provides an option to extract and load data for Teradata and Aster tables using Load Data Wizard and Export Data Wizard. Large Teradata tables and files will use the JDBC FastLoad or FastExport option depending on the number of rows and column data types supported. Otherwise, the JDBC load batch interface is used, and as well for Aster tables. Right click on the table and select the option from the Data drop down context menu.

             

Selecting the Load Data... option invokes the Data Transfer Wizard. If you are not in the Data Transfer Perspective, you will be prompted to switch to the Data Transfer Perspective where the Teradata Progress View and Transfer History View are displayed.

                  

The Data Transfer Wizard will provide the list of sources for the table load. Choose the External File option to launch the Load Data Wizard.

               

The Load Data Wizard will prompt the user for the file to load and define the necessary file format needed to identify columns when loading data into the table. File format options include the column delimiter (comma, semicolon, space, tab, fixed width, or vertical bar “|”) along with the character string delimiter (double quote, single quote, or none), OS Line Separator, File Encoding, and starting row number. If data already exists in the table, the load data will append or replace the existing data. You can also indicate whether column labels are in the first row, as well as whether to lock the table during the load process.

A preview of the input file is also provided, using the file options specified. This will help you make sure you have the correct file options before starting the load.     

   

        

Selecting the Export Data option invokes the Data Transfer Wizard with the list of destination options for exporting the data to. Choose the External File to launch the Export Data Wizard.

              

The Export Data Wizard prompts the user for similar information as the Load Data Wizard. Choose the Output File, File Type and File Options.

               

The status of the data load or export job is displayed in the Transfer Progress View. When the job has completed, an entry is placed in the Transfer History View. Refer to the section below regarding the Transfer History View.

Smart Loader

The Smart Loader feature allows the user to load a text delimited or Excel file into a Teradata or Aster table. The Smart Loader is invoked from the Tables folder within the destination database via the Data Transfer Wizard.

        

 

From the Data Transfer Wizard, choose the Source Type as External File (Smart Load) to launch the Smart Load Wizard.

        

 

Like the other load data utilities, the Smart Loader will prompt the user for the input file.

         

Next, the Smart Loader will scan the file to determine the "best" column types and present these to the user.

          

Clicking the ellipses button (...) provides an option to modify the column definition.

          

Next, the table DDL is presented. The user can choose the 'Show in Editor' option to save the table DDL to the SQL Editor and manually change the table definition. Otherwise, clicking Finish will execute the DDL statement and begin loading the data into the newly created table.

         

The status of the Smart Load job is displayed in the Transfer Progress View. When the job has completed, an entry is placed in the Transfer History View.

Transfer History View

The Transfer History View displays the information about the data transfer, such as the name (given by the Export, Load, or Copy wizards), timestamp, source and destination systems, status, duration, rows transfered (if known), note, and summary. This information is stored in an embedded Derby database. You can sort and filter the rows and columns, edit the name or note. The data transfer job can be re-executed by selecting the entry and pressing the Re-execute toolbar button. The output of the transfer data job is displayed by pressing the Job Output button.

Copy Object Wizard

The Copy Object Wizard allows you to copy Teradata database objects from one Teradata Database system to another. Click here to view the Copy Object Wizard article and read more about copying Teradata database objects.

Hadoop Transfer Wizard (aka Smart Loader for Hadoop)

As with the Teradata Copy Wizard, you can transfer tables between Teradata Databases and Hadoop Systems or Hadoop Systems and Aster Databases. And, as with the Teradata Copy Wizard, there are two ways to invoke the Hadoop Transfer Wizards, drag and drop between the Data Source Explorer and the Transfer View or right click on the table and choose Data>Export Data or Data>Load Data. Click here to view the Smart Loader for Hadoop article and read more about transfering tables between Teradata, Aster, and Hadoop.

Table Data Editor

The Table Data Editor provides an enhanced feature to edit tables within the Data Source Explorer for Teradata and Aster tables. The data in the editor is broken up into pages, which allows for efficiency in updating large tables. Along with the standard insert, update, and  delete, data filtering, sorting, and in-memory filtering are also supported. The user also has the option of exporting the data to Excel or Text files.  It may be advantageous to export the data before it is modified.

The Teradata Table Data Editor is accessed via the Teradata menu. Select the table in the Data Source Explorer and right click and choose the Data>Edit Data... option.

The Table Data Editor will present the current rows of data from the table in a table grid display within the SQL Editor. You can click on a cell in the table grid to modify the cell contents or add a new row by clicking on the <new row> entry. A toolbar contains navigation aids and action buttons to control the row displayed, number of rows per page, page controls, as well as Filter, Sort, and Refresh actions.

Column operations are provided to hide or resize columns, find, replace or show values.    

           

Row operations are provided to copy and paste row data, insert and delete rows, or mark rows for export.

            

Row background colors are used to indicate whether a row has been updated (rose), inserted (yellow), or deleted (grey strike-through). These colors can be changed, along with other Table Editor options in the Table Data Editor Preference Page.

           

Depending on the edit option, a SQL UPDATE, INSERT, or DELETE statement is created. SQL Preview toolbar button, , is provided to show the SQL commands that will be run. Click the 'Commit Changes to the Database' toolbar button, , to run the SQL and commit the changes. A status message is displayed to indicate the actions committed to the database.

Project Explorer View

The Project Explorer View panel provides a hierarchical view of the Projects and their resource files. It is here that you can select SQL files for editing or create new SQL files. SQL files can also be imported from or linked to your file system. When Teradata Studio is first installed, an initial project called 'SQL' is created for you. You can create additional projects using the New>Project... menu option.

               

Within the Project Explorer, you can also choose an option to compare two files. The Compare Option displays the two files side by side with buttons that allow you to jump from one difference to another or move changes from one file to another. This is very handy when comparing SQL files or result set data.

The Project Explorer also provides an option to execute one or more SQL files from list of files.

          

Result Set Viewer

The Result Set Viewer displays the result set or parameter data from executing SQL statements and running database routines, such as stored procedures, macros, user-defined functions, and triggers. The Result Set Viewer provides an enhanced display of the Result Set data. It displays the result sets as tabs within the Teradata Result Set Viewer, allowing the user to select rows or cells from the Result Set data table grid. The Result Set Viewer provides menu options to hide or show all columns, move and sort columns, find a value within the result set data, perform aggregate functions on selected numeric columns, filter column data, format cells, and copy cells. Select a row or cell and right click to display the menu options.

       

Multiple result sets can be displayed as tabs or in a side-by-side sash display.

Large Objects (LOBS) can also be displayed in the Result Set Viewer. When a query result is being processed that contains an LOB result column, a dialog appears for the user to specify where and how to store the LOB values on the file system.

    

A toolbar is also provided with options to rename the Result Set tab, show the SQL associated with the result set, switch the display between text and grid display, switch the display between tabs and side-by-side sash display, and save, export, and print result set data. SQL result data can also be saved to a file within your Project or exported to an external file in the file system.

If the Export or Export All option is chosen, the Export Result dialog is displayed. Select a file name, format, and export options. Then click the finish button to create the exported file. The Save option presents a similar dialog.

    

The following screen capture shows the result data with totals on integer columns as exported to an Excel file.

Result Set Export Mode

Studio provides an option to automatically export result data to an external file.  This will bypass displaying the result set data in the Result Set Viewer and instead invoke the Teradata Export Wizard to send the result set data to an external file. To select this options, 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.

SQL History

The SQL History View provides an enhanced display of the SQL History information. As SQL statements are executed from the SQL Editor or functions are run from the Data Source Explorer, an entry is placed in the SQL History. The SQL History view displays the data in a grid format, allowing the user to select rows and cells. The SQL History provides extensive information about the SQL execution, such as execution elapsed time, DBMS time, and fetch time, database server, row count, parameter display for macros and stored procedures, SQL statement, and SQL statement type. It provides options to copy cells or rows, edit the 'Note' or SQL for the SQL History entry, search for result history data, and re-execute SQL statements. Choosing the 'Edit SQL' option will open a SQL Editor file with the selected history entry's SQL statement. This allows users to further fine tune their SQL statements.

The SQL History view also provides a toolbar menu with options to delete rows or all of the history entries, filter columns, format cells, and sort columns, The SQL History uses an embedded Derby Database to manage the SQL History entries.

               

Object List Viewer

The Object List Viewer displays the list of objects and options to 'Open' an object to display more detailed information about the database object. When in the Teradata Administration perspective, select a connection and category from the Navigator View. You can expand the Databases category in the Navigator or drill into the list within the Object List Viewer.

Double-click on a database (or schema) to display the list of tables. Choose the toolbar or menu action to show other objects for a database or schema.

 

 

Compare Objects

Teradata Studio provides a wizard to help you compare the DDL of two Teradata or two Aster (6.10) objects. First select the Query Development perspective. Then choose a Teradata or Aster database object in the Data Source Explorer, right click and choose Teradata>Compare With... or, for Aster, choose Aster>Compare With...

This will invoke the Compare Objects Wizard for you to choose the object to compare with. Select the connection profile and database. If you are comparing schemas, tables, macros, views, stored procedures, user-defined functions, user-defined types, or indexes, you will be prompted to select another object of the same type.

If you are comparing database or user objects, click Next to choose child objects that will also be generated for the comparison.

Click Finish to display the two objects in the Compare Editor. The Compare Editor will provide toolbar buttons to navigate differences between elements in the two windows.

 

Generate DDL

Studio provides an option to generate the DDL for a database object and its contained objects. This feature is provided for Teradata and Aster (6.10). To use this feature you must be in the Administrator perspective. In the Data Source Explorer, select the object you wish to generate the DDL for, right click and choose the Teradata>Generate DDL, or Aster>Generate DDL menu option.

The Generate DDL wizard will prompt you to choose CREATE and/or DROP statements. Next, you will select the contained objects you wish to generate. In the example below, an Aster Schema object was selected in the Data Source Explorer. The user has the option to select MR Functions, Schemas (itself), Tables, and Views to include in the DDL.

Click Next to generate the DDL for the selected object types.

Check the 'Open DDL file for Editing' to save the generated DDL to a Scripte.SQL file in the SQL Editor. Click the Next button to display Summary information, then click Finish to close the Generate DDL Wizard.

Aster Administration

Studio provides an administration user interface for Aster objects: databases, users, roles, schemas, tables, and views. Aster administration is invoked from the Navigator and Object List Viewer. Choose the Administration Perspective and select an Aster connection profile from the Navigator. Choose the category of the object you wish to administer. You can expand the list of Databases and Schemas and double-click to display the list in the Object List Viewer. The Object List Viewer will provide toolbar and menu actions to Open, Create, or Drop objects. To modify an object's privlieges, 'Open' the object and select the Privileges tab.

Aster Analytical Templates

Aster Analytical Templates are provided for Aster users to help with the syntax when writing the SQL for Aster Analytical functions. Below are the steps to display the templates:

  1. You must first switch to the Query Development perspective
  2. Open a SQL Editor for your Aster Database
  3. Go to Window>Show View>Other... and choose General>TemplatesView and click OK

                         

  1. The TemplatesView is opened. You can drag the view to the Data Source Explorer/Project Explorer area so that it displays next to the SQL Editor.
  2. Select a template and you can preview the text in the Preview window below the list of templates.
  3. Double click on a template to copy its text to the SQL Editor window.

Help

Help is provided by clicking on the Help>Help Contents in the main toolbar.

Conclusion

Teradata Studio provides a multi-platform, administration tool for supporting your Teradata Database. Dialogs are provided to guide the user through creating and administering database objects.

To get up and running with Teradata Studio use the Download links above or go to the Tools Download area, pick your OS and take a look at the README.

To ask questions or discuss issues, refer to the Teradata Studio Forum and post your question.

Discussion
teraJava 10 comments Joined 08/12
29 Apr 2015

@fgrimmer, thank you but I cant see a download link. Also Teradata Studio seems to have dropped off from the Tools section in the downloads area. Express' link is still available.
So to get to TD Studio, we need to now use 'All' section instead. is this intentional?

fgrimmer 660 comments Joined 04/09
29 Apr 2015

@teraJava, It is intentional. We found a problem that needs to get resolved in the packages. We will post them as soon as possible. Sorry for the inconvenience.

pblaszka 2 comments Joined 04/11
13 May 2015

@fgrimmer, I am getting a Null pointer Exception when attempting to pull a filtered object view into Data Source Explorer.  The Error is DTS0129 Badly Behaved View Handler
The Log file shows the Following for the top of stack dump:
at com.teradata.datatools.dao.TeradataCatalongDAO.getDatabaseOnlyInclause(TeradatacatalogDAO.java:1070)
 

fgrimmer 660 comments Joined 04/09
13 May 2015

Paul, please open an incident for this and attach the logs so we can see what is happening. thanks.

pblaszka 2 comments Joined 04/11
13 May 2015

@fgrimmer, will do.

18 May 2015

How can I find databases/user in Teradata Studio "Teradata Administrator"?
Users are interested to go to particular database, which is under different level of Parent child hierarchy. E.g. DBC --> SYSDBA --> All_DBS --> TableDB --> SalesDB.
In stand alone ODBC based "Teradata Administrator", I can press Ctrl+F and search for "SalesDB", it will open SalesDB in databases explorer. Same functionality doesn't work in Teradata Studio. It gives me result as "No Databases and Users Name Objects found for Search text: SalesDB".
Is it bug?
I am using "Teradata Studio 15.00" with database TD 14.10.04.03.
Any comments and solution will be helpful.
Thank you,
Shrinivas Sagare

Thanks & Regards,
Shrinivas Sagare

fgrimmer 660 comments Joined 04/09
19 May 2015

Shrinivas, The Find command  should open the DSE to the database or user, given the Object Type of Databases and Users Name that match the name SalesDB, relative to where you start the search. Where in the DSE did you start the search?

19 May 2015

Hello Francine,
Thanks for your response. I started search from SYSDBA. Even I did as below
Select SYSDBA on DSE --> Right Click --> Teradata --> Find Object (Object Type Databases and User Name) -- Search
I got the same result as "No Databases and Users Name Objects found for Search text...".
 

Thanks & Regards,
Shrinivas Sagare

fgrimmer 660 comments Joined 04/09
19 May 2015

Shrinivas, Thanks, I'll try to recreate the problem.

21 May 2015

Hello Francine,
Did you get a chance to check it? Do I need to open incident about it?
 

Thanks & Regards,
Shrinivas Sagare

fgrimmer 660 comments Joined 04/09
22 May 2015

Shrinivas, I have tested the Find with nested databases and it was able to find the database. Can you try the Find>Advanced option (on the Databases folder) and specify 'Starts with the characters' and enter SalesDB and check 'All Databases and Users Name' and let me know what it returns in the Find Objects Results tab? thanks.

26 May 2015

Francine,
Thank you for your suggestion. I did the same way search as your suggested but it didn't worked for me. I got same answer "No Databases and Users Name Objects found for Search text..."
 

Thanks & Regards,
Shrinivas Sagare

fgrimmer 660 comments Joined 04/09
27 May 2015

Shrinivas, I am suspecting that there is a permission issue that prevents the search from returning the database.

WB255000 5 comments Joined 11/09
29 May 2015

Hi fgrimmer.

Do you know whether or not there is a method to see the FASTLOAD log when using the Data Transfer Wizard?

Because you can't define the file layout, so when the JOB fail you can't see why. Perhaps there is another form to do it.

 

Thanks.

 

WB255000 5 comments Joined 11/09
29 May 2015

Sorry... I didn't see "the  Job Output button"
But, in addition, is this log saved in somewhere?
Thanks.

fgrimmer 660 comments Joined 04/09
29 May 2015

Marcos, No, the Fastload error table is not longer available once FastLoad completes. We gather up any of the SQL Exceptions and warnings returned from the JDBC driver and put in the Job Details. I hope you will see in there what went wrong.

WB255000 5 comments Joined 11/09
01 Jun 2015

Yes I did.
Thanks

WB255000 5 comments Joined 11/09
02 Jun 2015

Hi.

 

I have a Issue with the sensivity of the words in the "where" clause.

 

While "where name='prdwqcpf '" works in Sql Assistant, in Teradata Studio I have to use "where name='PRDWQCPF'" (with uppercase words)

 

This is the CREATE TABLE where NAME column is not CASESPECIFIC:

 

 CREATE SET TABLE ctrl_proceso.cpic_bteq ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      NAME VARCHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC,

      TIPO CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,

      RENGLON SMALLINT,

      DESCRIPCION VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      Fecha_actualizacion DATE FORMAT 'YYYYMMDD' DEFAULT DATE )

PRIMARY INDEX bteq ( NAME );

 

 

Could be because a setting into the JDBC connection?

 

Thanks.

 

fgrimmer 660 comments Joined 04/09
02 Jun 2015

Marcos, Yes. Most likely the TMODE setting. With SQLA, the default is TERA mode. With Studio, the default is the recommended value of ANSI. You can change this JDBC property by right clicking on your Connection Profile and choose Properties. Then select Teradata JDBC Connection. In the JDBC Connection Propertes, select TMODE=ANSI and remove, then Add TMODE=TERA. If you want the default to be TERA for further connections, press the Save Properties button to persist the settings.

WB255000 5 comments Joined 11/09
24 Jun 2015

Hi Francine

 

Is possible to re-run a load table task?

 

As we have two database servers one for production and other for development, we usually get data between Servers.

 

I used to create TPTs jobs with the TPT Wizard, but now I use the load option form the Studio. The issue is that each time I have to transfer data I have to create the transference.

It's not a hard work, but could be easier if I could use the one I created before.
 
 

fgrimmer 660 comments Joined 04/09
24 Jun 2015

Marcos, Yes. Switch to the Data Transfer perspective in Studio and in the Transfer History there should be an entry for your load table job. Select that entry and there is a 're-execute' toolbar option to rerun the job. Make sure your connection profile is connected and the database and table exists.

27 Jul 2015

I can not run data transfer between 2 tables in 2 different teradata connections.
In function [Data transfer wizard]:
When I choose "Destination Type" is "Teradata" nothing can run.
But when I choose "Destination Type" is "External file", I can export data to file.
Please help me because I need to transfer data between 2 teradata connections.

fgrimmer 660 comments Joined 04/09
27 Jul 2015

Dennis, If you are using 15.10.00.03, there is a bug when copying from Teradata to Teradata. This has been fixed. To workaround this issue, you would need to select your table in the source Teradata system and copy to External File. Then in your Destination Teradata system, use the Data Transfer wizard to launch Smart Loader and create the new table and load the data. If the destination table already exists, then use the Data Transfer wizard to load data from the external file.

27 Jul 2015

I know the way you said. But now, I can not use function "transfer data from teradata to teradata", this bug has been fixed as you said, but I need to wait for next version, is it right ? ^^

fgrimmer 660 comments Joined 04/09
27 Jul 2015

Dennis, Yes, if you are using 15.10.00.03 and hit the "copy object between Teradata systems" problem, you will need to wait for the fix or use the steps above as a workaround.

bcarlson 2 comments Joined 10/08
11 Aug 2015

My team is looking at packaging 15.10.00.03 for our users.  I thought I had seen a note with 15.10.00.01 that there was a potential for Studio to hang when trying to cancel a query in Aster.  Has this been resolved?  I could not find anythign specific in the release notes for 15.10.00.03.

fgrimmer 660 comments Joined 04/09
11 Aug 2015

Brad, The Aster Cancel issue was resolved in in our 15.10.00.02 patch release. We are also working on a 15.10.00.04 patch release, due out by end of this week, to resolve a Studio copy object issue. If you are using the Studio copy object feature then I would recommend to waiting for this release.

r.piwonski 1 comment Joined 09/10
22 Sep 2015

Hi,
In Administrators perspective is there a way to change formmating of cels with big values etc. Current Perm not to display it in scientific notation?

fgrimmer 660 comments Joined 04/09
22 Sep 2015

Are you referring to the cells in the Result Set Viewer?

PGJW 1 comment Joined 05/15
28 Jan 2016

I just downloaded the current version of Teradata Studio - TeradataStudio__win64_x86.15.10.01.01.zip, released 26 Jan 2016.  When I tried to extract the files, Microsoft Forefront Endpoint Protection <virus definition 1.213.4702.0> blocked the  "setup.exe" file, stating that it was infected with Trojan:Win32/Varpes.J!plock.  Can you please verify that the file is *not* infected?  Thanks!  

fgrimmer 660 comments Joined 04/09
28 Jan 2016

@PGJW, We have scanned the download file and its contents and have not had any reports of a virus in the zip.

02 Mar 2016

Is there a way to import Query History from SQL Assistant to Teradata Studio?

fgrimmer 660 comments Joined 04/09
03 Mar 2016

Joseph, Yes. Once you have exported your history file from SQL Assistant, on the Studio SQL History toolbar there is an Import button that will prompt you for the SQL Assistant History file location to import in.

Abhishek_RC 2 comments Joined 09/14
03 May 2016

Hi, 
I am seeing a strange issue with TD Studio 15.10. When i run a select * on a table which has additional spaces in a few fields. TD studio automatically trimming the spaces when in resultset viewer. Its not a problem in SQL assistant.Is there any known way to fix it ?

Abhishek_RC 2 comments Joined 09/14
03 May 2016

hi, 
TD studio is trimming spaces in case any field has spaces in the beginning . SQL Assistant does not have this issue. 
Example :

insert into  DBNAME.test  values ('hello');

insert into  DBNAME.test  values (' hello');

 

sel * from DBNAME.test;-- this will show as only "hello" in resultset viewer

fgrimmer 660 comments Joined 04/09
04 May 2016

Abhishek, The trim is happening in the underlying code of Eclipse used by Result Set Viewer. We are looking for an bug fix or workaround this issue.

05 May 2016

Thanks Francine

03 Jun 2016

is there any way to export the comparison/differences when doing an object compare in Teradata Studio?  

fgrimmer 660 comments Joined 04/09
03 Jun 2016

Vicky, Not at this time.

franceszhu 5 comments Joined 08/16
11 Aug 2016

Hi Francine,
Your post described the Teradata Studio very well and clear. But I still have a question.
I have a table with unicode characters in its table name as well as in column names. I successfully created it from BTEQ and am able to view the names in correct form in BTEQ.
Here's the table: Country_国家_국가_Страна
The column names are:
CONTINENT_大陆
Einzelhändlerland
In Teradata studio, however, the table and column names are not displayed properly.
Table name is displayed as:
Country_国家__ð¡Ñ´Ñ ð°ð½ð°
Columns are in:
CONTINENT_åý§é
EinzelhÃýndlerland
Which commands or configurations can be used for displaying these names properly?

fgrimmer 660 comments Joined 04/09
11 Aug 2016

Frances, We are trying to recreate your issue. What OS are you running on and what is the CHARSET in your connection profile? Is there any language setting? What version of JRE?

franceszhu 5 comments Joined 08/16
12 Aug 2016

Here are the information:
OS: Windows 8.1 Enterprise.
System type: 64-bit OS, x64-based procesor.
JRE: 1.8.0_101
Teradata Studio: 15.11.0.2016.5311155
Some Session info:
* Collation: ASCII
* Character Set: UTF8
* Transation Semantics: ANSI
* Default Character Type: LATIN
 
In Teradata Studio, I set the unicode locale extensions to "UNICODE" from Windows->Prefrences->Globalization, but it didn't work.
 
I also noticed that my session Character Set is UTF8. Maybe changing it UTF16 will work. How do I change it from Teradata Studio? I used to change the CHARSET to other value in BTEQ, which didn't help the display. Instead it screwed up the display in BTEQ. Then I changed it back to UTF8 and the display didn't show the letter properly. I end up reset my Teradata DB VM.
 
I gave all the information and the steps I did before. Hopefully it will help you understand my issue. Thanks.
 
 

fgrimmer 660 comments Joined 04/09
12 Aug 2016

Frances, We ran some test and we think the problem is this: That you have placed non Latin characters in a non Extended Object Name Teradata  Database.  This requires that you provide a CLIENT_CHARSET for the JDBC Connection Properties (instead of the CHARSET=UTF8 option). We tried CLIENT_CHARSET=950 as an option and were able to see most of the characters, yet some still not appearing correctly (Table name showed as Country_?家_??_Страна).  To add the CLIENT_CHARSET JDBC property to your connection profile, you will need to modify your JDBC Properties. Choose the Add... button and in the JDBC Property, scroll down to the botton and choose USER_ENTERED_PROPERTY. This will prompt you for a Property Name and Property Value. Enter CLIENT_CHARSET for the Property Name and a charset code for the Property Value. Then press OK and OK to set the value. Also remove the CHARSET option. Like I mentioned, we tried 950 and got some of the characters to be displayed. I am not sure the correct value is to get all the characters displayed correctly. What is your Windows client charset value? This is displayed in the Studio Preferences>Workspace preference page. (My system shows Cp1252.) Maybe setting it to that will work?

franceszhu 5 comments Joined 08/16
15 Aug 2016

Changing JDBC property with CLIENT_CHARSET does affect the display. However, I can't find the correct charset code.
 
In most system, UTF-8 can display these Asian letters without any problem. Since it doesn't work with Teradata Studio, I tried many codes, but none of them works correctly, either no effect at all or display in wrong letters. I have tried UNICODE, GB2312, GB18030, CP1252, UTF-8.
 
Can you help me figure out the correct code? Thanks.

fgrimmer 660 comments Joined 04/09
15 Aug 2016

Frances, Just curious, what code is displayed in the preferences>workspace preference page I mentioned above?

franceszhu 5 comments Joined 08/16
15 Aug 2016

It is default one, Cp1252.

fgrimmer 660 comments Joined 04/09
16 Aug 2016

Frances, When I look into this, there seems to be 1000s of possible CLIENT_CHARSET values. I think you will need to find out the charset of the source and set that for your CLIENT_CHARSET. Otherwise, I am not sure.

franceszhu 5 comments Joined 08/16
18 Aug 2016

I have tried most possible character sets but no luck. Because the display shows wrong characters although somehow it displayed in Chinese.
 
After these days' search, I think this is something that Teradata Studio should improve. The reasons are:
 
1. The CLIENT_CHARSET is a legacy property and is not suggested to use. I found this description in one document. The CHARSET=UTF8 should work for international characters.
 
2. My table contains multiple internactional languages, such as Chinese, Japanese, Korean and Russian. So the display won't work for all even though I am able find a correct client character set for one language.
 
3. I compared another similar DB client application which uses JDBC. I have same type of table name and am able to see the table name correctly. The CHARSET is UTF8.
 
These are my thoughts. Anyway, I have gained a lot of knowledge from you. You pointed me correct path. Thank you very much. 

fgrimmer 660 comments Joined 04/09
18 Aug 2016

Frances, I agree that CLIENT-CHARSET is not a recommended option. Is this related to not having Extended Object Name support enabled on your Teradata Database? If we use the same DDL to create the table in a EON supported Teradata Database, we don't see the issue. Can you try this?

Pages

You must sign in to leave a comment.