Programming techniques for database performance

By changing the coding of your queries, you can improve their performance.

Use the OPTIMIZE clause
If an application is not going to retrieve the entire result table for a cursor, using the OPTIMIZE clause can improve performance. The query optimizer modifies the cost estimates to retrieve the subset of rows using the value specified on the OPTIMIZE clause.
Use FETCH FOR n ROWS
Applications that perform many FETCH statements in succession could be improved by using FETCH FOR n ROWS. With this clause, you can retrieve multiple rows of table data with a single FETCH, putting them into a host structure array or row storage area.
Use INSERT n ROWS
Applications that perform many INSERT statements in succession could be improved by using INSERT n ROWS. With this clause, you can insert one or more rows of data from a host structure array into a target table. This array must be an array of structures where the elements of the structure correspond to columns in the target table.
Control database manager blocking
To improve performance, the SQL runtime attempts to retrieve and insert rows from the database manager a block at a time whenever possible.
Optimize the number of columns that are selected with SELECT statements
For each column in the SELECT statement, the database manager retrieves the data from the underlying table and maps it to a host variable in the application program. By minimizing the number of columns that are specified, processing unit resource usage can be conserved.
Eliminate redundant validation with SQL PREPARE statements
The processing which occurs when an SQL PREPARE statement is run is like the processing which occurs during precompile processing.
Page interactively displayed data with REFRESH(*FORWARD)
In large tables, paging performance is typically degraded because of the REFRESH(*ALWAYS) parameter on the Start SQL (STRSQL) command. STRSQL dynamically retrieves the latest data directly from the table. Paging performance can be improved by specifying REFRESH(*FORWARD).
Improve concurrency by avoiding lock waits
The concurrent access resolution option directs the database manager on how to handle cases of record lock conflicts under certain isolation levels.

Parent topic: Database performance and query optimization