16.10 - Procedure Using BTEQ - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Perform the following procedure to create the tables for the query capture database using BTEQ. This procedure does not create the macros that the Visual EXPLAIN utility needs to work with a QCD. You must create these macros from the Visual EXPLAIN Control Center before the utility can visualize or load QCD query plans.

The procedure assumes that you have already created QCD_database_name.

  1. Start BTEQ.
  2. Change your current database or user to the database in which the QCD tables are to be created as follows.

      DATABASE QCF_database_name;

    where QCF_database_name is the name of the database you created for the QCD tables.

  3. Perform the following steps in the indicated order.
    1. .SET WIDTH 254
    2. .EXPORT FILE = file_name
    3. SELECT TabDefinition

      
FROM systemfe.CreateQCF

      ORDER BY SeqNumber;

    4. .EXPORT FILE = file_name
    5. .RUN FILE = file_name

      where file_name is the name of the file you create to contain the output of the SELECT request.

  4. Secure QCD by granting the appropriate privileges to the users who will be analyzing its data.

    To use the BEGIN QUERY CAPTURE request to insert data into the QCD tables in TDQCD, you must have the ALL privilege.

    See SQL Data Control Language for information about how to implement column-level and row-level security for your tables.

    You can implement additional secured access by creating views on QCD.

  5. Populate the QCD tables using the appropriate tools.
    • BEGIN QUERY CAPTURE requests to capture query plans. For the syntax of this statement, see SQL Data Definition Language Syntax and Examples.
    • INSERT EXPLAIN requests to capture request plans. For the syntax of this statement, see SQL Data Manipulation Language.
    • Any of the following statements, as appropriate, to capture statistical and demographic data.
      • COLLECT DEMOGRAPHICS
      • COLLECT STATISTICS (QCD Form)
      • INSERT EXPLAIN … WITH STATISTICS
    • The appropriate workload macros to create workloads for index analysis.

Client tools like Visual EXPLAIN and the Teradata Index Wizard access QCD tables using the views and macros created using the Control Center feature of Visual EXPLAIN.