QCD (Query Capture Database) in Visual explain Plan...

Database
l_k
Enthusiast

QCD (Query Capture Database) in Visual explain Plan...

Hi Teradata folks,

I have created a QCD database under the Production database via visual explain plan.
It created lot of stuffs (data demographics,index columns,tablestatisticsjoinindexcolumns,partitionindex,worktables etc.,).But when i
select those tables,it doesn't have any records inside..

How to populate or configure or enable these tables to have the data?

I believe it would be helpful to analyse the index columns,statistics info. and subtable size also if automated this process...

Tags (1)
5 REPLIES
l_k
Enthusiast

Re: QCD (Query Capture Database) in Visual explain Plan...

Hi,

Anyone please help me on this issue?

Lavakumar
Junior Contributor

Re: QCD (Query Capture Database) in Visual explain Plan...

Hi Lavakumar,
did you read the appropriate chapter in the manuals?

SQL Request and Transaction Processing: Query Capture Facility
SQL Data Manipulation Language: Query and Workload Analysis DML Statements

Dieter
l_k
Enthusiast

Re: QCD (Query Capture Database) in Visual explain Plan...

Hi dieter,

I will search the manuals for that..

Thanks for your information about manuals ...
BPP
Enthusiast

Re: QCD (Query Capture Database) in Visual explain Plan...

This is from DB admin doc...

Use the INSERT EXPLAIN statement to capture the query plan into the QCD database.

Note: You can alternatively store the query plan as XML into the QCD.XMLQCD table by
using the INSERT EXPLAIN …IN XML statement. The IN XML option is quick because it
stores the entire query plan as XML output into one table whereas the INSERT EXPLAIN
INTO QCD operation requires saving the data to multiple tables within the QCD
database.
Another option for logging the query plan as an XML is by using the BEGIN QUERY
LOGGING WITH XMLPLAN statement (see “WITH XMLPLAN” in “WITH Logging
Options” on page 336). However, note that this XML document is more compact and
contains less detail than the QCF plan.

Re: QCD (Query Capture Database) in Visual explain Plan...

Submitt INSERT EXPLAIN into SQL statement.
To get Statistics and demographics use Insert Explain with statistics and demographics option.
This statement will populate the QCD tables with plan details etc.
Visual explain will read the data from these tables and opens the captured execution plan.
If the query you submit through insert explain has index referenced columns and index is being used than index column details will appear otherwise index table remains empty.