SQL Optimizer for IBM® DB2® LUW
About SQL Optimizer for IBM DB2 LUW
Getting Started
Tutorials
User Logon Screen
Connect to the Database
User Logon Privileges
Special Registers Settings
Synchronize the Data Dictionary
Enter Authorization Key
Select Database Language
Status Bar—Main Window
Toolbar
Options
About Options
SQL Rewrite Settings
Editor Settings
Activity Log Settings
Plan Settings
SQL Scanner Settings
SQL Classification Settings
Directory Setup
Linkage Settings
General Settings
Generate Indexes Settings
SQL Scanner
SQL Scanner Concepts
SQL Optimizer
About SQL Scanner
Embedded and Dynamic SQL Statements
What does the SQL Scanner scan?
Scanned SQL Statement Classification
Data Directory Setting
SQL Scanner Conversions
Job Manager
About Job Manager
Job Manager Window
Group Manager Window
Open the SQL Scanner
Add Scanner Jobs
Scanned SQL Viewer
Add Jobs to the Job Manager Window
Add Database Objects
Add Event Monitors
Add Source Code
Add SQL Inspectors
Summary
Scan
Scan Source Code with Temporary Tables
Abort Scan
Find a Job
View Group Summary
View Group Properties
View Job Properties
Open Group Manager
Check Scanned SQL
Mark and Unmark Jobs
Delete Marked Jobs
Move or Copy Jobs to Another Group
Modify Job Details
Switch Schema
Change Event Monitor Path
Placing Bookmarks in the Job Manager Window
Create Benchmark Factory Import File
Job Manager Functions
SQL Optimizer Concepts
SQL Formatter
About the SQL Rewrite Function
What Function Should l Use to Retrieve the Run Time?
Unsatisfactory Performance Results
SQL Functions
SQL Optimizer Functions
SQL Optimizer Window
SQL Optimizer Window
SQL Page
Access Plan Page
Use the SQL Optimizer
Access Plan Page
Access Plan
Table Statistics Pane
Index Statistics Pane
Column Statistics Pane
Column Values Distribution Statistics Pane
Update Statistics
Plan Statistics Page
Summary Page
Compare Page
Execute Result Page
Enter the Original SQL Statement
Automatically Rewrite the Original SQL Statement
Rewrite Details
Add User-Defined SQL Alternatives
Add Virtual Index Alternatives
About the Generate Indexes Function
Index Expert Database Requirements
Generate Index-Set Alternatives
Index Generation Details
Add Your Own Virtual Index Sets
Analyze the Impact of New Indexes
Generate a Report for Optimized SQL
Verify the Correctness of Rewritten SQL
Test for Scalability
Create Benchmark Factory Import File
Batch Run
Retrieve the Run Time for a Group of Alternatives
Input Parameter Values
Time
Commit or Rollback
How to select the Optimal SQL Statement?
View Batch Run Details
Batch Run Criteria Window
Save Optimized SQL
About SQL Formatter
SQL Formatter Window
Format a SQL Statement
Host Variables within SQL Formatter
SQL Formatter Functions
SQL Inspector
SQL Inspector Overview
Data Directory Setting
Open SQL Inspector
SQL Inspector Window
Statistics: Executed SQL
Statistics: Executing SQL
Add Inspector
Database Explorer
Add Inspector
General Information
Collection Period
Collection Time
SQL Filter
Monitoring Period
Target Time
Target Applications and Users
Inspect
Ad hoc Inspect
Abort Inspect
Scan Inspector
Delete Inspector
Find Inspector
View Inspector Properties
Place Bookmarks in SQL Inspector Window
Create Benchmark Factory Import File
Sum Statistics for a Stored Procedure
SQL Inspector Functions
About Database Explorer
Database Explorer Window
Privileges
Opening Database Explorer
Sort Criteria
Modify Data
Copy Column Names to Another Window
Copy Data from Cells, Rows, or Columns
View LONG, CLOB and BLOB Columns
Scan Database Objects
Database Explorer Functions
SQL Repository
About SQL Repository
SQL Repository Window
Add SQL to SQL Repository
Add SQL Window: General Page
Add SQL Window: SQL Information Page
Refresh SQL Access Plan
Modify SQL
Rename SQL
Delete SQL
Create Benchmark Factory Import File
SQL Repository Functions
Index Impact Analyzer
About Index Impact Analyzer
Index Impact Analyzer Window
Index Usage Analyzer
Index Impact Analyzer Window
Right Pane for Analyzers
Right Pane for Scenarios Analyzed Folder
Right Pane for Scenario
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statements
Create an Index Impact Analyzer
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL Page
New Analysis Wizard: Index Page
Add Index Window
View Index Impact Analysis Details
Add SQL to an Index Impact Analysis
Add Scenario
Modify Index Impact Analyzer
Regenerate
Rename an Index Impact Analyzer
Delete an Index Impact Analysis
Index Impact Analyzer Functions
About Index Usage Analyzer
Index Usage Analyzer Window
User-Defined Temp Table
Index Usage Analyzer Window
Right Pane for Analyzers
Right Pane for Tables Analyzed
Right Pane for Analyzed Table Information
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statements
Create an Index Usage Analysis
Update an Index Usage Analysis
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL Page
View Index Usage Analysis Details
Modify an Index Usage Analysis
Rename an Index Usage Analysis
Delete an Index Unused Analysis
Index Usage Analyzer Functions
About User-Defined Temp Table
Privileges for Creating User-Defined Temp Tables
Create Temporary Tables
View SQL Scripts of Temporary Tables
Drop Temporary Tables
Copy SQL with Temporary Tables to SQL Optimizer
Preference Settings for Handling Temporary Tables
Editor Functions
About Editor Functions
Auto Correction
Brackets Pairing
Comment and Uncomment Text
Customize Editable Panes
Editor Panes Shortcuts
Indent and Outdent Text
Member and Argument Lookup
Syntax Highlight
Uppercase and Lowercase Text
SQL Functions
About SQL Functions
Retrieve Access Plan
Retrieve Run Result
Retrieve Run Time
Test for Scalability
Convert Parameter Markers
SQL Information and Functions
SQL Information Pane
Send SQL to SQL Optimizer
Send SQL to Index Expert
Open SQL from SQL Repository
Save SQL to SQL Repository
Create Benchmark Factory Import File
SQL Navigation
Find a SQL Using a Text String
Comments
Variables
Parameters Window
Filter Database Objects
Access Plan
Activity Log
Access Plan
Review Access Plans
Access Plan Options
Access Plan Functions
Plan Detail
Plan Help
Animate Access Plans
Copy Access Plans
DB2 Optimized Text
Search Functions
SQL Scanner Tutorial
SQL Optimizer Tutorial
SQL Formatter Tutorial
SQL Inspector Tutorial
User-Defined Temp Tables Tutorial
SQL Repository Tutorial
Save SQL to the SQL Repository from Other Modules Tutorial
Generate Virtual Indexes Tutorial
Index Impact Analyzer Tutorial
Index Usage Analyzer Tutorial
Database Explorer Tutorial
About us
Copyright
New Analysis Wizard: Select SQL Page
The Select SQL tab is used to select the SQL statements whose access plans you want to analyze to see which Indexes are used and which are not used by these SQL statements.
Select SQL from
The SQL statements used in an analysis can be taken from the SQL Repository or the SQL Scanner.
SQL Tree
Select the SQL statements for the analysis.
View Index Usage Analysis Details
The Index Usage Analysis Details window displays information about the retrieve of the access plan for each SQL statement.
The Index Usage Analysis Details window appears after the batch run process is completed unless the Show details on next batch run checkbox in the Index Usage Analysis Details window is unchecked.
To view the Index Usage Analysis Details window
After an Index Usage Analysis is complete, select View | Last Index Usage Analysis Details when the Index User Analyzer window is active.
Review this information to see if an error occurred during the retrieval of the access plan. Errors may occur if the selected SQL statements are from one schema and you have set another schema in the Schema list at the bottom left of the main window.
Modify an Index Usage Analysis
To modify the analysis name or description
-
From the Index Usage Analyzer window, select the Analyzer you want to modify from the left pane.
-
Right-click and select Modify to open the Modify Analyzer window.
-
Modify the name or description.
Rename an Index Usage Analysis
You can rename an analysis in the Index Usage Analyzer window in two ways.
To rename an Analyzer
-
Select the Analysis you want to modify from the left pane.
-
Right-click and select Rename. Change the name and press Enter.
or
Right-click and select Modify and change the name.