Dynamic SQL Versus Static SQL

In the context of application development, queries can be classified as either dynamic SQL or static SQL. In other words, applications are said to use dynamic SQL, static SQL, or both. Both dynamic and static SQL must go through the PREPARE and EXECUTE phases described in the previous section.

In order to use a static SQL statement, the complete statement must be specified. The names of the tables and columns as well as the data types being reference are known. The only information that can be specified at runtime are values in the WHERE clause of the SQL statement using host variables. Once an access plan for a static SQL is generated, it is stored in the database. The plan is persistent and reusable as long as the same static SQL statement is issued.

Dynamic SQL statements are dynamically processed at run time. Unlike static SQL, the statement structure is not required when the application is precompiled. Dynamic SQL statements are always prepared before execution, whether or not the same statement (and hence the same access plan) is used over and over again. To minimize the preparation cost, DB2 keeps frequently used access plans in the package cache (also known as query cache). The package cache significantly reduces the cost of repeated SQL requests, but there is still the overhead of issuing and responding to prepare requests.

Writing statements using static SQL, as you can imagine, can yield performance benefits at execution time because DB2 no longer has to "think" about how to resolve the statement.

Dynamic SQL, however, offers flexibility of building an SQL statement at design time that is not possible with static SQL.

Figure 7.1. Visual comparison of the difference between dynamic and static SQL.

Up to this point in the book, the examples presented have all used static SQL. Programming interfaces such as JDBC, CLI, and ODBC, however, generally use dynamic SQL.

Tip

Dynamic SQL does not necessarily mean that you must take a performance hit relative to static SQL. For applications that use a set of queries very frequently, their access plans are kept and reused in the DB2's package cache (known as EDM statement cache in zSeries and plan cache in iSeries). The first execution of any given query, however, must incur the full cost of the PREPARE phase.

Static SQL is fast and easy to program in SQL procedures, but there are circumstances where static SQL cannot be used. The most common need for dynamic SQL is when the final form of a query is not known at application design time. In such cases, static SQL is not an option.