PREPARE and EXECUTE: The Two Phases of Any SQL Statement

An SQL statement can be viewed as an operation requested from the application to the database engine, be it to retrieve data, perform an insert, or update existing data. Whenever you submit an SQL statement to DB2, it passes through two general phases called PREPARE and EXECUTE. For the purpose of this discussion, PREPARE and EXECUTE will be discussed in the context of SELECT (that is, retrieving data), but the concepts can be applied to INSERT and UPDATE SQL statements just as easily.

In the PREPARE phase, the DB2 query optimizer examines the SQL statement and determines the most efficient method to retrieve the requested data. Many decisions need to be considered by the optimizer, such as

Is the query syntax valid and is the query semantically correct?
How can the query be rewritten so that it can be more easily optimized?
What is the best index or combination of indexes to use?
For queries that join tables, in what order should they be joined to minimize disk I/O or optimize memory usage?

The method by which DB2 chooses to retrieve the data is called an access plan. Once the optimal access plan has been determined, the query enters the EXECUTE phase. In the EXECUTE phase, the database uses the access plan to fetch the data.