Access plan

The methods used for a specific SQL statement to get access to the data are stored in access plans. If the access plan does not exist, it is created the first time that an SQL statement is executed. If the access plan already exists, it is compared with the information provided by the Statistics Manager (SQE) or by the query optimizer in CQE. If the optimizer decides to use an other access path, the access plan is updated.

In contrary to CQE, the access plans that are created and used with the SQE are organized in a tree-based structure to allow for maximum flexibility.

If you use SQL statements in programs, there are different ways to embed, prepare, and execute your SQL statements. These different methods affect the creation time of the access plan for the specified SQL statements. All executable SQL statements must be prepared before they can be executed. The result of preparation is the executable or operational form of the statement. We can differentiate between the three methods:

򐂰 Static SQL

In static SQL, the SQL statements that must be executed are already known at compile time. The precompiler checks the syntax and converts the SQL statement into an executable form, as well as creates an access plan that is embedded into the program object. If the access plan is changed because of an altered data pool or new indexes, the access plan is updated in the program object. In this way, a program object can grow over the time, even if no modifications are performed.

򐂰 Dynamic SQL

Programs that contain embedded dynamic SQL statements must be precompiled like those that contain static SQL. Unlike static SQL, the dynamic SQL statements are checked, constructed, and prepared at run time. The source form of the statement is a character or graphic string that is passed to the database manager by the program that is using the static SQL PREPARE or EXECUTE IMMEDIATE statement. The operational form of the statement persists for the duration of the connection or until the last SQL program leaves the call stack. Access plans associated with dynamic SQL might not persist after a database connection or job is ended.

򐂰 Extended dynamic SQL

An extended dynamic SQL statement is neither fully static nor fully dynamic. The Process Extended Dynamic SQL (QSQPRCED) API provides users with extended dynamic SQL capability. Like dynamic SQL, statements can be prepared, described, and executed using this API. Unlike dynamic SQL, SQL statements prepared into a package by this API persist until the package or statement is explicitly dropped.

Note: For SQE, the indexes are ordered in general so that the indexes that access the smallest number of entries are examined first. For CQE, the indexes are generally ordered from mostly recently created to oldest.

The iSeries Access ODBC driver and JDBC driver both have extended dynamic SQL options available. They interface with the QSQPRCED API on behalf of the application program.

SQL packages

SQL packages are permanent objects with the object type *SQLPKG used to store

information related to prepared, extended dynamic SQL statements. They can be used by the iSeries Access for Windows® ODBC driver and the IBM Toolbox for Java JDBC driver. They are also used by applications which use the QSQPRCED API interface.

The SQL package contains all the necessary information to execute the prepared statement.

This includes registry of the statement name, the statement text, the internal parse tree for the statement, definitions of all the tables and fields involved in the statement, and the query access plan needed to access the tables at run time.

Creation time of SQL packages

In the case of ODBC and JDBC, the existence of the package is checked when the client application issues the first prepare of an SQL statement. If the package does not exist, it is created at that time, even though it might not yet contain any SQL statements. In the case of QSQPRCED, creation of the package occurs when the application calls QSQPRCED specifying function 1.

Advantages of SQL packages

Because SQL packages are a shared resource, the information built when a statement is prepared is available to all the users of the package. This saves processing time, especially in an environment when many users are using the same or similar statements. Because SQL packages are permanent, this information is also saved across job initiation or termination and across initial program loads (IPLs). In fact, SQL packages can be saved and restored on other systems. By comparison, dynamic SQL requires that each user go through the prepare processing for a particular statement and do this every time the user starts the application.

SQL packages also allow the system to accumulate statistical information about the SQL statements. Accumulating such information results in better decisions about how long to keep cursors open internally and how to best process the data needed for the query. As indicated previously, this information is shared across users and retained for future use. In the case of dynamic SQL, every job and every user must relearn this information.

Deletion of SQL packages

Packages must be deleted when the underlying metadata for statements stored in the package has been changed. If a table, view, procedure, or other SQL object is altered, the information in the package is not updated. If the package is not deleted, a variety of unusual errors can occur, including truncation, data mapping errors, incorrect describe information, and so on.

Delete packages whenever significant changes (those that might cause a large amount of access plan rebuilds) are made to the database, operating system, or hardware. Because extended dynamic SQL packages are recreated when the application is run, there is little harm in deleting them.

Note: When using embedded SQL, no separate SQL package is created, but the access plan is integrated into the program or service program object.

Plan Cache

The Plan Cache is a repository that contains query implementation plans for queries

optimized by the SQE Optimizer. Query access plans generated by CQE are not stored in the Plan Cache. The architecture of DB2 Universal Database for iSeries allows for only one Plan Cache per iSeries server or logical partition (LPAR).

The purpose of the Plan Cache is to facilitate the reuse of a query access plan at some future stage when the same query, or a similar query, is executed. After an access plan is created, it is available for use by all users and all queries, irrespective of the interface from which the query originates.

CQE already uses plan caches, but queries from different interfaces each go to their own Plan Cache. Furthermore, when an access plan is tuned, for example when creating an index, all queries can benefit from this updated access plan. The update plan eliminates the need to reoptimize the query and results in greater efficiency and faster processing time. In the case of CQE, each query has to update its own access plan to benefit from the newly created index.

Before optimizing an incoming query, the optimizer looks for the query in the plan cache. If an equivalent query is found, and the associated query plan is found to be compatible with the current environment, the already-optimized plan is used, avoiding full optimization.

Figure 2-9 shows the concept of reusability of the query access plans stored in the Plan Cache. The Plan Cache is interrogated each time a query is executed using the SQE.

Figure 2-9 Plan Cache

In addition, unlike CQE, SQE can save multiple different plans for the same query. This method is useful in more dynamic environments where the plan changes depending on user inputs, available memory, and so on.

Note: Access plans generated by CQE are not stored in the SQE Plan Cache.

Note: The Plan Cache is cleared during an IPL or varying the independent auxiliary storage pool (IASP).

For more information about SQE and CQE, see the IBM Redbook Preparing for and Tuning the V5R2 SQL Query Engine on DB2 Universal Database for iSeries, SG24-6598, and DB2 Universal Database for iSeries Database Performance and Query Optimization, which is available in the iSeries Information Center on the Web at:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajq/rzajqmst.htm