DB2 Version 9.7 for Linux, UNIX, and Windows

Application design

Database application design is one of the factors that affect application performance. Review this section for details about application design considerations that can help you to maximize the performance of database applications.

Application processes, concurrency, and recovery
All SQL programs execute as part of an application process or agent. An application process involves the execution of one or more programs, and is the unit to which the database manager allocates resources and locks. Different application processes might involve the execution of different programs, or different executions of the same program.
Concurrency issues
Because many users access and change data in a relational database, the database manager must allow users to make these changes while ensuring that data integrity is preserved.
Writing and tuning queries for optimal performance
There are several ways in which you can minimize the impact of SQL statements on DB2® database performance.
Improving insert performance
Before data is inserted into a table, an insert search algorithm examines the free space control records (FSCRs) to find a page with enough space for the new data.
Efficient SELECT statements
Because SQL is a flexible high-level language, you can write several different SELECT statements to retrieve the same data. However, performance might vary for different forms of the statement, as well as for different optimization classes.
Guidelines for restricting SELECT statements
The optimizer assumes that an application must retrieve all of the rows that are identified by a SELECT statement. This assumption is most appropriate in online transaction processing (OLTP) and batch environments.
Specifying row blocking to reduce overhead
Row blocking, which is supported for all statements and data types (including LOB data types), reduces database manager overhead for cursors by retrieving a block of rows in a single operation.
Data sampling in queries
It is often impractical and sometimes unnecessary to access all of the data that is relevant to a query. In some cases, finding overall trends or patterns in a subset of the data will suffice. One way to do this is to run a query against a random sample from the database.
Parallel processing for applications
The DB2 product supports parallel environments, specifically on symmetric multiprocessor (SMP) machines.

Parent topic: Factors affecting performance