ViewTable - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
ykx1561500561173.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

Function of ViewTable

Captures the DDL for any views used in a query.

ViewTable Table Definition

The following CREATE TABLE request defines ViewTable:

   CREATE SET TABLE ViewTable(
     QueryID   INTEGER NOT NULL,
     ViewName  VARCHAR128 CHARACTER SET UNICODE
               UPPERCASE NOT CASESPECIFIC NOT NULL,
     DBName    VARCHAR128 CHARACTER SET UNICODE
               UPPERCASE NOT CASESPECIFIC NOT NULL,
     ViewText  VARCHAR(30000) CHARACTER SET UNICODE
               NOT CASESPECIFIC,
     SeqNumber BYTEINT NOT NULL,
     Complete  CHAR(1)CHARACTER SET LATIN NOT CASESPECIFIC)
   PRIMARY INDEX QueryID_ViewName_DBName (QueryID,ViewName,DBName);

Attribute Definitions for ViewTable

The following table defines the ViewTable attributes:

Attribute Definition
QueryID
  • Unique identifier for the query.
  • Partial NUPI for the table.
ViewName
  • Name of the view.
  • Partial NUPI for the table.
DBName
  • Name of the database.
  • Partial NUPI for the table.
ViewText Stores the DDL view text.
  • If ViewText <= 30,000 characters, SeqNumber is set to 1.
  • If ViewText > 30,000 characters, SeqNumber is set to 1 + (1-n), where n is the identifier for the fragment of ViewText stored in the row following the first fragment.

When ViewText > 30,000 characters, only the first 30,000 are stored and the remaining text is stored in overflow rows within ViewTable. The overflow rows are identified by their SeqNumber value.
SeqNumber The sequence of ViewText stored in this row.
Complete Identifies whether ViewTable stores the complete view DDL or a truncated version.
  • If F, ViewText DDL is truncated.

    This case occurs when the size specified in the LIMIT SQL clause of the DUMP EXPLAIN or INSERT EXPLAIN statement that captured the view is less than the actual length of ViewText.

    See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

  • If T, Full ViewText DDL is stored.

    Overflow ViewText DDL is stored in additional rows in ViewTable. Individual overflow rows are identified by their respective SeqNumber.