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;
	}
}

 

 

Discussion
aradford 3 comments Joined 07/09
29 Jul 2009

when I right click on [User Defined Functions --> Teradata] I don't get the option to "Create Java User Defined Function".

js185064 48 comments Joined 03/09
29 Jul 2009

First check to see if you are running the 13.0 version of the Teradata database, it is required for this feature. If you are still having problems, this usually indicates an installation problem with Teradata Plug-in for Eclipse. You can follow the instructions in the Article "Getting Started with Teradata Plug-n for Eclipse" which should help you with any installation issues.

http://developer.teradata.com/tools/articles/getting-started-with-teradata-plug-in-for-eclipse

Anish 1 comment Joined 11/09
09 Nov 2009

Thanks for the great post. I installed Teradata 13 and got it working with my first JUDF.

I have the following concerns:
1) how java udf will perform - is it all AMP parallel execution when i select from table? if am calling an out of the box sql function vs java function (say subString), what would be the difference in performance ?
2) can i do I/O in java method ? can i make a web-service call in java method ? can i make a jdbc data connection in java method before returning the response.

Thanks
Anish

js185064 48 comments Joined 03/09
11 Nov 2009

A Java UDF execution is in parallel. The performance because the JVM is in a separate process is considered about two times slower than a C UDF. C UDFs are slower than an intrinsic function like TRIM by a fair amount. A Java UDF trim maybe four times slower than an intrinsic function. You can do I/O in the Java UDF method if the tdat user on the box has the correct permissions and the JVM is configured for that kind of access. Just keep in mind that you have multiple threads doing the I/O and that each node has its own file system. You could also use a Web service to obtain some external data, but if you had 1000 amps all of them could invoke the Web service at once. A better strategy would be to invoke the Web service once either from the application layer that then invokes the JUDF or from a Stored Procedure which again can then invoke the JUDF.

terausr123 3 comments Joined 04/12
09 May 2012

When I want to run the jar, I get the following error:

-----

Executed as Single statement. Failed [7583 : HY000] The secure mode processes had a set up error.
Elapsed time = 00:00:07.242

STATEMENT 1: Select Statement failed.

-----

with the VMWare version of Teradata Express (Linux); using Eclipse EE under Windows.

Could you please tell me what I have to do to make it run?

js185064 48 comments Joined 03/09
11 May 2012

The following might be causing the problem:
/etc/passwd - Reason for failure

tdatuser:x:0:1000:UDF user:/home/tdatuser:/bin/bash

The above setting is making the 'tdatuser' as 'root' user and preventing UDF secure server tasks invocation.The UID should be other than ' 0' on all nodes

terausr123 3 comments Joined 04/12
14 May 2012

I changed the line
tdatuser:x:1000:1000:UDF user:/home/tdatuser:/bin/bash
to
tdatuser:x:0:1000:UDF user:/home/tdatuser:/bin/bash

but the problem remains.

js185064 48 comments Joined 03/09
14 May 2012

You misunderstood me, the UID should be something other than ' 0'. It looks like that was already the case.

js185064 48 comments Joined 03/09
14 May 2012

Make sure you comple your project with JDK 1.5.

terausr123 3 comments Joined 04/12
15 May 2012

That is the case. The container project of the UDFs compiles against java 1.5 (uses jre1.6 though).

teraJava 10 comments Joined 08/12
13 Aug 2012

Thanks for the helpful article. The problem I am facing is that using Multi-page editor plug-in for eclipse it takes long time to be able to deploy the jar files using the 'Deploy' button on JAR Files tab as well as running sql using 'Run SQL' on the SQL tab. It can take up to 4 to 5 minutes for each operation. Also, after establishing connection a traversal in the Data Source explorer for database like SYSLIB triggers a time consuming operation during which nothing else can be done in eclipse. I have also seen perpetual running process visible in Window> Show view> Progress named 'Run Serialize Schemas'. Could you please suggest where would be the best place to start looking for what the problem might be for these long running processes?

js185064 48 comments Joined 03/09
14 Aug 2012

Are you caching your schemas? These properties are set in the Teradata JDBC Connection Properties->Cache Properties.

teraJava 10 comments Joined 08/12
15 Aug 2012

I have tried both ways. It doesn't make a difference to the time taken in deploying jar files and running the DDL query for the UDF. The 'Run Serialize Schema' process also runs with caching on or off and displays to be 'running' even when its 100% complete - in the process window or on the main status bar on eclipse (at the bottom of the eclipse window). I could send you screenshots if you need a visual.
What would be the best place (other than teradata machine itself) to see what interaction eclipse is doing with the teradata and how long its taking - I am thinking log files but specifically which ones? I will look for an article on logging through eclipse using the teradata plug-in shortly but if it’s already not present it would be great help if one can be written about it.

fgrimmer 660 comments Joined 04/09
16 Aug 2012

@teraJava, The Serialize Schema process only runs when caching is first turned on and you open the Schemas folder or you specify to 'Refresh the Schema Cache'. If for some reason this process is taking too long, you can kill it by clicking the red box from the Progress view. The cache files can be deleted by clicking the Delete Cache Files in the Connection Properties.

Another option if it is taking a long time to traverse objects in the Data Source Explorer tree is to filter out objects, such as schemas or UDTs, that you don't need. Filters can be enabled by right clicking on the folder and choosing the Properties option.

teraJava 10 comments Joined 08/12
20 Aug 2012

Thanks fgrimmer. This definitely resolves the slow traversal in the DSE. I finally managed to turn off the caching which also stops the Serialize Schema process. So please ignore my earlier comment about serialize running when caching was off. I realized that there was an additional pop-up after clicking 'Cache Properties' button which in my previous runs was either not showing up or was getting ignored due to my subsequent clicks etc. due to the long delay in eclipse response.

A suggestion\ question - Is it possible to not have this button but have the caching as one of the properties listed down the left hand side just like other properties e.g. filters, version etc.

Now on to other issues: I am still experiencing delays in deploying jar files and running the install SQL for java UDF through eclipse. Eclipse also seems to take a long time to open the .judf file. Are there logs that I can look at to work out what is taking so long? I suspect that there are non fatal or warning type errors happening which are causing delay in the operation.

js185064 48 comments Joined 03/09
21 Aug 2012

You can get to the Error log by selecting the menu option
Window->Show View->Error Log in Eclipse

teraJava 10 comments Joined 08/12
11 Nov 2012

@js185064, I have not been able to find anything that could help me in the error logs view mentioned. My operations of deploying the jar files using the JUDF and Run SQL functions still take a very long time. E.g. a test java UDF took me about 7 minutes today; all it has is 5 lines of code. It tool similar amount of time to run the SQL using the JUDF file.
Where could I start looking for the problem?

js185064 48 comments Joined 03/09
12 Nov 2012

You can try using Ant to create your Java User Defined Function. The following article will show you how to do an Ant Build from the Java User Defined Function Multi-Page Editor.
http://developer.teradata.com/tools/articles/automating-a-build-for-java-stored-procedures-using-the-teradata-plug-in-for-eclipse.

If the build is still taking a lot of time, use “-v” on the command line when you invoke the Ant build to see any errors or warnings that may come up.

27 Aug 2014

 I have following java UDF which i'm calling from external stored procedure. Issue is when an SQLException occurs i get truncated error message in Parent SP Exit handler MESSAGE_TEXT of GET DIAGNOSTIC EXCEPTION 1 clasue

 

 DECLARE EXIT HANDLER FOR SQLEXCEPTION

 BEGIN

  /* Preserve Diagnostic Codes from errored Statement*/

  SET vSQL_Code  = SQLCODE;

  SET vSQL_State = SQLSTATE;

  SET vCode = vSQL_State;

  GET DIAGNOSTICS EXCEPTION 1 vError_Text = MESSAGE_TEXT, vMsg_length = MESSAGE_LENGTH;
  END IF;
vError text:
"SQLSTATE 38U01: [Teradata Database] [TeraJDBC 14.00.00.09] [Error 3707] [S"
Whereas at trace write the error message is completely logged.
Code is below.
REPLACE PROCEDURE ExternalSP

  (IN iText CLOB(1048576) CHARACTER SET LATIN, 

   OUT oACTIVITY_COUNT INTEGER) 

 LANGUAGE JAVA 

 MODIFIES SQL DATA

 NO EXTERNAL DATA

 PARAMETER STYLE JAVA 

 SQL SECURITY DEFINER

 EXTERNAL NAME 'myExternJAR:ExecuteSQL.exec(java.sql.Clob,int[])'

 

JAVA CODE:

public class ExecuteSQL{

    /** The connection URL for the XSP internal session. */

    private static String connectionURL = "jdbc:default:connection";

 

    /**

     * Executes a piece of SQL.

     * @param sqlText the sql to execute.

     * @param activityCount return parameter

     * @throws if there is a problem of some sort.

     */

    public static void exec(Clob sqlText,int activityCount[])

            throws SQLException {

        // Now all we need to is run the query.

 

        String inRec;

        String sql = "";

        try {

            if(sqlText != null){

                BufferedReader br = new BufferedReader(sqlText.getCharacterStream());

                while ((inRec = br.readLine()) != null) {

                    sql = sql + inRec;

                }

                br.close();

            }

            

            DbsInfo.traceWrite("Extracted: " + sql);

 

            DbsInfo.traceWrite("Get connection...");

            Class.forName("com.teradata.jdbc.TeraDriver");

 

            Connection conn = DriverManager.getConnection(connectionURL);

 

            DbsInfo.traceWrite("Create Statement...");

            Statement stmt = conn.createStatement();

            DbsInfo.traceWrite("Execute SQL...");

            boolean isSelect = stmt.execute(sql);

            DbsInfo.traceWrite("After Execute SQL...");

            if(!isSelect) {

                DbsInfo.traceWrite("Within !isSelect If...");

                activityCount[0] = stmt.getUpdateCount();

                DbsInfo.traceWrite("Activity Count = "+activityCount[0]);

            } else {

                DbsInfo.traceWrite("Within Else part of !isSelect If...");

                activityCount[0] = 0;

                 DbsInfo.traceWrite("Activity Count = "+activityCount[0]);

            }

            DbsInfo.traceWrite("Close...");

            stmt.close();

            DbsInfo.traceWrite("Done...");

            

        } catch (IOException e) {

            DbsInfo.traceWrite("IOException: " + e.getMessage());

            throw new SQLException(e.getMessage(), "38U01");

        } catch(SQLException se){

            DbsInfo.traceWrite("SQL Exception :"+se.getMessage());

           throw new SQLException(e.getMessage(), "38U01");

        } catch(ClassNotFoundException CNF){

            throw new SQLException(CNF.getMessage(), "38U01");

        }

    }

}

 

Any solution to this?

 

js185064 48 comments Joined 03/09
04 Sep 2014

This is a better question for the Database forums
http://forums.teradata.com/forum/database

ММ186094 1 comment Joined 01/14
05 Nov 2014

Hello I am trying to create a Java UDF, that calls the Google Gecoding service and returns me the coordinates of a given address. 
I prepared and tested my project, it is working as a charm. After I install it into Teradata though it gives me "java.net.UnknownHostException: maps.googleapis.com". The strange thing is that the host is reachable from eclipse and the terminal. At first I started investigating the possibility that something is wrong with the DNS, but even if I try something like this(and I deploy to teradata) I still the host is unreachable. Basically the only reachable host is 127.0.0.1:
public static void test(){
  String ipAddress = "74.125.69.95";
  netAddress inet = InetAddress.getByName(ipAddress);

  if(inet.isReachable(5000)){

   

    throw new SQLException("Host is reachable: " + ipAddress);

  } else{

    throw new SQLException("Host is NOT reachable: " + ipAddress);

  }
}
Do you have any idea what may cause this ?
I use:
 - Eclipse Kepler
 - TDExpress 14.10
 - Java 6 - 1.6.0_43

tg186022 1 comment Joined 07/15
27 Nov 2015

Hi,
I'm trying to create and install a Java UDF (ExtractValue.java) but I keep getting an error.
I've already successfully installed the ExtractValue_JAR in SQLJ so now, I'm running the CREATE FUNCTION statement. However, I keep running into an error. It says,
Failure 7970 Create/Replace Java XSP/UDF passed an unacceptable signature. Correct the signature
I'm using the following query and I've already ensured that the syntax is correct. I've also made the Java method as static, just to make sure it will run as expected.

CREATE FUNCTION humedica_benchmark.extract_value

(p_txt VARCHAR(8000) CHARACTER SET UNICODE, p_debug NUMBER)

RETURNS NUMBER

LANGUAGE JAVA

NO SQL

PARAMETER STYLE JAVA

DETERMINISTIC

RETURNS NULL ON NULL INPUT
 

EXTERNAL NAME 'ExtractValue_JAR:ExtractValue.extract_value(java.lang.String, java.lang.int) returns java.lang.Number';

 

Can anyone help me find what's wrong with my query?

js185064 48 comments Joined 03/09
30 Nov 2015

What version of Java are you using?
What version of the Teradata Database are you using?
What version of the Teradata Plug-in for Eclipse are you using?
 

tomnolan 21 comments Joined 01/08
30 Nov 2015

Your EXTERNAL NAME clause is incorrect. You specified the second parameter as "java.lang.int", which cannot be correct.
 
Please post the method signature for the ExtractValue.extract_value method, so that we can help you correct the EXTERNAL NAME clause.

js185064 48 comments Joined 03/09
30 Nov 2015

Please in the Java User Defined Function Editor please select the correct data type for Integer.

StevenSchmid 13 comments Joined 07/11
15 Feb 2016

Hi JS..
Just wondering if we have to use Eclipse or other GUI's to setup Java UDF's and XSP's, or can we write the code natively as have the JRE installed.  It would mean writing the code from the SOURCE tab and the SQL table and then running the SQL code to create the UDF/XSP.
Cheers
Steven

Steven Schmid
Teradata DBA
Canberra, Australia

js185064 48 comments Joined 03/09
16 Feb 2016

You can do it your self but there are many steps invovled. The  Eclipse Plug-in for Teradata JUDF Wizard and Editor really walks you through the process. It also creates an ant build script so that you can run outside of Eclipse for Deployment and Automation.

You must sign in to leave a comment.