Dynamic SQL applications

 

Dynamic SQL allows an application to define and run SQL statements at program run time. An application that uses dynamic SQL either accepts an SQL statement as input or builds an SQL statement in the form of a character string. The application does not need to know the type of the SQL statement.

The application:

• Builds or accepts as input an SQL statement

• Prepares the SQL statement for running

• Runs the statement

• Handles SQL return codes

Interactive SQL is an example of a dynamic SQL program. SQL statements are processed and run dynamically by interactive SQL. Notes:

1. The processing of dynamic SQL can have substantially higher overhead than that for static SQL because the statement might need to be fully processed at run time. In the worst case, the statement must be fully prepared, bound, and optimized by the database before it is run. In many other cases, if the statement has been run before, parts of the processing can be skipped because of the algorithms used and caches maintained by the database. These features allow DB2^® UDB for iSeries™ to provide good performance for dynamic SQL statements. If performance for your dynamic application is critical, consider using the extended dynamic capability through the QSQPRCED API. This feature allows the application to maintain a persistent cache of SQL statements and substantially reduces runtime overhead when the application runs.

2. Programs that contain an EXECUTE or EXECUTE IMMEDIATE statement and that use a FOR READ ONLY clause to make a cursor read-only experience better performance because blocking is used to retrieve rows for the cursor.

   The ALWBLK(*ALLREAD) CRTSQLxxx option will imply a FOR READ ONLY declaration for all cursors that do not explicitly code FOR UPDATE OF or have positioned deletes or updates that refer to the cursor. Cursors with an implied FOR READ ONLY will benefit from the second item in this list.

Some dynamic SQL statements require use of address variables. RPG/400^® programs require the aid of PL/I, COBOL, C, or ILE RPG programs to manage the address variables.

• Designing and running a dynamic SQL application
  To issue a dynamic SQL statement, use the statement with either an EXECUTE statement or an EXECUTE IMMEDIATE statement, because dynamic SQL statements are prepared at run time, not at precompile time.

• CCSID of dynamic SQL statements
  An SQL statement is normally a host variable. The coded character set identifier (CCSID) of the host variable is used as the CCSID of the statement text.

• Processing non-SELECT statements
  Before building a dynamic SQL non-SELECT statement, verify that this SQL statement is allowed to be run dynamically.

• Processing SELECT statements and using a descriptor
  The basic types of SELECT statements are fixed list and varying list.

 

Parent topic:

Using SQL in different environments

 

Related concepts

Using interactive SQL

 

Related reference

Unit of work and open cursors
Processing non-SELECT statements
Actions allowed on SQL statements
Process Extended Dynamic SQL (QSQPRCED) API