Precompile Options on iSeries

The SET OPTION clause of the CREATE PROCEDURE statement on iSeries is used to specify precompile options for the SQL procedure. These can also be specified on the RUNSQLSTM command, allowing the options to be propagated to the SQL procedure creation. The precompile options used can have an impact on performance. A few of these options are described here.

The COMMIT option specifies the isolation level to use. Using a higher isolation level than required increases the amount of locking, thus reducing concurrency. For a description of isolation levels, refer to Chapter 5, "Understanding and Using Cursors and Result Sets." The valid values for this option ordered from lowest isolation level to highest isolation level are

• *NONE. Isolation level no commit, implies no ROLLBACK/COMMIT operations possible and any logging/journaling is not required.

• *CHG. Isolation level uncommitted read.

• *CS. Isolation level cursor stability.

• *ALL. Isolation level read stability.

• *RR. Isolation level repeatable read.

Specify the lowest isolation level that is possible for your application.

The ALWBLK option specifies the degree to which blocking will be used when processing cursors in the SQL procedure. Row blocking means the database manager will send data to the client in sets of rows, rather than one row at a time. Enabling blocking will increase performance. It is good practice to explicitly define the type of operation when opening a cursor through the use of the FOR READ ONLY or FOR UPDATE clauses. The valid values for the ALWBLK option are

• *ALLREAD. Blocking for read-only cursors when COMMIT is *NONE or *CHG.

• *NONE. No blocking.

• *READ. Blocking of cursors declared as READ ONLY, or when COMMIT is *NONE.

Where possible, use the ALLREAD option.

The ALWCPYDATA option is used to specify whether a copy of the data can be used when processing a SELECT statement. This option is especially important when you have a query that has multiple choices for index usage. The valid options for this clause are

• *OPTIMIZE. A copy of the data is used only when necessary, and the access plan is optimized to yield the fastest way to process the query.

• *YES. A copy of the data is used only when necessary.

• *NO. Data copying is not allowed. If the query processing requires a copy of the data, an error is returned.

Use the OPTIMIZE option. This option will use a combination of existing indexes and copies of data to process the query in the fastest time possible.

The DBGVIEW options specify the type of debug information to be provided by the compiler:

• *NONE. No debug information will be generated.

• *SOURCE. Allows debugging at the SQL statement level.

• *STMT. Allows debugging at the program statement number level.

• *LIST. Generates a listing view for debugging.

Using the SOURCE option makes debugging a lot easier, because you can debug at the actual SQL statement level as opposed to the generated C program statement level.

The DLYPRP option specifies whether to delay the dynamic statement validation in a PREPARE statement until an OPEN, EXECUTE, or DESCRIBE statement is encountered. Validation will always occur with OPEN, EXECUTE, or DESCRIBE, so the validation on PREPARE is redundant. The values are

• *NO. Do not delay validation.

• *YES. Delay validation.

Use the YES option to avoid redundancy and duplicate processing.