Friday, November 7, 2008

Peoplesoft and the iSeries (AS/400)

I don't know how many of my readers (ha ha) use Peoplesoft (EnterpriseOne 8.10) and/or the iSeries (AS/400 - V5R3), but I just found something out that just made my year...

For the past year or so, I've notice that our free disk space on our iSeries has been decreasing at an alarming rate. A little over a year ago, we were close to 90% of capacity on our iSeries. Since we were still over a year away from replacing the current box (we have a 3 year lease), we decided to add disk to the machine. That took us down to about 50% capacity, and also maxing out the total disks we could put in. Over the next year, I noticed that, for some unknown reason, our disk space still kept disappearing. Since we've added disk, we've gone from about 55% to 85% disk utilization. I just thought, since business has been fairly decent, we've just added a lot more records to our Peoplesoft EnterpriseOne (8.10) files. I found out today that I was wrong about that assumption.

Peoplesoft uses a lot of SQL packages. From what I understand, standard installation procedure is to use the library QRECOVERY to store some of the *SQLPGK's. Well, if you don't delete these *SQLPKG's on a regular basis, it begins to consume your available free disk space. Due to some Peoplesoft upgrades we are doing, I needed to delete some Peoplesoft created *SQLPKG's. The consultant I was working with noticed all the *SQLPKG's in the QRECOVERY library, and told me that I could delete those as well. I was told, or so I thought, that you can delete any *SQLPKG files, as long as they didn't start with a Q or were in a library that started with a Q (usually IBM specific libraries.) Well, that's not the case, evidently. Peoplesoft, by default, uses the QRECOVERY library to store some of the *SQLPKG files. These files begin with OW, R, and T as the file name.

Late this afternoon or early evening, I began deleting these *SQLPKG's. I did a wrksyssts to see where we were at, as far as disk space. We were at around 85.6% full. I deleted all the *SQLPKG's that began with OW. That deleted about 12,000 files. It took me down to about 75%. I then deleted all the *SQLPKG's that began with an R. There were only about 15 or so files, so it didn't decrease my disk space noticeably. I then began to delete all the *SQLFILE's that began with a T. I knew there were a ton of files that began with a T. When the delete was done, it had deleted about 62,000 files. It took my disk utilization from 75% down to 38%!!! (I should note here that I did end the Peoplesoft services on the iSeries, prior to deleting the *SQLPKG's)

The unfortunate thing for me is that I've already got my new iSeries system, waiting for IBM to install it. Since I ordered it with almost double the disk capacity that I currently had, it's too late to reduce the amount of disk I really needed. I know that disk is cheap, but it could have saved my company a little more money, had I not ordered as much disk. Disk is cheap, I know...

Talking with the consultant, I found out that this kind of stuff is either not documented, or if it is, it's very well hidden by JDE/Peoplesoft/Oracle. Knowing what I know now, I will be vigilant in deleting these *SQLPKG's on a monthly basis, so I don't panic about free disk space disappearing without explanation.

If just one person, who uses an iSeries and Peoplesoft, learns something new from reading this blog, it's worth it. I'm sure I'm not the only person who didn't know this and wondered where all that disk space went to.


Stewart Schatz said...


Welcome to the EnterpriseOne Blogging community.

Great tip on the SQLPKGS. They definitely hide in the least obvious places.

Deleting them once a month is a really good idea. you may also consider cleaning them up when there are any significant changes to the file structures: indexes, new columns, etc.

Anyway, great tip and keep them coming.

Stewart Schatz

TA Colgate said...

I just wanted to let you know that I've been using this tip since 2015 so it has helped at least one person ;-) And hopefully others. It is definitely a very valuable thing to know!!