Show TOC

SQL Package HandlingLocate this document in the navigation structure

Use

This section explains when you need to delete SQL packages and gives some information about naming conventions for package libraries and messages relating to packages.

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 need to reduce the number of PREPAREs if possible. For this IBM i gives you the option of saving prepared statements in SQL packages for reuse at a later time. This option considerably improves SAP system performance.

Features

Naming Conventions for SQL Package Libraries

The SQL packages (IBM i 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 let you relocate prepared statements.

Messages Related to SQL Packages

When statements have not been prepared, the messages SQL0204 and SQL0514 appear in the IBM i job log. These messages inform the SAP system when a statement has to be prepared. You can ignore these messages.

Activities

Deleting SQL Packages

There are certain situations where you have to delete SQL packages such as:

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

  • In some cases, you have to delete the packages manually. You can do this using command DLTR3PKG. For example, when you restore a database you have to delete all the SQL packages manually.

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

Apart from these exceptions, you do not normally need to delete the SQL packages because this affects SAP system performance.

Monitoring SQL Packages from the SAP System

To look at SQL packages from the SAP system, call transaction DBACOCKPIT. In the navigation frame, choose Start of the navigation path Performance Next navigation step SQL Packages End of the navigation path.