DIAGNOSTIC DUMP COSTS - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Extracts optimizer cost information calculated by the Trusted Parallel Application (TPA) and DBS Control field information from a target system, then writes the information to the following tables on that system:

  • SystemFE.Opt_Cost_Table
  • SystemFE.Opt_DBSCtl_Table
  • where:

     

    Syntax element …

    Specifies …

    target_system
    _name

    Name of the target (production) system from which optimizer cost information is calculated by the TPA. For information on naming objects, see SQL Fundamentals.

    target_system_name is not case sensitive and can be up to 63 characters in length.

    target_system_name is used as the primary index for the following tables.

  • SystemFE.Opt_Cost_Table
  • The primary index for SystemFE.Opt_Cost_Table is a UPI.

  • SystemFE.Opt_DBSCtl_Table
  • The primary index for SystemFE.Opt_DBSCtl_Table is a NUPI.

    comment

    Optional string literal description for the request.

    The default character string is the specification for target_system_name.

    comment can be no longer than 255 bytes in length.

    DIAGNOSTIC DUMP COSTS is a Teradata extension to the ANSI SQL:2011 standard.

    You must have the INSERT privilege on the following tables to dump costs:

  • SystemFE.Opt_Cost_Table
  • SystemFE.Opt_DBSCtl_Table
  • DIAGNOSTIC DUMP COSTS does not require Target Level Emulation to be enabled in order to extract target system environmental cost data.

    You cannot perform DIAGNOSTIC DUMP COSTS in ANSI session mode or within explicit transaction boundaries in Teradata session mode.

    Dump the system environmental cost values for the optimizer on the current system to SystemFE.Opt_Cost_Table and the DBS Control fields to SystemFE.Opt_DBSCtl_Table. This example are uses the name production_1 for the current system.

    Note that target_system_name is used as the primary index for SystemFE.Opt_Cost_Table and SystemFE.Opt_DBSCtl_Table.

        DIAGNOSTIC DUMP COSTS production_1;

    For more information about environmental costs, see:

  • “DIAGNOSTIC HELP COSTS” on page 598
  • “DIAGNOSTIC SET COSTS” on page 600
  • SQL Request and Transaction Processing
  • For more information about DBS Control fields, see Utilities.