How to Create and Debug an Aggregate 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 an Aggregate 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 aggregate 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 aggregate Java UDF takes a list of prices from a Products database table and returns the average price from that list.

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 an aggregate 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_aggregate", 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 javFnc.jar into the settings.

Click the Libraries tab to see the library default settings.

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

A JAR Selection window is displayed. Select File System in the left 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_aggregate 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 "aggregate" and Name "AveragePrice". Click the Finish button.

A new class AveragePrice will be created. 

2.2. Edit the Source File

Edit the Java source as shown below.

/**
* This example shows the Java code for an aggregate UDF that calculates the
* average price of products.
*
*/
package aggregate;

import java.sql.SQLException;

import com.teradata.fnc.Context;
import com.teradata.fnc.Phase;
import java.io.Serializable;

/**
* @author root
*
*/
public class AveragePrice {

public static double getAveragePrice(Phase phase, Context[] context,
double price) throws SQLException {

double returnValue = 0;

try {
AveStor s1 = null;

// AGR_DETAIL, AGR_COMBINE, and AGR_FINAL phases use the value from
// the aggregate storage area.
if (phase.getPhase() > Phase.AGR_INIT
&& phase.getPhase() < Phase.AGR_NODATA) {

s1 = (AveStor) context[0].getObject(1);

}
switch (phase.getPhase()) {
// The AGR_INIT phase is executed once.
case Phase.AGR_INIT:
s1 = new AveStor();
// Allocate intermediate storage and initialize it.
context[0].initCtx(s1);
// Fall through to detail phase also.
case Phase.AGR_DETAIL:
// Perform SUM
double total = s1.getPrice() + price;
s1.setPrice(total);
int countTotal = s1.getCount() + 1;
s1.setCount(countTotal);
break;
case Phase.AGR_COMBINE:

AveStor s2 = (AveStor) context[0].getObject(2);

// SUM between AMPs.
double totalCombine = s1.getPrice() + s2.getPrice();

s1.setPrice(totalCombine);
int countTotalCombine = s1.getCount() + s2.getCount();
s1.setCount(countTotalCombine);
break;
case Phase.AGR_FINAL:
// Final, return SUM.
return s1.getAverage();
case Phase.AGR_NODATA:
// Not expecting no data.
return -1;
default:
throw new SQLException("Invalid Phase", "38U05");
}

// Save the intermediate SUM in the aggregate storage.
context[0].setObject(1, s1);

} catch (Exception ex) {
throw new SQLException(ex.getMessage(), "38101");
}

return returnValue;
}

}

class AveStor implements Serializable {
private double price;
int count = 0;

public int getCount() {
return count;
}

public void setCount(int count) {
this.count = count;
}

public AveStor() {
// Initialize to context object to maximum size
}

public double getPrice() {
return this.price;
}

public void setPrice(double price) {
this.price = price;
}

public double getAverage() {
return (price / ((double) count));
}
}

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/jarAveragePrice.jar.

Click the Finish button.

If this is not your first time exporting the JAR file, a Confirm Replace window will be displayed. Just click Yes to continue.

And a JAR Export warning window will be displayed. Click OK to continue.

A JAR file jarAveragePrice.jar will be created (or updated) 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/jarAveragePrice.jar','jarIdAveragePrice',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/jarAveragePrice.jar','jarIdAveragePrice');

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 getAveragePrice, please execute the following command at the bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE FUNCTION "getAveragePrice" ("price" FLOAT)
RETURNS FLOAT
CLASS AGGREGATE (66)
SPECIFIC "getAveragePrice"
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'jarIdAveragePrice:aggregate.AveragePrice.getAveragePrice(com.teradata.fnc.Phase,com.teradata.fnc.Context[], double) returns double';

An output message will be displayed when the execution finishes.

 *** Function has been created.

4.2 Update Existing JUDF

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

 BTEQ -- Enter your SQL request or BTEQ command:

REPLACE FUNCTION "getAveragePrice" ("price" FLOAT)
RETURNS FLOAT
CLASS AGGREGATE (66)
SPECIFIC "getAveragePrice"
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'jarIdAveragePrice:aggregate.AveragePrice.getAveragePrice(com.teradata.fnc.Phase,com.teradata.fnc.Context[], double) returns double';

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.

5.1. Data Preparation

To run your aggregate JUDF, you will need a database table with data.

Submit the following SQLs at the bteq prompt to create and populate the products table.

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE TABLE products (
id INTEGER NOT NULL PRIMARY KEY,
description varchar(255),
price decimal(15,2)
);

INSERT INTO products (id, description, price) values(1, 'Lamp', 25.0);
INSERT INTO products (id, description, price) values(2, 'Table', 50.0);
INSERT INTO products (id, description, price) values(3, 'Chair', 15.0);

Submit the following query to verify the contents of this table.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT id, price, description FROM products;

The result will be displayed when the query finishes.

5.2. Run JUDF

Submit the following query  to execute getAveragePrice UDF.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT getAveragePrice(price) FROM products;

The result will be displayed when the execution finishes. The getAveragePrice UDF correctly returns the avarage price of "30"  for data in products table.

 *** 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 of line 26. A blue point shows the breakpoint has been set at line 26 of the source code.

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

6.2. Debug configuration

Click the project judf_aggregate 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_aggregate 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 change the Port value to 10000.

Note: If we have not executed any Java UDF 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 Eclipse doesn't change its Perspective automatically, please select the pull-down menu Window -> Open Perspective -> Debug to switch to Debug perspective.

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 bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT getAveragePrice(price) FROM products;

The breakpoint will be hit.

We hit the breakpoint at line 26. The backtrace shows the thread was paused at AveragePrice.getAveragePrice(Phase, Context[], double) line: 26.

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

In the Variables view, the price is "15.0" and the Phase is "1" (Phase.AGR_INIT).

Here only one thread was hit because all data is stored in one AMP. Press F8 four times to resume the running. The query will finish and the result will be shown in the bteq window.

We now add data to another AMP, and show how debugging can be done in two threads (one thread per AMP).

Enter the following SQL at the bteq prompt to add data to products table.

 BTEQ -- Enter your SQL request or BTEQ command:

INSERT INTO products (id, description, price) VALUES(9, 'Zhao',  33.4);

Submit the following query to verify the contents of this table.

SELECT id, price, description FROM products;

The result will be displayed when the query finishes.

After appending data, execute this query at the bteq prompt one more time.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT getAveragePrice(price) FROM products;

The same breakpoint will be hit in two different threads at the same time.

The first breakpoint is hit  in AMP 0 when it processes the row [3, 'Chair', 15.0].

The second breakpoint is hit in AMP 1 when it processes the row  [9, 'Zhao', 33.4] which was just added.

Click the 2nd suspended thread in Debug view to switch thread. The variables of this thread will be shown in Variables 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)

Here, we can debug the two threads separately like other multiple-thread program. 

We can use Step Over button (F6) to execute the code step by step, and Resume button (F8) to resume the thread running. We also can check the variable values in the Variables view.

Note: The query will complete and give us the result after both threads resume running.

The average price should be 30.85 (3.08500000000000E001) at this moment.

6.5. Stop Debugging

To stop the debugging, click the Disconnect button.

The debugging session will be terminated, and the UDF execution will be resumed.

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

2 REPLIES

Re: How to Create and Debug an Aggregate JUDF using Eclipse

Your link to "How to Setup the UDF / XSP Debugging Environment" is broken.  It should point to "http://developer.teradata.com/extensibility/articles/udf-debugging/udf-xsp-debugging-environment" from what I can tell.

Also, this is the first article I've seen that includes the Phase and Context in the external method signature found in the REPLACE FUNCTION statement.  The Teradata SQL External Routine Programming document for Teradata 14.0 does not include these in the external method signature found in the REPLACE FUNCTION (see the Java aggregate examples in appendix A), even though they are found in the underlying java implementation.  Is this a change from 13.10 to 14.0?  Or a mistake in the documentation?  We currently use 13.10.




Teradata Employee

Re: How to Create and Debug an Aggregate JUDF using Eclipse

Hi Paul,

Thanks for reading and comments. The link has been fixed.

The sample code is from the following article which version is 13.0, and creating the UDF by Teradata Plugin for Eclipse.

http://developer.teradata.com/tools/articles/creating-an-aggregate-java-user-defined-function-with-t...

You can try that Plugin if you using Eclipse. And would get a good experiment for developing UDF by that Plug-in. I use that Plugin when I writing these articles for UDF confirmation.