Determining from the system catalog views whether a Table is GLOBAL TEMPORARY or GLOBAL TEMPORARY TRACE

Database

Determining from the system catalog views whether a Table is GLOBAL TEMPORARY or GLOBAL TEMPORARY TRACE

Hi,

I am connecting to a Teradata 12.0 server and quering for a list of table with "select * from dbc.tables". I can reconstruct the CREATE TABLE DDL for the tables except the GLOBAL TEMPORARY CLAUSE because there doesn't seem to be any column that contains information to identify the table as GLOBAL TEMPORARY or GLOBAL TEMPORARY TRACE. The columns in the dbc.tables are as follows ...

select DatabaseName, TableName, Version, TableKind, ProtectionType, JournalFlag, CreatorName, RequestText, CommentString, ParentCount, ChildCount, NamedTblCheckCount, UnnamedTblCheckExist, PrimaryKeyIndexId, RepStatus, CreateTimeStamp, LastAlterName, LastAlterTimeStamp, RequestTxtOverflow, AccessCount, LastAccessTimeStamp, UtilVersion, QueueFlag, CommitOpt, TransLog, CheckOpt

Does anyone know if there is a way to determine the status of GLOBAL TEMPORARY TRACE in the system catalog tables?

thanks
-Niels

2 REPLIES
Teradata Employee

Re: Determining from the system catalog views whether a Table is GLOBAL TEMPORARY or GLOBAL TEMPORARY TRACE

In my opinion one way to find could be using dbc.tables requesttext column and search for keywords

select tablename,databasename ,requesttext , 'TRACE TABLE' from dbc.tables where requesttext like '%CREATE%Global%Temporary%TRACE %'
UNION
select tablename,databasename ,requesttext ,'NOT TRACE TABLE' from dbc.tables
where (requesttext not like '%CREATE%Global%Temporary%TRACE %' and requesttext like '%CREATE%Global%Temporary%' )

Thanks
Anand Agarwal
ADC-India
N/A

Re: Determining from the system catalog views whether a Table is GLOBAL TEMPORARY or GLOBAL TEMPORARY TRACE

Hi Niels,
it's the column CommitOpt, if it's 'D' or 'P' then it's a GTT (on commit Delete/Preserve rows).

You got luck, before TD12 it was not available in dbc.tables, only in dbc.tvm.

Dieter