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
Monitoring Period
When you collect SQL currently executing my monitoring an application our a user during a specific period, the Monitoring Period page of the Add Inspector wizard displays:
| Item | Description | |
| Start Date | Specify the start date of the monitoring period. | |
| End Date | Specify the end date of the monitoring period. | |
| Interval (minutes) |
Specify how frequently the Inspector monitors SQL in milliseconds. | |
Target Time
When you collect SQL currently executing by an application or a user at a specific moment, the Target Time page of the Add Inspector wizard displays:
| Item | Description | |
|
Capture immediately the currently executing SQL when the Inspect button is clicked |
Click Inspect button to start capture immediately. | |
|
Capture SQL executing at: |
Date |
Specify the date you want to capture executing SQL. |
|
Time (HH:MM): |
Specify the time you want to capture executing SQL in hours and minutes. | |
|
Wait for some time and then capture what SQL are executing: |
Wait time (HH:MM): |
Specify the time you want to wait before you capture executing SQL in hours and minutes. |
Target Applications and Users
When you collect SQL currently executing, the Target Applications and Users page of the Add Inspector wizard displays:
|
Client Applications |
Description |
|
All Applications |
Monitor all applications currently running. |
|
Selected Application |
Monitor a specific application. |
|
Selected Agent ID |
Displays Agent ID for the selected application. |
| Users | Description |
|
All Users |
All available users. |
|
Selected User |
Use the arrows to select from Available Users. |
Inspect
After adding or modifying an Inspector in the SQL Inspector window, SQL statements and statistics from the database are retrieved by executing the Inspect function. Only one Inspector can be started at a time.
To start the inspecting process
-
Select an Inspector job.
-
Click
.
If the start time of the Inspector has not been reached, the SQL Inspector waits until it is time to begin. During and at the end of the inspecting, information is updated on the SQL Inspector window. Inspect terminates automatically once the end time is reached, except for ad-hoc inspecting. The ad-hoc inspecting process has no ending time so it must be terminated manually.
To abort the inspecting
Click 
Note: If you have already executed the Inspect function for an Inspector, re-executing the Inspect function will erase all existing information.