Effects of precompile options on database performance
Several precompile options are available for creating SQL programs with improved performance. They are only options because using them could impact the function of the application. For this reason, the default value for these parameters is the value that ensures successful migration of applications from prior releases. However, you can improve performance by specifying other options.
The following table shows these precompile options and their performance impacts.
Some of these options might be suitable for most of your applications. Use the command CRTDUPOBJ to create a copy of the SQL CRTSQLxxx command. and the CHGCMDDFT command to customize the optimal values for the precompile parameters. The DSPPGM, DSPSRVPGM, DSPMOD, or PRTSQLINF commands can be used to show the precompile options that are used for an existing program object.
| Precompile Option | Optimal Value | Improvements | Considerations |
|---|---|---|---|
| ALWCPYDTA | *OPTIMIZE (the default) | Queries where the ordering or grouping criteria conflicts with the selection criteria. | A copy of the data could be made when the query is opened. |
| ALWBLK | *ALLREAD (the default) | Additional read-only cursors use blocking. | ROLLBACK HOLD might not change the position of a read-only cursor. Dynamic processing of positioned updates or deletes might fail. |
| CLOSQLCSR | *ENDJOB, *ENDSQL, or *ENDACTGRP | Cursor position can be retained across program invocations. | Implicit closing of SQL cursor is not done when the program invocation ends. |
| DLYPRP | *YES | Programs using SQL PREPARE statements could run faster. | Complete validation of the prepared statement is delayed until the statement is run or opened. |
| TGTRLS | *CURRENT (the default) | The precompiler can generate code that takes advantage of performance enhancements available in the current release. | The program object cannot be used on a system from a previous release. |