How to Create and Debug a Java XSP 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 Java XSP using Eclipse

In addition to stored procedures, which use SQL control and condition-handling statements, input and output parameters, and local variables to provide applications with a server-based, precompiled procedural interface, Teradata Database supports external stored procedures.

You can write external stored procedures in the Java programming language, install them on the database, and then use the SQL CALL statement to call them like other stored procedures. JXSP (Java eXternal Stored Procedures) can use JDBC to directly execute SQL statements.

This article will show how to create a simple Java XSP, execute it in protected mode, and debug it using Eclipse. In protected execution mode, the XSP runs as a separate process under 'tdatuser', a local operating system user that the Teradata Database installation process creates. The XSP can access the system resources for which tdatuser has privileges.

In this example, the Java XSP returns the last name from a given full name.

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 simple JXSP using Eclipse.

  1. Create Java Project in Eclipse
  2. Create JXSP Source File in Java Project
  3. Deploy JAR File into Database
  4. Install DDL in Database
  5. Run JXSP in Database
  6. Debug JXSP 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.

New Java Project window comes up. Enter the Project name "jxsp_simple", 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.

JAR Selection window is displayed. Select File System on the left Places, and select the file /usr/tdbms/bin/javFnc.jar on 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 jxsp_simple will be created.

2. Create JUDF Source File in Java Project

2.1. Create New Class

Right click the project, select New -> Class.

New Java Class window is displayed. Enter Package "jxsp" and Name "LastName". Click the Finish button.

A new class LastName will be created. 

2.2. Edit The Source File

Edit the Java source as shown below.

/**
* This example shows the Java code for a Java XSP that
* get the last name from a name string.
*/
package jxsp;

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

/**
* @param name
* @param lastName
*/
public static void getLastName(String name, String[] lastName) {
if (name != null) {
int index = name.indexOf(" ");
if (index == -1) {
lastName[0] = name;
} else {
lastName[0] = name.substring(index, name.length());
}
}
}
}

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/jarLastName.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 jarLastName.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 JXSP

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

 BTEQ -- Enter your SQL request or BTEQ command:

call sqlj.install_jar('CJ!/ceud/jarLastName.jar','jarIdLastName',0);

An output message will be displayed when the execution finishes.

 *** Procedure has been executed.

3.2.3 Existing JXSP

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

 BTEQ -- Enter your SQL request or BTEQ command:

call sqlj.replace_jar('CJ!/ceud/jarLastName.jar','jarIdLastName');

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

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE PROCEDURE "getLastName" (
IN "name" VARCHAR(256),
OUT "lastName" VARCHAR(256))
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
EXTERNAL NAME 'jarIdLastName:jxsp.LastName.getLastName(java.lang.String,java.lang.String[])';

An output message will be displayed when the execution finishes.

 *** Procedure has been created.

4.2. Update Existing JXSP

If the getLastName XSP already exists, please execute the following command at the bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

REPLACE PROCEDURE "getLastName" (
IN "name" VARCHAR(256),
OUT "lastName" VARCHAR(256))
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
EXTERNAL NAME 'jarIdLastName:jxsp.LastName.getLastName(java.lang.String,java.lang.String[])';

An output message will be displayed when the execution finishes.

 *** Procedure has been replaced.

5. Run JXSP in Database

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

Execute the following statement to run the getLastName XSP.

 BTEQ -- Enter your SQL request or BTEQ command:

Call getLastName('John Smith',name);

The result will be displayed when the execution finishes.

 *** Procedure has been executed.

6. Debug JXSP in Eclipse

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

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

There is no difference for debugging a local JXSP or a remote JXSP, 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 18 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 jxsp_simple in Package Explorer and then select the pull-down menu Run -> Debug Configurations....

Debug Configurations setting window is displayed. Double click Remote Java Application on the left list, and a debug configuration jxsp_simple 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. For a JXSP running on secure servers , the offset depends on which instance of the server is executing the routine (there could be upto 10 secure servers per vproc),  and on the type and ID of the vproc that is running the routine.

In our TDExpress VM, the JXSP offset could be 0 ~ 9 or 1000 ~ 1019, as indicated in the below table:



JXSP debug port offset
No. Vproc Type Vproc ID Offset Comment
1 PE 16383 0 ~ 9 The XSP is running in PE.
2 AMP 0 0 1000 ~ 1009 The XSP is running in AMP 0.
3 AMP 1 1 1010 ~ 1019 The XSP is running in AMP 1.

So in our case, the JXSP debug port must be in the range 8000 ~ 8009 or 9000 ~ 9019.

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

A. Local DBS

Here we use the following command to find out all the TCP/IP ports which are opened by secure server (udfsectsk)  processes.

TDExpress14.0_Sles10:~ # netstat -tanp | grep udfsectsk

We found there are 2 ports opened by udfsectsk. Port 10000 is used for JUDFs running on a hybrid server; so  8001 is our JXSP debug port.  Set  the Port value in Debug Configurations window to "8001".

Note: In your case, there might be more than one XSP debug ports opened. Please find the right debug port from the above table.

Click the Debug button, the Eclipse debugger will try to connect to the remote 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

Open a console window, and execute SSH command to connect to the DBS.

TDExpress14.0_Sles10:~ # ssh root@192.168.85.128

When the first connection, a question Are you sure you want to continue connection (yes/no)? will be asked. Please answer it yes. After that, please enter the password of root user.

If the connection is established, a welcome message is displayed and the command prompt is shown.

We use the following command to find out all the TCP/IP ports which are opened by secure server (udfsectsk)  processes in remote DBS.

TDExpress14.0_Sles10:~ # netstat -tanp | grep udfsectsk

So 8000 is our JXSP debug port.

Set  the Port value in Debug Configurations window to "8000", and set the Host to "192.168.85.128".

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 remote 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:

Call getLastName('John Smith',name);

The breakpoint will be hit.

We hit the breakpoint at line 18. The backtrace shows the thread was paused at LastName.getLastName(String, String[]) line: 18.

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

6.5. 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 Step Over button or press F6. Thread execution now pauses at line 19.

Click the Resume button or press F8. The UDF execution will be resumed.

6.6. Stop Debugging

To stop the 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.

Tags (3)
7 REPLIES

Re: How to Create and Debug a Java XSP using Eclipse

Nice article.

The link you give for setting up the debugging environment doesn't work.

I found http://developer.teradata.com/extensibility/articles/udf-debugging/udf-xsp-debugging-environment

which I think is the correct one.

Teradata Employee

Re: How to Create and Debug a Java XSP using Eclipse

Hi araford, thanks for your comments. The link has been updated.

Fan

Re: How to Create and Debug a Java XSP using Eclipse

I implemented a jasp to send emails using SMTP protocol.  The sp_sendemail procedure can send emails out whe I executed the sp from bteq.  However, this sp raises an exception as follows when I execute from SQLA or Teradata Studio Express (SQLA - java version)

Executed as Single statement.  Failed [7828 : 38000] Unexpected Java Exception SQLSTATE 38000: An java.lang.RuntimeException (Couldn't connect to host, port: localhost, 25; timeout -1) exception was thrown.

Elapsed time = 00:00:00.295

STATEMENT 1: CALL  failed.

This does SQLA execute JXSP differently from BTEQ?

Any help appreciated.

Xinmin

Teradata Employee

Re: How to Create and Debug a Java XSP using Eclipse

Hi Xinmin, sorry i can not familiar with jasp, so i can not know what your question is. Maybe you can try your question to SQLA group. Sorry for inconvenience.

Enthusiast

Re: How to Create and Debug a Java XSP using Eclipse

That was very infomative and detailed article. Great effort to put all the pieces together. Thanks for sharing

Enthusiast

Re: How to Create and Debug a Java XSP using Eclipse

Can an XSP do the following 

1- write/read to/from a file 

2- execute an OS command

Thanks

Teradata Employee

Re: How to Create and Debug a Java XSP using Eclipse

Hi,

This is a very useful article for beginners. I had a doubt about JXSP, can I pass a table column as input to JXSP?

Thanks in advance