Eliminate redundant validation with SQL PREPARE statements

The following processing occurs for the statement that is being prepared:

• The syntax is checked.
• The statement is validated to ensure that the usage of objects is valid.
• An access plan is built.

Again when the statement is executed or opened, the database manager revalidates that the access plan is still valid. Much of this open processing validation is redundant with the validation which occurred during the PREPARE processing. The DLYPRP(*YES) parameter specifies whether PREPARE statements in this program completely validates the dynamic statement. The validation is completed when the dynamic statement is opened or executed. This parameter can provide a significant performance enhancement for programs which use the PREPARE SQL statement because it eliminates redundant validation. Programs that specify this precompile option must check the SQLCODE and SQLSTATE after running the OPEN or EXECUTE statement to ensure that the statement is valid. DLYPRP(*YES) does not provide any performance improvement if the INTO clause is used on the PREPARE statement, or if a DESCRIBE statement uses the dynamic statement before an OPEN is issued for the statement.