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
Notes:
  • 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.