Before an SQL statement can be executed on a database, a PREPARE operation has to run. The PREPARE operation analyzes the syntax of the SQL statement and does some of the request optimization. Since PREPARE is generally a comparably expensive operation, you should try to keep the number of PREPAREs as low as possible. To assist you in this, DB2 UDB for iSeries gives you the option of saving prepared statements in SQL packages for reuse at a later time. This option, which is made use of by the SAP System, provides considerable performance benefits.

The SQL packages (OS/400 objects of type *SQLPKG) are held in special package libraries with names that start with ‘R3’ followed by the SAP system ID. By looking at the library name, you can see the area of the SAP System in which the SQL statements are used. For example, there are libraries for statements from reports, screens or the SAP kernel. These naming conventions make it possible for you to relocate prepared statements.

When statements have not been prepared, the messages SQL0204 and SQL0514 appear in the iSeries job log. The reason for these messages is to inform the SAP System when a statement has to be prepared. These messages can be ignored.

There are certain situations where you have to delete SQL packages.

In some instances, this is done automatically. For example, when you upgrade to a higher release, all the packages are deleted and when you start the main instance, any packages that are no longer required are deleted.

You sometimes have to delete the packages when you switch to a more recent version of the SAP kernel. For more information, see the relevant Note describing how to apply the kernel.

Apart from these exceptions, you should not normally delete the SQL packages as doing so will temporarily affect the SAP System performance.

To look at SQL packages from within the SAP System, call transaction DB4COCKPIT. In the navigation frame choose Performance ® SQL Packages.