How to Create and Debug a CUDF under an Authorized User

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 CUDF under an Authorized User

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 connect to a remote DBS using your own username/password by VNC, and create a simple aggregate CUDF under your operating system account, execute it in protected mode, and debug it using Eclipse CDT in the remote DBS. The UDF runs under your account, and can access the system resources for which your account has privileges.

In this example, the aggregate CUDF takes a list of prices from a Products database table and returns the sum of all prices from that list.

Prerequisite for this Article

  1. If you have not worked through the guide VNC Easy Setup, please do it now before you continue.
  2. If you have gone throught the article How to Create and Debug an Aggregate CUDF using Eclipse, it will be very helpful.
  3. There is an available account in the remote DBS. Its Default Group must be tdatudf for debugging. In this article, we use the account vnc_user.

Note: An authorized UDF can be associated with an OS user account no matter if it is in the tdatudf group. But if you want to debug that UDF under this user, the user' default group must be tdatudf. After the debugging is done, you can move the user out of the tdatudf group.

Article Outline

In this article, we will do the following things to show how to connect to a remote DBS by VNC, create and debug an aggregate CUDF on the remote DBS under a remote DBS user account.

  1. Connect to Remote DBS
  2. Confirm Settings in Remote DBS
  3. Create CUDF Project in Eclipse
  4. Install CUDF in Database
  5. Run and Debug CUDF in Database

1. Connect to Remote DBS

We can use our account information to connect to the remote Desktop and the remote terminal.

1.1. Remote Terminal

We execute some terminal commands via terminal window. Here we use PuTTY as our terminal tool.

Please open 2 terminal windows for debugging. One is for bteq, and the other one is for shell command.

Note: PuTTY is a free telnet/SSH client. You can find the introduction hereIf you do not have PuTTY installed, please download a free PuTTY before connection.

1.2. Remote Desktop

Open the VNC Viewer, and input VNC Server address with the window number 192.168.208.130:1.

Click the Connect button.

An Authentication window comes up. Input the password to login the system.

2. Confirm Settings in Remote DBS

Some necessary settings must be set correctly for UDF debugging.

2.1. Database prepartion

Before we connect to the database, we must create a Teradata Database user.

Here we create a udfdebugger user and give it neccessary privileges to create and execute UDF.

vnc_user@s1-1400:~> bteq .logon 127.0.0.1/dbc,dbc

CREATE USER udfdebugger
AS PERMANENT=50E6,
SPOOL=100E6,
TEMPORARY=50E6,
PASSWORD=udfdebugger;
GRANT CREATE FUNCTION ON udfdebugger TO udfdebugger WITH GRANT OPTION;
GRANT CREATE EXTERNAL PROCEDURE ON udfdebugger TO udfdebugger WITH GRANT OPTION;
GRANT EXECUTE PROCEDURE ON SQLJ TO udfdebugger WITH GRANT OPTION;

Quit the current session and login under the udfdebugger.

 BTEQ -- Enter your SQL request or BTEQ command:

.quit

vnc_user@s1-1400:~> bteq .logon 127.0.0.1/udfdebugger,udfdebugger

Create an AUTHORIZATION using system account information.

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE AUTHORIZATION vnc_definer as definer
USER 'vnc_user'
password 'vncuser';

Note: The vnc_user is the system user and vncuser is its password in the system.

Quit the current session.

 BTEQ -- Enter your SQL request or BTEQ command:

.quit

2.2. Install the Eclipse with CDT

Download an Eclipse package for your system using the following link.


Unzip the package to the desktop and create a launcher in the desktop.

The remote desktop is as below.

Double click the Eclipse Start launcher to startup Eclipse.

Set the default workspace /home/vnc_user/workspace, and click Use this as the default and do not ask again. Then click OK.

The Eclipse will be started.

3. Create CUDF Project in Eclipse

3.1. Create a new C/C++ Project

Select the pull-down menu Window -> Open Perspective -> Other .... Select C/C++ and click OK to switch to C/C++ perspective.

Go to the Project Explorer in Eclipse and right click New -> C Project. A C Project window comes up.

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

A new C project vnc_aggregate will be created.

3.2. Change Project Build Settings

3.2.1. Show Build Settings

Right click the project, and select Properties. The next window shows the default Properties for vnc_aggregate.

Click C/C++ Build -> Settings. The default C/C++ build settings will be shown at the right.

3.2.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).

3.2.3. Change Linker Settings

Select GCC C Linker -> Libraries. Click the + button to add a new path to the 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.

3.2.4. 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.

Make sure the settings match the below window and click the OK button to save the settings.

3.3. Create Source File

3.3.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.

3.3.2. Create New Source File

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

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

A new source file vnc_sumfloat.c will be created. 

3.3.3. Edit The Source File

Edit the source file as shown below.

/*
* vnc_sumfloat.c
*
* Created on: Dec 18, 2012
* Author: vnc_user
*/
#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
typedef struct arg_storage {
FLOAT xSum;
} AgrStorage;

/* Computes SUM. */
void vnc_sumfloat(FNC_Phase phase, FNC_Context_t *fctx, FLOAT *x, FLOAT *result,
int *x_i, int *result_i, char sqlstate[6], SQL_TEXT fncname[129],
SQL_TEXT sfncname[129], SQL_TEXT errorMsg[257]) {
AgrStorage *s1 = fctx->interim1;
AgrStorage *s2 = fctx->interim2;

switch (phase) {
case AGR_INIT:
if ((s1 = FNC_DefMem(sizeof(AgrStorage))) == NULL) {
strcpy(sqlstate, "U0001");
return;
}

s1->xSum = 0.0;

// No break, so execution falls through to AGR_DETAIL case
case AGR_DETAIL:
if (*x_i != -1) { // if not null accumulate summation
s1->xSum += *x; // s1 points to intermediate storage
}

break;

// AGR_COMBINE is encountered multiple times depending on the
// number of AMPs and caching effects.
case AGR_COMBINE:
s1->xSum += s2->xSum;
break;

case AGR_FINAL:
*result = s1->xSum;
break;
case AGR_NODATA:
*result_i = -1;
break;
default:
strcpy(sqlstate, "U0005");
break;
}
return;
}

4. Install CUDF in Database

4.1. Logon Bteq

Execute the bteq logon command in the PuTTY window.

Logon to the DBS using the IP address 127.0.0.1 for the local host.

vnc_user@s1-1400:~> bteq .logon 127.0.0.1/udfdebugger,udfdebugger

4.2. Create New CUDF

To create a new CUDF vnc_sumfloat, please execute the following command at the bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE FUNCTION vnc_sumfloat
(aFloat FLOAT)
RETURNS FLOAT
CLASS AGGREGATE (50)
LANGUAGE C
NO SQL
SPECIFIC vnc_sumfloat
PARAMETER STYLE SQL
EXTERNAL NAME 'D!CS!vnc_sumfloat!/home/vnc_user/workspace/vnc_aggregate/src/vnc_sumfloat.c'
EXTERNAL SECURITY definer vnc_definer;

An output message will be displayed when the execution finishes.

 *** Function has been created.

5. Run and Debug CUDF in Database

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

5.1. Data Preparation

To run your aggregate CUDF, 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)
);

-- the data in AMP 0
INSERT INTO products (id, description, price) values(1, 'Lamp', 25.0);
-- the data in AMP 0
INSERT INTO products (id, description, price) values(2, 'Table', 50.0);
-- the data in AMP 0
INSERT INTO products (id, description, price) values(3, 'Chair', 15.0);
-- the data in AMP 1
INSERT INTO products (id, description, price) values(9, 'Zhao', 33.4);

Note: If the table or data already exists, ignore the errors of above SQLs.

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 CUDF

Execute the following query to run the vnc_sumfloat CUDF.

 BTEQ -- Enter your SQL request or BTEQ command:

select vnc_sumfloat(price) from products;

The result will be displayed when the execution finishes. In this case, vnc_sumfloat correctly returns the sum of 123.4 for all the prices in products table.

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

5.3. Set Breakpoint

In Eclipse, select the pull-down menu Window -> Open Perspective -> Debug to switch to the Debug perspective. 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 20 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 vnc_aggregate Debug is created automatically.

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

Enter process name udfsectsk. The list will filter the items with input characters. In our case, there are 3 udfsectsk processes. The 9113 udfsectsk is executed by tdatuser (the default UDF execution user), and the others are executed by vnc_user.

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

Select the second udfsectsk - 8309 process, and click the OK button to attach to this process. The process will be paused in debug mode. Click the Resume button or press F8 to continue.

Click the triangle button to the right of Debug button, and select vnc_aggregate Debug which we just created above. 

The Select Process window comes up. Input our process name like above. Select the third udfsectsk process and click the OK button to connect the udfsectsk - 8312 process.

The third udfsectsk process will be paused in debug mode. Click the Resume button or press F8 to continue.

We have connected 2 udfsectsk processes.

Note: If there are more than 2 udfsectsk processes which are executed by your account in the process list, please make sure you have connected all of them because we cannot easily know which processes will execute our debugging CUDF.

There is a udfsectsk process which is executed under the default operation system user tdatuser

We can also check the udfsectsk processes in the PuTTY window using ps command.

vnc_user@s1-1400:~> ps -ef | grep udfsectsk

If we try to debug the first udfsectsk process which is not executed by our user , an error will be returned.

The error message is ptrace: Operation not permitted. It means we do not have privilege to attach that process.

This will protect the debugger from attaching to the processes which do not belong to him.

5.5. Breakpoint Hit

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

Enter the following query at bteq prompt:

 BTEQ -- Enter your SQL request or BTEQ command:

select vnc_sumfloat(price) from products;

Because the sample code is an aggregate CUDF and the data is distributed in 2 AMPs, the breakpoint will be hit in both udfsectsk processes.

We hit the breakpoint at line 20. The backtrace shows the thread was paused at vnc_sumfloat() at vnc_sumfloat.c: 20 0x7ffff41698cc.

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

For comparison, we can check the data content as shown in section [5.1. Data Preparation]

Select the first process in Debug view, the Variables view will be refreshed with its context. 

For more CDT tips and tricks, please refer to the following link.

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