Colin,
1. The AS/400 uses SQLPKGs as a time saving device for repeated SQL statements. The system determines the best method for accessing the data based on the SQL statement, and then saves that information in the SQLPKG. If a later, identical SQL statement comes along, the system simply re-uses the access path/method from the earlier SQL statement. This saves the query optimizer time executing the SQL statement, by not having to determine the best access path all over again. Works nicely for UBEs but not so nicely for SQL statements generated by ad hoc queries (like those generated when you use QBE).
2. In your ODBC connections setup, there is a check box on the Packages tab: Enable extended dynamic (package) support. Uncheck the box to disable SQL packages for a particular ODBC connection (like Business Data - PROD). There may or may not be a performance impact from disabling SQL packages. There are other threads that discuss the impact of disabling SQL packages. In short, your mileage may vary. Also, it appears UBEs that execute on the server will create SQL packages regardless of your ODBC settings, because they're not using ODBC settings. The jury is still out on how to disable this behavior, if it's possible at all.
3. Problems with SQL packages can occur if the SQLPKG becomes full or if there is a change to one or more of the tables. In our experience, we've had issues with reports over custom tables when our developers have made changes to the table structure. There are probably other reasons that SQL packages become unusable.
On our system, we delete SQL packages weekly as well, but you could easily delete just the R* SQLPKGs every night. This would likely slightly impact performance the first time a UBE is executed, but should not after that unless data selection is changed. But deleting them nightly would prevent the problem you discovered.
Hope this is helpful.
