Query Capture Database | Teradata Vantage - Query Capture Database - Analytics Database - Teradata Vantage

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.

Vantage 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;

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.