Reasons You Should Know Dynamic SQL

You should understand what dynamic SQL is and what it can do for you for many reasons. As IBM improves the efficiency and functionality of dynamic SQL, more applications will use dynamic SQL. A working knowledge of dynamic SQL is necessary if you want to use DB2 fully and understand all its applications and utility programs. This section should make abundantly clear the fact that dynamic SQL is here to stay.

Dynamic SQL makes optimal use of the distribution statistics accumulated by RUNSTATS . Because the values are available when the optimizer determines the access path , it can arrive at a better solution for accessing the data. Static SQL, on the other hand, cannot use these statistics unless all predicate values are hard-coded or REOPT(VARS) is specified.

Distributed queries executed at the remote site using DB2 DUW private protocol use dynamic SQL. Some current distributed application systems are based on this requirement.

QMF, SPUFI, and many other DB2 add-on tools for table editing and querying use dynamic SQL. Also, many fourth-generation language interfaces to DB2 support only dynamic SQL. Although the users of these tools are not required to know dynamic SQL, understanding its capabilities and drawbacks can help users develop efficient data access requests . Also, the JDBC and ODBC call-level interfaces deploy dynamic SQL, not static.

Using dynamic SQL is the only way to change SQL criteria such as complete predicates, columns in the SELECT list, and table names during the execution of a program. As long as application systems require these capabilities, dynamic SQL will be needed.

Dynamic SQL is optimized at runtime, and static SQL is optimized before execution. As a result, dynamic SQL may perform slower than static SQL. Sometimes, however, the additional overhead of runtime optimization is offset by the capability of dynamic SQL to change access path criteria based on current statistics during a program's execution.

The four classes of dynamic SQL are EXECUTE IMMEDIATE , non- SELECT dynamic SQL, fixed-list SELECT , and varying-list SELECT . The following sections cover each of these classes in depth.