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
Scan Database Objects
The SQL statements in the DDL for a database object can be scanned in the SQL Scanner to classify the SQL statements to help identify which SQL statements are likely to be causing performance problems.
To scan the DDL for a database object from the Database Explorer window
- In the left pane, right-click the database object name and select Scan.
- If the Job Manager window is opened, then the selected database object is added as a job in the window and the scanning starts automatically. If the Job Manager window is not open, the Group Manager window is opened for you to select or create a Job Group first.
Database Explorer Functions
Below is a list of available functions within the Database Explorer window.
|
Button or Menu |
Function |
|---|---|
|
Edit Menu
|
|
|
Edit Menu
|
|
|
Right-click Menu |
|
|
Right-click Menu |
|
|
Right-click Menu |
|
|
Data Tab |
SQL Repository
Quest SQL Optimizer for IBM® DB2® LUWmaximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. SQL Optimizer analyzes, rewrites, and evaluates SQL statements within multiple database objects, files, or SQL statements captured by the DB2 Event Monitor. With SQL Optimizer, you can analyze and optimize all your problem SQL from multiple sources. SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations to simulated index impact analysis, through comparison of multiple SQL access plans.
SQL Optimizer provides you with the following main modules.
SQL Optimizer (including SQL Rewrite and Generate Indexes functions)
About SQL Repository
The SQL Repository stores the SQL statements that are used in the analysis of database performance. These may be SQL statements that you have identified as critical to the performance of your database application.
The SQL Repository is stored in a file directory that can be accessed by your computer, either a directory on your local computer or a network drive. This directory is specified in the Options settings.