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
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
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
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:
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
Use the YES option to avoid redundancy and duplicate processing.