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:
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.
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.
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
INSERT INTO dept_deleted (cola, dpt_id, dpt_name)
SELECT 1,
dpt_id,
dpt_name
FROM dept_old;
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;
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.
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = ?
AND EMP_NAME = ?
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = ?1
AND EMP_NAME = ?2
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.
"SELECT " + VEMPID + " FROM EMPLOYEE WHERE EMP_ID > 100"
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.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
