CREATE GLOBAL TEMPORARY TABLE Examples | Teradata Vantage - 17.00 - CREATE GLOBAL TEMPORARY TRACE TABLE Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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.