Rules and Limitations for Global Temporary Trace Tables
Because global temporary trace tables are not hashed, they have many definition and
manipulation restrictions that distinguish them from ordinary global temporary tables
(see “Global Temporary Tables” on page 512).
The following rules and limitations apply to global temporary trace table columns.
Any other columns are optional and user‑defined.
You cannot define global temporary trace tables as SET tables. All global temporary
trace tables are restricted to the MULTISET table type and cannot be altered to be
You cannot specify fallback characteristics for a global temporary trace table. They
are always created as NO FALLBACK by default and cannot be altered to be FALLBACK
If you specify FALLBACK, Teradata Database aborts the request and returns an error.
You cannot define any indexes for the table, including primary, secondary, join, and hash indexes.
This restriction explicitly includes both partitioned and unpartitioned primary indexes.
You also cannot define a global temporary trace table using the NO PRIMARY INDEX option.
You cannot specify partitioning for a global temporary trace table.
You cannot specify default column values for the table.
The following list describes the other rules and limitations for global temporary
If you try to use the same trace table to simultaneously trace a UDF that runs on
an AMP and an external procedure or UDF that runs on a PE, the sequence number written
to the second column is not in true sequential order. 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 has the sequence number 6.
The best practice is to avoid simultaneously tracing on an AMP and PE.
See SQL External Routine Programming for more information.
If you specify ON COMMIT PRESERVE ROWS, then the system preserves the rows of the
materialized trace table after the transaction commits or after it aborts; otherwise,
all rows are deleted from the table.
If the trace table was materialized for the session by the transaction that aborts,
it is deleted, so in that case, no rows are preserved. Once the trace table is successfully
materialized, however, the system retains any rows a UDF had written to it before
the abort occurred.
You cannot join a global temporary trace table with another table.
You cannot update a global temporary trace table. In other words, you cannot perform
DELETE, INSERT, or UPDATE statements against a global temporary trace table, though
you can specify DELETE ALL.
You can use INSERT … SELECT operations to insert rows from a global temporary trace
table into another table.
You can select the contents of the trace table for output to a response spool file
The SELECT request used for this purpose can specify a WHERE clause to determine the
criteria used to select the rows. It can also specify an ORDER BY clause.
You can perform DROP TEMPORARY TABLE on a global temporary trace table.
Because there is no primary index, all requests that are normally primary index retrievals
become full‑table scans.