Archives of the TeradataForum

Message Posted: Mon, 27 Feb 2006 @ 16:44:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here is a knowledge article on the subject:

Question:

How do I access the complete object definition for very large Teradata DDL?

Answer:

The DBC.TVM table contains one row for each table, view, trigger, stored procedure,join index, macro, or user defined function in the database. Included in the table are the columns RequestText and CreateText. RequestText can hold 12,500 characters and contains the original SQL Request text. CreateText can hold 26,000 characters, and contains the original text with SQL comments removed, extraneous spaces removed, and names fully qualified.

To support very large object definitions, the table DBC.TextTbl is available. This table stores the "CreateText" and/or "RequestText" in case the text cannot fit in the DBC.TVM row.

     CREATE SET TABLE DBC.TextTbl ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           TextId BYTE(6) NOT NULL,
           TextType CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           LineNo SMALLINT FORMAT '---,--9' NOT NULL,
           TextString VARCHAR(32000) CHARACTER SET UNICODE NOT CASESPECIFIC
               FORMAT 'X(255)' NOT NULL,
           DatabaseId BYTE(4) NOT NULL)
     PRIMARY INDEX ( TextId );

Within DBC.TVM, the two flags: CreateTxtOverFlow and RequestTxtOverFlow are defined to indicate an instance of in which the definition exceeds 12,500 characters. If CreateTxtOverFlow = 'C' or RequestTxtOverFlow = 'R', the complete text of the object definition can be found in the table DBC.TextTbl.

If the Request text is 12,500 characters or less, the SQL Request Text is saved in DBC.TVM. If the Request Text exceeds 12,500 characters then the SQL Request Text is saved in DBC.TextTbl, and the DBC.TVM.RequestTxtOverFlow flag is set to 'R'. For a Request Text overflow, access to the extended definition is available from the "TextString" field through the view DBC.TableText:

     REPLACE VIEW DBC.TableText
     AS SELECT
        dbase.DatabaseName,
        tvm.TVMName (NAMED TableName),
        tvm.TableKind,
        texttbl.TextString (NAMED RequestText),
        texttbl.LineNo
     FROM  DBC.tvm,
          DBC.texttbl,
          DBC.dbase
     WHERE tvm.DatabaseId = dbase.DatabaseId
     AND tvm.TVMId = texttbl.TextID
     AND Texttbl.TextType = 'R' WITH CHECK OPTION;

If the Create Text is 32,000 characters or less, the SQL Create Text is saved in DBC.TVM. If the Create Text exceeds 32,000 characters then the SQL Create Text is saved in DBC.TextTbl, and the DBC.TVM.CreateTxtOverFlow flag is set to 'C'. The following SQL will extract the complete text:

     LOCK dbc.tvm FOR access
     LOCK dbc.dbase FOR access
     LOCK dbc.texttbl FOR access
     SELECT texttbl.TextString (NAMED CreateText)
     FROM DBC.tvm,
     DBC.texttbl,
         DBC.dbase
     WHERE tvm.DatabaseId = dbase.DatabaseId
      AND tvm.TVMId = texttbl.TextID
      AND Texttbl.TextType = 'C'
      AND DatabaseName = 'DATABASE NAME'
      AND TVMName = 'TABLE/VIEW/MACRO NAME'
     ORDER BY LineNo;

	<Prev	Next>		<<First	<Prev	Next>	Last>>