Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

A Teradata Database Java User Defined Function (JUDF) is a program that operates on data stored in relational tables. UDFs allow users to add their own extensions to the Teradata SQL language. JUDFs are implemented as external functions. This means the source is compiled externally to the DBS and kept in Java Archive (JAR) files. These JAR files are installed into the database using stored procedures in the SQLJ database. Once a JAR file is installed a JUDF can be defined to use a Java class and method within the JAR file. The JUDF is executed via a protected mode server separate from the database process. Parameters passed from the DBS are converted to their Java form and the Java return type from the JUDF  is converted back to its DBS form.
 

A JUDF is referred to as a function in the SQL-99 standard. It provides a rich object oriented feature set including networking. It is also platform independent which allows code developed on one platform to be easily moved to another. Java objects lend themselves to be developed as components for business logic. Applications written in Java and User Defined Functions can be written in the same style and language.

This article will show how the Teradata Plug-in for Eclipse facilitates the process of creating and installing the Java parts and DDL for a scalar JUDF using the JUDF Wizard and Multi-page Editor.

 
Prerequisite for this Article
If you have not work through the guide Getting Started with Teradata Plug-in for Eclipse, do so now before you continue.  Also the 13.0 version of the Teradata database is required for this feature.
 

Using JUDF Wizard
In this example, you will be creating a JUDF Function which takes a full name as a parameter and returns the last name from the name passed into the function. You will use the JUDF Wizard to implement this JUDF.

Create a Java Project
You will need to create a Java project to store the Java source of the JUDF. Go to the top of the Eclipse IDE and select the pull down menu Window ->Perspective->Other... ->Java (Default). Then go to the package explorer in Eclipse and right click New->Java Project. Enter “teradata_judf” for the Java project name. Once this is done, select the "Next" button.

Remove “src” Folder
Remove your default source folder by selecting “src” and then picking the "Remove source folder" option.

Add “src/java” Folder
In this example, the “src” folder is going to be deleted and the “src/java” folder will be added. This is done to give the Java project structure. Later in following articles different source folders will be added such as “src/config” and “src/test”.

Now select the “Create a new source folder” option and enter “src/java” in for the folder name and select “Finish”. 
 

Now select “Finish” again in the New Project Wizard.

Launch JUDF Wizard
Go to the top of the Eclipse IDE and select the pull down menu Window ->Perspective->Other…->Database Development. Select the database you created a profile for in the Data Source Explorer. Right click on the database node and select connect. Open the tree in the Explorer and select the “User Defined Function” tree node for the schema in which you wish to create your JUDF. Right click on the “User Defined Function” tree node and select the menu item Teradata->Create Java User Defined Function.
 

JUDF Wizard
At this point, the JUDF Wizard will come up. You will need to enter the container name "/teradata_judf" and the name of the JUDF properties file "ReturnLastName". Once you have done this, select the “Next” button.

JUDF Class Definition
The next page is where the JUDF class is defined. Enter the source folder “teradata_judf/src/java”, package name “judf” and the class name “ReturnLastName”. It is the best practice not to use the default package name. Once this is done hit the “Next” button.
 

Define JUDF Method
This page allows the user to define the function method for the JUDF. SQL data types are used to define the parameter types in this panel. The SQL data types are mapped to specific Java types as shown in Appendix A.

Now, enter the method name “returnLastName”. Next go to the "New" button for the parameter list and then add the parameter name “name”. Pick the data type VARCHAR. Enter in the size of 256 and hit the "Apply" button. Now select the "Next" button.
 

Define Return Type
The Next page of the Wizard defines the return type for the JUDF. The return types use SQL data types. The SQL data types are mapped the same as the JUDF parameters to specific Java types as shown in Appendix A.

Now select the VARCHAR from the “Type” combo box. Then enter the size of 256 for the type option. Once this is done, select the “Finish” button.
 

Using the JUDF Multi-Page Editor
The JUDF Multi-Page Editor brings up the contents of the JUDF properties defined in the JUDF Wizard. The Multi-Page editor allows the user to edit and deploy a JUDF.

JUDF Function Source
Go to the “Source” page of the editor. You will see the generated Java code for the JUDF. In this case, it is up to you to enter the content of the JUDF.

Change Source
Edit the Java source on this page entering the code shown below to get the last name from a full name.

Once this is done, go to the “JAR File” page in the editor. A popup dialog will come up asking you if you want to save the source you just changed. Now select the “Yes” button.

Deploy JAR
On the JAR Files page, you will see the JAR file and JAR ID for the JUDF. Select the “Deploy” button. This will deploy the JAR for the JUDF on the database server.
 

Install DDL

Go to the “SQL” page in the editor. Select the “Run SQL” button. This will install your JUDF on the database server.
 

Run JUDF
Once your JUDF is installed you can run it. Go to the DTP tree in the Data Source Explorer. Select the User Defined Function tree node in which you launched the Wizard and select refresh. You will now see the “returnLastName” function. Select the procedure and right click. Now select the "Run" menu item. A popup dialog will come up called "Configure Parameters". Enter in the value column of the Dialog “John Smith” and hit the “OK” button.

Results
Your results of running your JUDF will end up in the bottom of the Eclipse IDE. Select the "Results1" tab and see the results of the execution of your JUDF.

Conclusion
This article has shown how Teradata Plug-in for Eclipse facilitates the process of creating and installing the Java parts and DDL for a JUDF using the JUDF Wizard and Multi-page Editor. Using these tools, will help the user create, edit and run JUDFs easily and efficiently.
 

Appendix A

SQL Data Types Mapping
The following table defines how data is mapped between SQL and Java. The SQL data type is converted to/from the corresponding Java data type based on the type of parameter mapping. The JUDF defaults to simple mapping. The user may specify object mapping via the External Name clause.

Appendix B

 JUDF Example Source Code

/**
 * 
 */
package judf;

/**
 * @author js185064
 * 
 */
public class ReturnLastName {

	/**
	 * @param name
	 * @return
	 */
	public static String returnLastName(String name) {

		String returnValue = null;
		if (name != null) {
			int index = name.indexOf(" ");
			if (index == -1) {
				returnValue = name;
			} else {
				returnValue = name.substring(index, name.length());
			}
		}
		return returnValue;
	}
}