17.10 - XMLQCD - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Function of XMLQCD

Captures the XML QCD output from an INSERT EXPLAIN or BEGIN QUERY CAPTURE request submitted with the IN XML option.

XMLQCD Table Definition

The following CREATE TABLE request defines the XMLQCD table:

CREATE MULTISET TABLE qcd.XMLQCD, NO FALLBACK, NO BEFORE JOURNAL,
NO AFTER JOURNAL, CHECKSUM = DEFAULT (
 ID               INTEGER NOT NULL,
 Kind             CHARACTER(1) NOT NULL CHARACTER SET LATIN
                  NOT CASESPECIFIC,
 Seq              INTEGER NOT NULL,
 Length           INTEGER NOT NULL,
 Text             VARCHAR(31000) NOT NULL CHARACTER SET UNICODE
                  NOT CASESPECIFIC,
 UDB_Name         VARCHAR(128) CHARACTER SET UNICODE
                  NOT CASESPECIFIC NOT NULL,
 CaptureTimeStamp TIMESTAMP(0) NOT NULL,
 SessionID        INTEGER FORMAT ‘--,---,---,--9’ NOT NULL,
 WorkloadName     VARCHAR(128) CHARACTER SET UNICODE UPPERCASE
                  NOT CASESPECIFIC NOT NULL,
 XMLDocType       INTEGER FORMAT ‘--,---,---,--9’)
UNIQUE PRIMARY INDEX (ID, Kind, Seq);

Attribute Definitions for XMLQCD

The following table defines the XMLQCD table attributes.

Attribute Definition
ID
  • Unique identifier for the set of rows associated with this XML document.
  • Partial UPI for the table.
Kind
  • A flag that describes the kind of operation characterized by this row.
  • Partial UPI for the table.

    If Q, Vantage captures the value for ID from qcd.SeqNumber.QueryID (see SeqNumber) whenever this query capture operation is performed.

Seq
  • Unique identifier for the query that specifies the order of the generated row.

    The first row generated from a given XML document is assigned the sequence number 1, the second row is assigned a sequence number 2, and so on.

  • Partial UPI for the table.
Length The number of characters that remain in the XML document beginning with this row.

The equation for determining the approximate value for Length is as follows:

Determining approximate value for Length equation

For example,

  • If Seq is 1, then Length is the total number of characters.
  • If Seq is 2, then Length is the total number of characters minus 31,000.
  • If Seq is 3, then Length is the total number of characters minus 62,000.

and so on.

Text The Seqth slice of the XML document.
UDB_Name Name of the user who submitted the captured query.
CaptureTimeStamp Time stamp of when the query was captured.
SessionID ID for the session in which the query was captured.
WorkloadName Name of the workload specified in the BEGIN QUERY CAPTURE request used to capture the query.
XMLDocType The type of XML content stored in the Text column.