The SQL Scanner finds the SELECT, SELECT INTO, INSERT, UPDATE, and DELETE SQL statements from database objects, application source code, and text or binary files. It extracts the SQL statements by analyzing the source or binary code without executing the application. This scanning process can locate problematic SQL that a system monitor frequently does not find because some problematic SQL statements execute in too short a period of time or select too small a data volume for the monitor to pick it up.
The SQL Scanner can locate embedded SQL statements and what it is called in the product single command line dynamic (SCLD) SQL statements. You may have embedded, SCLD, and dynamically constructed SQL in the same source code.
Embedded SQL
Embedded SQL statements are SQL statements that are placed within the source programs as a complete or syntactically correct statement. For example:
SELECT emp_id, emp_salary FROM employee
Single Command Line Dynamic SQL
Another type of SQL is called ?ingle command line dynamic?(SCLD) SQL.? It is a SQL statement that is on one command line within the application source code. It consists of one or more quoted strings and variables that are replaced by the application before the SQL statement is sent to the database server. The SQL Scanner finds each quoted string and variable on a single command line and builds the complete SQL statement. For example:
"SELECT emp_id, " + variableA + " FROM employee"
will be transformed to:
SELECT emp_id, &[variableA] FROM employee
Since &[Variable] is not a valid syntax format for a variable, before you can optimize or execute the transformed SQL statement you must manually substitute a value for the variable.
Note: Programming Languages such as Java, Visual Basic, HTML, C++ use this format.
Dynamic SQL
A dynamic SQL statement is one that is constructed by the application during run-time from several command lines. ?The individual segments of the SQL statements are combined when the program is run to create the entire SQL statement.? The SQL Scanner will find only the beginning part of a dynamic SQL statement in the source code. You can capture these dynamic SQL statements as they are being executed with the following:
In the products for Sybase:
Use the SQL Monitor or SQL Inspector to capture the dynamic SQL statements as they are executed.
Use the SQL Scanner to extract the SQL statements from the file and automatically classify the dynamic SQL to identify which SQL statements may be causing performance problems.
