The IBM Data Studio family made its debut in 2007, with the goal of increasing organizational productivity and effectiveness while improving the quality of service, cost of ownership, and governance of diverse data, databases, and data-driven applications.

A key component of that vision is the goal of increasing development productivity while optimizing the performance of Java data access development. In The Easy Way to Quick Data Access, the authors illustrate how the rather disconnected worlds of Java development and database access can be brought together with groundbreaking technology called pureQuery, a high performance Java data access platform delivered in IBM Data Studio Developer and IBM Data Studio pureQuery Runtime.

pureQuery makes it easy to develop Java data access applications that can switch between static and dynamic execution without changing a line of code. IBM is bullish on static SQL for Java; after reading this article, you may be bullish on it as well.

What do we mean when we talk about Static SQL?

If you search for “static SQL” on the Web, you’ll find lots of definitions and discussion, but most of it isn’t very clear. In the context of DB2, when we talk about static or dynamic SQL, we’re really referring to the point in time in which an access plan – the server’s strategy for executing the statement — is determined and the code to execute that plan is generated. There are other differences, such as using parameter markers for dynamic SQL instead of host variables for static SQL, but for the purposes of this article, a s tatic SQL statement is embedded within an application program and is precompiled and bound into a packagebefore the application is executed. This gives the data server the opportunity to lock in the access plan before the program runs. In contrast, dynamic statements are prepared and an access plan selected while the program is running.

One simple way to think of this is that static vs. dynamic SQL is similar to compiled vs. interpretive applications in that the executable code is generated before execution time rather than during execution time. Because there is a CPU cost associated with analyzing an SQL statement and creating an access plan (see in Figure 1), dynamic SQL can hinder application performance,

Figure 1 . Dynamic SQL has more execution overhead.

Let’s step back and take a look at how IBM’s definition of static SQL is different (and differentiated) from what is sometimes used in the industry. Many vendors simply define static SQL as meaning the SQL statement is embedded in the program and doesn’t change. What’s different about their meaning of static SQL is that there is no concept of selecting the access path prior to executing the program. So, in essence, their treatment of static SQL is the same as DB2’s treatment of dynamic SQL.

Many vendors, including IBM, work hard to minimize the overhead of preparing the access path at runtime by using prepared statement caches to make dynamic SQL execute as fast as possible. However, achieving high cache hit ratios typically requires system tuning of cache sizes and also requires that your SQL statement in JDBC is coded correctly. In typical client environments, this optimization doesn’t occur. Therefore, in many cases, static SQL can provide faster performance, more consistent response times, and other benefits as well.

Compare static and dynamic behind the scenes

Figure 2 is a high level look behind the scenes at the back and forth between the application and the data server for both dynamically executed SQL in Java (JDBC, or pureQuery deployed in dynamic mode) vs. statically executed (SQLJ, described below, or pureQuery deployed in static mode) .

Figure 2 . Dynamic vs. static execution.

With dynamic execution, the SQL statement is completely ignored at program preparation time. So when you write a program using dynamic SQL, at runtime the application has to communicate more information with the database server before it executes the statement. Invoking the JDBC Connection.prepareStatement method for example, sends a message for prepare and for describing the output, and then sends another message for describing the input. Note that the network message for a prepare could be quite large since the entire statement string is sent over the network. Although the JDBC driver works hard to optimize the number of network messages, it’s highly possible that you will have multiple messages going back and forth, and that those messages will be larger.

There may also be issues with catalog contention because during the prepare (compilation), all the objects referenced in the statement must be resolved against the same set of system catalogs.

So what does this have to do with Java?

Now let’s examine how static and dynamic SQL are used in Java. With Java, you have historically had a couple of different options for data access:

• JDBC, the dominant Java data access API. The hassle of coding with JDBC has led to the development of frameworks, such as Hibernate or Spring or EJBs, to help ease the coding burden. It doesn’t matter which of these frameworks is used, because at the moment of truth, the program executes as dynamic SQL (that is, it is prepared when the program runs). Sometimes the dynamic SQL generated by such frameworks is less than ideal from a data access-performance perspective.

• SQLJ, a standard API for enabling embedded SQL for Java programs . As one of the authors of this standard, IBM believes that the performance benefits of static SQL should also be extended to Java programs and provides customizations to enable static execution as we have defined it here. (Note that other vendors who support SQLJ do not provide this customization.)