Example: Creating a Global Temporary Trace Table
This example defines the mandatory columns for a global temporary trace table as well as several optional, application-specific, columns. When the transaction that materializes udf_test commits, its contents are to be 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 simple query to retrieve the results recorded in udf_test might look like this:
SELECT * FROM udf_test ORDER BY proc_id ASC, sequence ASC;
Example: Creating a Trace Table
The following example defines a simple trace table that has a single-column, variable-length string to capture function output.
This single VARCHAR column approach lends itself to a flexible trace output that can be used by many different functions without having to resort to specific single-purpose trace table column definitions.
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 following simple procedure trace example is a complete scenario that shows the various aspects of using global temporary trace tables to debug a procedure.
The following CREATE FUNCTION request, returned by a SHOW FUNCTION request submitting using BTEQ, defines the traceback UDF that was used for this example. You can make the procedure as simple, or as complex, as your application requires. This particular trace UDF is fairly simple.
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 will exit 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.
Note that global temporary trace tables are user-defined except for first two columns, which are always 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 select the contents of the global temporary trace table tracetst to ensure that it 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 your global temporary trace table tracetst after the procedure executes. Refer to the procedure definition to see exactly what it 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 the global temporary trace table again. Note that it takes a different path because the global temporary trace table has already been populated with rows this time.
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.