Getting Started with Teradata Plug-in for Eclipse
At this point you should already be familiar with Eclipse. But if you are not, Eclipse is the de facto integrated development environment (IDE) for developing Java applications. It provides comprehensive support for Java technologies, as well as a platform for plug-in tools to extend its capabilities. Teradata Plug-in for Eclipse extends the Eclipse IDE with plug-ins that support Teradata application developers. It provides dialogs and wizards to ease the techincal and operational challenges when creating database objects, such as databases (or schemas), tables, stored procedures, user-defined functions, etc. Teradata Plug-in for Eclipse is targeted at improving the productivity of Java oriented developers by bringing more of their activities into a single Eclipse based IDE.
The computer where the Teradata Plug-in for Eclipse will be installed must be running one of the following operating systems:
|Windows Server 2003||
Apple Mac OSX
Before we can start using the Teradata Plug-in, we need to create our Eclipse DTP environment. The first step is to download the required software: Java Runtime Environment (JRE), Eclipse, and DTP software.
The following packages need to be installed:
- Java Runtime Environment 1.8 (Java Runtime Environment)
Teradata Plug-in for Eclipse requires Eclipse (Neon) version 4.6
Eclipse has bundled the required packages (Eclipse SDK, EMF, SDO, GEF, DTP, and WTP) into a single download, Eclipse IDE for Java EE Developers.
Simply unzip the zip files or, in the case of the JRE, run the execution file.
Next, bring up Eclipse by double clicking on eclipse/eclipse.exe. Eclipse will prompt you for a Workspace folder that it uses to maintain the metatdata for your Eclipse session. You can browse your directory for an existing workspace folder or enter the name for your new workspace. It is recommended that you enter a name relavent to your developement, for example 'JXSP_workspace' for your Java external stored procedure development.
Next a Welcome screen will appear. Click the ‘X’ at the top left hand corner to close the 'Welcome' tab.
Install Teradata Plug-in
►NOTE: Mars JEE Eclipse bundle is missing the EMF Client Platform (ECP) 1.9.0 packages. Before installing the Teradata Plug-in, you must:
- Install the EMF Client Platform 1.9.0 by going to the Eclipse Help menu, selecting Install New Software... and entering the EMF ECP 1.9.x Update Site: http://download.eclipse.org/ecp/releases/releases_19/.
Note: If you have the previous version of EMF ECP installed, you want to remove it from Eclipse before installing the new version.
To install the new version:
- Press the Add... button to add the location of the EMF ECP 1.9.0 Update Site (see image below).
- Choose this site and install the "ECP SDK 3.x" in the "All SDKs" category. Press Next to install EMF ECP.
- Before letting Eclipse restart to finish the installation, remove the file
- To remove this plugin, locate your Eclipse directory, and delete plugins
- Restart Eclipse, File>Restart
- Install Teradata Plug-in for Eclipse
Install Teradata Plug-in from the Teradata Datatools Update Site. Within Eclipse, go to the Help menu and click the Install New Software... option. From the Available Software screen, click the Add button to add the Teradata Update Site.
In the Add Site pop up window, enter the Teradata Update Site URL in the Location field (http://downloads.teradata.com/download/cdn/tools/tdide/tdide-16.00.00.00/update). Click OK. You should notice that the Teradata update site is displayed in the 'Work with:' window and the Teradata Plug-in for Eclipse category is displayed in the Name/Version list. If the update site is not in the Available Software list, go to the top of the list and select --All Available Sites--. This should display the full list of update sites it knows about. You should find the site in the list and check the box.
The Teradata Plug-in for Eclipse is divided into seven features: Teradata Basic Functionality, Teradata DTP Enhancements, Teradata DTP Refactor, Teradata DTP Support, Teradata Java Development, Teradata Plug-in for Eclipse, and Teradata XML Services. Select the top level category, Teradata Plug-in for Eclipse, to include all of the Teradata Plug-in features. Note: Uncheck the 'Contact all update sites during install to find required software' to avoid unnecessary searching of Eclipse update sites. Click Next to verify the Install Details and License Agreement. Click Finish. After the install, Eclipse will prompt you to restart Eclipse. Click Yes.
Create a Connection Profile
The next task is to create a connection profile for your Teradata Database. Make sure that the Eclipse Data Source Explorer View is opened. Eclipse Views provide an alternative presentation of the data, as well as an additional way to navigate information in the Eclipse workbench. The Data Source Explorer View provides a tree view of the database objects and tools for running routines and viewing results.
To open the Data Source Explorer View, click the Window>Show View>Other... option in the top-level menu. Double click on the Data Management>Data Source Explorer item. You will notice the Data Source Explorer (DSE) tab in the workspace. Drag the tab over to the left hand pane for more space to browse the databases and their associated database objects, such as tables, columns, stored procedures, macros, user-defined functions, user-defined types, indexes, and views.
To create a Teradata, Aster, or Hadoop connection profile, right click on the Database Connections folder in the Data Source Explorer and select the New… option. The New Connection Profile wizard will display the list of Connection Profile Types. Choose the Teradata Database, Aster Database, or Hadoop System type and enter a name for your profile. Click Next to launch the New Teradata Connection Profile, New Aster Connection Profile, or New Hadoop Connection Profile wizard. If this is your first time installing the Teradata Plug-in you will need to click on the New Driver Definition icon,, and select the Teradata Embedded JDBC Driver or Aster Embedded JDBC Driver. Otherwise, the Teradata or Aster Embedded JDBC Driver template has been selected for you depending on which type you have selected.
Next, enter the Database Server Name, User Name, Password and Database values. The Authentication Mechanism allows you to choose an advanced logon mechanism such as LDAP or Kerberos or select the default, PASSWORD_PROTECTED, mechanism. 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 throught the Eclipse session. When Eclipse is closed, the unsaved password is removed before the profile is written to disk, and you will be prompted for the password the next time you connect to that profile.
You can also add or modify JDBC properties for your connection. Click the Add button. This will bring up another dialog for you to select the JDBC property and its value and add it to the list. Or to remove an attribute, select it from the list and click the Remove button.
The Save Properties button will save the JDBC properties to an external file in the workspace. 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 Plug-in 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.
Another option is to Cache the Schemas (or databases) for the connection. This feature allows you to cache the schema metadata to disk, saving time when loading the objects in the Data Source Explorer. Click on the Cache Properties button to bring up the Cache Schema dialog.
Optionally, you can click the Test Connection button to verify that your connection profile definition is correct. Click Finish to return to the DSE and connect to your Teradata Database.
To provide a different Teradata or Aster JDBC driver, click the New Driver Definition icon, , to the right of the drop-down list. Select the Teradata or Aster External JDBC Driver template from the list of available driver templates. Next, click the Jar List tab to enter the Jar list for the JDBC driver. Click the Add JAR/Zip button to select your Teradata JDBC driver Jars. Click OK to return to the New Teradata Connection Profile wizard.
For Hadoop, enter the WebHDFS Hostname, WebHDFS Port number, and System Username. The Hadoop connection uses the WebHCat interface to gather information about the Hadoop system.
You now have a Teradata or Aster Database, or Hadoop System connection profile that you can use to browse the databases and their database objects or invoke the ‘Create’ actions (Teradataand Aster only) to create new database objects, such as Users, Databases, Tables, Macros, Views, or Stored Procedures. By right clicking an entry in the Data Source Explorer tree, the Teradata, Aster, or Hadoop menu option will appear. Open this option to find the appropriate actions for the system selected.
Administering Objects: Navigator View, Filter View, and 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 Object List Viewer displays the list of objects and options to 'Open' an object to display more detailed information about the database object. 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) in the Navigator to display the list of tables. Choose the toolbar or menu action to show other objects for a database or schema.
Another option for browsing information about the Teradata Database objects is the ‘Open in Property Form’ option. This option will open the property forms and display more detailed information for the objects that are selected in the Data Source Explorer. In the example below, the ‘copy_db’ database has been selected in the DSE and the ‘Open in Property Form’ option was selected.
Create a Teradata Project
A Teradata Project is an extension of a Java Project in Eclipse. It provides project preferences to help the developer when building Teradata Java objects, such as Java stored procedures, Java user-defined functions, JavaBeans, and Spring DAOs. The preferences include a standard folder layout for placing your project source and test code, libraries, as well as configuration and resource files. Also included in the Teradata Project is a set of Teradata libraries. These libraries include the Java Function library (javFnc.jar), Teradata Commons Access library (tdcommons-access.jar), and Teradata Commons Context library (tdcommons-context.jar). These libraries are often accessed from Java stored procedures or JavaBeans. The Java stored procedure, JavaBean, and Spring iBatis Wizards have been enhanced to take advantage of the new functionality from the Teradata libraries.
Teradata Project Wizard
To create a new Teradata Project, select the File>New>Other... option from the main toolbar. Open the Teradata Wizards folder and select Teradata Project and click Next. The Teradata Project wizard will create a new Eclipse project using the folder names as specified in the Teradata Project Preferences.
NOTE: If you are creating Java stored procedures or user-defined function, make sure the JRE environment for your Teradata Project is set to JRE 1.5.
Enter a project name and click Next to view your project options. Click Finish to create your Teradata Project.
Configuration Options for Java Stored Procedures and Java User Defined Functions
If you are creating a Java stored procedure, make sure that you have selected a system that has Teradata Database version 12.0 or greater. If you are creating a Java user-defined function, make sure that you have selected a system that has Teradata Database version 13.0 or greater. Along with the correct version of Teradata, you must also have the SQLJ feature installed. SQLJ can only be installed on 64-bit server machines. For smaller, laptop configurations, Teradata Express Edition also supports SQLJ features. Teradata Express can be found on the Teradata Download Center.
SQLJ contains stored procedures and views used to manage the JARs used by Java stored procedures and Java user-defined functions. A JAR or Java Archive is used for aggregating many Java class files into one. It is used to deploy the Java classes on the Teradata Database. If you click on the SQLJ schema and open the Stored Procedures folder, you should see several stored procedures, such as install_jar, replace_jar, remove_jar, and alter_java_path. You must also have ‘Execute Procedure’ permissions granted on the SQLJ database.
SQLJ is installed by running the SQLJ Database Initialization Program (DIP) script called DIPSQLJ. DIP is a series of executable script files packaged with Teradata Database. Refer to the Teradata Utilities documentation for more detailed information on running DIP.
Java Compliance Level
If you are using Java Runtime Environment version 1.6 (jre6), your compiler must have the compliance level set to 1.5 when creating Java stored procedures or Java user-defined functions on Teradata. The reason for this is that the JRE version used by Teradata is 1.5 and classes compiled with JRE 1.6 are not compatible. In order to set the compliance level, select the Window>Preferences option from the top-level menu. This will bring up the Preferences popup dialog. Open the Java>Compiler preference and change the 'Compiler compliance level' to '1.5' from the drop down list. Click the Apply, then OK button to close the preferences dialog.
Additional information on using Teradata Plug-in for Eclipse is provided in the form of online Help. To open the Help content, click Help>Help Contents from the main toolbar.
You are now ready to browse your Teradata database systems or create new database objects. The Teradata Plug-in for Eclipse provides the following easy to use features:
- Connection Management (Aster Database Support)
- Data Source Explorer
- Create Schema, User, Table, Procedure, View, and Macro Dialogs
- FastLoad and FastExport of data to and from tables
- Grant and Revoke Privileges using the Object List Viewer and property forms display (describe in more detail in the New Studio Administration User Experience)
- Ad hoc SQL Editor
- LOB Support in the Result Set Viewer and parameterized SQL.
- SQL Editor Templates (INSERT, SELECT, DELETE, UPDATE, and more)
- Parameterized Queries (Named Parameters)
- Java Stored Procedure Wizard/Editor (Related Article: Creating a Simple Java Stored Procedure, Automating Build for Java Stored Procedures, Answer sets with Java Stored Procedures, Instant Web Service from Java Stored Procedures, Instant Java Stored Procedures (Just Add Queries)
- Java User Defined Function Wizard/Editor (Related Article: Creating a Simple Java User Defined Function, Creating an Aggregate Java User Defined Function, Creating a Table Java User Defined Function)
- JXSP Jar Management
- SQL ANT Build Wizard (Related Article: SQL Ant Wizard )
- iBatis and Spring DAO Support (Related Articles: Using the Spring DAO Wizard, Creating an iBatis SQL Map, iBatis DAO with Web Services Wizard, iBatis CRUD Wizard, iBatis (MyBatis) Customer Data Types, Web Service with MyBatis and User Generated Keys )
- Teradata Project (Related Article: Creating a Teradata Project)
- Execution Plan Support, Compare, and Statistics
- Interdependency Browser (Related Article: Are We Related?)
- Find, Copy, and Compare Objects
- Teradata XML Services Support
- JavaBean Wrapper Wizard
- Teradata SQL Parser
- Teradata SQL Formatter
- XML schema generation, XML style sheet generation, XSLT Editor (Related Article: XML Document Loading)
Each of these functions simplifies the effort required to develop active applications using Java and Eclipse.
To ask questions or discuss issues, refer to the Teradata Studio Forum and post your question.