Including dynamic SQL in your program
Dynamic SQL is prepared and executed while the program is running.
Before you begin
Before you use dynamic SQL, consider whether static SQL or dynamic SQL is the best technique for your application, and consider the type of dynamic SQL that you want to use. Also consider the performance implications of using dynamic SQL in application programs. For information about methods that you can use to improve the performance of dynamic SQL statements, see Improving dynamic SQL performance.
About this task
Dynamic SQL prepares and executes the SQL statements within a program, while the program is running.
- Interactive SQL
- A user enters SQL statements through SPUFI, the command line processor, or an interactive tool, such as QMF™ for Workstation.DB2 prepares and executes those statements as dynamic SQL statements.
- Embedded dynamic SQL
- Your application puts the SQL source in host variables and includes PREPARE and EXECUTE statements that tell DB2 to prepare and run the contents of those host variables at run time. You must precompile and bind programs that include embedded dynamic SQL.
- Deferred embedded SQL
- Deferred embedded SQL statements are neither fully static nor fully dynamic. Like static statements, deferred embedded SQL statements are embedded within applications; however, like dynamic statements, they are prepared at run time. DB2 processes the deferred embedded SQL statements with bind-time rules. For example, DB2 uses the authorization ID and qualifier (that are determined at bind time) as the plan or package owner.
- Dynamic SQL executed through ODBC or JDBC functions
- Your application contains ODBC function calls that pass dynamic SQL statements as arguments. You do not need to precompile and bind programs that use ODBC function calls.
For most DB2 users, static SQL, which is embedded in a host language program and bound before the program runs, provides a straightforward, efficient path to DB2 data. You can use static SQL when you know before run time what SQL statements your application needs to execute.
