Procedure Using the Teradata System Emulation Tool
Capturing data from a target production system and applying it to a test system is a two-phase process.
The following is a high level overview of this procedure.
1 Depending on your requirements, perform one or all of the following steps.
a 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).
This operation extracts the Optimizer environmental cost value and DBS Control 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.
b Populate SystemFE.Opt_RAS_Table on the target system by running the DIAGNOSTIC DUMP SAMPLES statement (see SQL Data Manipulation Language).
This operation extracts dynamic AMP statistics from the header of the specified table on 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.
c Populate SystemFE.Opt_XMLPlan_Tbl by running the DIAGNOSTIC DUMP XMLEXPLAIN statement (see SQL Data Manipulation Language).
2 Log onto Teradata System Emulation Tool from a client connected with the target system.
3 Create a target system profile to define the databases, tables, views, macros, and related environmental parameters or dynamic AMP statistics and DBS Control information to be captured for loading onto the test system.
4 Collect the Optimizer cost value information or dynamic AMP statistics, the DBS Control information, and any necessary object definitions and test queries from the target system and place it in a staging area from which the test system can import it.
To do this, invoke the Teradata System Emulation Tool Export function from the target system. This action writes the data created by DIAGNOSTIC DUMP COSTS or DIAGNOSTIC DUMP SAMPLES into a staging area.
5 Invoke the Teradata System Emulation Tool Import function from a client connected with the test system to import the data from the staging area on the target system to SystemFE.Opt_Cost_Table or SystemFE.Opt_RAS_Table and to SystemFE.Opt_DBSCtl_Table on the test system.
The Import function permits you to select from several different load utilities to use to transfer the data.
6 Depending on your requirements, perform one or both of the following steps:
a 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.
b 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.
You can also use Teradata System Emulation Tool to copy any of the following target system data to an appropriate test system: