Rules and Limitations for Global Temporary Trace Tables - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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).

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 SET tables.
  • 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 tables.
  • You cannot define any indexes for the table, including primary, primary AMP, secondary, join, and hash indexes.

    This restriction explicitly includes both partitioned and nonpartitioned 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 trace tables.
  • 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 Teradata Vantage™ - SQL External Routine Programming, B035-1147 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 for examination.

    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.