Application design tips for database performance

There are some design tips that you can apply when designing SQL applications to maximize your database performance.

Using live data
The term live data refers to the type of access that the database manager uses when it retrieves data without making a copy of the data. Using this type of access, the data, which is returned to the program, always reflects the current values of the data in the database. The programmer can control whether the database manager uses a copy of the data or retrieves the data directly. This control is done by specifying the allow copy data (ALWCPYDTA) parameter on the precompiler commands.
Reducing the number of open operations
The SQL data manipulation language statements must do database open operations in order to create an open data path (ODP) to the data. An open data path is the path through which all input/output operations for the table are performed. In a sense, it connects the SQL application to a table. The number of open operations in a program can significantly affect performance.
Retaining cursor positions
You can improve performance by retaining cursor positions.

Parent topic: Database performance and query optimization