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...
|
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...
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:
- Write another UDF that defines its input argument as a TIMESTAMP and converts it to a string result.
- 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.
- 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.