During scanning, if the scanned SQL statement is used to create or modify a temp table these SQL statements are automatically executed if the Create Scanner Temp Table option in the Preferences is selected. The created Scanner Temp Tables are dropped after the Job finishes scanning.
The DDL for creating the temporary tables displays under Scanner Temp Table in the bottom pane. This includes the DDL found by the SQL Scanner or the DDL used to create the User-Defined Temp Table.
The SQL Text pane and the SQL Information pane in SQL Scanner window enables the viewing and analysis of the SQL statements scanned. The following information is provided:
Formatted scanned SQL statement
- 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 query 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
- Checked description are displayed
Note: When the SQL Scanner finds a SQL statement that contains a variable, it will assign the BINARY data type to the variable as it retrieves the execution plan.
To assign a different data type to the variable
Copy the SQL statement to the SQL Editor pane in the SQL Optimizer window.
Click Show Plan .
Select the data type from the Parameters window which automatically displays when you have a variable in a SQL statement.
In the SQL Scanner window, you can find a contain that contains a specific text string,
To search for a text string in all Jobs
Select Job | Find Job.
Enter the text to be located.
All Jobs and SQL number that satisfy the search criteria are listed.
The Find SQL function is available in the SQL Scanner window. This enables the location of SQL statements that contain a specified text string within a particular Job taking the View criteria into account.
To find SQL with specific text
Select SQL | Find SQL to open the Find SQL window.
Enter the text string to be found.
To continue searching for the same text