Use the SQL Scanner to analyze SQL statements embedded within database objects, DB2 Event Monitor files/tables, text/binary files and application source code. The SQL Scanner extracts each SQL statement embedded within the scanned objects or files, retrieves their respective access plans from DB2 LUW, and performs an analysis that determines which of these SQL statements may be performance bottlenecks. You can examine the extracted SQL statements with their access plans and then, copy the SQL statements identified as problematic (top priority) or complex (second priority) into the SQL Optimizer or the Index Expert.
When you scan the database objects or the application files, you first create a Group to store the items you want to scan. If this is the first time you have used the SQL Scanner, the Create Group window appears. Otherwise, click Create in the Group Manager window.
Enter a new Group name, e.g. "Test." Click OK to close the Create Group window.
Check that your new Group name is highlighted in the list box. Click Open.
The selected group is opened in the Job Manager window. For a new Group, the Add Jobs wizard automatically opened so you can select what files or database objects you want to scan.
Note: If you are using an existing Group, click .
In the Add Jobs wizard, click the Next button until you are at the page for the item that you want to scan.
Database Objects page
Expand the database user branch on the left side of the window.
Highlight the schema, a database object type, or an individual database object, and click to move the item to the right pane. (Whether or not you can scan all of the selected database objects depends on your database privileges.)
DB2 Event Monitor
From the left pane, select the Event Monitor.
Click to move the Event Monitor to the right pane.
Set the schema in the Specify Schema list to correspond with the SQL that you are scanning.
Source Code page
Click the Text or binary files or COBOL programming source code option.
Click and select the files you want to scan.
Click Open to insert the files in the Add Jobs wizard.
Set the schema in the Schema list to correspond with the SQL that you are scanning.
Review the jobs you selected.
Click Scan .
Review the details that are filled in the Job Manager grid as the scanning process completes each job. It will show you how many SQL statements found in the Job and how each SQL statement is classified.
To view the scanned SQL statements, highlight the item by clicking the row and click .
The name of the source file or database object appears at the top of the window in the Job list.
The first SQL statement found is shown in the left pane. Click the tabs, e.g. SQL1, SQL2, SQL3, etc., at the bottom left of the window to view the other SQL statements.
Notice the buttons on the top of the right pane. These buttons display in the right pane the access plan, the SQL classification and connection information, the DDL for temporary tables used by the SQL, and details about SQL statements that you have reviewed.
You can narrow the number of SQL statements to view only the problematic and/or complex statements with View | Problematic SQL and/or View | Complex SQL.
Select one SQL statement you want to analyze for performance improvement. Click to copy the SQL statement to the SQL Optimizer window and start the optimization process. Alternatively, you can also have index option generated for the SQL. Click to copy the SQL statement to the Index Expert window and generate index options.