SQL Optimizer for IBM® DB2® LUW
UsageStatistics
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
SQL Formatter Window
View the 
The SQL Formatter window is divided into two sections:
Original SQL (Left pane)
Provides an area for entering the SQL statement to be formatted.
Formatted SQL (Right pane)
Displays the formatted SQL statement. Host variables, SQL options and comments are color-coded for easy recognition.
Format a SQL Statement
To format a SQL statement
-
Click
.
-
If the SQL Optimizer window is opened, the selected SQL statement will automatically be copied to the SQL Formatter window. Otherwise, enter a SQL statement onto the left pane] of the SQL Formatter window.
-
Click
.
The SQL Formatter will check the syntax of the SQL statement. Only if the syntax is correct will the SQL statement be formatted. The formatted SQL statement. is displayed in the right pane of the SQL Formatter window.
To terminate the format process
- Click
.
Host Variables within SQL Formatter
Declarations of variables within the SQL statement are allowed, prefixed with a ":" sign or without. If variables are recognized, they will be highlighted in red after executing the Format function.
Note: If table names are in red it indicates that the table and corresponding columns are not recognized. Check these things:
-
Is there sufficient privileges under the login user to access the table
-
Are you connected to the correct database and schema
-
Execute the Synchronize Data Dictionary function to make sure you have the most up-to-date database information in memory.
SQL Formatter Functions
Below is a list of available functions within the SQL Formatter window.
|
Button or Menu |
Function |
|---|---|
|
SQL Menu
|
|
|
Edit Menu
|
|
|
Edit Menu
|
|
|
File Menu
|
|
|
File Menu |
