Function of Global Temporary Trace Tables
Like global temporary tables, global temporary trace tables have a persistent definition, but do not retain rows across sessions.
Global temporary trace tables are not hashed. Instead, each table row is assigned a sequential hash sequence. You can perform an INSERT … SELECT operation on the table to copy its rows into a normal hashed table with a primary key, indexes, or additional attributes common to other base table types.
Define the information you want to trace as the input to the UDF. The UDF can call the FNC_Trace_Write_DL function that puts the information in a global temporary trace table. If the trace is not turned on, the system does no function traceback, but there is overhead, because the system calls the UDF. See SET SESSION FUNCTION TRACE Usage Notes.
Run the trace UDF in nonprotected mode after debugging to make sure the UDF runs in-line with the procedure. A premature end of the procedure or a rollback of the transaction has no impact on the global temporary trace table, and its contents are not deleted until the session logs off. Enabling function traceback makes your procedure run more slowly because each trace call forces the data to be written into the trace table. Nothing is buffered to make sure that nothing is lost while you are testing a function.
Rules and Limitations for Global Temporary Trace Tables
Global temporary trace tables are not hashed, and therefore have definition and manipulation restrictions that distinguish that ordinary global temporary tables do not (see Global Temporary Tables).
- 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.
- On the Block File System, a global temporary trace table is created as NO FALLBACK by default and cannot be altered to be FALLBACK.
On the Object File System, the NO FALLBACK attribute is ignored.
- You cannot define any indexes for the table, including primary, primary AMP, secondary, and join 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.
- If you try to use a 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 sequential order. The AMP gets the next sequence number by adding one to the sequence number of the last row of the trace table, 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.
- If you specify ON COMMIT PRESERVE ROWS, the system preserves the rows of the materialized trace table after the transaction commits or terminates abnormally. Otherwise, all rows are deleted from the table.
If the trace table was materialized for the session by the transaction that stops, the trace table is deleted and no rows are preserved. When the trace table is successfully materialized,the system retains any rows a UDF had written to the trace table before the stop.
- You cannot join a global temporary trace table with another table.
- You cannot update a global temporary trace table. That is, 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.
This SELECT request can specify a WHERE clause, to determine the criteria for selecting the rows, and 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 typically primary index retrievals become full-table scans.