Writing Trace Output to a Trace Table - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

When you create a trace table with a CREATE GLOBAL TEMPORARY TRACE TABLE statement, you define columns that a UDF can use to write trace output during execution.

To write trace output to the trace table columns, call FNC_Trace_Write_DL in the UDF. The call takes three arguments:
  • An array of pointers to data to store in the trace table columns
  • A count of the array elements
  • An array of the length of each array element

Consider the following trace table definition:

CREATE GLOBAL TEMPORARY TRACE TABLE UDF_Trace
  (vproc_ID BYTE(2)
  ,Sequence INTEGER
  ,UDF_name CHAR(15)
  ,x_value INTEGER)
ON COMMIT DELETE ROWS;

The Teradata function trace subsystem writes values to the first two columns in the trace table.

Column Contents
1 PE or AMP vproc number on which the UDF is running
2 If FNC_Trace_Write_DL is called from …
  • a UDF running on a PE, then the value in the second column is a sequence number that increments by one for any call to FNC_Trace_Write_DL until the session logs off, regardless of which function makes the call.
  • a UDF running on an AMP, then the value in the second column is one more than the last sequential number written to the AMP for the trace table.

The following statements in a UDF output values to the UDF_name and x_value columns of the trace table:

INTEGER   x_value;
void      *column_list[2];
int       length[2];
   
column_list[0] = sfncname;          /* UDF input argument */
column_list[1] = &x_value;
   
length[0] = strlen((const char *)sfncname);
length[1] = sizeof(INTEGER);
x_value = 45;
   
FNC_Trace_Write_DL(2, column_list, length);