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.

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

If you have not worked through the guide VNC Easy Setup, please do it now before you continue.

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.

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

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