Example: Index Analysis
Assume that the QCD named MyQCD exists on the system. The following INITIATE INDEX ANALYSIS request performs successfully:
INITIATE INDEX ANALYSIS ON table_1 FOR MyWorkload IN MyQCD AS table_1Index;
If MyQCD does not exist in the system, the same request fails.
INITIATE INDEX ANALYSIS ON table_1 FROM MyWorkload IN MyQCD AS table_1Index; *** Failure 3802 Database 'MyQCD' does not exist.
Example: Index Analysis and Privileges
Assume that you have the INSERT privilege on the IndexRecommendations and IndexColumns tables or on database QCD_name. The following INITIATE INDEX ANALYSIS request performs successfully.
INITIATE INDEX ANALYSIS ON table_1 FOR MyWorkload IN MyQCD AS table_1Index;
If you do not have INSERT privilege on MyQCD, the same request fails.
INITIATE INDEX ANALYSIS ON table_1 FOR MyWorkload IN MyQCD AS table_1Index; *** Failure 3523 The user does not have INSERT access to MyQCD.IndexRecommendations.
Example: Index Analysis and Table List
When you specify a table list, then only that list of tables is considered for index analysis. If the workload does not have any tables matching at least one table in the list, then an error is reported. Otherwise, only the matching list of tables is considered for selecting the indexes.
Assume that MyWorkload describes the workload for tables table_1, table_2 and table_3. The following INITIATE INDEX ANALYSIS request performs successfully and the recommendations are considered only for table_1 and table_2 because table_3 is not specified in the table list.
INITIATE INDEX ANALYSIS ON table_1, table_2 FOR MyWorkload INTO MyQCD AS table_1Index;
Suppose MyWorkload describes the workload for table_1 and table_2 only. The following INITIATE INDEX ANALYSIS request fails because table_3 and table_4 are not defined in the workload.
INITIATE INDEX ANALYSIS ON table_3, table_4 FOR MyWorkload IN MyQCD AS table_1Index; *** Failure 5638 No match found for specified tables in the workload 'MyWorkload'.
Example: Index Analysis and Duplicate Table Specifications
No error is reported if the same table is specified more than once for index selection. In the following example, table_1 is specified twice.
INITIATE INDEX ANALYSIS ON table_1, table_2, table_1 FOR MyWorkload IN MyQCD AS table_1Index;
Example: Index Analysis and Repeated Parameters
If index analysis parameters are specified, they must not be repeated. If the parameters are repeated, an error is reported.
In the following example, the only index analysis parameter specified, IndexesPerTable, is specified once.
INITIATE INDEX ANALYSIS ON table_1 FOR MyWorkload IN MyQCD AS table_1Index SET IndexesPerTable = 2;
In the following example, the index analysis parameter IndexesPerTable is specified twice, so an error is returned.
INITIATE INDEX ANALYSIS ON table_1 FOR MyWorkload IN MyQCD AS table_1Index SET IndexesPerTable=2, ChangeRate=4, IndexesPerTable=4; *** Failure 3706 Syntax error: Multiple "IndexesPerTable" options.
Note that the error is not caused by the discrepancy between the specified boundary conditions for the IndexesPerTable parameter: the same error would be reported if the boundary conditions matched in both specifications.
Example: Using a CHECKPOINT
You should specify the CHECKPOINT option for long operations and for workloads containing many SQL requests that must be analyzed. The checkpoint_trigger value indicates the frequency with which a checkpoint is taken. After every checkpoint_trigger number of SQL requests, a checkpoint is taken and completed IndexRecommendation information is saved in the AnalysisLog table in the QCD.
INITIATE INDEX ANALYSIS ON table_1 FOR MyWorkload IN MyQCD AS table_1Index SET IndexesPerTable = 2 KEEP INDEX CHECKPOINT 10;
Example: Setting a TIME LIMIT on an Index Analysis
This example places a 10 minute time limit on the index analysis:
INITIATE INDEX ANALYSIS FOR myworkload IN myqcd AS myfastidxanalysis TIME LIMIT = 10;
Example: Specifying a CHECKPOINT and a TIME LIMIT
This example takes a checkpoint for every request in the workload and places a 1 minute time limit on the index analysis.
INITIATE INDEX ANALYSIS FOR wl1 IN myqcd AS wl1_analysis1 CHECKPOINT 1 TIME LIMIT = 1;
Example: Include NUSI and Simple Join Indexes Only in the Analysis
This example considers only NUSIs and simple single-table join indexes for the index analysis.
INITIATE INDEX ANALYSIS ON tab1 FOR MyWorkload IN MyQCD AS tab1Index WITH INDEX TYPE 4, 5;
Example: Exclude All Join Indexes From The Analysis
This example excludes all types of join indexes from the index analysis.
INITIATE INDEX ANALYSIS ON tab1 FOR MyWorkload IN MyQCD AS tab1Index WITH NO INDEX TYPE 5, 6;
Example: Include All Valid Index Types in the Analysis
This example considers all valid index types for the index analysis by default.
INITIATE INDEX ANALYSIS ON tab1 FOR MyWorkload IN MyQCD AS tab1Index;