Scanned SQL Viewer
Quest SQL Optimizer for IBM® DB2® LUW maximizes 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 Scanned SQL Viewer
The Scanned SQL Viewer window enables the viewing and analysis of the SQL statement scanned. It displays the formatted scanned SQL statement, access plan, temp table (if applicable), and information about the SQL statement.
-
Formatted scanned SQL statement
-
Access plan
-
SQL Information
-
SQL statement type classification: Problematic, Complex, Simple, or Invalid.
-
Database error message if SQL is classified as Invalid,
-
Information about any SQL conversion the SQL Scanner applied to the SQL statement in order for it to generate a access plan
-
Line and column where the SQL statement was found in the source (for database object and source code files only)
-
SQL statement used to create and populate any temporary tables the SQL Scanner created
-
Checked SQL Information (if the SQL has been marked as checked)
-
Date time of when the SQL statement was checked
-
Name of who checked the SQL
-
Status
-
Checked description are displayed
Scanned SQL Viewer Window
View the Scanned SQL Viewer
The Scanned SQL Viewer window is divided into these sections:
Job list
The Job list at the top of the Scanned SQL Viewer window displays the Job description of the selected Job and contains the list of Jobs that satisfy the view criteria.
Scanned SQL (Top Pane)
The top pane of the Scanned SQL Viewer window displays the scanned SQL statement for a particular scan Job. If the SQL statement is valid, the scanned SQL statement will be laid out according to the indentation algorithm. Otherwise, the scanned SQL statement will be unformatted.
If more than one SQL statement is found in a Job, multiple tabs for selecting the SQL are displayed at the bottom right corner of the window.
SQL Information (Bottom Pane)
The SQL Information pane has buttons at the top of it to select whether to display the access plan graph, access plan tree, DB2 Optimized Text, Information (the classification of the SQL, the location in the file or database object and login information), the scanner temp table DDL, and the checked SQL details. If the button is enabled, there is information on that page. If the button icon is grayed, then no information is available for that SQL statement.
Understand the Access Plan
If the scanned SQL statement is valid, the access plan for the SQL statement will be retrieved. The access plan is a combination of steps the DB2 LUW optimizer selects to execute the SQL statement. It is a sequential set of steps required to carry out the query, complete with the access methods chosen for each table.
Examination of the access plan shows how the database executes the SQL statement and aids in the analysis of whether the SQL statement is the most efficient.
The access plan help provides online help for each operation within the access plan to help you understand each step of the plan.
To view the help for an operation
Right-click the access plan step for which you want help and select Get help on operation_name.