Use the BTEQ Procedure | Teradata Vantage - 17.00 - 17.05 - Procedure Using BTEQ - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

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 Teradata Vantage™ - SQL Data Control Language, B035-1149 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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
    • INSERT EXPLAIN requests to capture request plans. For the syntax of this statement, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
    • 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.