15.10 - QRYLogXMLDocV - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Query

DBC

 

View Column

Data Type

Format

Source Table.Column

ProcID

DECIMAL(5,0)

NOT NULL

-(5)9

DBQLXMLTbl.ProcID (Composite NUPI)

CollectTimeStamp

TIMESTAMP(6)

NOT NULL

YYYY-MM-DDBHH:MI:SS

DBQLXMLTbl.CollectTimeStamp (Composite NUPI)

QueryID

DECIMAL(18,0)

------------------9.

DBQLXMLTbl.QueryID

XMLTextDoc

XML

Teradata XML

DBQLXMLTbl.XMLTextInfo

Note: XMLTextDoc is a result of combining multiple DBQLXMLTbl.XMLTextInfo rows with the same QueryID. A Teradata XML document is created from that combination.

XMLDocType

INTEGER

--,---,---,--9

DBQLXMLTbl.XMLDocType

XMLPlanEnabled

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

StatsUsageEnabled

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

VerboseEnabled

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

DetailedStatsEnabled

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

HasXMLPlanData

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

HasStatsUsageData

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

HasVerboseData

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

HasDetailedStatsData

VARCHAR(1)

UNICODE

X(1)

DBQLXMLTbl.XMLDocType

QRYLogXMLDocV provides a view into DBQLXMLTbl that combines multiple rows of XMLTextInfo with the same QueryID into a single XMLTextDoc document.

Note: In a future release the XMLTextInfo column in base table DBQLXMLTbl will change its data type from VARCHAR to CLOB type casted to Teradata XML type. To ensure your applications remain compatible with this future enhancement Teradata recommends using the QryLogXMLDocV view instead of QryLogXMLV.

Possible Values for HasXMLPlanData, HasStatsUsageData, HasVerboseData, and HasDetailedStatsData

For these columns, a value of Y or N can be specified. If you specify Y, the data is contained in the XMLTextInfo column on behalf of the specific logging [Option], where Option represents the STATSUSAGE or XMLPlan.

Possible Values for XMLDocType

Note: Teradata recommends that you use the Has[Option]Data and [Option]Enabled columns rather than the XMLDocType column directly, where Option represents the STATSUSAGE or XMLPlan. Queries against these columns allow you to specify a Y or N value rather than bit mapped values. For more information about these columns, see “Possible Values for HasXMLPlanData, HasStatsUsageData, HasVerboseData, and HasDetailedStatsData” or “Possible Values for XMLPlanEnabled, StatsUsageEnabled, VerboseEnabled, and DetailedStatsEnabled” on page 263.

Possible Values for XMLPlanEnabled, StatsUsageEnabled, VerboseEnabled, and DetailedStatsEnabled

For these columns, a value of Y or N can be specified. If you specify Y, the [Option], where Option represents the STATSUSAGE or XMLPlan, was enabled when the data in XMLTextInfo was logged.

Example  

SELECT XMLTextDoc FROM QRYLogXMLDocV;

Result: The result is exported to one or more XML documents. The following is an excerpt from one of the XML files.

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<QryPlanXML xsi:schemaLocation="http://schemas.teradata.com/queryplan http://schemas.teradata.com/queryplan/queryplan.xsd" xmlns="http://schemas.teradata.com/queryplan" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 
  <Query CollectTimeStamp="2014-07-17T19:11:30.54" QCFCaptureTimestamp="2014-07-17T19:15:21.80" QueryID="307195536655421804" StartTime="2014-07-17T19:15:21.78">
    <Request CheckpointNum="0" DefaultDatabase="TEST_DB" MaxStepMemory="1.062" NumResultRows="0" QueryText="select c1 from bigtable;">
      <Statement QCFStatementType="RET" StatementGroup="Select" StatementType="Select"></Statement>
             .
             .
             .
< <Session AppID="BTEQ" ClientAddr="153.65.214.159" ClientID="HN186003" InternalRequestNum="3" KeepFlag="false" LogicalHostID="1" LogonDateTime="2014-07-17T19:15:21.44" LogonSource="(TCP/IP) ef6a 153.65.214.159 SDLL7880.LABS.TERADATA.COM      1940  HN186003  BTEQ  01 LSS" NumRequestCtx="1" RequestNum="3" SessionID="1017" SessionWDID="13"></Session>
    <WLMgmt FinalWDID="13" LastStateChange="2014-07-17T17:49:32.49" OpEnvID="1" ResponseTimeMet="true" SysConID="1" TDWMEstLastRows="4" TDWMEstMaxRows="4" TDWMEstMemory="4" TDWMEstTotalTime="74" ThrottleBypass="false" TxnUniq="1196294145" WDID="13"></WLMgmt>
    <ReDrive QueryReDriven="N"></ReDrive>
  </Query>
</QryPlanXML>

Example  

This example shows how to verify that XMLTextDoc is a valid XML document.

SELECT QueryID,
case XMLTextDoc.isdocument()
    WHEN 1 THEN ‘Yes’
    ELSE ‘No’
    END as “Well-formed”
FROM DBC.QRYLogXMLDocV
ORDER BY QueryID;

Example  

This example serializes the XMLTextDoc document into a string and returns the number of bytes contained in the string.

SELECT QueryID,
CHARACTER_LENGTH(xmlserialize(document XMLTextDoc)) as PlanSize
FROM DBC.QRYLogXMLDocV
ORDER BY QueryID;

For more information about the DBQL logging options, see Database Administration.