If you use SQL to access your database objects, you may be aware of the SQL Query Engine (SQE) introduced in V5R2. SQE offers algorithms and features that give it distinct performance advantages over its predecessor, the Classic Query Engine (CQE).

One of SQE's most powerful features is the SQE plan cache, an internal matrix-like repository that holds the access plans for queries optimized by SQE. The primary purpose of the plan cache is to facilitate reuse: Whenever SQE encounters a query that is the same as or similar to one stored in the plan cache, it reuses the plans associated with that query, regardless of the user or interface. That way, the optimizer does not have to create new access plans, thereby saving time and resources. In addition, the plan cache contains valuable feedback information about the execution of the queries, including elapsed run time, access methods used, Open Data Path (ODP) creation, and index advisories.

In V5R4, IBM provided an interface to the plan cache through iSeries Navigator, giving administrators direct access to a vast and useful source of database information. They can view the statements in the plan cache, perform analysis using the provided reports, compare statements, and even launch Visual Explain to view a graphical representation of any statement resident in the plan cache. In many cases, problem queries can be captured and analyzed in the plan cache. This eliminates the need to collect performance data using the much more resource-intensive database monitors.

Due to the complexities involved with maintaining the plan cache data through an IPL process, IBM has chosen to implement it as an internal object that is deleted during the IPL shutdown phase and then recreated at startup. While this may seem reasonable, it can be a source of frustration if you want to keep the data in the plan cache for future analysis and comparison.

In addition, the active plan cache is fixed in size. In V5R2 and V5R3, the maximum size was 256 MB, a limitation that was increased to 512 MB in V5R4. The number of plans held in the plan cache obviously depends on the complexity of the SQL statements, but you should expect the capacity to be in the range of 10,000 to 20,000 unique access plans for a V5R4 system. To maintain this capacity, the database constantly monitors the plan cache size and removes plans based on an aging algorithm. Once a plan is determined to be "old" and kicked out of the plan cache, that information is lost and cannot be recovered.

All must not be lost, however. In V5R4, IBM also introduced plan cache snapshots, a feature designed to provide a mechanism to periodically capture statement information. Snapshots represent the data in the plan cache captured at some point in time and then store this information in a single database table. This provides the following:

• A means for plan cache data to persist across IPLs
• A way to capture plans before they are aged and cast out of the plan cache

Perhaps the best thing about snapshots is this: All the features available in the iSeries Navigator interface for the active plan cache are also supported for plan cache snapshots. And because the plan cache snapshot is a table, you can run your own custom queries against the data, something unavailable for the active plan cache data. In fact, the snapshot table format is identical to a detailed database monitor collection (a nice feature if you consider that custom-built queries and reports that analyze database monitor data can also be used to analyze plan cache snapshot data).

Capturing a plan cache snapshot is not an automated process. It must be done either manually through iSeries Navigator interface or programmatically by calling the system-supplied stored procedure QSYS2/DUMP_PLAN_CACHE. In either case, remembering to perform a plan cache dump prior to every IPL can be problematic: If this step in the shutdown process is forgotten or bypassed, all plan cache data is lost. A method of automation can be quite useful, and this is where exit points come in.

By definition, an exit point is a specific point in a system function or program where control can be passed to one or more specified exit programs. In other words, when a specific event occurs on the system, a user-written program can be called to perform a specific task. One of the supported exit points on the System i is QIBM_QWC_PWRDWNSYS, an exit point that is invoked whenever the PWRDWNSYS or ENDSYS command is issued. The program registered under this exit point is called before the system actually powers down.

By now, you probably see where this is going: Combine this exit point with the ability to programmatically dump the plan cache and voila! Plan cache snapshot automation is in place! No longer do you need to worry about doing it manually. Set it up once, and let the system handle the rest.

Here are the steps involved in setting up this automated behavior:

1. Write the exit point program.
2. Compile the exit point program.
3. Register the program to the exit point.
4. Import the snapshot (after an IPL).

Write the Exit Point Program

The exit point program can be written in any System i–supported language. Here's a source code example written in free-format RPG:

Notice that the program uses embedded SQL to call the system-supplied stored procedure DUMP_PLAN_CACHE in library QSYS2. This is important to note because a stored procedure can be called only from an SQL interface.

This stored procedure accepts two input parameters:

• Snapshot table library—The 10-character system object name of the library to hold the specified snapshot table
• Snapshot table—The 10-character system object name of the table to dump the plan cache data into

Note: This procedure itself does not clear the plan cache. It only dumps the contents of the plan cache into the specified snapshot table. As mentioned, the plan cache is deleted and recreated during IPL.

In this example, the current date is used to assign a dynamic name to the snapshot table, which helps to easily determine when the snapshot was taken. However, if this example is used and more than one IPL is performed per day, subsequent calls to the DUMP_PLAN_CAHCE stored procedure will fail with the SQL error SQL0601 (file already exists). Therefore, you should consider a naming scheme with more granularity, such as appending a counter to the end of the name or using a sequence object to generate a unique name.

Compile the Program

Now, issue the command to create the program:

Once the program object has been created, it is recommended that you perform a simple test to verify that the program works and that a plan cache dump can be generated. To do this, simply call the program from a command line:

To make sure that the snapshot contains data, use your favorite database viewing tool to examine the contents of the table.

Register the Program to the Exit Point

The next step is to register the exit program to the exit point. To use this registration facility, issue the following command from a command line:

This will cause the system to invoke the program IPLEXIT in library QGPL whenever a user issues the PWRDWNSYS or ENDSYS command.

The exit point is now configured and ready to dump the plan cache into the specified snapshot table.

Import the Snapshot

One last step: You must import the plan cache snapshot before it appears in iSeries Navigator. To do this, open a connection and select Databases -> SQL Plan Cache Snapshots. From the right-mouse-click menu, select Import, as shown in Figure 1.

Figure 1: Import the snapshot.

The Import SQL Performance Data dialogue window is displayed. Enter a brief description, schema, and table name of the snapshot table, as shown in Figure 2.

Figure 2: Specify the snapshot table to import.

Click OK to complete the import process. The snapshot now appears in the SQE Plan Cache Snapshots list and is ready to be viewed, compared, and analyzed.

Feel the Power

The SQE plan cache and exit points are two very powerful features available on the System i. Use them together to capture valuable database performance information in an automated, worry-free manner.

Gene Cobb is a DB2 Technology Specialist on IBM's ISV Enablement team for System i. He has worked on IBM midrange systems since 1988, with 10 years in the IBM Client Technology Center (CTC), IBM Rochester. While in the CTC, he assisted customers with application design and development using RPG, DB2 for i5/OS, CallPath/400, and Lotus Domino. His current responsibilities include providing consulting services to System i developers, with special emphasis in application and database modernization.