About the Query Capture Database - Teradata Database - Teradata Vantage NewSQL Engine

A query capture database, or QCD, stores the Optimizer query analysis workload data and the statistical and demographic data needed to support query analysis.

The SystemFE DIP creates a default QCD called TDQCD beneath the SystemFE user, but you can also create your own QCD databases, and you can define multiple QCD databases for your systems.

You must be granted the INSERT privilege to use a BEGIN QUERY CAPTURE request to capture query plans in XML format at the session level and write data into TDQCDor any other user-defined QCD.

Teradata Database determines the size of TDQCD based on the number of system AMPs using the following equation.

TDQCD_size = 50MB + (number_of_system_AMPs x 100 x 1,024 bytes)

You can use the modify_tdqcd.spl SQL stored procedure to modify the size of the TDQCD database based on the number of AMPs in a test system. The definition of modify_tdqcd.spl is as follows.

REPLACE PROCEDURE TDQCD.MODIFY_TDQCD(IN Basesize int, OUT Success int)
BEGIN
DECLARE TotalAmps INTEGER;
DECLARE CurrentSize INTEGER;
DECLARE TargetSize INTEGER;

SELECT COUNT(DISTINCT Vproc) FROM Dbc.DiskSpaceV INTO TotalAmps ;
select ZEROIFNULL(permspace) into :CurrentSize from Dbc.DatabasesV where databasename = 'tdqcd';
SET TargetSize = Basesize + (TotalAmps * 100 * 1024);

IF (CurrentSize < TargetSize) then
CALL dbc.sysexecsql('MODIFY DATABASE TDQCD AS PERM = ' ||:TargetSize || 'BYTES');
END IF;

SET Success = 1;

END;

Use the Control Center feature of the Visual EXPLAIN tool (see Procedure Using the Visual EXPLAIN Utility) or BTEQ using the CreateQCF table (see Procedure Using BTEQ) to create your query capture databases.QCD tables are populated using various methods, including the following:

• BEGIN QUERY CAPTURE
• COLLECT DEMOGRAPHICS
• COLLECT STATISTICS (QCD Form)
• INSERT EXPLAIN … WITH STATISTICS
• BEGIN QUERY CAPTURE
• Invoking workload macros from the client-based Teradata Index Wizard utility.

The reason for this constraint is that the SQL statements BEGIN QUERY CAPTURE, DUMP EXPLAIN, INSERT EXPLAIN, INITIATE INDEX ANALYSIS, RESTART INDEX ANALYSIS, COLLECT DEMOGRAPHICS, and COLLECT STATISTICS (QCD Form) all assume the default physical model when they capture the specified query, statistical, and demographic information for insertion into the QCD tables.

The results of changing the structure of the QCD tables are unpredictable.