AREF UBE Speed Issues

I'm going through the process of trying to speed up our AREF (Advanced Real Estate Forecasting)UBE's. Using the standard DB2/400 toolset I've been able to isolate the workloads review the underlying DB activity. So far, it's looking as if the long waits are not tied up in DB retrieval time. All retrieves are subsecond and if I look at total processing time of all queries, I'm less than 10 seconds. Yet, the UBE's are running for 45-60 minutes.

This makes me wonder how much of the time is simply tied up in compute intensive processing (number crunching).

10 years ago I heard through the grapevine that UBE's, begin C-based and all, were running much quicker on the faster Wintel processor clocks than the slower (clock wise) AS400 chips.

Obviously things are very different today. My Power architecture based 400 is running a faster clock than any of my Wintel boxes. However, wasn't sure if there was still any inherent benefit to running compute intensive UBE's on a Wintel app server (other than load balancing). My 400 is in now way over taxed so I have no other reason than raw UBE speed to consider this.

I would love to hear from any AREF users out there and how things are running for you...what you had to do, etc.

Thanks

Ryan Hunt

Power Systems i520 (AS400), V6R1
OAS for client access

Ryan,

The biggest performance boost, you can give a UBE, other than streamlining the Indexes - Replace the Internal ER with Custom Functions. YES, REALLY.

If you have already done the legwork to identify the performance issues are not Database related - go after the ER. The simplest way, is to rewrite each sections' ER into a NER (that can be coded Very Similar to your existing UBE.ER).

YES - Functions are MUCH Faster than the Interpretive UBE.ER! I had an issue sever years ago where I was Substringing and Concatenating dates in the ER, and the program took hours. I recreated the logic within a NER and the UBE finished in ten minutes (with the same results and no other changes).

The other bennie to replacing a sections ER with NER/C - is portability (you don't have to recode for a new report that will use the same logic) AND, if re-used, Fix Once, Fix Everywhere!

(db)