Using BEGIN QUERY CAPTURE | Teradata Vantage - 17.10 - Rules and Restrictions for BEGIN QUERY CAPTURE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

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

The following rules and restrictions apply to the BEGIN QUERY CAPTURE statement.

BEGIN QUERY CAPTURE and BEGIN QUERY LOGGING

BEGIN QUERY CAPTURE has the same functionality as the following form of BEGIN QUERY LOGGING, except that the XML query plans are captured in a query capture database (QCD) instead of DBQL logs.

BEGIN QUERY LOGGING WITH XMLPLAN,STATSUSAGE,VERBOSE,STATSDETAILS;

The database does not capture the XML plans in DBQL logs, ensuring that NO EXEC queries are not mixed with executed queries in the DBQL logs. Instead, the system captures the XML plans in the QCD table XMLQCD.

The scope of BEGIN QUERY CAPTURE is at the session level, not at the user level like BEGIN QUERY LOGGING. Unlike DBC.DbqlRuleTbl, which is maintained for query logging, the database does not maintain query capture rules in a query capture database. For more information about the relationship between BEGIN QUERY CAPTURE and BEGIN QUERY LOGGING, see Teradata Vantage™ - Database Administration, B035-1093.

BEGIN QUERY CAPTURE supports the same statement types as BEGIN QUERY LOGGING.

BEGIN QUERY CAPTURE does not capture query plans for the EXPLAIN request modifier or for the INSERT EXPLAIN and DUMP EXPLAIN statements.

You cannot submit a BEGIN QUERY LOGGING request for a user that has an active BEGIN QUERY CAPTURE session.

Query Banding and BEGIN QUERY CAPTURE

You can use query banding with BEGIN QUERY CAPTURE statements, but you cannot enable or disable query banding within the scope of a BEGIN QUERY CAPTURE and END QUERY CAPTURE statement pair.

The database captures the name of the query band used and stores it in the QCD XMLQCD table.

VERBOSE Option for BEGIN QUERY CAPTURE

When you specify the VERBOSE option for BEGIN QUERY CAPTURE, the logged XML document contains details on Object, SQL, Step Details, and Verbose EXPLAIN text regardless of whether you specified the XMLPLAN option.

The database logs the Verbose EXPLAIN text in the existing <PlanStep>@StepText attribute.

Verbose EXPLAIN text always includes the normal EXPLAIN text for a request.

STATSDETAILS and XMLPLAN Options

When you specify the STATSDETAILS option but not the XMLPLAN option, the logged XML document contains details on Object, SQL, Step Details, and Statistics Details from Statistics header and Statistics Recommendation DDLs.

When you specify both STATSDETAILS and XMLPLAN, the database logs a single integrated document containing data from both options.

The database logs Object, SQL, and Step Details only once. Statistics Details from the Statistics Header are logged into a new XML element.

BEGIN QUERY CAPTURE Statement Scope

You cannot submit a BEGIN QUERY CAPTURE statement within the scope of a previously submitted BEGIN QUERY CAPTURE statement.