Deleting SQL Packages on iSeries server, what do you do?

Hi All,

We are running 8.12 on a iSeries server. Every Sunday morning we have a program that runs to do the following:
ENDS JDE Services
Clears IPC
DLTSQLPKG (QRECOVERY/O*)
DLTSQLPKG (QRECOVERY/T*)
DLTJRNRCV JRNRCV(OWJRNL/OWJRNR*) DLTOPT(*IGNINQMSG)
START JDE Services

We are having issues where we are having random JAS web client errors (errors like prepared statement in use, unknown jas sign in error, etc) and the only solution is to bounce the web instance. We have tweaked these web servers to death per Oracles reccomendations but this still seems to happen once a week on at least one of our web servers.

Oracle say it must be SQL package related. What strategy does everyone else for deleting packages on their iSeries? Do you just remove whats in QRECOVERY? What about the packages in all pathcodes/data source libraries? We currently dont touch these and am not sure what performance impact there will be if we delete this weekly.

We delete when we deploy full packages. We also delete the UBE packages on demand they package has the same name as the UBE.

From a JAS perspective I change the package library to be QTEMP, that way they are self deleting. (this is done in the JDBj.ini param AS400PackageLibrary or AS400PackageLib not sure as I am doing this from memory)

Simply making the JAS change solved a lot of our issues.

Hope this helps.

Tom

Try doing a search for this 'are weekly +bounces required?' Don’t forget the + sign before bounces. You should find what you’re looking for in that thread. Best I know, 100% of all *SQLPKG on a '400' can be deleted except for three system required *SQLPKG's in QSYS and QGPL. On occasion JDE and/or IBM might ask you to delete a system *SQLPKG like QZDAPKG which is relatively simple to recreate. Check this out is so interested - http://www-912.ibm.com/s_dir/slkbase.NSF/DocNumber/377117296.

Michael
7.1.4 thru 9.x

The fact that you are deleting SQL packages that start with a 'T' tells me you have your SQL Package Library setting in your JDE.INI set improperly, probably to a 2 I think. You should change it to a 1. This will allow the same UBE to use the same SQL package instead of creating a new SQL package every time it's run. For example, if you run R42565 a lot then all instances of that job running will use the same SQL package which will be created as R42565 in QRECOVERY. If you have your setting set to 2 then each time R42565 runs it will create a new SQL package with starting with 'T' appended by the job number. Channging the value to a 1 will also increase performance of your frequently run UBEs since it won't have to create a new SQL package every time the job runs. This will also decrease the number of SQL packages you have in QRECOVERY drastically.

As far as how often you are deleting SQL packages I would say you are doing it way too often. We clear ours maybe every three months, which includes the ones in the data and pathcode libraries. The only time you should be forced to clear them is when you make a change to the layout of a table because the SQL package will reference the old layout.

IBM will probably tell you that you don't ever or at least very rarely have to delete the SQL packages but my past experience tells me that it's a good idea to clear them a few times a year.

Also, I believe that you can have the 400 manage your journals and it will remove them based on how many days old they are, etc. We run Mimix and Mimix manages how long to keep the journals based on certain criteria and it will delete the others that don't meet that criteria.

I also probably should mention we tend to leave our services and JAS instances up for months at a time....

You are right in that our SQL Package Library setting is set to 2. After reading up on this setting (as much as I can find which is a ube tuning pdf on My oracle support) it seems that a 2 is not reccomended for daily use. Its strange that Oracle has not advised me to change this when looking at our .ini files. I will look at changing this when we bounce services on the weekend.

The packages in the COPD812, PRODDTA, PRODCTL, SY812, DD812, OL812, etc libraries have never been cleared in 4+ years. Oracle have basically given me the default answer of deploy a full package and clear sql packages which brought me here.

I think I will create a script to clear all SQL packages besides the Q* but only run this one every month or so as opposed to the weekly one. Would it be best to deploy a new package and then clear all SQL packages, or clear all SQL packages and then deploy a full package?

A lot depends on the version of the OS. The newer versions generally do not have a problem with larger sql package sizes that was presenting the need to periodically delete them. You can also set a QAQQINI SQL_INCREASE_PKG_LIMIT option to increase the max size to 1GB. See http://www-01.ibm.com/support/docview.wss?uid=nas1ff50f31c9d3a35f786256743005688cc for good info on how to manage sql packages.
We only delete sql packages under the following conditions:
1. Database changes - new tables, table changes, new indexes or changed indexes.
2. OS changes - database PTF group installed.
Otherwise we leave the packages alone as they help to optimize database access.
We do delete all non-IBM sql packages including those in the control and business data libraries.

You should be deleting packages when you make table/index changes, Tools upgrades etc. There is a document on the Oracle regarding managing SQL packages which explains this in more depth.

Regards
Kieran