Example: Creating a Global Temporary Trace Table
This statement creates a global temporary trace table with required columns and optional, application-specific, columns. When the transaction that materializes udf_test commits, its contents are deleted.
CREATE GLOBAL TEMPORARY TRACE TABLE udf_test ( proc_ID BYTE(2), sequence INTEGER, udf_name CHARACTER(15), in_quantity INTEGER, trace_val1 FLOAT, trace_text CHARACTER(30)) ON COMMIT DELETE ROWS;
A query to retrieve the results recorded in udf_test may look like this:
SELECT * FROM udf_test ORDER BY proc_id ASC, sequence ASC;
Example: Creating a Trace Table
This statement creates a trace table with a single-column, variable-length string to capture function output. Multiple functions can use the same trace table.
CREATE GLOBAL TEMPORARY TRACE TABLE udf_test, NO LOG ( proc_ID BYTE(2), sequence INTEGER, trace_string VARCHAR(256)) ON COMMIT DELETE ROWS;
Example: Complete Function Traceback Scenario
The tables in this example are on the Block File System.
This example shows the aspects of using global temporary trace tables to debug a procedure.
The following SHOW FUNCTION statement shows a CREATE FUNCTION statement that defines the traceback UDF used for this example. You can make the procedure as simple or complex as your application requires.
SHOW FUNCTION sptrace;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
------------------------------------------------------------------
CREATE FUNCTION sptrace (
p1 VARCHAR(100) CHARACTER SET LATIN)
RETURNS INTEGER
SPECIFIC sptrace
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
NOT DETERMINISTIC
RETURNS NULL ON NULL INPUT
EXTERNAL NAME sptrace
*** Text of DDL statement returned.
------------------------------------------------------------------
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
/*
Install in SYSLIB for general use and change to NOT PROTECTED mode
ALTER FUNCTION sptrace EXECUTE NOT PROTECTED;
*/
/* Assumes that the following trace table has been created */
/*
CREATE MULTISET GLOBAL TEMPORARY TRACE TABLE tracetst,
NO FALLBACK, NO LOG (
proc_id BYTE(2),
sequence INTEGER,
trace_str VARCHAR(100))
ON COMMIT PRESERVE ROWS;
Turn on tracing with following SQL (decide what your options mean):
SET SESSION FUNCTION TRACE USING 'T' FOR TABLE tracetst;
*/
void sptrace(VARCHAR_LATIN *trace_text,
INTEGER *result,
char sqlstate[6])
{
SQL_TEXT trace_string[257];
void *argv[20]; /* only need 1 arg -- its an array */
int length[20]; /* one length for each argument */
int tracelen;
/* read trace string */
FNC_Trace_String(trace_string);
/* Get length of string */
tracelen = strlen(trace_string);
/* Make sure tracing is turned on */
if (tracelen == 0)
return;
if (trace_string[0] == 'T')
{
argv[0] = trace_text;
length[0] = strlen(trace_text) +1;
/* Have something to trace */
FNC_Trace_Write_DL(1, argv, length);
}
}
The following SQL text defines the procedure that calls the trace function sptracedemo.
SHOW PROCEDURE sptracedemo;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
-----------------------------------------------------------------
CREATE PROCEDURE sptracedemo (
num_rows INTEGER )
BEGIN
-- Teradata mode SQL procedure
DECLARE dummy INTEGER;
DECLARE start_val INTEGER;
DECLARE fnum FLOAT;
SET dummy = sptrace('Start of sptrace demo');
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '52010'
BEGIN
-- Table already exists, delete the contents
DELETE sp_demo1 ALL;
SET dummy=sptrace('Deleted contents of sp_demo1 in handler');
-- Get error here and procedure exits
END;
CREATE TABLE sp_demo1 (
a INTEGER,
b FLOAT);
SET dummy = sptrace('Table sp_demo1 created');
END;
SET start_val = 1;
SET fnum = 25.3;
WHILE start_val <= num_rows DO
INSERT INTO sp_demo1 (start_val, fnum);
SET dummy = sptrace('did: insert (' || start_val ||','|| fnum
||');');
SET start_val = start_val +1;
SET fnum = sqrt(fnum);
END WHILE;
SET dummy = sptrace('Got to end of sptrace demo'); END;
The remainder of the scenario constitutes the actual example.
Global temporary trace tables are user-defined except for first two columns, which are fixed.
First show the definition of the global temporary trace table, tracetst, used for this example.
SHOW TABLE tracetst;
*** Text of DDL statement returned. *** Total elapsed time was 1 second. ------------------------------------------------------------------- CREATE MULTISET GLOBAL TEMPORARY TRACE TABLE tracetst,NO FALLBACK, CHECKSUM = DEFAULT, NO LOG ( proc_id BYTE(2), sequence INTEGER, trace_str VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC) ON COMMIT PRESERVE ROWS;
The next request enables function traceback for the session (see SET SESSION FUNCTION TRACE for additional information). The variable t is a user-defined string, interpreted by your trace UDF, that can be up to 255 characters long.
SET SESSION FUNCTION TRACE USING 't' FOR TABLE tracetst;
*** Set SESSION accepted. *** Total elapsed time was 1 second.
Function traceback has been enabled for the session.
Now check that the global temporary trace table tracetst has no rows:
SELECT * FROM tracetst ORDER BY 1, 2;
*** Query completed. No rows found. *** Total elapsed time was 1 second.
The trace table is empty.
Run the trace procedure sptracedemo, whose definition is indicated by the SHOW PROCEDURE request at the end of this example:
CALL sptracedemo(3);
*** Procedure has been executed. *** Total elapsed time was 1 second.
Select the contents of tracetst after the procedure runs. See the procedure definition to see what tracetst traces.
SELECT * FROM tracetst ORDER BY 1, 2;
*** Query completed. 6 rows found. 3 columns returned. *** Total elapsed time was 1 second. proc_id Sequence trace_str ------- -------- ------------------------------------------------- FF3F 1 Start of sptrace demo FF3F 2 Table sp_demo1 created FF3F 3 did: insert ( 1, 2.53000000000000E 001); FF3F 4 did: insert ( 2, 5.02991053598372E 000); FF3F 5 did: insert ( 3, 2.24274620409526E 000); FF3F 6 Got to end of sptrace demo
Now call sptracedemo again.
CALL sptracedemo(3);
*** Procedure has been executed. *** Total elapsed time was 1 second.
Select the contents of tracetst again. Populated, tracetst takes a different path.
SELECT * FROM tracetst ORDER BY 1, 2;
*** Query completed. 12 rows found. 3 columns returned. *** Total elapsed time was 1 second. proc_id Sequence trace_str ------- -------- ------------------------------------------------ FF3F 1 Start of sptrace demo FF3F 2 Table sp_demo1 created FF3F 3 did: insert ( 1, 2.53000000000000E 001); FF3F 4 did: insert ( 2, 5.02991053598372E 000); FF3F 5 did: insert ( 3, 2.24274620409526E 000); FF3F 6 Got to end of sptrace demo FF3F 7 Start of sptrace demo FF3F 8 deleted contents of sp_demo1 in handler FF3F 9 did: insert ( 1, 2.53000000000000E 001); FF3F 10 did: insert ( 2, 5.02991053598372E 000); FF3F 11 did: insert ( 3, 2.24274620409526E 000); FF3F 12 Got to end of sptrace demo
Disable function traceback to show that nothing additional is written to the trace table when the procedure is called, but function traceback is not enabled.
SET SESSION FUNCTION TRACE OFF;
*** Set SESSION accepted. *** Total elapsed time was 1 second.
CALL sptracedemo(3);
*** Procedure has been executed. *** Total elapsed time was 1 second.
SELECT * FROM tracetst ORDER BY 1, 2;
*** Query completed. 12 rows found. 3 columns returned. *** Total elapsed time was 1 second. proc_id Sequence trace_str ------- -------- ------------------------------------------------ FF3F 1 Start of sptrace demo FF3F 2 Table sp_demo1 created FF3F 3 did: insert ( 1, 2.53000000000000E 001); FF3F 4 did: insert ( 2, 5.02991053598372E 000); FF3F 5 did: insert ( 3, 2.24274620409526E 000); FF3F 6 Got to end of sptrace demo FF3F 7 Start of sptrace demo FF3F 8 deleted contents of sp_demo1 in handler FF3F 9 did: insert ( 1, 2.53000000000000E 001); FF3F 10 did: insert ( 2, 5.02991053598372E 000); FF3F 11 did: insert ( 3, 2.24274620409526E 000); FF3F 12 Got to end of sptrace demo
The identical 12 rows are selected from tracetst, so function traceback was successfully disabled.