| Before deciding to use dynamic SQL, you should consider whether using static SQL or dynamic SQL is the better technique for the application. For most DB2 users, static SQLembedded 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. Dynamic SQL is of four types: 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 con tents 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 another facility. DB2 prepares and executes these statements as dynamic SQL statements. The programs that implement this functionality use embedded 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 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. Programs that use ODBC function calls do need to be precompiled and bound. Dynamic SQL executed through JDBC methods. Java applications that contain JDBC class objects can use their methods to pass dynamic SQL statements as arguments. There is no need to precompile and bind programs that use JDBC methods.
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 or 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 (an SQL cursor definition), OPEN the cursor, FETCH from the cursor, and 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 the cursor, FETCH using DESCRIPTOR, and CLOSE the cursor.
Using dynamic SQL has many other variations. Host variables can also be used, and the literal data can be put into the dynamic SQL statement. When parameter markers are used in the dynamic SQL statement, to be replaced by the host variables later, the bind feature REOPT(ONCE or ALWAYS) can also be used to assist the optimizer in picking the right access path, based on the host variable data, at execution time. The use of parameter markers instead of literals is encouraged to allow the reuse of prepared dynamic SQL statements. DB2 caches dynamic SQL statements and their access paths in a memory area called the dynamic statement cache. Reuse of an access path from the dynamic statement cache can save 90 percent of the resources to prepare the same statement twice. |
