Finding and Deleting Extended Dynamic SQL Packages Used By Access Client Solutions ODBC

The Access Client Solutions ODBC driver uses extended dynamic support by default. In order to use this support, it must create and use SQL packages on the IBM i that contain statement information. Occasionally, these objects must be deleted for reasons ranging from causing errors, corruption, to normal cleanup. The default for SQL packages created by the ODBC driver is that they are created in the QGPL library, and the first six or seven letters of package are the application name. For example, a package created by the ODBC driver when Microsoft Access was used is QGPL/MSACCEAFBA. The last three or four letters are generated by the ODBC driver, depending on the settings in the data source. Therefore, running a command like WRKOBJ QGPL/*ALL *SQLPKG and deleting the items in that list that do not have any text in the Attribute column goes a long way toward cleaning them all up. Do not delete any of the packages that have the Attribute 'PACKAGE'. Those packages are DRDA SQL packages, not extended dynamic SQL Packages and they must be re-created by other means or restored when you delete them. In most cases, removing the extended dynamic packages in QGPL is all that is needed because it is rare that a data source is set to use a different library. There is a package in QGPL named QZDAPKG that is not used directly by ODBC. Refer to IBM technote, Deleting the Database Host Server's SQL Package: for instructions on how to delete that package, if there is a need to delete it.

In a case where you need to be sure that you are deleting the right package or looking for the right one, you can find the package information in the ODBC data source by doing the following:

1.	Open the ODBC Administrator. There is a shortcut to it in the System i Access for Windows program group and in the Windows control panel.
2.	Locate the data source that your application is using on one of the first three tabs: User DSN, System DSN, or File DSN (see Figure 1):
3.	Double-click the data source to open it in the data source editor, and click the Packages tab (see Figure 2).
4.	Click the button labeled, "Customize settings per application" to open the Package information for application window shown in Figure 3. The Application name: drop down list can be used to select the applications that used the data source. When an application is selected, the information in the window is updated. Note the last three characters of the package name (last four characters starting with V6R1) are determined by settings in the data source itself, and no mapping of those values to their associated characters is provided; therefore, you might want to delete all packages that start with the characters displayed in this window. The packages are created by the ODBC driver before the first use of it. The ODBC driver handles the creation of new packages. There is no danger in deleting the extended dynamic SQL packages that it creates.