The topics in this section describe how to create a simple scalar UDF that calculates the sum of two integers, execute the UDF in protected mode, and debug it. The same high-level process can be used to debug any external routine that the debugger supports.
Setting Up the System
Some setup is needed before actually debugging the UDF. First, you must enable UDF debugging to permit database users with sufficient permissions to debug a UDF. This option is disabled by default to deter misuse on production systems. The UDF Debugging option on the debug screen in the ctl utility enables it.
- Start ctl from a command shell on the target system and go to the debug screen:
# ctl > screen debug (0) Start DBS: On (1) Break Stop: Off (2) Start With Logons: All (3) Start With Debug: Off (4) Save Dumps: Off (5) Snapshot Crash: Off (6) Maximum Dumps: -1 (7) Start PrgTraces: Off (8) Restart Dump Type: System (9) UDF Debugging: Off
- If UDF Debugging is not already on, enable it and quit ctl:
> 9=on > quit CTL: Write Control GDO Changes? y CTL: Control GDO successfully written. Warning: A change has been made to one or more fields that has a deferred effect. TPA reset must be performed so that those changes can take effect.
- As the message from ctl says, you must restart Teradata before the new setting takes effect. You can do that with a tpareset command at the shell prompt:
# tpareset –y Enable UDF Debugging
Creating a Database User
Debugging requires that special privileges be given to the debugger user. This example creates a debugger user with permissions to create and execute UDFs. Any DBS user can debug UDFs, but every user that does so must have permission.
- Login to bteq as dbc user. This example assumes you do this from a command shell on the target system, but bteq can run from any machine that can access the Teradata system.
From a shell command prompt, logon to bteq:
# bteq .logon localhost/dbc,dbc_password
- Once bteq issues its command prompt, create a user named, “debugger”:
create user debugger as permanent=50e6, spool=100e6, temporary=50e6, password=debugger;
- Submit the following commands to grant this user the required privileges:
grant create function on debugger to debugger with grant option; grant execute function on debugger to debugger with grant option;
- Log out and quit.
Creating the C UDF source file
After the debugger user is set up, you can use this user account to create a simple scalar UDF and install the function in the database. Here is a sample of C source code that you can use to create a source file named plusudf.c. The function calculates the sum of two integers.
- For this example, create the plusudf.c file in the /tmp directory.
/* * plusudf.c * * Sample UDF for debugging */ #define SQL_TEXT Latin_Text #include "sqltypes_td.h" void plusudf(INTEGER *a, INTEGER *b, INTEGER *result, char sqlstate[5]) { *result = *a + *b; }
Log Into bteq
- Log into bteq as the debugger user:
# bteq .logon localhost/debugger,debugger
The UDF will be installed in the database to which you are logged on. The function is stored in a library that contains code for all the UDFs created by this user account. In this example the DBS is running on the local machine.
Create the New UDF
- Create the new UDF, named “plusudf,” from your C file using CREATE FUNCTION with the “d” option in the EXTERNAL NAME clause:
create function plusudf( a integer, b integer ) returns integer language c no sql parameter style td_general external name 'd!cs!plusudf!/tmp/plusudf.c';
You must use the “d” option so that the UDF is compiled with debug symbols. Every time you create or replace a UDF the library is rebuilt. For more information on CREATE FUNCTION, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
Verify the UDF
- Run the UDF to ensure that it works properly:
select plusudf(5,10);
The results should show:
*** Query completed. One row found. Onlne column returned. *** Total elapsed time was 2 seconds. plusudf(5,10) 15
Run the UDF to Be Debugged
After the UDF is created and verified, it can be run for debugging.
- Log into a normal SQL session:
# bteq .logon localhost/debugger,debugger
- Request that any subsequent SQL statements in the SQL session that invoke the UDF (named “plusudf” in this example) will run under the Teradata C/C++ UDF Debugger:
set session debug function plusudf on;
- Issue an SQL statement that causes plusudf to run.
select plusudf(5,10);
The UDF halts the execution of the query and waits for the debugger to join the session. The query will not complete until the debugger allows the function to continue. If your query puts into execution multiple instances of the UDF, the query will not complete until the debugger has let all the UDFs finish running.
Join the UDF to a Teradata C/C++ UDF Debugger Session
The debugger claims the UDF and controls it.
- Start the debugger in a separate window on the target system. You can use either tdgdb or /usr/pde/bin/gdb from a shell prompt:
# tdgdb
- To start the debugging session, use the attach command with the udf option and the debugger account name and password:
(gdb) attach udf localhost/debugger,debugger Attaching to UDF server via localhost/debugger,debugger
The UDF server makes debugging UDFs running in the database possible for GDB (with the Teradata C/C++ UDF Debugger extensions). - To list the UDFs waiting to be debugged that can be joined by this debugger account, use the info udf command. This command produces something like:
(gdb) info udf Sessno Name Type Language Count Joined 1001 plusudf UDF C 1 No
where:- Sessno is the session number.
- Name is the name of the UDF.
- Type is always UDF.
- Language is the programming language in which the source for the function is written.
- Count is the number of instance of the UDF currently being debugged.
- Joined indicates whether the SQL session in which the UDF is running has been joined by the debugger.
- Use the session number supplied by the info udf display to select the UDF to associate with the debugging session. Type:
join <session number>For session 1001, the command output produces something like:
(gdb) join 1001 Reading symbols for task udfsectsk... (libudf.so, 0x7ffff7bb7000) (libudf1.so, 0x7ffff79b4000) (libstdc++.so.6, 0x7ffff76a9000) (libjil.so, 0x7ffff748b000) (libnetpde.so, 0x7ffff723f000) (libpde.so, 0x7ffff6f80000) (libemf.so, 0x7ffff6d72000) (libpdesym.so, 0x7ffff6b5b000) (libpthread.so.0, 0x7ffff693e000) (libelf.so.1, 0x7ffff672a000) (libnsl.so.1, 0x7ffff6512000) (libm.so.6, 0x7ffff62bc000) (libc.so.6, 0x7ffff5f5a000) (libdl.so.2, 0x7ffff5d56000) (ld-linux-x86-64.so.2, 0x7ffff7dde000) (libgcc_s.so.1, 0x7ffff5b3f000) (libacl.so.1, 0x7ffff5937000) (libcrypto.so.0.9.8, 0x7ffff5598000) (libthread_db.so.1, 0x7ffff5390000) (libattr.so.1, 0x7ffff518b000) (libz.so.1, 0x7ffff4f75000) (libudf_1026_17.so, 0x7ffff355b000) Node Pid Tid Type 16383 7066 7066 C
Note that Node shows the vproc ID on which the UDF is running.
Debug the UDF
Now you can debug using standard GDB commands to set breakpoints, display variables, and step or continue execution.
- Use the dir command to tell the Teradata C/C++ UDF Debugger where the source file is located:
(gdb) dir /tmp Source directories searched: /tmp:$cdir:$cwd
If the source file is not available, users with sufficient permissions can use SHOW FUNCTION in BTEQ to show the C source code for a UDF. The output can be saved to a file and used for debugging. For more information, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. - Set a breakpoint in the file at line 12:
(gdb) br plusudf.c:12 Breakpoint 1 at 0x2aaaac25f714: file plusudf.c, line 12.
After the debugger has seen the library that contains code for the UDF, you can set breakpoints in that library regardless of whether the UDF is currently running. Any additional breakpoints will be included in new instances of the UDF as they start. - Type C to let the query run to the breakpoint:
(gdb) c Continuing. Breakpoint 1, plusudf (a=0x2aaaac21e940, b=0x2aaaac21e950, result=0x2aaaac21e964, sqlstate=0x2aaaac21e96c "00000") at plusudf.c:12 12 *result = *a + *b;
- Print the current values of the variables:
(gdb) print *a $1 = 5 (gdb) print *b $2 = 10
- Type c to continue.In this example the query runs to completion in your bteq window.
gdb) c Continuing.
When a query is complete the session remains joined until you quit the debugger. If in BTEQ you were to reissue the same request in your SQL session a new UDF instance would display in the debugger window when the UDF hits the breakpoint.
- Press Ctrl+c to terminate the debugger command in progress and return to the GDB command prompt; then type quit to allow your query to run to completion and then exit the debugger.
<Ctrl-c> Debug Server stopped without any debug processes! (gdb) quit Ending debugging session.
If your debugging uncovers the need for changes to your UDF, the best practice is to quit the debugger after you recompile a UDF and restart the debugger to reestablish whatever breakpoints are needed in the new debugging session, much the same as you would do to debug a stand-alone program.
You can update the source code and recompile it with a REPLACE FUNCTION statement in the same bteq session that you used to debug it. However, replacing the function causes the library to be rebuilt so any breakpoints you set on the initial UDF are not preserved.