 | Team-Fly  | |
 | DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
| | Table of Contents | | | | Chapter 5. Using SQL in an Application Program |
Using Dynamic SQL Before you decide to use dynamic SQL, some consideration should be taken for whether using static SQL or dynamic SQL is the best technique for the application. For most DB2 users, static SQL embedded in a host language program and bound before the program runsprovides a straightforward, efficient path to DB2 data. Static SQL should be used when the SQL statements the application needs to execute are known before runtime. Dynamic SQL prepares and executes the SQL statements within a program while the program is running. There are four types of dynamic SQL: -
Embedded dynamic SQL: The 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 runtime. The programs that include embedded dynamic SQL must go through the precompile and bind steps. -
Interactive SQL: A user enters SQL statements through SPUFI, QMF, or other facility. DB2 prepares and executes these statements as dynamic SQL statements. -
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, but like dynamic statements, they are prepared at runtime. DB2 processes deferred embedded SQL statements with bind-time rules. For example, DB2 uses the authorization ID and qualifiers determined at bind time as the plan or package owner. -
Dynamic SQL executed through ODBC functions: The application contains ODBC function calls that pass dynamic SQL statements as arguments. There is no need to precompile and bind programs that use ODBC function calls. Coding Dynamic SQL in Applications Applications using dynamic SQL use three types of SQL statements: -
Dynamic SQL for non-SELECT statements : Those statements include DELETE, INSERT, and UPDATE. For execution, use either of the following SQL statements: -
- EXECUTE IMMEDIATE -
- PREPARE and then EXECUTE -
Dynamic SQL for fixed-list SELECT statements : A SELECT statement is fixed-list if you know in advance the number and type of data items in each row of the result. For execution, use the following SQL statements: -
- PREPARE (a SQL cursor definition) -
- OPEN the cursor -
- FETCH from the cursor -
- CLOSE the cursor -
Dynamic SQL for varying-list SELECT statements : A SELECT statement is varying-list if you cannot know in advance how many data items to allow for or what their data types are. -
- DECLARE CURSOR for the statement -
- PREPARE the statement -
- DESCRIBE the statement -
- OPEN cursor -
- FETCH using DESCRIPTOR -
- CLOSE cursor There are many other variations to using dynamic SQL. Host variables can also be used, and the literal data can be put into the dynamic SQL statement. When using parameter markers in the dynamic SQL statement, to be replaced by the host variables later, the bind feature REOPT(VARS) can also be used to assist the optimizer in picking the right access path, based on the host variable data, at execution time. |
