SQL Optimizer for DB2 LUW 4.4 - User Guide

About SQL Scanner Conversions

In order to render the SQL statement as a valid standalone SQL from other program syntax, one or more conversions may need to be applied to the SQL statement.

Conversions includes:

Trigger Conversion

Parameter Markers Conversion

Local Variable Conversion

Quoted String Conversion

COBOL Conversion

If the above conversion had been applied to the SQL statement, the Information pane shows what conversions had been applied and what changes had been made to the SQL text.

Note: If conversion had been applied, it may be necessary to reverse the changes after optimization when it is pasted back to the original source code.

Trigger Conversion

There are two temporary tables which identifies the set of affected rows prior to the triggering operation ( OLD_TABLE ) and which identifies the set of affected rows after the triggering operation ( NEW_TABLE ) defined the triggers REFERENCING clause. These temporary tables cannot be referenced outside the trigger body; therefore to simulate the SQL statement execution, creation of two temp tables are used to simulate the OLD_TABLE and NEW_TABLE temporary tables.

Trigger

CREATE TRIGGER TRG_DEL_TEST1

 AFTER DELETE on department

REFERENCING OLD_TABLE AS dept_old

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO dept_deleted (cola, dpt_id, dpt_name)

    SELECT 1,

            dpt_id,

            dpt_name

      FROM dept_old;

END

Scanned SQL

INSERT INTO dept_deleted (cola, dpt_id, dpt_name)

SELECT 1,

       dpt_id,

       dpt_name

  FROM dept_old;

After conversion

DECLARE GLOBAL TEMPORARY TABLE dept_old

LIKE department

NOT LOGGED

 

INSERT INTO dept_deleted (cola, dpt_id, dpt_name)

SELECT 1,

       dpt_id,

       dpt_name

  FROM dept_old;

Related Topics

SQL Scanner Conversions Overview

Parameter Marker Conversion

For some source code, the ? is used to define parameter markers, therefore to enable unique referencing we have changed the name so it is unique within the SQL statement.

Original SQL statement

SELECT EMP_ID

  FROM EMPLOYEE

 WHERE EMP_ID = ?

   AND EMP_NAME = ?

After conversion

SELECT EMP_ID

  FROM EMPLOYEE

 WHERE EMP_ID = ?1

   AND EMP_NAME = ?2

Related Topics

SQL Scanner Conversions Overview

Local Variable Conversion

The local variable conversion finds SQL statements that are found in the application source code on one command line and also contain at least one "local variable" which will be replaced by the application before the SQL statement is sent to the server. The SQL Scanner precedes the variable name with "&" and removes the concatenate character and the quotes surrounding the SQL text.  

Original SQL statement

"SELECT " + VEMPID + " FROM EMPLOYEE WHERE EMP_ID > 100"

After conversion

SELECT &VEMPID

  FROM EMPLOYEE

 WHERE EMP_ID > 100

Note: Each local variable in a scanned SQL statement may be replaced with a value or leave the variable in the SQL statement. The SQL Scanner uses "&" to differentiate the local variables from the host variables. The &variablename is not valid syntax for a SQL statement but the program will recognize this format and prompt you to input a value for the variable when needed to execute the SQL statement.

Related Topics

SQL Scanner Conversions Overview

Quoted String Conversion