How to Create and Debug a Table 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 Table 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 languages, 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 table 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 table Java UDF takes in raw data for sales personnel for a sales region and returns a table of sales people based on a region.

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 create and debug a table 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 is displayed. Enter the Project name "judf_table", and click the Next button.

1.3. Change Default Setting

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 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. A new Java project judf_table will be created.

2. Create JUDF Source File in Java Project

2.1. Create New Classes

We will create two Java classes GetData and SalesPeople for this project.

2.1.1. GetData

Right click the project, select New -> Class.

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

A new class GetData is created. 

2.1.2. SalesPeople

Right click the project, select New -> Class.

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

A new class SalesPeople is created. 

2.2. Edit the Source Files

2.2.1. GetData

Edit the Java file GetData.java as shown below.

/**
* This example shows the Java code for a table UDF that takes in raw data
* for sales personnel for a sales region and returns a table of sales people
* based on a region.
*
*/
package util;

import java.sql.SQLException;

/**
* @author root
*
*/
public class GetData {
public static String[] getRows(String rawData) throws SQLException {
if (rawData == null) {
throw new SQLException("Error proccessing data ", "38T09");
} else if (rawData.length() == 0) {
throw new SQLException("Error proccessing data", "38T09");
}
String[] returnValue = rawData.split(",");

return returnValue;
}

public static String[] getColumns(String rawData) throws SQLException {
if (rawData == null) {
throw new SQLException("Error proccessing data ", "38T09");
} else if (rawData.length() == 0) {
throw new SQLException("Error proccessing data", "38T09");
}

String[] returnValue = rawData.split(":");

return returnValue;
}
}

2.2.2. SalesPeople

Edit Java file SalesPeople.java as shown below.

/**
* This example shows the Java code for a table UDF that takes in raw data
* for sales personnel for a sales region and returns a table of sales people
* based on a region.
*
*/
package table;

import java.io.IOException;
import java.io.Serializable;
import java.sql.SQLException;

import util.GetData;

import com.teradata.fnc.Tbl;

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

/**
* @param region
* @param rawData
* @param id
* @param saleRegion
* @param name
* @param colType
*/
public static void getSalesPeople(int region, String rawData, int[] id,
int[] saleRegion, String[] name, String[] colType)
throws SQLException {

/**
* Inner context class for SalesPeople Java User Defined Function
*
*
*/

class GenCtx implements Serializable {
private int region;
private String[] rows;
int count = 0;

public GenCtx() {

}

public GenCtx(int maxRows, int MaxRowSize) {
rows = new String[maxRows];
for (int i = 0; i < maxRows; i++) {
rows[i] = new String(new char[100]);
}
}

/**
* Get Context Class member region
*
* @return region
*/
public int getRegion() {
return this.region;
}

/**
* Set Context class member region
*
* @param region
*/
public void setRegion(int region) {
this.region = region;
}

/**
* Set Context class member rawData
*
* @param rawData
* @throws SQLException
*/
public void setRawData(java.lang.String rawData)
throws SQLException {
rows = GetData.getRows(rawData);
count = rows.length;
}

/**
* Get Context Class member rawData
*
* @return rawData
*/
public java.lang.String[] getColumns() throws SQLException {
count--;
if (count < 0) {
throw new SQLException("EOF", "02000");
}
return GetData.getColumns(this.rows[count]);
}

}
int[] phase = new int[1];
Tbl tbl = new Tbl();
GenCtx obj = null;
try {

switch (tbl.getPhase(phase)) {

case Tbl.TBL_MODE_CONST:

switch (phase[0]) {
case Tbl.TBL_PRE_INIT:

if (tbl.firstParticipant()) {
/* participant */
return;
} else {
/* don't participate */
if (!tbl.optOut()) {
throw new SQLException("Opt-out failed.", "38T06");
}
return;
}
case Tbl.TBL_INIT:
obj = new GenCtx();
obj.setRegion(region);
obj.setRawData(rawData);
tbl.allocCtx(obj);
tbl.setCtxObject(obj);

break;
case Tbl.TBL_BUILD:

obj = (GenCtx) tbl.getCtxObject();
String[] columns = obj.getColumns();
try {
id[0] = Integer.parseInt(columns[0]);
} catch (Exception e) {
throw new SQLException("Error proccessing data ",
"38T09");
}
saleRegion[0] = obj.getRegion();
name[0] = columns[1];
colType[0] = "Const";
tbl.setCtxObject(obj);

break;
case Tbl.TBL_END:
// Put in ending logic
break;
}
break;
/**********************************/
/* Process the varying expression */
/**********************************/
case Tbl.TBL_MODE_VARY:
switch (phase[0]) {
case Tbl.TBL_PRE_INIT:
obj = new GenCtx(10, 100);
tbl.allocCtx(obj);
tbl.setCtxObject(obj);
break;
case Tbl.TBL_INIT:
obj = (GenCtx) tbl.getCtxObject();
obj.setRegion(region);
obj.setRawData(rawData);
tbl.setCtxObject(obj);
break;
case Tbl.TBL_BUILD:

obj = (GenCtx) tbl.getCtxObject();
String[] columns = obj.getColumns();
try {
id[0] = Integer.parseInt(columns[0]);
} catch (Exception e) {
throw new SQLException("Error proccessing data ",
"38T09");
}
saleRegion[0] = obj.getRegion();
name[0] = columns[1];
colType[0] = "Vary";
tbl.setCtxObject(obj);

break;
case Tbl.TBL_FINI:
// Finish logic
break;
case Tbl.TBL_END:
// Ending logic
break;
case Tbl.TBL_ABORT:
// Abort logic
break;
}
return;
}

} catch (IOException eio) {
throw new SQLException("IO Exception", "U0002");
} catch (ClassNotFoundException e) {
throw new SQLException("ClassNotFoundException", "U0003");
}
}
}

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.

Input the JAR file export destination "/ceud/jarSalesPeople.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.

A JAR Export warning window is displayed. Click OK to continue.

A JAR file jarSalesPeople.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/jarSalesPeople.jar','jarIdSalesPeople',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/jarSalesPeople.jar','jarIdSalesPeople');

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

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE FUNCTION "getSalesPeople" (
"region" INTEGER,
"rawData" VARCHAR(256))
RETURNS TABLE (id INTEGER, saleRegion INTEGER, name VARCHAR(64), colType VARCHAR(64))
SPECIFIC "getSalesPeople"
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'jarIdSalesPeople:table.SalesPeople.getSalesPeople';

An output message will be displayed when the execution finishes.

 *** Function has been created.

4.2. Update existing JUDF

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

 BTEQ -- Enter your SQL request or BTEQ command:

REPLACE FUNCTION "getSalesPeople" (
"region" INTEGER,
"rawData" VARCHAR(256))
RETURNS TABLE (id INTEGER, saleRegion INTEGER, name VARCHAR(64), colType VARCHAR(64))
SPECIFIC "getSalesPeople"
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'jarIdSalesPeople:table.SalesPeople.getSalesPeople';

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

The table JUDF can be executed in 2 modes, constant mode and variable mode,  depending on input arguments.

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

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

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE SET TABLE raw_sales_people, NO FALLBACK,                     
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
region INTEGER,
pending_data VARCHAR(1024) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX (region);

INSERT INTO raw_sales_people VALUES(2,'1:Joe bishop,2:Mark Bishop');
INSERT INTO raw_sales_people VALUES(1,'3:Jack Brown,4:Mark Brown,5:Harry Brown');
INSERT INTO raw_sales_people VALUES(1,'6:John Smith,7:Sara Smith,9:Mathew Smith,10:Peter Smith"');


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

 BTEQ -- Enter your SQL request or BTEQ command:

Select region, pending_data from raw_sales_people;

The result will be displayed when the query finishes.

5.2. Run JUDF

5.2.1. Constant Mode Table JUDF

The constant mode table JUDF is where data being passed to the table JUDF is a literal string.

Submit the following query to execute getSalesPeople UDF in constant mode.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT  * FROM  Table (getSalesPeople(1,'6:John Smith,2:Mark Bishop,3:Jack Brown')) as t1;

The result will be displayed when the execution finishes. The getSalesPeople UDF in constant mode correctly returns 3 rows of data from UDF constant arguments.

 *** Query completed. 3 rows found. 4 columns returned.

5.2.2. Variable Mode Table JUDF

The variable mode table JUDF is where the data passed into the JUDF is from a database table.

Submit the following query to execute getSalesPeople UDF in variable mode.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT DISTINCT
sales_people.id,
sales_people.name,
sales_people.colType,
sales_people.saleRegion
FROM
raw_sales_people,
TABLE (
getSalesPeople(
raw_sales_people.region,
raw_sales_people.pending_data))
AS sales_people
WHERE sales_people.saleRegion = 1;

The result will be displayed when the execution finishes. The getSalesPeople UDF in variable mode correctly returns 7 rows of data in raw_sales_people table for region "1".

 *** Query completed. 7 rows found. 4 columns 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 between debugging a local JUDF and debugging 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.

6.1.1. Constant Mode Table JUDF

Double click the line number of line 110 to set the 1st breakpoint used for constant mode table JUDF.

A blue point shows the breakpoint has been set at line 110 of the source code.

6.1.2. Variable Mode Table JUDF

Double click the line number of line 156 to set the 2nd breakpoint used for variable mode table JUDF.

A blue point shows the breakpoint has been set at line 156 of the source code.

6.2. Debug configuration

Click the project judf_ table 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 then a debug configuration judf_ table 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 the 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.

6.3.1. Constant Mode Table JUDF

Enter the following query at bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT  * FROM  Table (getSalesPeople(1,'6:John Smith,2:Mark Bishop,3:Jack Brown')) as t1;

The 1st breakpoint which is at line 110 for constant mode table JUDF will be hit.

We hit breakpoint at line 110 . The backtrace shows the thread was paused at SalesPeople.getSalesPeople(int, String, int[], int[], String[], String[]) line: 110.

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

In the Variables view, the rawdata is "6:John Smith,2:Mark Bishop,3:Jack Brown".

Press F8 to resume execution. The query will finish and the result will be shown in the bteq window.

6.3.2. Variable Mode Table JUDF

Enter the following query at bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT DISTINCT
sales_people.id,
sales_people.name,
sales_people.colType,
sales_people.saleRegion
FROM
raw_sales_people,
TABLE (
getSalesPeople(
raw_sales_people.region,
raw_sales_people.pending_data))
AS sales_people
WHERE sales_people.saleRegion = 1;

The 2nd breakpoint which is at line 156 for variable mode table JUDF will be hit.

We hit breakpoint at line 156 . The backtrace shows the thread was paused at SalesPeople.getSalesPeople(int, String, int[], int[], String[], String[]) line: 156.

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

In the Variables view, the rawdata is "3:Jack Brown,4:Mark Brown,5:Harry Brown".

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)

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

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 Java development user guide.

3 REPLIES
Enthusiast

Re: How to Create and Debug a Table JUDF using Eclipse

I'm using the latest VM (TDExpress14.10_Sles11) and I can't get the debugger to attach - the message is "Failed to connect to remote VM. Connection refused. Connection refused: connect".

Looking on the VM I can't find anything listening on port 10000 - is there anything I need to do to enable debugging on the server?

Enthusiast

Re: How to Create and Debug a Table JUDF using Eclipse

I found this http://developer.teradata.com/extensibility/articles/udf-debugging/udf-xsp-debugging-environment - it appears you have to change some database parameters and create a user "DEBUGGER" for the debugging.

A concise guide to all this stuff would be nice - I don't need screen shots but I do need to know what needs changing / setting up.

Teradata Employee

Re: How to Create and Debug a Table JUDF using Eclipse

Hi MarkMarsh,  the reason why creating a new user is to confirm all the conditions will be satisfied to debug a UDF. 

If you already have one, just confirm your privileges in section 4.3 Grant Privileges, and confirm the settings by cufconfig command in section 5.3 Change Settings.

Thanks.