Admin Alert: One Common Cure for SQL0901 Package Errors

The SQL0901 error is an incredibly frustrating error that occurs with i5 ODBC, OLE DB, and JDBC connections. Because SQL0901 is a general SQL error, it can be difficult to diagnose and harder to solve. This week I will look at one specific SQL0901 scenario, where the failure occurs inside SQL’s extended dynamic package support, and I will show you how to deal with this error in an i5 environment.

What is Extended Dynamic Package Support?

While the SQL0901 error can refer to a broad range of issues, this article focuses on diagnosing and correcting problems associated with SQL extended dynamic package support (package support).

When enabled, package support tells i5/OS to save the data access plans that the i5 query optimizer creates for your prepared SQL statements. The optimized access plans, which are sometimes called implementation plans or access paths, are saved in an i5/OS object called an SQL package (object type *SQLPKG). They are saved every time that a client submits any new SQL statement to your i5 box through an ODBC, JDBC, or OLE DB driver.

When a client submits a previously executed SQL statement using package support, the i5 box will use the optimized access plan information in the client’s SQL package (along with the database host server’s extended dynamic package, an i5/OS object called QZDAPKG) to process that statement faster. The performance improvement occurs because the stored access path does not have to be regenerated, which is particularly valuable if many users are executing the same or similar statements. It is also valuable because SQL packages are permanent objects, which can be accessed by users over many different i5 sessions.

According to IBM, package support is available for many, but not all, SQL statements submitted to an i5 partition. IBM says that it supports iSeries Access ODBC cached packages for positioned UPDATE and DELETE statements, INSERT statements that use subselect parameters, and the DECLARE PROCEDURE statement (though there is a procedure for overriding these restrictions). For SQL statements from other sources, there is a larger list of allowed statements. Information on which SQL statements can be used in which connectivity environments can be found at the IBM SQL Questions and Answers Web page. Additional information about creating, saving, and working with SQL commands in packages can be found on the SQLThing Web site.

How to Tell When Good Packages Go Bad

The package support scenario generally works well but it can break down in two specific situations.

1. After an i5/OS upgrade, you may find that you start receiving SQL0901 errors for your remote connections. Depending on which i5/OS or OS/400 version you are upgrading to, the old QZDAPKG package may be incompatible with the new i5/OS software.
2. If the QZDAPKG *SQLPKG object on your i5 box becomes corrupted or exceeds its size limits, packaged support may also fail with SQL0901 errors.

There is a (relatively) easy fix for these problems, but the trick lies in determining whether your system’s QZDAPKG object is causing your problem. To determine whether the QZDAPKG object is your problem, find the QZDASOINIT job associated with the user who is trying to execute SQL commands on your partition. QZDASOINIT jobs are used by i5/OS for connection pooling to the i5 host database server. Finding the QZDASOINIT job that is associated with a failing connection can be somewhat tricky, because the system can generate many QZDASOINIT jobs and these jobs are all assigned to the QUSER user, regardless of which user profile your connections are actually using. On larger systems, the number of QZDASOINIT jobs can literally number in the hundreds, making it impossible and impractical to search through each job’s joblog to find the one particular job your user is accessing (user connection information is usually included at the beginning of the QZDASOINIT joblog).

To better find your target QZDASOINIT job, you can download a freeware program by Brian Dietz called WRKODBCJOB, which is available at Brian Dietz’ AS/400-iSeries-i5 Freeware Page). WRKODBCJOB lists all the QZDASOINIT jobs currently running on your system, along with the IP address of the connecting machine, and the actual i5/OS user ID each job is using for its connection. If you download and run WRKODBCJOB, you can pick out your target QZDASOINIT job by identifying either the connecting user or his IP address. This allows you to more easily select the target job that you want to examine.

Note: Don’t be misled by the WRKOBDCJOB command name that Brian gave to his freeware. This software will detect and show the user IDs and IP addresses for any QZDASOINIT jobs on the system, regardless of whether they were started through an ODBC, OLE DB, JDBC, .NET, or any other connection. While the program is named WRKODBCJOB, it discovers and displays information for any QZDASOINIT job.

Once you find the QZDASOINIT job you want to examine, look in its job log. If it contains a CPD4392 error (Attributes of value number &1 in field &2 not valid) while the client is receiving a SQL0901 error, this is a good indicator that your program is caused by one of the package support issues mentioned above.

Solving Your Package Support Problems

Once you have determined that the problem is probably caused by a corrupt or over-sized QZDAPKG object on your i5, download and read IBM’s Software Technical Document 377117296, which explains how to delete the Host Server’s SQL Package. This document provides step-by-step instructions for shutting down all the i5/OS prestart jobs and host servers that use QZDAPKG, deleting the QZDAPKG object itself, and then restarting all the relevant jobs that serve up SQL processing. Don’t worry about rebuilding QZDAPKG; i5/OS will recreate the object when it restarts the host database server. IBM documentation on how to do this is pretty good, but be careful to watch out for the following items:

• Document 377117296 is written to an i5/OS V5Rx standard and there are some differences in earlier OS/400 versions. In OS/400 V4RX, for example, the QZDAPKG object is stored in the QIWS library, not in QGPL as it is in V5RX. During this writing, I was also unable to find a corresponding 377117296 document for deleting QZDAPKG in a pre-V5R1 environment. So if necessary, you may need to adapt the V5R1 instructions to your OS/400 V4RX environment (if any reader knows of a readily available document for V4Rx, please email me and I’ll include its location in a future column).
• Be careful not to delete any other IBM Q* packages as you’re deleting QZDAPKG–As i5/OS administrators, it’s been beaten into our heads with big sticks that you must never ever delete any object whose name begins with a ‘Q’, because those are system objects. As a result, deleting the QZDAPKG object goes against our nature and this should cause us to be doubly alert. Why? Besides QZDAPKG there are two other i5/OS SQL packages that begin with the letter ‘Q’ (QSQLPKG2 and QSQXDPKG) and these packages really should never ever be deleted (and IBM means it here). QSQLPKG2 and QSQXDPKG are regular system objects and if deleted, they must be restored from a backup tape or FTPed over from another partition. Either way, it’s a world of trouble if you delete these objects by mistake.
• Be careful when you delete and recreate QZDAPKG. In order to delete QZDAPKG, the object cannot be locked by any other job. This means that any user or program that is using SQL to communicate with your i5 will be shut out while the deletion is occurring.
• Deletion is a short process but you may have to shut down several applications, including possible Web-server connections, to make it happen. As such, you may have to perform this procedure during off-hours.

In addition to deleting and recreating your QZDAPKG object, the most significant thing you can do to keep SQL connections humming away is to keep your software up to date. First, try not to fall too far behind on i5/OS PTFs, particularly Database PTFs. This will keep you current with the latest server-side fixes for your connections. It’s also important that for users who use the iSeries Access for Windows ODBC, JDBC, or OLE DB drivers, you keep their software up to date as well. I’ve run into several instances where an iSeries Access upgrade solved desktop problems using SQL statements inside a Microsoft application. Finally, you may also need to keep your third-party software up to date with vendor fixes and service packs, particularly after you perform an i5 upgrade.

RELATED RESOURCES

Brian Dietz’ AS/400-iSeries-i5 Freeware Page, Brian Dietz

Software Technical Document 377117296, Deleting the Host Server’s SQL Package, IBM

SQL Packages Questions and Answers, IBM

What Are Packages and Basic Package Problems, SQLThing