How to Create and Debug a Scalar JUDF using Eclipse

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Teradata Employee

How to Create and Debug a Scalar JUDF using Eclipse

SQL provides a set of useful functions, but they might not satisfy all of the particular requirements you have to process your data.

User-defined functions (UDFs) allow you to extend SQL by writing your own functions in the Java programming language, installing them on the database, and then using them like standard SQL functions.

Teradata Database supports three types of Java UDFs. They are scalar UDF, aggregate UDF, and table UDF. 

This article will show how to create a simple scalar Java UDF, execute it in protected mode, and debug it locally or remotely using Eclipse. The UDF runs under the default operating system user "tdatuser", and can access the system resources for which tdatuser has privileges. Local Debugging means Eclipse and DBS are running on the same machine. Remote Debugging means Eclipse and DBS are running on separate machines.

In this example, the scalar Java UDF calculates the factorial of the specified value.

Prerequisite for this Article

If you have not worked through the guide How to Set up a UDF / XSP Debugging Environment, please do it now before you continue.

Article Outline

In this article, we will do the following things to show how to create and debug a scalar JUDF using Eclipse.

  1. Create Java Project in Eclipse
  2. Create JUDF Source File in Java Project
  3. Deploy JAR File into Database
  4. Install DDL in Database
  5. Run JUDF in Database
  6. Debug JUDF in Eclipse

1. Create Java Project in Eclipse

1.1. Java Perspective

Select the pull-down menu Window -> Perspective -> Other ...

An Open Perspective window is displayed. Select Java (default), and click the OK button.

1.2. Create Java Project

Go to the Package Explorer in Eclipse and right click New -> Java Project.

A New Java Project window comes up. Enter the Project name "judf_scalar", and click the Next button.

1.3. Change Default Settings

The next window shows the default settings of a new Java project.  We will add DBS's javFnc.jar into the settings.

Click the Libraries tab to see the library default settings.

Click the Add External JARs... button on the right.

A JAR Selection window is displayed. Select File System in the left pane Places, and select the file /usr/tdbms/bin/javFnc.jar in the right list-box. Then click the OK button.

The JAR file /usr/tdbms/bin/javFnc.jar will be added to the new project.

Click the Finish button to finish the project creation.

A new Java project judf_scalar will be created.

2. Create JUDF Source File in Java Project

2.1. Create New Class

Right click the project, select New -> Class.

A New Java Class window is displayed. Enter Package "scalar" and Name "Factorial". Click the Finish button.

A new class Factorial will be created. 

2.2. Edit The Source File

Edit the Java source as shown below.

/**
* This example shows the Java code for a scalar UDF that calculates factorials.
* JAR File
*/
package scalar;
/**
* @author root
*
*/
public class Factorial {
public static Integer fact(Integer x) {
if (x == null)
return null;
int x_t = x.intValue();
if (x_t < 0)
return new Integer(0);
int factResult = 1;
while (x_t > 1) {
factResult = factResult * x_t;
x_t = x_t - 1;
}
return new Integer(factResult);
}
}

3. Deploy JAR File into Database

3.1. Export JAR File

Right click the src folder in the Package Explorer, and select Export....

An Export window is displayed. Select Java -> JAR file, and click Next.

Enter the JAR file export destination /ceud/jarFact.jar.

Click the Finish button. If this is not your first time exporting the JAR file, a confirmation window will be displayed. Just click Yes to continue.

A JAR file jarFact.jar will be created in the folder /ceud.

3.2. Install JAR File into Database

3.2.1. Logon Bteq

Open a console window, and execute the bteq logon command.

There are 2 cases, depending on whether the DBS is running on the local machine or not.

A. Local DBS

Logon to the DBS  using the  IP address 127.0.0.1 for the local host.

TDExpress14.0_Sles10:~ # bteq .logon 127.0.0.1/debugger,debugger

B. Remote DBS

Logon to the DBS running in a remote machime.  Its IP address is 192.168.85.128 in this example.

TDExpress14.0_Sles10:~ # bteq .logon 192.168.85.128/debugger,debugger

3.2.2 New JUDF

If this is to create a new JUDF, please execute the following command at the bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

call sqlj.install_jar('CJ!/ceud/jarFact.jar','jarIdFact',0);

An output message will be displayed when the execution finishes.

 *** Procedure has been executed.

3.2.3 Existing JUDF

If this is to update an existing JUDF, please execute the following command at the bteq prompt.

BTEQ -- Enter your SQL request or BTEQ command:

call sqlj.replace_jar('CJ!/ceud/jarFact.jar','jarIdFact');

An output message will be displayed when the execution finishes.

 *** Procedure has been executed.

4. Install DDL in Database

If there is no bteq prompt, please execute [3.2.1. Logon Bteq] section.

4.1 Create New JUDF

If this is to create a new JUDF fact, please execute the following command at the bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE FUNCTION fact
(x INTEGER)
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
CALLED ON NULL INPUT
EXTERNAL NAME 'jarIdFact:scalar.Factorial.fact(java.lang.Integer) returns java.lang.Integer';

An output message will be displayed when the execution finishes.

 *** Function has been created.

4.2 Update Existing JUDF

If the fact UDF already exists, please execute the following command at the bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

REPLACE FUNCTION fact
(x INTEGER)
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
CALLED ON NULL INPUT
EXTERNAL NAME 'jarIdFact:scalar.Factorial.fact(java.lang.Integer) returns java.lang.Integer';

An output message will be displayed when the execution finishes.

 *** Function has been replaced.

5. Run JUDF in Database

If there is no bteq prompt, please execute [3.2.1. Logon Bteq] section.

Execute the following query to run the fact UDF.

 BTEQ -- Enter your SQL request or BTEQ command:

Select fact(4);

The result will be displayed when the execution finishes.

 *** Query completed. One row found. One column returned.

6. Debug JUDF in Eclipse

There are 2 debugging types for JUDF, depending on DBS location.

  • Local JUDF debugging - Eclipse and DBS are running on the same machine.
  • Remote JUDF debugging - Eclipse and DBS are running on separate machines.

There is no difference for debugging a local JUDF or a remote JUDF, except for the host IP address entered in Debug configuration.

6.1. Set Breakpoint

For easy reference, we will show the line numbers in the text editor. To do this, right click the left margin of the text editor, and select Show Line Number.

Double click the line number where you want to set breakpoint. A blue point shows the breakpoint has been set at line 12 of the source code.

To remove the breakpoint, just double click the blue point in the left margin.

6.2. Debug Configuration

Click the project judf_scalar in Package Explorer and then select the pull-down menu Run -> Debug Configurations....

A Debug Configurations setting window is displayed. Double click Remote Java Application in the left list, and a debug configuration judf_scalar is created automatically.

The Port value must equal JavaBaseDebugPort + offset. We have set the JavaBaseDebugPort to 8000 in the guide How to Setup the UDF / XSP Debugging Environment, and the UDF offset is always 2000. So set the Port value to 10000.

If we have not executed any Java UDF code since database startup, the remote debug port is not opened. So please make sure you have executed a Java UDF once before debugging.

A. Local DBS

Change the Port to 10000.

Click the Debug button, the Eclipse debugger will try to connect to the debug port. 

If the connection is created successfully, Eclipse will show the connected JVM with all the threads in Debug view.

B. Remote DBS

Change the Host to 192.168.85.128 which is the IP address of the machine running DBS. And change the Port to 10000.

Click the Debug button, the Eclipse debugger will try to connect to the remote debug port. 

If the connection is created successfully, Eclipse will show the connected JVM with all the threads in Debug view.

6.3. Breakpoint Hit

If there is no bteq prompt, please execute [3.2.1. Logon Bteq] section.

Enter the following query at the bteq prompt:

 BTEQ -- Enter your SQL request or BTEQ command:

Select fact(4);

The breakpoint will be hit.

We hit the breakpoint at line 12. The backtrace shows the thread was paused at Factorial.fact(Integer) line: 12.

Now we can see the variables in the Variables view and check other information in corresponding view.

6.4. Debugging

There are several debug buttons used for debugging.

  1. Resume (short-cut key: F8)
  2. Suspend
  3. Teminate
  4. Disconnect
  5. Step Into (short-cut key: F5)
  6. Step Over (short-cut key: F6)
  7. Step Return (short-cut key: F7)

Press the Step Over button or press F6 2 times. Thread execution now pauses at line 15.

Double click the left margin of line 19 to set a new breakpoint there, and click the Resume button or press F8 to continue.

We hit the new breakpoint at line 19. Press F8 again to continue.

We hit the breakpoint at line 19 again when we go through the second cycle of the while loop. In the Variables view, we can see the line color of the changed variables is changed to yellow.

Double click the breakpoint at line 19 to remove this breakpoint. Click the Resume button or press F8 again. The UDF execution will be resumed.

6.5. Stop Debugging

To stop debugging, click the Disconnect button.

The debugging session will be teminated, and the udf execution will be resumed.

For more tips and tricks, please refer to Eclipse (Indigo) Java development user guide.