DIAGNOSTIC DUMP COSTS - Teradata Database

Purpose

For more information about DBS Control fields, see Utilities .

Syntax

Syntax Elements

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 is a UPI.
• SystemFE.Opt_DBSCtl_Table, the primary index 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 up to 255 bytes in length.

ANSI Compliance

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

Required Privileges

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

• SystemFE.Opt_Cost_Table
• SystemFE.Opt_DBSCtl_Table

Target Level Emulation and DIAGNOSTIC DUMP COSTS

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

Session Mode Restrictions

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

Example: Extract Optimizer Cost Information

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.

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

    DIAGNOSTIC DUMP COSTS production_1;