How to Create and Debug a Scalar CUDF 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 CUDF 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 C/C++ programming languages, installing them on the database, and then using them like standard SQL functions.

Teradata Database supports three types of CUDFs (C/C++ User-Defined Functions). They are scalar UDF, aggregate UDF and window aggregate UDF, and table UDF. 

This article will show how to create a simple scalar CUDF, execute it in protected mode, and debug it locally or remotely using Eclipse CDT. 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 CUDF calculates the sum of two integers.

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 CUDF using Eclipse CDT.

  1. Create C Project in Eclipse
  2. Create CUDF Source File in C Project
  3. Install DDL in Database
  4. Run CUDF in Database
  5. Debug CUDF in Eclipse

1. Create C Project in Eclipse

1.1. C/C++ Perspective

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

An Open Perspective window is displayed. Select C/C++, and click the OK button.

1.2. Create C Project

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

C Project window comes up.

Enter Project name cudf_scalar, select Project type Shared Library, and select Toolchains Linux GCC. Then click the Finish button.

A new C project cudf_scalar will be created.

1.3. Change Build Settings

1.3.1. Show Build Settings

Right click the project, and select Properties.

The next window shows the default Properties for cudf_scalar .

Click C/C++ Build -> Settings. The right of window shows default C/C++ build settings.

You can move the scrollbars at the right or bottom to see the part that is out of view.

1.3.2. Change Compiler Settings

Select GCC C Compiler -> Includes. Click the + button. An Add directory path window shows up. Enter Directory /usr/tdbms/etc.

The /usr/tdbms/etc path will be added into the right list.

Select Miscellaneous in GCC C Compiler. Check the flag Position Independent Code (-fPIC).

1.3.3. Change Linker Settings

Select GCC C Linker -> Libraries. Click the + button as below to add Library search path.

An Add directory path window shows up. Enter /usr/tdbms/lib in the text box and click OK.

The /usr/tdbms/lib path will be shown in the lower list.

Click the OK button to save the settings.

2. Create CUDF Source File in C Project

2.1. Create Source Folder

Right click the project, and select New -> Source Folder.

A New Source Folder window comes up. Please enter the source folder name src and click the Finish button.

The src source folder will be added to the project.

2.2. Create New Source File

Right click the src folder, select New -> Source File.

New Source File window is displayed. Enter Source file plusudf.c. Click the Finish button.

A new source file plusudf.c will be created. 

2.3. Edit The Source File

Edit the source file as shown below.

/*
* plusudf.c
*
* Created on: Sep 3, 2012
* Author: root
*/
#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"

void plusudf(INTEGER *a, INTEGER *b, INTEGER *result, char sqlstate[5])
{
*result = *a + *b;
}

3. Install DDL in Database

3.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 machine. Its IP address is 192.168.85.128 in this example.

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

3.2 Create New CUDF

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

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE FUNCTION plusudf(
a INTEGER,
b INTEGER
) RETURNS INTEGER
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'D!CS!plusudf!/usr/eclipse/workspace/cudf_scalar/src/plusudf.c';

An output message will be displayed when the execution finishes.

 *** Function has been created.

3.3 Update Existing CUDF

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

 BTEQ -- Enter your SQL request or BTEQ command:

REPLACE FUNCTION plusudf(
a INTEGER,
b INTEGER
) RETURNS INTEGER
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'D!CS!plusudf!/usr/eclipse/workspace/cudf_scalar/src/plusudf.c';

An output message will be displayed when the execution finishes.

 *** Function has been replaced.

4. Run CUDF in Database

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

Execute the following query to run the plusudf UDF.

 BTEQ -- Enter your SQL request or BTEQ command:

Select plusudf(5,10);

The result will be displayed when the execution finishes. The plusudf CUDF correctly returns 15 as the sum of 5 and 10 in this case.

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

5. Debug CUDF in Eclipse

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

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

There is no difference between debugging a local CUDF or a remote CUDF, except for the setup of the debug session.

5.1. Setup Source Lookup Path

Select the pull-down menu Window -> Preferences.

The Preferences window comes up.

Select C/C++ -> Debug -> Source Lookup Path. The Source Lookup Path settings will be shown at the right.

Please select the following values and click the Remove button to remove them one by one if they are in the Default Source Lookup Path list.

  • Absolute File Path
  • Program Relative File Path
  • Project

Click the Add button to add Project-Path Relative to Source Folders into the list.

Click the OK button to save the setting.

5.2. Switch to Debug Perspective

Select the pull-down menu Window -> Open Perspective -> Debug.

The Eclipse will switch to Debug perspective.

5.3. 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. Also a line has been added into the Breakpoints view.

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

5.4. Debug Configuration

Select the pull-down menu Run -> Debug Configurations....

Debug Configurations setting window is displayed. Double click C/C++ Attach to Application in the left list. A debug configuration cudf_scalar Debug is created automatically.

A. Local DBS

Click the Debug button, the Eclipse debugger will try to show all the processes in the VM. A Select Process window comes up.

Enter our process name udfsectsk. The list will filter the items with input characters. Here only a udfsectsk-8123 entry is left.

Note: If we have not executed any CUDF since database startup, the udfsectsk process is not started. So please make sure you have executed a CUDF once before debugging.

Click the OK button to attach to udfsectsk process. This process will be paused in debug mode. Click the Resume button or press F8 to continue.

B. Remote DBS

Click the Debugger tab, and change the Debugger from gdb to gdbserver.

Click the Connection tab in Debugger Options and enter Host name or IP address.

To open a remote debug connection port 10000, please submit the following command in a console window in client.

TDExpress14.0_Sles10:~ #ssh root@192.168.85.128 gdbserver --multi :10000

You will be prompted for the password of the root user on the remote DBS in order to connect.

In our case, enter DBS IP address 192.168.85.128 and port 10000 which we just created in the remote DBS.

Click the Debug button to connect to remote DBS debug port. 

Click the Connect to process button, a Select Process window comes up. Enter process name udfsectsk and click the OK button.

A window comes up. Please find your compiled binary file and click the OK button. In our case, the Location should be /usr/eclipse/workspace/cudf_scalar/Debug/libcudf_scalar.so.

The remote connection will be established, and the remote udfsectsk process attached. That process will be paused in debug mode.

Click the Resume button or press F8 to continue.

5.5. Breakpoint Hit

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

Enter the following query at bteq prompt:

 BTEQ -- Enter your SQL request or BTEQ command:

Select plusudf(5,10);

The breakpoint will be hit.

We hit the breakpoint at line 12. The backtrace shows the thread was paused at plusudf() at plusudf.c: 12 0x2aaaac3507ac.

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

5.6. 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 13. The value of result is changed to 15.

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

5.7. 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 the following link.

Eclipse (Indigo) C/C++ Development User Guide

6 REPLIES
Fan

Re: How to Create and Debug a Scalar CUDF using Eclipse

Hi,

I have done above steps. But when debugging, I am unable to file udfsectsk process in Select Process window. Can any one help me. Any setting is required in eclipse.

Thanks... Hesham

Teradata Employee

Re: How to Create and Debug a Scalar CUDF using Eclipse

I guest you are using Eclipse in Windows.

I so, please use Eclipse in Linux for this reason. Seems there is some bug in that Select Process window in Windows.

Fan

Re: How to Create and Debug a Scalar CUDF using Eclipse

Thanks for reply.

I have checked in both environment Windows and Linux getting same issue. Using Eclipse Indigo and Juno.

Teradata Employee

Re: How to Create and Debug a Scalar CUDF using Eclipse

If you can get the Select Process window, and the only issue is not finding udfsectsk process, then it's not the CDT or Eclipse 's issue. Maybe you need check if you have run the udf correctly.

Fan

Re: How to Create and Debug a Scalar CUDF using Eclipse

Yes... I got the Select Process windows. I have check udf is running fine and i got the result also. But still I am unable to select udfsectsk process. I windows environment it is showing udfsectsk as a child process in procexp.

Teradata Employee

Re: How to Create and Debug a Scalar CUDF using Eclipse

Sorry for inconvienence, this articles is only focus on UDF debugging in specified environment, it can not cover all the situations.

If you are using procexp, maybe we need check it out from procexp teams why we can  not select its child process in CDT.

Sorry for inconvienence.