16.10 - XMLQCD - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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, Teradata Database 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:

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 Seq th 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.