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)
The SQL Scanner is used to detect potentially problematic SQL statements in source code and database objects without execution.
The SQL Scanner extracts SQL statements embedded in database objects, captured from the DB2 Event Monitor, or stored in application source code and binary files. It retrieves and analyzes the execution plans for the extracted SQL statements. It then categorizes the SQL statements according to the complexity of the execution plan and determines whether it has the characteristics that typically cause performance problems. The SQL Scanner allows you to quickly review SQL statements in existing code and detect potential problems. With this approach, you can be proactive in the detection of performance problems and identify the SQL statements that need to be optimized without physical execution of the applications or manual intervention.
Once the problematic SQL statements have been identified, you can determine the best solution by
Sending a SQL statement to the SQL Optimizer for optimization.
Saving a group of SQL statements to the SQL Repository for further analysis.
Each item that is scanned is referred to as a "job" and can be a database object, text file, binary file, or DB2 Event Monitor file/table.
The SQL Scanner has the following functions:
Provides a work area for adding and deleting scan Jobs. It also displays information on the Job name, location, number of SQL statements scanned, SQL classification, start date and time, and the processing time.
Scans the selected programming source code, event monitor files, and database objects to extract SQL statements. Once identified, the scanned SQL statements are ranked based on a set of user-defined SQL classification criteria.
Displays the SQL statements that were found. The viewer shows the scanned SQL statement, corresponding access plan, and the associated information to help you identify the problem SQL. You can send these SQL statements directly to the SQL Optimizer for optimization. or the Index Expert to generate index candidates.
The SQL Scanner can extract a SQL statement that is an embedded SQL statement or is a dynamic SQL statement on a single command line. A dynamic SQL statement which is constructed from several command lines is not created until the user and program make decisions on what to include at run time. The SQL Scanner cannot extract this type of SQL statement.
Embedded SQL statements refer to SQL statements that are placed within the source programs and are constructed at compilation time. Embedded SQL statements are complete statements found in the source code in one continuous string.
Database Objects and files may contain a SQL statement that is on one command line, is placed within quotes, and may be concatenated with other parts of the statement and/or local program variables.
For example:
SQL1:= "SELECT emp_id , " + variableA + " FROM employee";
Dynamic SQL statements may also be constructed from parts of the SQL statement which are on several command lines. The user or program must decide at run time how the entire SQL statement will be constructed.
Note: If your SQL statements are dynamic SQL statement generated by the application at run time from several command lines, you can use the DB2 Event Monitor to capture the SQL statements. Then, the SQL Scanner can be used to analyze the captured SQL statements.
The SQL Scanner extracts SQL statements from the following items:
|
Item |
Description |
|---|---|
|
Database Objects |
Database objects. The SQL Scanner only scans the database objects that contain SQL statements: materialized query tables, views, procedures, user-defined functions, triggers, packages, and plan tables. |
|
Files or tables created by the DB2 Event Monitor. An Event Monitor collects data when certain events occur on the database such as the completion of the execution of a SQL statement. | |
|
Application source code files and binary files. | |
|
COBOL source code files. |
© ALL RIGHTS RESERVED. 利用規約 プライバシー Cookie Preference Center
