How to Set up a UDF / XSP Debugging Environment

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 Set up a UDF / XSP Debugging Environment

An integrated development environment is crucial to software developers for editing, building and debugging their program all within one environment. Therefore, setting up the right development environment is an important first task.

Here we set up a User-defined Functions (UDFs) / External Stored Procedures (XSPs) debugging environment used to debug typical UDF/XSPs. Eclipse Indigo is used as the IDE. A Teradata Express (TDExpress) VM is used as the running DBS.

Prerequisite for this Article

We use a TDExpress VM as the running DBS; hence a VMware Workstation or a VMware Player is needed. If you don't have either of these on your  PC, please download the free VMware Player from the following link: https://my.vmware.com/web/vmware/free#desktop_end_user_computing/vmware_player/4_0

If you have not worked through the guide Teradata Express 14.0 for VMware User Guide, please do it now before you continue.

Article Outline

In this article, we will do the following things to setup the UDF/XSP debugging environment.

  1. Get an Official TDExpress VM
  2. Install Eclipse for Java Developer
  3. Install CDT Eclipse Plug-in for C/C++ Developer
  4. Create a Teradata Database User
  5. View & Change Teradata UDF/XSP Settings
  6. Set up for C/C++ Remote Debugging

There are 2 typical UDF/XSP debugging scenarios depending on the location of the running DBS.

A. Local debugging

DBS and the Eclipse IDE are running on the same machine. 

Please execute sections 1-5 in this machine.

B. Remote debugging

DBS and the Eclipse IDE are running on separate machines. We will refer to the machine running Eclipse as client, and the remote DBS machine as DBS.

Please execute sections 1-3 and part of section 6 in client, and sections 4-6 in DBS.

1. Get an Official TDExpress VM

1.1. Register for DevX Account

Before you login to the DevX network, please register for a free account.

Open the DevX website, and click the Register link in the top right corner.

http://developer.teradata.com/

The Create Account window is displayed. Please input all the information and check the checkbox I agree to the Terms of Use.

After that, click Create Account. If there is no error in the input, your account will be created.

Please click Update Profile to fill in other user information. 

1.2. Login to DevX

If you just created an account in section [1.1. Register], you will be logged in automatically.

Otherwise, please click the Sign In link in the top right corner.

Input your username and password, and click the Sign in button to log in.

1.3. Download TDExpress VM

Open the Teradata Express VM for VMware Player download page.

http://downloads.teradata.com/download/database/teradata-express/vmware

If you have not filled in enough contact information in your DevX account, you will not be able to download.

Download TDE 14.00.00.01 for VMware(40GB) by clicking the link Teradata Express - 40GB capacity (.7z, 3.0GB). The download will start automatically.

1.4. Start up TDExpress VM

When the download finishes, unzip the file td-express-14.00.00.01-vm-40gb.7z to a folder, and then double click the TDExpress14.0_Sles10.vmx file to power on the VM.

The VMware Player will start up the VM automatically.

If this is your first time starting up this VM, the following window will appear:

Click the I copied it button to continue. This will get a new IP address for your VM.

1.6. Login to VM

When the login screen is displayed, input default username/password as root/root, and press Enter.

We have started the Teradata Express VM successfully. The following activities are all in the TDExpress VM.

Note: When the system has started, execute Viewpoint Stop in the desktop to stop Viewpoint services. This will save memory since we don’t use Viewpoint.

1.7. Setup ceud Directory in VM

In the TDExpress VM, the / filesystem is very small but  /var/opt/teradata has a lot of free disk space.  So we will create a ceud folder in /var/opt/teradata to be used for our debugging.

Open a console window, and submit the following commands:

TDExpress14.0_Sles10:~ # md /var/opt/teradata/ceud

Create a soft link /ceud to /var/opt/teradata/ceud folder.

TDExpress14.0_Sles10:~ # ln -s /var/opt/teradata/ceud/ /ceud

2. Install Eclipse for Java Developer

2.1. Download Eclipse

Open the Eclipse Indigo SR2 download page in your VM.

http://eclipse.org/downloads/packages/release/indigo/sr2

Select Eclipse IDE for Java Developers for Linux 64 bit version.

Alternately, we can download the package directly through the following link.

http://www.eclipse.org/downloads/download.php?file=/technology/epp/downloads/release/indigo/SR2/ecli...

2.2. Unpack

Unpack the package to /ceud folder using File Roller.

2.3. Make a Link

For convenience, make a soft link for eclipse folder.

TDExpress14.0_Sles10:~ # ln -s /ceud/eclipse/ /usr/eclipse

2.4. Create EclipseStart Launcher

For convenience, we create a launcher in the Desktop.

Right-click in the Desktop, and click Create Launcher....

Fill in the Create Launcher window. Input the following items:

Name: EclipseStart

Command: /usr/eclipse/eclipse

And click the OK button.

An EclipseStart launcher is created in the Desktop.

2.5. Change Eclipse Default Setting

Edit the Eclipse configuration file /usr/eclipse/eclipse.ini before starting up Eclipse.

Add the following 2 lines after -Dosgi.requiredJavaVersion=1.5

-Dorg.eclipse.swt.browser.UseWebKitGTK=true
-Dorg.eclipse.swt.browser.XULRunnerPath==/usr/lib/xulrunner-1.8.1.21

This will prevent you from getting the below error when running Eclipse.

2.6. Start up Eclipse

Double-click the EclipseStart launcher to start up Eclipse.

If this is the first time starting Eclipse, a Workspace Launcher setting window will appear.

Enter the Workspace /usr/eclipse/workspace, and check the Use this as the default and do not ask again check box. Click OK to continue.

Note: The default workspace /root/workspace might be used by another application in the VM. So we use another workspace /usr/eclipse/workspace to avoid possible conflicts.

A welcome window will be displayed. Just click the close icon in the welcome window title bar.

Eclipse has been started successfully.

3. Install CDT Eclipse Plug-in for C/C++ Developer

If you are a C/C++ developer and need to work on a  UDF/XSP written in C/C++, please install the CDT Eclipse plug-in.

3.1. Open Install Window

Open Help -> Install New Software...

An Install window comes up. Type the following CDT update site address in the Work with: input box, and press Enter.

http://download.eclipse.org/tools/cdt/releases/indigo

3.2. Install CDT Features

Click the triangle of the first line CDT Main Features, and check the first C/C++ Development Tools check-box.

Click the triangle of the second line CDT Optional Features, and check the Eclipse Debugger for C/C++ check-box.

Click Next, and Next button again.

In the Review Licenses window, check the radio button I accept the terms of the license agreement, and click the Finish button.

An Installing Software progress window will display. It can take several minutes to install.

3.3. Restart Eclipse

When the installation is finished, a restart is needed. Click Restart Now to restart Eclipse.

After the restart, the welcome window will be displayed, with the C/C++ Development feature added to the Overview. Just click the close icon in the welcome window title bar.

The Eclipse CDT plugin has been installed successfully.

4. Create a Teradata Database User

In DBS, we now create a DEBUGGER user and give this user permissions to create and execute UDF/XSPs.

Note: As stated previously, this section will be done in your DBS.

4.0. Change DBS Prompt

To avoid confusion from client, we change the DBS prompt temporarily. 

TDExpress14.0_Sles10:~ # export PS1="TDExpress14.0_DBS:\w # "

TDExpress14.0_DBS:~ #

Note: This will only change the current terminal window's prompt. If you open a new terminal window, please run this command first before running any other command.

4.1. Start Teradata Database

At the command prompt, enter the following:

TDExpress14.0_DBS:~ # /etc/init.d/tpa start

After a few moments, the system reports:

Teradata Database Initiator service is starting...

Teradata Database Initiator service started successfully.

If the database is already running, then a message will show:

PDE( PdeMain ) already running:

Then give the database some time to initialize.

To verify the database is fully up and operational, enter the following command:

TDExpress14.0_DBS:~ # pdestate -a

The result should show:

PDE state is RUN/STARTED.

DBS state is 5: Logons are enabled - The system is quiescent

4.2. Create the DEBUGGER user

Login to bteq as dbc user, and create a DEBUGGER user.

From the command prompt, enter the following:

TDExpress14.0_DBS:~ # bteq .logon 127.0.0.1/dbc,dbc

BTEQ -- Enter your SQL request or BTEQ command:

CREATE USER DEBUGGER
AS PERMANENT=50E6,
SPOOL=100E6,
TEMPORARY=50E6,
PASSWORD=debugger;

 *** User has been created.

 *** Total elapsed time was 2 seconds.

4.3. Grant Privileges

For UDF/XSP debugging, necessary privileges must be given to the DEBUGGER user. Please submit the following commands.

BTEQ -- Enter your SQL request or BTEQ command:

GRANT CREATE FUNCTION ON DEBUGGER TO DEBUGGER WITH GRANT OPTION;
GRANT CREATE EXTERNAL PROCEDURE ON DEBUGGER TO DEBUGGER WITH GRANT OPTION;
GRANT EXECUTE PROCEDURE ON SQLJ TO DEBUGGER WITH GRANT OPTION;

After execution, the result messages will display.

*** Grant accepted.

 *** Total elapsed time was 1 second.

4.4. Logout

Submit the following command to logout.

BTEQ -- Enter your SQL request or BTEQ command:

.quit

After execution, the result messages will display.

 *** You are now logged off from the DBC.

 *** Exiting BTEQ...

 *** RC (return code) = 0

5. View & Change UDF/XSP Settings

Teradata DBS has a cufconfig utility which allows you to view and change configuration settings for the UDF and XSP subsystem.

Note: As stated previously, this section will be done in your DBS.

We will change the UDFServerTasks and JavaBaseDebugPort values to make the debugging easier.

UDFServerTasks

The UDFServerTasks is the number of protected mode UDFs, XSPs, or UDMs (User-defined Method) that can be run simultaneously per vproc. The default value is 2. We will change the value to 1.

JavaBaseDebugPort

The JavaBaseDebugPort is the base number used to determine the debug port for Java UDF and Java XSP. Its default value is 0, meaning the JVM debug port is disabled. We will change the value to 8000.

The actual port number to use for attaching a debugger to a Java UDF or XSP is the value of JavaBaseDebugPort plus an offset. 

  1. For Java UDF running on a hybrid server, the offset is always 2000, so the Java UDF remote debug port is 8000 + 2000 = 10000.
  2. For Java XSP running on secure servers, the offset depends on which instance of the server is executing the routine (there can be up to 10 secure servers per vproc), and on the type and ID of the vproc that is executing the routine. 

The logic to calculate the debug port is complicated.  Another way to find the port will be shown in How to Create and Debug a Java XSP using Eclipse article.

5.1. Establish the Teradata Database Environment

In DBS, enter the following command in a terminal window. It will add the Teradata utilities (e.g. cufconfig) path into the system path.

TDExpress14.0_DBS:~ # tdatcmd

Note: This will change the prompt back to the original value, so please execute [4.0. Change DBS Prompt] section again to avoid confusion.

TDExpress14.0_DBS:~ # export PS1="TDExpress14.0_DBS:\w # "

5.2. Show Settings

We will use the command cufconfig -o to show the current UDF and XSP subsystem settings, and use cufconfig -f filename to change the settings.

Enter the following command in the terminal window to show the current UDF/XSP configuration settings.

TDExpress14.0_DBS:~ # cufconfig -o

5.3. Change Settings

Use the following procedure to change UDFServerTasks to 1, and JavaBaseDebugPort to 8000.

5.3.1. Create a New Settings File

Create a new settings file named newsetting.cuf in /tmp. Input the content as below, and click the Save button.

JavaBaseDebugPort: 8000
UDFServerTasks: 1

5.3.2. Import the New Settings File

Enter the following command in the terminal window to import the new settings file.

TDExpress14.0_DBS:~ #cufconfig -f /tmp/newsetting.cuf

5.3.3. Restart the Database

For the changes to take effect, restart the database via the following command:

TDExpress14.0_DBS:~ # tpareset -f change cufconfig setting

A confirmation message will come up. Type y, and hit Enter.

6. Set up for C/C++ Remote Debugging

If you want to debug a CUDF or CXSP in a remote DBS, some additional settings should be made in both client machine and DBS.

In our example, we are doing remote CUDF / CXSP debugging from a Linux client machine to a remote DBS. The client machine is our TDExpress VM, and the remote DBS's  IP address is 192.168.85.128.

We have not successfully tested out the case of using a Windows machine as a client machine to remote debug CUDF/CXSP.

6.1. Upgrade Gdb in Linux Client

Download a new version of gdb in the client because we cannot use the default gdb6.8 in our Linux client VM to debug the remote CUDF/CXSPs in DBS. We need gdb version 7.0 or higher to do remote debugging.

Note: This section [6.1. Upgrade Gdb In Client] should be done in the Linux client.

Please use the link gdb-7.0.1a.tar.gz to download gdb7.0.1.

Unzip the file to /ceud folder using File Roller. A /ceud/gdb-7.0.1 folder is created.

Open a terminal window, and submit the following commands to compile and install the gdb7.0.1 from source.

TDExpress14.0_Sles10:~ # cd /ceud/gdb-7.0.1

TDExpress14.0_Sles10:/ceud/gdb-7.0.1 # ./configure

TDExpress14.0_Sles10:/ceud/gdb-7.0.1 # make

TDExpress14.0_Sles10:/ceud/gdb-7.0.1 # make install

Submit the follwing command to verify the installed gdb version.

TDExpress14.0_Sles10:/ceud/gdb-7.0.1 # gdb -version

6.2. Gdbserver in DBS

Before remote debugging, confirm there is an available gdbserver program in the remote DBS. The gdbserver version must be equal to or greater than the client gdb version.

Submit the follwing command to verify the installed gdb version.

TDExpress14.0_DBS:~ # gdbserver --version

If there is no gdbserver installed in DBS or the installed gdbserver version in DBS is less than the gdb in client, please install an equal or higher version gdbserver in DBS. In our case, we should install a gdbserver with a version equal to or greater than 7.0.1.

We can install gdb7.0.1 which is used in the [6.1. Upgrade Gdb in Linux Client] section because the gdbserver is also included in the gdb package. The gdbserver is installed with gdb installation at the same time.

Download gdb7.0.1 using the link gdb-7.0.1a.tar.gz.

Unzip the file to /root folder. A /root/gdb-7.0.1 folder is created.

Open a terminal window, and submit the following commands to compile and install the gdb7.0.1 from source.

TDExpress14.0_DBS:~ # cd /root/gdb-7.0.1

TDExpress14.0_DBS:/root/gdb-7.0.1# ./configure

TDExpress14.0_DBS:/root/gdb-7.0.1# make

TDExpress14.0_DBS:/root/gdb-7.0.1# make install

6.3. Samba Setup

To share the DBS udf library files to the client VM, we set up a shared folder using the Samba service.

6.3.1. Samba Setup in DBS

This section should be executed in the remote DBS, which has IP Address 192.168.85.128 in our case.

6.3.1.1. Establish the Teradata Database environment

In DBS, enter the following command in a terminal window. It will add the Teradata utilities (e.g. cufconfig) path into the system path.

TDExpress14.0_DBS:~ # tdatcmd

Note: This will change prompt back to the original value, so please execute [4.0. Change DBS Prompt] section again to avoid confusion.

TDExpress14.0_DBS:~ # export PS1="TDExpress14.0_DBS:\w # "

6.3.1.2. Get the UDF Library Path

In DBS, enter the following command in the terminal window to show the current UDF library path.

TDExpress14.0_DBS:~ # cufconfig -o | grep UDFLibrary

We should get the following default path.

UDFLibraryPath: /etc/opt/teradata/tdconfig/udflib/

6.3.1.3. Edit the Samba Configuration File

In DBS, edit the Samba configuration file /etc/samba/smb.conf and add the following content at the end of the file.

[sharedUDFLibPath]
comment = for UDF remote debugging
path = /etc/opt/teradata/tdconfig/udflib
browseable = Yes
read only = Yes
directory mask = 0775
guest on = ok
[usrtdbmsPath]
comment = for UDF remote compilation
path = /usr/tdbms
browseable = Yes
read only = Yes
directory mask = 0775
guest on = ok

6.3.1.4. Set the Samba User Password

We need to setup a new password for the Samba service.

TDExpress14.0_DBS:~ # smbpasswd -a root

You will be prompted for a new SMB password.

6.3.1.5. Restart the Samba Service

For the changes to take effect, restart the Samba service via the following command in DBS:

TDExpress14.0_DBS:~ # /etc/init.d/smb restart

6.3.2. Mount the Remote Samba Folder in the Linux Client

Switch to the client machine, open a terminal window and submit the following command to set up a mapping folder using [6.3.1.2. Get UDF Library Path] UDF library path. Before submitting the command please make sure the folder /etc/opt/teradata/tdconfig/udflib and /usr/tdbms exists; otherwise a mount error will be returned.

In our case, the DBS IP address is 192.168.85.128, and the shared folder path is sharedUDFLibPath and usrtdbmsPath which we just set in section [6.3.1.3. Edit Samba Configuration File].

TDExpress14.0_Sles10:~ # mount -t cifs //192.168.85.128/sharedUDFLibPath /etc/opt/teradata/tdconfig/udflib

TDExpress14.0_Sles10:~ # mount -t cifs //192.168.85.128/usrtdbmsPath /usr/tdbms

We will be prompted for a password. Use the SMB password set earlier in [6.3.1.4. Set Samba User Password].

We can now access the folders /etc/opt/teradata/tdconfig/udflib and /usr/tdbms to see the mapping files in DBS.

The setup for the UDF/XSP debugging environment is completed. You are now ready to debug your UDF/XSPs. Next step is to view the articles written for debugging specific types of UDF/XSPs.