16.10 - Procedure Using SQL DIAGNOSTIC Statements - Teradata Database

The following list provides a high-level procedure for capturing Optimizer environmental cost information from a production system and applying that information to a test system.

1. Depending on your requirements, perform one or both of the following steps.
   1. Populate SystemFE.Opt_Cost_Table and SystemFE.Opt_DBSCtl_Table on the target system by running the DIAGNOSTIC DUMP COSTS statement (see SQL Data Manipulation Language for the syntax and usage information for this statement).

      This extracts the Optimizer environmental cost value information from the target system and writes it to a relational table so it can be copied and used to drive the Optimizer on a test system.

   2. Populate SystemFE.Opt_RAS_Table on the target system by running the DIAGNOSTIC DUMP SAMPLES statement (see SQL Data Manipulation Language for the syntax and usage information for this statement).

      This extracts the dynamic AMP statistical samples from the target system and writes them to a relational table so they can be copied and used to drive the Optimizer on a test system.

2. Depending on your requirements, perform one or both of the following steps.
   1. Copy the Optimizer cost value information and any necessary object definitions and test queries from the target system to the test system.

      To do this, select rows from SystemFE.Opt_Cost_Table on the production system and export them to a file using an appropriate load utility, such as FastExport, CLIv2, or a BTEQ SQL script.

   2. Copy the dynamic AMP statistical samples and any necessary object definitions and test queries from the target system to the test system.

      To do this, select rows from SystemFE.Opt_RAS_Table on the production system and export them to a file using an appropriate load utility, such as FastExport, CLIv2, or a BTEQ SQL script.

3. Depending on your requirements, perform one or both of the following steps.
   1. Import the rows into SystemFE.Opt_Cost_Tableon the test system using an appropriate load utility, such as FastLoad, MultiLoad, Teradata Parallel Data Pump, CLIv2, or a BTEQ SQL script.
   2. Import the rows into SystemFE.Opt_RAS_Tableon the test system using an appropriate load utility.
4. Import the rows into SystemFE.Opt_DBSCtl_Table on the test system using an appropriate load utility from the following list.
   • FastLoad
   • MultiLoad
   • Teradata Parallel Data Pump
   • CLIv2
   • BTEQ SQL script
5. Import the rows into SystemFE.Opt_XMLPlan_Tblusing an appropriate load utility from the following list.
   • FastLoad
   • MultiLoad
   • Teradata Parallel Data Pump
   • CLIv2
   • BTEQ SQL script
6. Populate the test system with appropriate test data imported from the production system for which testing is to be performed.
7. Depending on your requirements, perform one or both of the following steps.
   1. Run the DIAGNOSTIC SET COSTS SQL statement (see SQL Data Manipulation Language) to specify the exact level of cost value information (and for which target configuration) you want to set.
   2. Run the DIAGNOSTIC SET SAMPLES SQL statement (see SQL Data Manipulation Language) to specify the exact level of dynamic AMP statistical sampling (and for which target configuration) you want to set.

      For an illustration, see About Mapping Target System Files to a Test System.