About the Query Capture Database - Teradata Database

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 TDQCD or any other user-defined QCD.

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

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” on page 396) or BTEQ using the CreateQCF table (see “Procedure Using BTEQ” on page 397) 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.

Notice:

Because the QCD is often a set of user tables, you can change both their individual structure and the structure of the QCD; however, you should never change any of the QCD structures in any way.

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.