Dynamic SQL

Dynamic SQL is embedded in an application program and can change during the program's execution. Dynamic SQL statements are coded explicitly in host-language variables , prepared by the application program, and then executed. QMF and SPUFI are two examples of programs that execute dynamic SQL statements.

Recall that the two types of SQL are static SQL and dynamic SQL. The primary difference between static and dynamic SQL is described capably by their names . A static SQL statement is hard-coded and unchanging. The columns , tables, and predicates are known beforehand and cannot be changed. Only host variables that provide values for the predicates can be changed.

A dynamic SQL statement, conversely, can change throughout a program's execution. The algorithms in the program can alter the SQL before issuing it. Based on the class of dynamic SQL being used, the columns, tables, and complete predicates can be changed "on the fly."

As might be expected, dynamic SQL is dramatically different from static SQL in the way you code it in the application program. Additionally, when dynamic SQL is bound, the application plan or package that is created does not contain the same information as a plan or package for a static SQL program.

The access path for dynamic SQL statements cannot be determined before execution. When you think about it, this statement makes sense. If the SQL is not completely known until the program executes, how can it be verified and optimized beforehand? For this reason, dynamic SQL statements are not bound, but are prepared at execution. The PREPARE statement is functionally equivalent to a dynamic BIND . The program issues a PREPARE statement before executing dynamic SQL (with the exception of EXECUTEIMMEDIATE , which implicitly prepares SQL statements). PREPARE verifies, validates , and determines access paths dynamically.

A program containing dynamic SQL statements still must be bound into an application plan or package. The plan or package, however, does not contain access paths for the dynamic SQL statements.

DB2 provides four classes of dynamic SQL: EXECUTE IMMEDIATE , non- SELECT PREPARE and EXECUTE , fixed-list SELECT , and varying-list SELECT . The first two classes do not allow SELECT statements, whereas the last two are geared for SELECT statements.

Dynamic SQL is a complex topic that can be difficult to comprehend and master. It is important that you understand all aspects of dynamic SQL before deciding whether to use it. Dynamic SQL is covered in depth in Chapter 12.