Chat now with support

Get Live Help
Complete Registration

Sign In

Request Pricing

Contact Sales

FREE TRIALS

SQL Optimizer for DB2 LUW 5.0 - User Guide

Table of Contents

SQL Optimizer for IBM® DB2® LUW

UsageStatistics Getting Started

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

About Options SQL Rewrite Settings

SQL Rewrite Settings Intelligence SQL Rewrite (1) SQL Rewrite (2) SQL Options Quota General

Editor Settings Activity Log Settings Plan Settings SQL Scanner Settings

SQL Scanner Settings Options General

SQL Classification Settings

SQL Classification Settings SQL Statement with no Access Plan

Directory Setup Linkage Settings General Settings

General Settings General Message Result Set

Generate Indexes Settings

Generate Indexes Settings Intelligence Index Options General

SQL Scanner Concepts

About SQL Scanner Embedded and Dynamic SQL Statements What does the SQL Scanner scan? Scanned SQL Statement Classification Data Directory Setting SQL Scanner Conversions

About SQL Scanner Conversions Trigger Conversion Parameter Marker Conversion Local Variable Conversion Quoted String Conversion COBOL Conversion

About Job Manager Job Manager Window Group Manager Window Open the SQL Scanner Add Scanner Jobs

Add Jobs to the Job Manager Window Add Database Objects 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 Placing Bookmarks in the Job Manager Window Create Benchmark Factory Import File Job Manager Functions

Scanned SQL Viewer

About Scanned SQL Viewer Scanned SQL Viewer Window Understand the Access Plan Open Scanned SQL Viewer View a Particular Type of SQL Statement Job Navigation Generate Report for Scanned SQL Statements Create Benchmark Factory Import File Scanned SQL Viewer Functions

SQL Optimizer Concepts

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

SQL Page SQL Text Pane SQL Information Pane Run Time Pane

Access Plan Page

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

Use the SQL Optimizer

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

Selected SQL SQL Termination Batch Termination Run Time Mode/ Repeat Test Batch Run Schedule

Save Optimized SQL

Save Optimized SQL Open Optimized SQL View Open Optimized SQL Details Refresh Plan Details Window

About SQL Formatter SQL Formatter Window Format a SQL Statement Host Variables within SQL Formatter SQL Formatter Functions

SQL Inspector Overview Data Directory Setting Open SQL Inspector SQL Inspector Window Statistics: Executed SQL Statistics: Executing SQL Add Inspector

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

Database Explorer

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

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 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

Index Usage Analyzer

About Index Usage Analyzer Index Usage Analyzer Window

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

User-Defined Temp Table

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

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

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

Search Functions Find Quick Find Find Next Replace Goto Line Regular Expression

About Activity Log View Activity Log Report Purge Activity Log Data Start Recording Activities Stop Recording Activities

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

Job Navigation

There are several ways to navigate through the Jobs in the Scanned SQL Viewer window:

Double-click the Job to be viewed in the Job Manager window.
Select the Job from the Job list.
Use the navigation buttons on the toolbar or corresponding menu items.

Button	Menu Item	Description
$image\B_FirstJob.gif$	Job \| First Job	Display the first Job.
$image\B_PreviousJob.gif$	Job \| Previous Job	Display the previous Job.
$image\B_NextJob.gif$	Job \| Next Job	Display the next Job.
$image\B_LastJob.gif$	Job \| Last Job	Display the last Job.

Generate Report for Scanned SQL Statements

To generate a report for scanned SQL statements

With the Scanned SQL Viewer window open select Report | Scanned SQL to open the Scanned SQL Report Criteria window.
Select the report criteria.
The information in the report can be saved or printed.

Note: A few minutes may be needed to generate a long report.

Create Benchmark Factory Import File

All the SQL statements can be saved in a text file from the Job Manager, the Scanned SQL Viewer, the SQL Repository, and the SQL Optimizer windows. These SQL statements can then be imported into Benchmark Factory program (version 4.6 or later).

To create the text file to import into Benchmark Factory

Right-click and select Create Benchmark Factory Import File.
Select the specific SQL statements that you want to save. Click OK.
Enter the filename and select the file location. Click Save.

Scanned SQL Viewer Functions

Below is a list of available functions within the Scanned SQL Viewer window.

Button or Menu	Function
Navigate Menu $image\B_FirstSQL.gif$ $image\B_PreviousSQL.gif$ $image\B_NextSQL.gif$ $image\B_LastSQL.gif$	First SQL/ Previous SQL/ Next SQL/ Last SQL
Navigate Menu $image\B_FirstJob.gif$ $image\B_NextJob.gif$ $image\B_NextJob.gif$ $image\B_LastJob.gif$	First Job/ Previous Job/ Next Job/ Last Job
Navigate Menu	Go to SQL
Edit Menu $image\B_SendToSQLOptimizer.gif$	Send to SQL Rewrite
Edit Menu $image\B_CopyToIndexExpert.gif$	Send to Generate Indexes
File Menu	Save SQL to SQL Repository
SQL & Right-click Menu	Checked List
SQL Menu	Find SQL
SQL Menu	Find Next SQL
View Menu	All SQL
View Menu	Simple SQL
View Menu	Complex SQL
View Menu	Problematic SQL
View Menu	Invalid SQL
View Menu	Different Bound and New Access Plan
View Menu	No Bound Access Plan
View Menu	Checked SQL
View Menu	Unchecked
Report Menu	Scanned SQL Report
Right-click Menu	Create Benchmark Factory Import File

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating