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.
Sending a SQL statement to the Index Expert for index generation.
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.