|
Charlie, Thanks for sharing the info on STRDBMON...I wanted to share our experiences as we endeavored to improve the processing time of some of our long-running jobs...
Prior to starting this investigation we had two "batches" of AFI extract jobs (an AM batch and a PM batch) because we could not run 74 AFI extracts at one time. (Keep in mind that you must run ALL AFI Extracts in the same single-threaded jobQ to avoid co-mingling of plant data within GL Sessions). Running that many AFI jobs simply took far too long. Between 10 and 12 hours for both batches combined, every day...! The peak-time demands on the system simply required the AFI Extracts to be performed during off-peak hours.
We run 37 Inventory and 37 Production AFI Extracts each day from Machine Manager. Inventory was running quickly but Production was not. STRDBMON quickly identified and suggested an Index be created over PCP93 and we did so.
The performance enhancement if any was not sufficient so when I saw the FIPCEXTRCT jobs were building indexes over PCP59 and PCP62 I looked at the FI* CL programs to discover the key that the OPNQRYF command was requesting the index be built for and I manually created indexes to match those key values.
STRDBMON did NOT recognize nor suggest an index be created over PCP59 or PCP62. I wonder why it failed to suggest an index be created...?! We are still at V5R2...
Once I created the indexes over PCP59 and PCP62 several of the FIPCEXTRCT jobs did run significantly more quickly. There were still some jobs that took hours to run.
Over the holiday weekend we were able to purge tens of millions of records out of 3 key files: PCP59, PCP62 and PCP93, and reorg them to remove deleted records. When that failed to reduce the processing time for those few jobs that were still running a long time we decided to look closer still.
In the submission screen of the FIPCEXTRCT job there is a flag for "Post Material Movements". The few plants that were still having a problem had these flags selected. When we took those off, the jobs finished in under 2 minutes. Recent changes in our AFI rules meant that these transactions were no longer needed for our accounting practices.
We now only retain between 90 and 120 days of data in the family of PCP* transaction files i.e. PCP01, PCP02, etc... including FIP45 & FIP51. Reduced transaction file sizes, along with the indexes we built and the removal of the unneeded flag in the FIPCEXTRCT screen has made it possible for us to run 74 AFI extract jobs every day in under two hours. And they are all done before 6:00 AM every day. That is roughly an average of 1.6 minutes per job...!
A significant performance increase without changing a single line of code...!
There are probably many opportunities within S/21 where similarly significant performance enhancements could easily be realized. Has anyone else already found any they'd like to share...? We will be looking for more as we work next on automating our Inventory Monthend Close.
These are the 3 indexes we added for the AFI Extract: PCP59 : Company Code K01 CONO59 Transaction # K02 TRNN59 PCP62 : Company Code K01 CONO62 Transaction # K02 TRNN62 PCP93 : Company Code K01 CONO93 General Ledger Update K02 GLUP93 Mps/mrp Model Number. K03 MODN93 Regards, Jeff Klipa ----Original Message Follows---- From: CMassoglia@xxxxxxxxxxxxx Subject: [SYSTEM21] Improving System/21 Performance Date: Mon, 29 Aug 2005 08:26:00 -0400 If you have not analyzed access path creation in System/21 using STRDBMON, your system probably runs far slower that it should. We substantially reduced our month end processing time without changing a single program. Some on demand and daily reports that used to take minutes to run now take seconds. Using STRDBMON, we were able to identify temporary access paths created by SQL, OPNQRYF, and Query/400 that either took forever to create or were created frequently. All we had to do was create logical paths using the suggested key structure. If anyone is interested, I have a short 9 page PowerPoint presentation that describes the technique in more detail. Thanks. Charlie Massoglia Chief Information Officer cmassoglia@xxxxxxxxxxxxx 517-780-4336 Fax: 517-789-4503
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
