Procedure Using SQL DIAGNOSTIC Statement Requests
The procedure defined here uses the second method.
1 Determine the name of the session profile.
DIAGNOSTIC HELP PROFILE;
Assume the request returns the name prof-name for the session profile.
2 Capture the cost segment and SQL to generate the emulation cost profile.
DIAGNOSTIC DUMP COSTS Wellington;
.EXPORT FILE=ProfileSQL
EXEC DBC.ShowCostProfileAs ( 'prof-name'
, 'Wellington_Profile'
, 'Active cost profile for Wellington')
.EXPORT reset
3 Load the file named ProfileSQL.
To do this, execute the following BTEQ installation command.
.RUN FILE=ProfileSQL
This installs as a Wellington_Profile variable Cost Profile.
4 Insert the cost segment row for Wellington into SystemFE.Opt_Cost_Table and his DBS Control and internal parameter rows into SystemFE.Opt_DBSCtl_Table using DIAGNOSTIC DUMP COSTS (see “Capturing Target System Optimizer Cost Values and Dynamic AMP Statistical Samples” on page 406).
5 Insert the internal parameter row for Wellington into SystemFE.Opt_RAS_Table using DIAGNOSTIC DUMP SAMPLES (see “Capturing Target System Optimizer Cost Values and Dynamic AMP Statistical Samples” on page 406).
6 Run the appropriate SQL DIAGNOSTIC requests to install the Target Level Emulation data.
DIAGNOSTIC SET COSTS Wellington
, PROFILE Wellington_Profile
ON FOR scope_level;
where scope_level indicates one of the following.
|
This scope level... |
Applies to the current... |
|
REQUEST |
SQL request. |
|
IFP |
parsing engine. |
|
SESSION |
session. |
|
SYSTEM |
system, meaning there is no restriction enforced. |
For details, see “SET DIAGNOSTIC COSTS” in SQL Data Manipulation Language.
DIAGNOSTIC SET SAMPLES Wellington
ON FOR scope_level;
where scope_level indicates one of the following.
|
This scope level... |
Applies to the current... |
|
SESSION |
session. |
|
SYSTEM |
system, meaning there is no restriction enforced. |
For details, see “DIAGNOSTIC SET SAMPLES” in SQL Data Manipulation Language.
7 This step is optional.
Remove the profile.
EXEC DBC.DeleteCostProfile ('Wellington_Profile');