Effects of precompile options on database performance

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 may impact the function of the application. For this reason, the default value for these parameters is the value that will ensure 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 may 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 may be made when the query is opened.
ALWBLK *ALLREAD (the default) Additional read-only cursors use blocking. ROLLBACK HOLD may 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 may 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 will take advantage of performance enhancements available in the current release. The program object cannot be used on a server from a previous release.

Parent topic:
General DB2 UDB for iSeries performance considerations

Related reference

Effects of the ALWCPYDTA parameter on database performance
Control database manager blocking
Retaining cursor positions for non-ILE program calls
Eliminate redundant validation with SQL PREPARE statements

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 may be made when the query is opened.
ALWBLK	*ALLREAD (the default)	Additional read-only cursors use blocking.	ROLLBACK HOLD may 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 may 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 will take advantage of performance enhancements available in the current release.	The program object cannot be used on a server from a previous release.