16.20 - FNC_Trace_Write_DL - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Purpose

Called by an external stored procedure, UDM, or UDF to write trace output into a temporary trace table defined by a CREATE GLOBAL TEMPORARY TRACE TABLE statement.

Syntax

void
FNC_Trace_Write_DL ( int    argc,
                     void  *argv[],
                     int    length[] )
int argc
the count of output arguments in the argv array.

This value should match the number of columns in the trace table beyond the first two mandatory columns that are used by the Teradata function trace subsystem.

void *argv[]
an array of pointers to data to write to the columns in a temporary trace table.
int length[]
an array of the length, in bytes, of each output argument in the argv array.

The sum of the values of the length elements must be positive and must not exceed the size of a row in the trace table.

Argument argv[]

Each array element in argv[] corresponds to a column in the trace table (beyond the first two mandatory columns). The order of the array elements matches the order of the columns in the trace table.

Consider the following trace table definition:

CREATE GLOBAL TEMPORARY TRACE TABLE Debug_Trace
  (vproc_ID BYTE(2)
  ,Sequence INTEGER
  ,Sum_X INTEGER
  ,Sum_Y INTEGER
  ,Trace_Text CHAR(30))
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, UDM, or external stored procedure is running
2 If FNC_Trace_Write_DL is called from...
  • An external stored procedure, UDM, or 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 UDF, UDM, or external stored procedure makes the call.

    If the system restarts, the sequence number resets to one.

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

In the preceding example, the argv[] array elements match the trace table columns as follows.

Array Element Column
argv[0] Debug_Trace.Sum_X
argv[1] Debug_Trace.Sum_Y
argv[2] Debug_Trace.Trace_Text

The following rules apply:

IF ... THEN ...
argv[] has more elements than the corresponding columns in the trace table the additional elements are ignored.
an argv[] element is null

or

argv[] has fewer elements than the corresponding columns in the trace table

the columns in the trace table corresponding to argv[] elements that are null or not supplied are set according to the following rules:
If the corresponding column is defined as...
  • nullable, then the value is set to NULL.
  • NOT NULL, then if the type of the column is...
    • numeric, then the value is set to zero.
    • variable-length character, then the value is set to a zero-length string.
    • fixed-length character, then the value is set to all blanks.
    • fixed-length byte, then the value is set to all binary zeros.

For argv[] elements that are null, corresponding elements in the length[] array must be zero.

Usage Notes

Trace output values are not validated. For example, stored values are incorrect when numeric values are too big for the receiving column or when character strings contain characters that are not valid.

If you store incorrect values, you might not be able to select data from the trace table. For example, if you write an invalid date to a TIMESTAMP column, a subsequent select on that data returns an invalid date error and no result. To read the bad TIMESTAMP data from the trace table and determine what is wrong, take the following steps:

  1. Write another UDF that defines its input argument as a TIMESTAMP and converts it to a string result.
  2. Use that UDF in the SELECT statement to read the TIMESTAMP data from the trace table to see what is wrong with it.

To avoid storing incorrect values that you might not be able to select, create the trace table with only character columns (beyond the first two mandatory columns) and format the output data using sprintf. For an example, see Example: Debugging a UDF Using a Trace Table.

If an output string is too long for a corresponding character column, it is truncated without generating an error.

A UDF, UDM, or external stored procedure can call FNC_Trace_Write_DL as often as required.

The FNC_Trace_Write_DL call is ignored if any of the following conditions are true:
  • No SET SESSION FUNCTION TRACE statement has been submitted to enable a trace table for trace output.
  • The sum of the values of the length array elements is negative or exceeds the size of a row in the trace table.

As described in the preceding section, Argument argv[], the Teradata function trace subsystem writes values to the first two columns in a trace table. If you try to use the same trace table to simultaneously trace a UDF that runs on an AMP and an external stored procedure, UDM, or UDF that runs on a PE, the sequence number written to the second column will not be sequential. This is because the AMP bases the next sequence number on the sequence of the last row of the trace table and adds one to it no matter where the row originated. The rows inserted into the trace table from the PE are hashed to one AMP based on the PE vproc number and the current sequence number. Therefore if the current sequence number for the PE is 5 and the trace row is added to AMP 1, then a trace write into that table from AMP 1 will have a sequence of 6. The best practice is to avoid simultaneously tracing on an AMP and PE.

Example Using FNC_Trace_Write_DL

INTEGER          Sum_x;
INTEGER          Sum_y;
CHARACTER_LATIN  message[45];
void            *argv[3];
int              length[3];

...

Sum_x = *In_data_x;
Sum_y = *In_data_y;

argv[0] = &Sum_x;
length[0] = sizeof(INTEGER);

argv[1] = &Sum_y;
length[1] = sizeof(INTEGER);

message = strcpy((char *)message, "The input values");
argv[2] = &message;
length[2] = strlen((const char *)message);

 FNC_Trace_Write_DL(3, argv, length);

Related Topics

For more information on CREATE GLOBAL TEMPORARY TRACE TABLE, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.