SQL DDL statements that affect database objects that static SQL statements reference

SQL Data Definition Language (DDL) statements can cause problems for Data Manipulation Language (DML) statements that are run statically if the DDL statements create, drop, or alter DBMS objects that the DML statements reference. The problems can occur even if the DDL statements are run only once after the DDL and DML statements have been bound.

When the DDL statements are run, they invalidate the access plans of any static SQL DML statements that reference the objects that the DDL statements create, alter, or drop. The DML statements can still run even though their access plans are invalidated. Running the DML statements does not generate any warning or error messages.

Two consequences are possible when access plans are invalidated:

In both cases, your non-pureQuery API application loses two of the primary benefits of static SQL: unchanging access paths and possible performance gains.

IBM® recommends that applications that you use with client optimization not run DDL statements.

If your application uses DDL statements that are bound and the value of executionMode is STATIC, pureQuery runs the DDL statements.

Also, if your application uses DDL statements that are not bound, the value of executionMode is STATIC, and the value of allowDynamicSQL is TRUE, pureQuery runs the DDL statements. However, if your application uses DDL statements that are not bound, the value of executionMode is STATIC, and the value of allowDynamicSQL is FALSE, pureQuery runs the DDL statements and throws a "Static statement not found" exception.


Feedback