Admin Alert: One Common Cure for SQL0901 Package Errors
September 13, 2006 Joe Hertvik
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.
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:
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.
Brian Dietz’ AS/400-iSeries-i5 Freeware Page, Brian Dietz