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
- The processing of dynamic SQL can have substantially higher overhead than the processing of 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 the DB2® for i database to provide good performance for dynamic SQL statements. If performance for your dynamic application is critical, consider using the extended dynamic capability through the Process Extended Dynamic SQL (QSQPRCED) API. This feature allows the application to maintain a persistent cache of SQL statements and substantially reduces runtime overhead when the application runs.
- 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 the use of pointer variables. RPG/400® programs require the aid of PL/I, COBOL, C, or ILE RPG programs to manage the pointer variables.