Use Extended Dynamic SQL

Traditional SQL interfaces used an embedded SQL approach. SQL statements were placed directly in an application's source code, along with high-level language statements written in C, COBOL, RPG, and other programming languages. The source code then was precompiled, which translated the SQL statements into code that the subsequent compile step could process. This method is referred to as static SQL. One performance advantage to this approach is that SQL statements were optimized at the time the high-level program was compiled, rather than at runtime while the user was waiting.

ODBC, however, is a call level interface (CLI) that uses a different approach. Using a CLI, SQL statements are passed to the database management system (DBMS) within a parameter of a runtime API. Because the text of the SQL statement is never known until runtime, the optimization step must be performed each time an SQL statement is run. This approach commonly is referred to as dynamic SQL.

The use of this feature (which is enabled by default) not only can improve response times, but can improve dramatically server utilization. This is because optimizing SQL queries can be costly, and performing this step only once is always advantageous. This works well with a unique feature of Db2® for i. Unlike other DBMSs, it ensures that statements which are stored in packages are kept up-to-date in terms of optimization, without administrator intervention. Even if a statement was prepared for the first time weeks or months ago, Db2 for i automatically regenerates the access plan when it determines that sufficient database changes require reoptimization.

For more information on packages and the types of SQL statements stored in them, see the SQL packages topic in the IBM i Information Center.